Blog

MySQL: Root password reset

Tags: MySQLmysql root passwordpassword reset

Scenario:

Have you ever for­got­ten the root pass­word 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

Category : Howtos, Linux, MySQL, Troubleshooting