6.5.5 How to Recover MySQL Root Password
If you lose your MySQL root password, you can easily recover it by having root access to the server itself. This is done by logging into MySQL in safe mode, as described below.
Advice: the root password on the server and the root password for MySQL are two different passwords.
Step-by-step instructions using Ubuntu as an example
Important: if you have a system based on OS+ISPManager4, be sure to read the second section of this article.
1. First, stop the MySQL service. For some other Linux systems the commands may be different:
sudo service mysql stop
2. Start MySQL without loading the grant tables:
sudo mysqld_safe --skip-grant-tables --skip-networking &
Info: what to do if mysqld_safe does not start? Common solutions are:
Use sudo mysqld
An alternative command looks like this:
sudo mysqld --skip-grant-tables --skip-networking &
Disable monit or similar services
On servers with the ISPManager panel and many other panels, monit or similar services are used. In this case, you must first disable monitoring of the mysqld process with the command monit unmonitor mysqld and only then kill the process. Then enable it again with monit monitor mysqld.
Or temporarily disable monit altogether:
service monit stop
Running MySQL processes
Already running parallel MySQL processes may require their manual termination. First, get a list of them with the command:
ps aux | grep mysql
Then “kill” the processes with the following command, where <PID> is replaced with the process ID:
sudo kill -9 <PID>
Insufficient rights or directory problems
Sometimes mysqld_safe fails to start due to rights problems or missing directories. Try:
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod -R 755 /var/lib/mysql
Also make sure /var/run/mysqld/ exists and has the correct permissions:
sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld
3. Now connect to the MySQL server without a password:
mysql -u root
4. First switch to the mysql database:
USE mysql;
5. First, we need to identify how many root users there are in our MySQL. This can be done as follows:
SELECT host, user, password FROM user WHERE user = 'root';
Which will produce the following result:
You will need to reset the password for each of these users, unless your project requires otherwise.
6. Then you need to reset the password to a new one for each root user (replace NewPassword with the password and localhost with the desired host variable, the list of which you got in the previous step):
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
For MySQL 5.7 and earlier versions
UPDATE user SET authentication_string=PASSWORD('NewPassword') WHERE User='root';
FLUSH PRIVILEGES;
5. Restart MySQL in normal mode. To do this, exit MySQL:
EXIT;
And stop MySQL:
sudo service mysql stop
6. Start it again:
sudo service mysql start
7. Check access. Try logging in with a new password:
mysql -u root -p
Enter the new password. If the login is successful, the reset is complete.
Nuances with control panels
On servers with an installed control panel, the panel itself must also be notified of the MySQL root password change, otherwise it will not be able to function normally.
Below we will provide examples for relevant control panels.
ISPManager4
1. Go to Database servers section as root, select MySQL and click Edit:
2. Set a new MySQL root password instead of the old one:
3. Restart the panel with the command killall -9 ispmgr or a full server reboot.
