20120207 mysql root password recovery - plembo/onemoretech GitHub Wiki

title: MySQL root password recovery link: https://onemoretech.wordpress.com/2012/02/07/mysql-root-password-recovery/ author: lembobro description: post_id: 2121 created: 2012/02/07 13:46:51 created_gmt: 2012/02/07 17:46:51 comment_status: closed post_name: mysql-root-password-recovery status: publish post_type: post

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>