Home›SQL›MySQL – Reset the Root Password

MySQL – Reset the Root Password

December 17, 20243 min readUpdated 8/25/2026

Locked out of your own server. The procedure is short, and the important part is understanding what it does to the server's security while it is running.

First: are you actually locked out?

Check the cheap possibilities before restarting anything:

  • Wrong host. 'root'@'localhost' and 'root'@'%' are different accounts with different passwords. Try -h 127.0.0.1 as well as -h localhost — see users and privileges.
  • Socket authentication. On Debian and Ubuntu, packaged MySQL often authenticates root@localhost by operating-system user, so sudo mysql connects with no password at all.
  • A saved credential. ~/.my.cnf or ~/.mylogin.cnf may already hold it.
  • A container. Then the answer below is much simpler.

In Docker, the easy case

If the data does not matter — a local development database — delete it and start again:

docker compose down -v          # -v removes the volume, and the data with it
docker compose up -d

If it does matter, the container has a running server you can reconfigure the same way as below, or you can start a fresh container against the same volume with --skip-grant-tables.

The --init-file method — prefer this one

Write the statement to a file, and have the server run it at startup:

cat > /tmp/reset.sql <<'SQL'
ALTER USER 'root'@'localhost' IDENTIFIED BY 'a-new-strong-password';
SQL
chmod 600 /tmp/reset.sql && chown mysql /tmp/reset.sql

sudo systemctl stop mysql
sudo mysqld --init-file=/tmp/reset.sql --user=mysql &
# ... wait for it to come up, confirm you can log in ...
sudo systemctl restart mysql
rm -f /tmp/reset.sql

This is the safer method and the one to reach for, because authentication stays on the whole time. The server starts normally, runs your one statement, and carries on. Nobody else can connect without a password at any point.

The --skip-grant-tables method

The one every search result shows. It works, and you should know exactly what it costs:

sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root                      # no password is required — or possible
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'a-new-strong-password';
sudo systemctl restart mysql
mysql -u root -p                   # confirm the new password works

Two details that trip people up.

FLUSH PRIVILEGES first. With --skip-grant-tables the grant tables are not loaded, so ALTER USER fails with "You are not allowed to create a user with GRANT" — which is a confusing message for what is really "the privilege system is not running". FLUSH PRIVILEGES loads it, after which account statements work. This is the one situation where that command is genuinely required; after an ordinary GRANT it is not.

--skip-networking is not optional. While the server runs in this mode, every account is unauthenticated — anyone who can reach the port is root. --skip-networking restricts it to the local socket for the couple of minutes it is up. Leaving it off on a machine with a public interface is handing over the database.

Older guides say UPDATE mysql.user SET authentication_string = PASSWORD(...). The PASSWORD() function was removed in MySQL 8; use ALTER USER.

Afterwards

SELECT user, host, plugin FROM mysql.user ORDER BY user, host;

Confirm the server is back in its normal mode and check the account list while you are there. Anything with an empty user is an anonymous account and should not exist; a root entry with host % is reachable from anywhere and rarely wanted.

sudo grep -i 'skip.grant' /var/log/mysql/error.log | tail

The error log records that the server started in an insecure mode, which is worth confirming has stopped — and worth noting for whoever reviews the incident.

Not needing this again

  • Keep credentials in a password manager or a secrets store, not in someone's memory.
  • Use mysql_config_editor to store a login path — it writes an obfuscated ~/.mylogin.cnf rather than a plaintext ~/.my.cnf.
  • Give humans named admin accounts rather than sharing root. Then one forgotten password is one person's problem, and you can see who did what.
  • On a cloud database there is a "reset master password" button and none of this applies — which is one of the better arguments for managed hosting.

What to remember

  • Check for socket auth and the @localhost / @% confusion first.
  • Prefer --init-file: authentication never goes away.
  • With --skip-grant-tables, FLUSH PRIVILEGES comes first and --skip-networking is mandatory.
  • ALTER USER, not UPDATE mysql.user — PASSWORD() is gone.