Recover the MySQL Root Account

Background

A MySQL database can give different rights to different accounts. Every account has a username, a list of privileges, and a hash of the password.

A hash function always produces the same output given the same input, but it's nearly impossible to use the output to figure out the input.

When you log in, MySQL hashes the password you typed, then compares the new hash with the hash on file. If they match, MySQL knows that you know the password, even though MySQL (or a hacker) can't use your hash to figure out your password.

The downside of hashing is that if you forget your password, it can't be recovered. Instead, you can only overwrite the password hash with a new value.

Like the operating system, MySQL has a special root account with privilege to do anything, including overwrite other users' password hashes. But what if you lose the root account's password? This procedure lets you set a new password for the MySQL root, as long as you have root or sudo privilege on the operating system.

Steps

Connect to the Lab Server

Connect into your server, lostroot.example.com.

Stop MySQL

Stop the MySQL server process.

lostroot ~ $ sudo service mysqld stop
Stopping mysqld:                                           [  OK  ]
After this step is completed:
  1. MySQL process is stopped
Start MySQL with Default Accounts

Start MySQL in safe mode and skip the GRANT tables.

lostroot ~ $ sudo mysqld_safe --skip-grant-tables &
[1] 4323
lostroot ~ $ 120619 04:58:29 mysqld_safe Logging to '/var/log/mysqld.log'.
120619 04:58:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
After this step is completed:
  1. MySQL process is running
  2. MySQL process has the --skip-grant-tables flag
  3. From console, 'root' can log into MySQL with no password
Log In to MySQL as Root

Connect to MySQL as 'root'.

Because the MySQL server is running with --skip-grant-tables, it won't ask you for a password.

lostroot ~ $ mysql -u root mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
After this step is completed:
  1. SHOW PROCESSLIST; shows 'root' using the MySQL console.
Set the New Password

Overwrite the root password with the new value.

mysql> update mysql.user set password=PASSWORD("EatHipDeadMove") where User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
After this step is completed:
  1. 'root' user's password hash has been updated.
  2. From console, 'root' can not log into MySQL with no password
  3. From console, 'root' can log into MySQL with password 'EatHipDeadMove'
Restart MySQL
Exit, and restart MySQL server process. 
mysql> exit
Bye
lostroot ~ $ sudo service mysqld restart
120619 05:02:46 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[1]+  Done                    sudo mysqld_safe --skip-grant-tables

Check your work, log in as 'root' with the new password.

lostroot ~ $ mysql -u root mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
lostroot ~ $ mysql -u root mysql -p
Enter password: EatHipDeadMove
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
After this step is completed:
  1. MySQL process is running
  2. MySQL process does not have the --skip-grant-tables flag
  3. From console, 'root' can log into MySQL with password 'EatHipDeadMove'