Fixing Raspberry Pi MySQL Access Denied for user root with non root user

This blog post talks about how you can log in as root to your MySQL database with a non-root user on your Raspberry Pi.

The “Access denied for user ‘root’@’localhost'” error

After setting up your database you might have changed the root password using mysql_secure_installation however it will still give an error when trying to log in from a non-root user. Here I am trying to log in as the Raspberry Pi pi user.

pi@bunker-master3:~ $ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

A common error relating to this will be as above, where MySQL just denies access to the user. This will however work if you try logging when using the root account user.

It is recommended not to use the root user normally so you can change MySQL so you can log in as root, as a non root user.

Fixing “Access denied for user ‘root’@’localhost” error

Typically this problem occurs just after installing your webserver. A number of Ubuntu based distributions now install the root user with no password but set the authentication method to auth_socket.

This means that the root user can log in using the socket but no other users will be able to.

To change this we can modify the root user to have a new password and ensure it is authenticated using the mysql_native_password method. This can be done with the following MySQL command.

UPDATE mysql.user SET
   authentication_string=password('your_password_here'),
   plugin='mysql_native_password'
   WHERE user='root';
FLUSH PRIVILEGES;

You will want to ensure you use a different password in the above SQL.

Once ran your root MySQL user will be able to be logged in by any non-root user with the given password.