Have you ever forgotten the root password on one of our clients MySQL servers? No worries, please follow these five steps to do the MySQL root password reset.
1. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.
service mysqld stop
Or
/etc/init.d/mysqld stop
Please note that instead of the mysqld service name, it can be mysql or mysql-server or even mysqld-server or even anything else. But I guess you got the trick, just check the /etc/init.d and apply some common sense.
2. Now let’s start the mysql server manually without permission tables (skip the grant tables which store the passwords) which allows us to login as root user without password, using the following command
mysqld_safe --skip-grant-tables &
You should see mysqld start up successfully, like the message below,
130122 07:08:56 mysqld_safe Logging to ‘/var/log/mysqld.log’.
130122 07:08:56 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
If not, well you have bigger issues. Please see the messages below. The last line says, it was started but it was stopped as well. But you will have the reason of that abrupt stoppages in the log file mentioned there, /var/log/mysqld.log
130122 07:06:46 mysqld_safe Logging to ‘/var/log/mysqld.log’.
130122 07:06:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130122 07:06:47 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
3. However if it did startup successfully, you should now be able to connect to mysql without a password.
mysql –user=root mysql
4. Finally reset the password from the mysql’s users table, flush privileges, and then exit the MySQL console using the following commands. Please note that ‘yourchoicepassword’ means a password of your choice.
mysql> UPDATE user SET password=PASSWORD('yourchoicepassword') WHERE user='root';
mysql > flush privileges;
mysql > exit;
5. Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again. You may enter the password in /root/.my.cnf in case you want to connect to the MySQL server without it asking any password.
/etc/init.d/mysqld stop /etc/init.d/mysqld start
