How to Reset Your Forgotten MySQL Root Password When the Official Guide Doesn\'t Work?

How to Reset MySQL Root Password When Forgotten

You have forgotten the password for your MySQL console and are receiving error #1045 when attempting to access PHPMyAdmin. The official MySQL documentation suggests resetting the root password through a series of steps. However, you are facing difficulties implementing this solution.

Official Reset Steps

The steps mentioned in the documentation are:

create a mysql-init.txt file containing
UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root';
FLUSH PRIVILEGES;
save it as C:\me\mysql-init
In the command prompt, run:
C:\wamp\bin\mysql\mysql5.5.8\bin\mysqld --init-file=C:\me\mysql-init.txt

Troubleshooting

Despite following these steps, you are unable to reset the password. The reason behind this could be an incorrect path to the mysql-init.txt file or misconfiguration in the MySQL configuration file.

Solution

To address this issue, follow these steps:

1. Locate the MySQL configuration file:

   • Run: $ mysql --help | grep -A 1 "Default options"
   • For Ubuntu 16, the file is usually located at /etc/mysql/mysql.conf.d/mysqld.cnf.

2. Edit the configuration file:

   • Use $ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf to edit the file.
   • Add skip-grant-tables under the [mysqld] block and save the changes.

3. Restart MySQL service:

   • Run: sudo service mysql restart.
   • Verify the status using: sudo service mysql status.

4. Log into MySQL without a password:

   • Run: $ mysql -u root.

5. Change the root password:

   mysql> FLUSH PRIVILEGES;
   mysql> ALTER USER 'root'@'localhost'
          IDENTIFIED WITH mysql_native_password BY 'MyNewPass';

6. Revert configuration file changes:

   • Remove or comment out the skip-grant-tables line in the MySQL configuration file.

7. Restart MySQL service:

   • Run: sudo service mysql restart.

The above is the detailed content of How to Reset Your Forgotten MySQL Root Password When the Official Guide Doesn\'t Work?. For more information, please follow other related articles on the PHP Chinese website!