To be able to login to your MySQL 5.7+ server as root without sudo
This recipe is for people using MySQL locally for development that wants to use root user to access the database without a password and without the need to issue a sudo command to be able to access it.
Notice, however, that this recipe should not be used for production environments and, instead, you should use specific users with their own schema permissions for the applications you develop (e.g., if you are implementing an application named application1, maybe you should have a database schema named application1 and a user named application1 to access that schema)!
As an important note before going through the steps of the recipe, I have to point out this post, from which this solution was originally inspired.
- Connect to MySQL with root:
$ > sudo mysql -uroot
- Check table user in schema mysql. In the resulting command, you should have a root/localhost result that you will have to change:
mysql > SELECT User, Host FROM mysql.user;
- Delete the user root for host localhost
mysql > DROP USER 'root'@'localhost';
- Recreate root user for host localhost:
mysql > CREATE USER 'root'@'localhost' IDENTIFIED BY '';
- Grant the necessary privileges to user root for localhost, including the flush privileges command, as follows:
mysql > GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; mysql > FLUSH PRIVILEGES;
- Exit mysql (either by inserting CTRL+D or quit in the MySQL command prompt) and try to reconnect with the following command:
$ > mysql -uroot
MySQL 5.7 changed the security model: now, MySQL root login requires sudo (while the password can still be blank). The best solution now is to access MySQL through a new user with its own privileges, thus avoiding using root user for normal access.
However, and if you are a developer like myself, you probably don’t mind to use root to access your local database. If that is the case, then the steps provided within this recipe should do the trick.
Alternatively, as suggested by my colleague Filipe Ildefonso, above, in a comment for this recipe, you may (I would even say that his suggestion is a better alternative), simply change the authentication “algorithm” for root user, as such:
mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ''; mysql > FLUSH PRIVILEGES;
mysql> flush privileges