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.
