Recover mysql root password

Posted on Jan 15, 2012

It happens quite often, that You need to do something on database, which was installed long long time ago. And nobody remembers the root password.

mysql_root

Kinda good practice is to save mysql password in root user directory with a ‘.’ in front of it (hidden), for example .my.cnf Nobody can read root users directory, except root, and anyway, root user can change mysql password at any time, so it’s not an security issue.
If all users are able to see root users directory, that is a problem.

Easy steps how to recover mysql root password.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET password=PASSWORD("new_root_password") WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> QUIT
# killall -9  mysqld_safe
# /etc/init.d/mysql start
# mysql -uroot -p

That’s it :)

If You need, below are the same steps in details:

# means root shell

  1. Stopping mysql service
1
2

# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
  1. Starting mysql without permissions and passwords
1
2
3
4

# mysqld_safe --skip-grant-tables &
[1] 12985
111101 21:52:06 mysqld_safe Logging to syslog.
111101 21:52:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
  1. Loging in to mysql as root user without password
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-3 (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql>
  1. Choosing database where all grants are
1
2
3
4
5

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
  1. Changing root password
1
2
3

mysql> UPDATE user SET password=PASSWORD("new_root_password") WHERE user='root';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 3  Changed: 0  Warnings: 0
  1. Apply changes
1
2

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
  1. Quit mysql shell
1
2

mysql> QUIT
Bye
  1. Stop mysql service running without permissions
1
2

# killall -9  mysqld_safe
[2]+  Killed                  mysqld_safe
  1. Start normal mysql process
1
2

# /etc/init.d/mysql start
Starting MySQL database server: mysqld ..
  1. Here You go, try Your new password :)
1
2

# mysql -uroot -p
Enter password: