MySQL 5.7 root login without sudo

Goal

To be able to login to your MySQL 5.7+ server as root without sudo

Description

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)!

How to

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.

  1. Connect to MySQL with root:
    $ > sudo mysql -uroot
    
  2. 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;
    
  3. Delete the user root for host localhost
    mysql > DROP USER 'root'@'localhost';
    
  4. Recreate root user for host localhost:
    mysql > CREATE USER 'root'@'localhost' IDENTIFIED BY '';
    
  5. 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;
    
  6. Exit mysql (either by inserting CTRL+D or quit in the MySQL command prompt) and try to reconnect with the following command:
    $ > mysql -uroot
    

Explanations

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

2 comments

  1. Hi there, nice tips as always! 😉

    But you can achieve this with fewer steps, and without deleting accounts. MySQL now requires you to login via sudo or with the root account because of the new “auth_socket” plugin, but you can change this by specifying the new user password and authentication plugin, in this case for the root user to have an empty password you would use the following:
    mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ”;
    mysql> flush privileges;

    Keep up the nice work! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s