MySQL root password recovery

Just had to do this and want to write it down while I still remember.


So here’s the scenario: we migrated a whole MySQL database over from another host. Problem is, we didn’t know what the root password was.

Do this as system root.

[root@myhost ~]# /etc/init.d/mysqld stop
[root@myhost ~]# mysqld_safe --skip-grant-tables \
--skip-networking &
[root@myhost ~]$ mysql -u root
mysql>
mysql> use mysql;
mysql> update user
    -> set password=PASSWORD("mynewrootpass")
    -> where user='root';
mysql> flush privileges;
mysql> quit;

Find the processes for the currently running mysqld (“ps -ef | grep mysqld”) and kill:

[root@myhost ~]# kill 54321 12345 43210
[root@myhost ~]# p140408 08:58:50 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Restart MySQL

[root@myhost ~]# /etc/init.d/mysqld start

and log in as root to test.

[root@myhost ~]# mysql -u root -p
Enter Password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 19
Server version: 5.0.77 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>