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.



[[email protected] /]# 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:



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

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

[[email protected] /]# chown mysql:mysql /mysql-reset-pass.sql
[[email protected] /]# chmod 666 /mysql-reset-pass.sql

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

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

Now start MySQL server and test the new password.

[[email protected] /]# service mysqld start
[[email protected] /]# 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.cfg)
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.

[[email protected] /]# 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.



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

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

[[email protected] /]# 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.

[[email protected] /]# 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.