Removing the MySQL root password

Removing the MySQL root password

Image for post

There you go again. You just spinned up a virtual machine to do some testing, installed MySQL using your favourite package manager, started the server, and failed to connect:

$ mysql –user=rootERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Since version 5.7, MySQL is secure-by-default:

  • a random root password is generated upon installation; you need to read this password from the server log
  • you have to change this password the first time you connect
  • you cannot use a blank password because of the validate_password plugin

This is all good security-wise. But if you?re just installing MySQL on a local VM for your own testing, this can become really annoying.

To remove the MySQL root password, just run the following script right after installing and starting the MySQL server:

On MySQL 5.7:

On MySQL 8.0:

Note: you must execute this script as root.

The script performs the following actions:

  • reads the temporary password from the log file
  • changes this password to another temporary password that passes the validate_password checks
  • uninstalls the validate_password plugin (or component in MySQL 8)
  • sets a blank password

You can now connect without a password:

$ mysql –user=rootWelcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 7~Server version: 5.7.19 MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.mysql>

Be careful that this leaves your MySQL installation unsecured, you should not use this for anything serious!

A secure alternative

If you?re mainly using MySQL from the command line, you can keep the root account protected by a password, while still avoiding the inconvenience of having to provide the password on the command line.

Just create a ~/.my.cnf file:

[client]user = rootpassword = xxx

You can now just type mysql, and the MySQL client will automatically log in with these credentials.


No Responses

Write a response