How to Reset MySQL Root User Password

When you working on Linux system.  Sometimes you forget MySQL root user password.  As a DBA you need to recover/Reset it. As recovery of password is not possible, you need to reset it. To reset the password you need to follow below steps.

1.    Log on to your system as the UNIX mysql user (User by which your MySQL Service is running).

2.    Locate the .pid (Process ID file) file that contains the server's process ID (MySQL process ID). The exact location and name of this file depend on your installation (RPM/Binary/Source), host name, and configuration (my.cnf ). Some common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. File name has an extension of .pid and begins with either mysqld or your system's host name.

3.    Stop the MySQL Service. You can stop MySQL Service by using different way.

1.    Service mysql stop: It will work if your mysql is installed as a service

2.    /etc/init.d/mysql stop : It will work on some system

3.    You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command

shell> kill `cat /mysql-data-directory/host_name.pid`

                       

Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

4.    Search running MySQL Process  on server using below command

 shell> ps –aef|grep mysql

5.    The above command will return the detail about MySQL Running process, copy the process id and kill the process by executing below command

shell>kill -9 ‘Process id of MySQL Process’

4.    Once you have stopped the MySQL service you have 2 ways to resetting the root password.

1.    Using the init file

2.    Using --skip-grant-tables options

1.    Using the init file: Create a text file and place the following statements in it. Replace the password with the password that you want to use.

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

The UPDATE and FLUSH statements each must be written on a single line. The UPDATE statement resets the password for all existing root accounts, and the FLUSH statement tells the server to reload the grant tables into memory.

Save the file. For this example, the file will be named /home/mysql/myl-init. Now you can start MySQL Service using 2 ways.

a. Start the MySQL server with the special --init-file option:

shell> mysqld_safe --init-file=/home/mysql/myl-init &

The server executes the contents of the file named by the --init-file option at startup, changing each root account password.

Now login to MySQL instance and start using it

2.    Using --skip-grant-tables options

shell> mysqld_safe  --skip-grant-table &

Log in to MySQL using no password

shell> mysql –uroot

On MySQL prompt execute below command

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

                                                                                                       

Restart the MySQL process and use it with newly configured password

5.    Now your MySQL root password has been changed. You can use new password.

Happy Working.. J