Change root password in MySQL database

Environment:

MySQL 5.1 production, PRODDB

Description:

We had an issue in one of our production environments where in the root password was changed and no one was able to keep track of the same.

Because of the root password change, the backups were failing and we took the below measures to change the root password.

Step 1: Login as root user in the server

Step 2: Stop the MySQL process as shown below:

PRODDB> /etc./init.d/mysql stop

Stopping MySQL database server: mysqld.

Step 3: once the mysql daemon is stopped, we need to start the mysql in safe mode with the skip table’s option. This is basically done in order to skip the grants table which contains the password related information of the users.

mysql> mysqld_safe --skip-grant-tables

Step 4: now the database should be able to connect without entering the password.

PRODDB> mysql --user=root mysql

Step 5: Once you are logged into the database, change or set a new password for the root user using below:

mysql> update user set Password=PASSWORD('temp') where user='root';
mysql> flush privileges;

Step 6: Stop the Mysql Daemon and start it normally

PRODDB> /etc./init.d/mysql stop

Stopping MySQL database server: mysqld.

PRODDB> /etc./init.d/mysql stop

Starting MySQL database server: mysqld.

Step 7: to be on the safer side, we created one more user with admin privileges and kept with the DBA team so that we can avoid outages like this.