MySQL root password reset fails: skip-grant-tables will not run ALTER USER
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statementShort 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.
Fix it fast
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;"2Match what you see, then jump to its fix
- 1
Error 1290 on the ALTER USER or SET PASSWORD line, and the first-step query returns 1ALTER USER is run before FLUSH PRIVILEGESFix - 2
mysql 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
FLUSH 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
- 1
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 statementERROR 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
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 mysqld2Fix
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 mysqld3Verify
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
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 fixDatabases
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 fixDatabases
phpMyAdmin: Access denied for user 'root'@'localhost'
#1045 - Access denied for user 'root'@'localhost' (using password: YES)
MySQL refused the root login.
4 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fix