Sep 7, 2017
Removing the MySQL root password
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=root
ERROR 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_passwordchecks - uninstalls the
validate_passwordplugin (or component in MySQL 8) - sets a blank password
You can now connect without a password:
$ mysql --user=root
Welcome 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 its
affiliates. Other names may be trademarks of their respective
owners.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 = root
password = xxxYou can now just type mysql, and the MySQL client will automatically log in with these credentials.
