Reset MySQL root password on centos Linux

It may look strange but unfortunately is a common situation, you are trying to login into MySQL server with root credentials and you have no idea what is the correct password. Fortunately if you have root access on system you can do everything, right? In this case yes, it is possible to change MySQL root password.


This procedure require to stop the MySQL database in order to make the modification, so it is not suitable for live applications connected to that database.


So, first of all you have to stop MySQL Server.



[root@comcmd /]# service mysqld stop

From here there are 2 methods to reset MySQL server root password:



  1. Editing my.cfg (MySQL config file) in order to load a sql file before opening any connection.
  2. Starting MySQL server with --skip-grant-tables parameter.

First method:

Create a sql file (text file) with mysql command inside:



[root@comcmd /]# echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'My-New-Pass-W0rd' WITH GRANT OPTION;" > /mysql-reset-pass.sql
Or:

[root@comcmd /]# echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD( 'My-New-Pass-W0rd' );" > /mysql-reset-pass.sql
Change ownership of file to mysql:mysql.

[root@comcmd /]# chown mysql:mysql /mysql-reset-pass.sql
[root@comcmd /]# chmod 666 /mysql-reset-pass.sql

Open mysql config file my.cnf (/etc/my.cnf) and add parameter init-file:

[mysqld]
init-file=/mysql-reset-pass.sql

Now start MySQL server and test the new password.

[root@comcmd /]# service mysqld start
[root@comcmd /]# mysql -u root -p

If root password has changed then delete the sql file /mysql-reset-pass.sql and also remove it from config file (my.cnf)
Always check the logs if this method doesn't work or the MySQL service doesn't start at all, mostly due to file permissions of application armor.



Second method:
Start MySQL server with --skip-grant-tables parameter.

[root@comcmd /]# mysqld_safe -user=mysql --skip-grant-tables --skip-networking &

Login to MySQL with root credentials without password because skipping grant tables enables anyone to connect without a password and with all privileges. For security reasons you have to use --skip-networking in order to disable network access.



[root@comcmd /]# mysql - u root
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD( 'My-New-Pass-W0rd' );
mysql> quit
[root@comcmd /]# pkill mysqld

Check if mysqld or mysqld_safe is stopped before starting MySQL as a service.

[root@comcmd /]# ps -A | grep mysql
If there is any instance of MySQL running you have to stop it with kill or pkill command.

Now start MySQL as a service and login with new root password.

[root@comcmd /]# service mysqld start

This is not the safest way to reset MySQL root password and require two restarts (One service stop, one process start, one process kill and one service start). Using the first method you have to restart your MySQL server just one time.