Getting back into MySQL when root won't let you

20 January 2017

A fresh Ubuntu 16.04 server with the packaged MySQL 5.7 (5.7.16 at the time). Logged in as root on the box, mysql -u root worked without a password, but phpMyAdmin, Virtualmin and any script using a password got:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Setting a password made no difference. It looked like the password was being ignored, and it was.

Why it happens

On Ubuntu the 5.7 package creates root@localhost with the auth_socket plugin. MySQL checks the Unix user on the other end of the socket instead of a password. If you are the Unix root user, you are let in; anyone else, including PHP running as www-data, is refused whatever password they send.

Check which plugin root is using:

mysql -u root -e "SELECT User, Host, plugin FROM mysql.user WHERE User='root';"

+------+-----------+-------------+
| User | Host      | plugin      |
+------+-----------+-------------+
| root | localhost | auth_socket |
+------+-----------+-------------+

The fix when you can still get in as the Unix root user

If sudo mysql gets you a prompt, you do not need to restart anything. Switch root to native password authentication and set the password in one statement:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-password';
FLUSH PRIVILEGES;

On older 5.x servers you will see this done as:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your-password');

On 5.7 SET PASSWORD ... = PASSWORD() still works but is deprecated; use ALTER USER. Then test with a password, as a normal user:

mysql -u root -p

The fix when you cannot get in at all

If the password is lost and socket authentication is not an option, start the server without the grant tables. Two things catch people out here. Running mysqld --skip-grant-tables directly as root fails, and if the service is stopped the socket directory may not exist, so the client reports error 2002 (Can't connect to local MySQL server through socket). Do it like this:

systemctl stop mysql
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
mysqld_safe --skip-grant-tables --skip-networking &

--skip-networking matters: while the grant tables are off, anyone who can reach the server is root. With networking off, only the local socket works.

Now connect with no password. ALTER USER refuses to run while grant tables are skipped, so load them first with FLUSH PRIVILEGES, then change the user:

mysql -u root

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-password';

If ALTER USER still complains, update the row directly. On 5.7 the hash lives in authentication_string; the old password column is gone:

UPDATE mysql.user
   SET plugin = 'mysql_native_password',
       authentication_string = PASSWORD('your-password')
 WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

Stop the temporary server and start the service normally:

mysqladmin -u root -p shutdown
systemctl start mysql
mysql -u root -p

Things to watch for

Updating authentication_string without changing plugin does nothing while the plugin is still auth_socket. That was the trap here: the password was set, it just was not being checked.
dpkg-reconfigure mysql-server-5.7 did not help here; it only reran the upgrade check and reported the install was already up to date.
Keep the password off the command line. mysql -u root -p with no value prompts for it, and it does not end up in shell history or the process list.

Today

On MySQL 8.0 the PASSWORD() function is removed, so only ALTER USER works. mysql_native_password is deprecated in 8.0, disabled by default in 8.4 and removed in 9.0; use caching_sha2_password. The better habit is to leave root on socket authentication and create a separate admin user with a password for tools that need one.