---
title: "Getting back into MySQL when root won't let you"
date: "2017-01-20"
path: "/182-getting-back-into-mysql-when-root-wont-let-you.html"
section: "/ramblings"
description: "Why MySQL 5.7 on Ubuntu 16.04 refuses the root password, and how to switch root back to password authentication or reset it."
---

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:

```text
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:

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

```text
+------+-----------+-------------+
| 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:

```sql
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:

```sql
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:

```bash
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:

```bash
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:

```bash
mysql -u root
```

```sql
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:

```sql
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:

```bash
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.