DB-유

Resetting a Forgotten MySQL Root Password on MySQL 5.7

· mysql, security, recovery

Every sysadmin ends up here eventually: the server was built by someone who left, the password isn't in the password manager, and I need MySQL root. The old 5.5 recipe (--skip-grant-tables, then UPDATE mysql.user SET password=PASSWORD(...)) fails on 5.7 because the password column is gone. Below are two methods that work on 5.7 with systemd. You need OS root on the host and a window where MySQL can restart twice; examples use CentOS 7 and Ubuntu 16.04.

Try the easy things first

Before restarting anything, see if a client config already holds credentials:

sudo cat /root/.my.cnf
sudo cat /etc/mysql/debian.cnf     # Ubuntu/Debian only

On Debian-based packages debian.cnf contains the debian-sys-maint account, which has full privileges. If it works, you can run ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password'; with it and stop reading.

Method A: an init file

I stick to this one because the server never runs without authentication. It executes the statements in a file at startup, before accepting connections.

Create the file:

sudo tee /var/lib/mysql/reset-root.sql >/dev/null <<'EOF'
ALTER USER 'root'@'localhost' IDENTIFIED BY 'N3w-R00t-Passw0rd!';
EOF
sudo chown mysql:mysql /var/lib/mysql/reset-root.sql
sudo chmod 600 /var/lib/mysql/reset-root.sql

Stop the server and start it once with --init-file. With the systemd units shipped by Oracle's packages you can pass extra options through the MYSQLD_OPTS environment variable:

sudo systemctl stop mysqld
sudo systemctl set-environment MYSQLD_OPTS="--init-file=/var/lib/mysql/reset-root.sql"
sudo systemctl start mysqld

On Ubuntu the unit is mysql.service, and it does not honor MYSQLD_OPTS, so run the binary directly instead:

sudo systemctl stop mysql
sudo -u mysql /usr/sbin/mysqld --init-file=/var/lib/mysql/reset-root.sql --daemonize

Then remove the option, delete the file and restart normally:

sudo systemctl unset-environment MYSQLD_OPTS
sudo rm /var/lib/mysql/reset-root.sql
sudo systemctl restart mysqld      # or mysql on Ubuntu

Method B: --skip-grant-tables

If the init file approach doesn't work for you, the traditional method still does, with different statements. The server is wide open while it runs, so always add --skip-networking:

sudo systemctl stop mysqld
sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"
sudo systemctl start mysqld
mysql -u root

Inside the client you must reload the grant tables first, otherwise ALTER USER fails:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'N3w-R00t-Passw0rd!';

If the root account somehow uses auth_socket (common on Ubuntu 16.04 where mysql_secure_installation was answered a certain way), switch it to a password plugin at the same time:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'N3w-R00t-Passw0rd!';

Exit and restart without the special options:

sudo systemctl unset-environment MYSQLD_OPTS
sudo systemctl restart mysqld

Put the new password somewhere sane

Password manager first. If automation needs local root access, create /root/.my.cnf with mode 600:

[client]
user=root
password=N3w-R00t-Passw0rd!

Cleanup check

I once left the recovery flags in the systemd environment and only noticed on the next reboot. Now I check:

mysql -u root -p -e "SELECT user, host, plugin FROM mysql.user WHERE user='root';"
sudo systemctl show mysqld -p Environment

The second command must not still list MYSQLD_OPTS with the recovery flags. Also confirm the server is listening again if you used --skip-networking: ss -ltnp | grep 3306.

If it still won't work

  • ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement – You forgot FLUSH PRIVILEGES before ALTER USER in method B.
  • ERROR 1819 (HY000): Your password does not satisfy the current policy requirements – validate_password is loaded. Choose a password with upper and lower case, a digit and a symbol, at least 8 characters.
  • The init file is ignored and the old password still applies – Check /var/log/mysqld.log for [ERROR] ... reset-root.sql: Permission denied. The file must be readable by the mysql user, and on CentOS with SELinux enforcing it must be in a directory labelled mysqld_db_t, which is why the example puts it in the data directory.
  • Job for mysqld.service failed after set-environment – Some builds ship a unit without $MYSQLD_OPTS in ExecStart. Check with systemctl cat mysqld and, if it is missing, use the direct mysqld --daemonize approach shown for Ubuntu.
  • ERROR 1698 (28000): Access denied for user 'root'@'localhost' after the reset – The account uses the auth_socket plugin; either connect with sudo mysql or switch the plugin as shown in method B.

Keep a copy of this in your runbooks. You won't be calm when you need it.