Yuan Yao, An Oracle ACE's Blog

Forgetting an ordinary MySQL user’s password is usually not a big problem, since the root user can reset it. However, if the root password itself is forgotten, the situation becomes more complicated.

oracle@Yuan ~$ mysql -uroot -pyuanyao
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

There are two ways to reset the MySQL root password.

Method 1: Using --skip-grant-tables Option

When MySQL is started with the skip-grant-tables option, authentication checks are skipped for local connections. In MySQL 8, the server automatically enables skip-networking in this mode to block remote access.

This option can be enabled temporarily from the command line:

$ mysqld_safe --skip-grant-tables &

It can also be added to the MySQL configuration file:

[mysqld]
skip-grant-tables

After modifying the configuration, restart the MySQL service:

$ sudo service mysqld restart

At this point, you can access the MySQL server without providing a password:

oracle@Yuan ~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 7

The privilege tables should be reloaded first:

mysql> FLUSH PRIVILEGES;

Using the ALTER USER command to reset the password:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'yuanyao';

After resetting the password, the server should be stopped, the skip-grant-tables line should be removed from the configuration file if it was added, and the service should be restarted normally.

It is important to note that when running MySQL with skip-grant-tables, anyone with access to the server can connect without authentication and control all data. This method should therefore be used with caution and only as a temporary measure.

Method 2: Using --init-file System Variable

The init-file method provides a safer and more efficient way to reset the password because it requires only one restart and does not disable authentication.

First, create a SQL script file that contains the password reset statement. For example, save the following command in /tmp/chpw.sql:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'yuanyao';

Make sure that the MySQL server process has read access to this file. Then, start the MySQL server with the init-file option pointing to this script:

$ mysqld_safe --init-file=/tmp/chpw.sql &

If MySQL is managed as a service, an alternative is to add the following line to the configuration file under the [mysqld] section:

[mysqld]
init-file=/tmp/chpw.sql

Afterwards, restart the service:

$ sudo service mysql restart

When the instance starts, the SQL command in the file will run automatically and reset the root password. Login can then be verified with:

oracle@Yuan ~$ mysql -uroot -pyuanyao
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 8

Finally, the init-file entry should be removed from the configuration file to prevent the command from being executed again on future restarts.

Conclusion

The skip-grant-tables and init-file approaches are both effective for resetting a forgotten root password. The init-file method is generally recommended because it requires only one restart and does not bypass the normal authentication process.