Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL root password reset fails: skip-grant-tables will not run ALTER USER

ErrorERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Short answer

A server started with --skip-grant-tables does not read the grant tables, so account statements like ALTER USER are refused until you run FLUSH PRIVILEGES. Run it first, then ALTER USER with the exact account name. On systemd systems pass the option with MYSQLD_OPTS, and restart normally afterwards.

  • Applies to MySQL 8.4, systemd
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Connect without a password and ask the server whether it really is in skip-grant-tables mode. The manual says that mode turns on skip_networking, so a result of 1 means yes. If you are refused with an access error, the server is running with its normal privilege system.

    mysql -e "SELECT @@skip_networking;"
  2. 2Match what you see, then jump to its fix

    1. 1Error 1290 on the ALTER USER or SET PASSWORD line, and the first-step query returns 1ALTER USER is run before FLUSH PRIVILEGESFix
    2. 2mysql without a password is refused with an access error, after you edited a file or started mysqld by handThe server was never restarted with --skip-grant-tablesFix
    3. 3FLUSH PRIVILEGES worked, but ALTER USER fails with error 1396 (Operation ALTER USER failed for ...)The account name in ALTER USER does not match an existing accountFix

What does the error look like?

  • ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
  • ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
  • mysql without a password is refused even though you think the server runs with --skip-grant-tables

Cause 1 of 3

ALTER USER is run before FLUSH PRIVILEGES

1Check

The manual says --skip-grant-tables makes the server start without reading the grant tables, and that the grant tables load at runtime only after a privilege-flushing operation. Until then, ALTER USER is refused. You can flush with the FLUSH PRIVILEGES statement or with mysqladmin flush-privileges.

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';"

2Fix

Flush the privileges in the same session, then change the password. Use your own password in place of MyNewPass.

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

3Verify

ALTER USER returns without an error. After you restart the server normally, the new password works.

mysql -u root -p -e "SELECT 1;"

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The server was never restarted with --skip-grant-tables

1Check

The manual says to stop the server and restart it with --skip-grant-tables. On an RPM platform the server is the systemd service mysqld, and the manual shows MYSQLD_OPTS as the way to hand server options to that service.

systemctl status mysqld

2Fix

Set the option through MYSQLD_OPTS and restart the service. When the password is changed, remove the variable and restart again so the server runs normally, with the grant tables and without skip_networking.

sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
sudo systemctl restart mysqld
mysql
# after the password is changed:
sudo systemctl unset-environment MYSQLD_OPTS
sudo systemctl restart mysqld

3Verify

mysql connects without a password and the first-step query returns 1; after the final restart a password is needed again.

mysql -e "SELECT @@skip_networking;"

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The account name in ALTER USER does not match an existing account

1Check

By default ALTER USER gives an error for an account that does not exist. The account is user name plus host name, and the host part defaults to '%' when you leave it out. 'root'@'localhost' and 'root'@'%' are two different accounts.

SELECT USER, HOST FROM INFORMATION_SCHEMA.USER_ATTRIBUTES;

2Fix

Copy the user name and host exactly from that list into the statement.

ALTER USER 'admin'@'%' IDENTIFIED BY 'MyNewPass';

3Verify

ALTER USER returns without an error and you can log in with that account after a normal restart.

mysql -u admin -p -h 127.0.0.1 -e "SELECT CURRENT_USER();"

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Keep the time with --skip-grant-tables short: the manual says it gives anyone with access to the server unrestricted access to all databases.
  • The other documented method is --init-file: put the ALTER USER statement in a file that the server runs at startup, and delete the file after the server has started, as the manual says.
  • Save the new password in a password manager before you close the session.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

CREATE USER fails with error 1396

CREATE USER is refused for an account whose user and host already exist (error 1396)

Error 1396 on CREATE USER means an account with that exact user and host already exists, often left from an earlier attempt.

3 causes, each with a fix

Databases

MySQL rejects a new password with error 1819

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Error 1819 means the validate_password component is installed and your cleartext password broke its policy.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.