Reset MySQL root password

Yesterday, I forgot the password of my MySQL dev server. It’s my own development setup; no one else was there to help me to remember the same. So the only solution- I reset it.

1. Stop MySQL server
sudo /etc/init.d/mysql stop

There may be a message like
Since the script you are attempting to invoke has been converted to an Upstart job, you may also use the stop(8) utility, e.g. stop mysql
mysql stop/waiting

This is a generic message, it’s just a suggestion to stop the same as sudo service mysql status

But anyway the service should be stopped already; to confirm the status, recheck
service mysql status

now we should see
mysql stop/waiting

But if not, then run
sudo kill `sudo cat /var/run/mysqld/mysqld.pid`

If the service were running, killing its pid should work.

2. Start up the mysql daemon, skip the grant tables which store the passwords
sudo mysqld_safe --skip-grant-tables &

If the response is
mysqld_safe Logging to '/var/log/mysql/error.log'.
mysqld_safe A mysqld process already exists

Need to kill the mysqld pid
sudo kill `sudo cat /var/run/mysqld/mysqld.pid`

And again execute the above step to start up the daemon. This time there should be success message
mysqld_safe Logging to '/var/log/mysql/error.log'.
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

3. connect to mysql without a password
mysql --user=root mysql

4. Reset root password
mysql> update user set Password=password('newpassword') where User='root';
mysql> flush privileges;
mysql> exit;

5. Stop and then start MySQL
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

That’s all.

Advertisements

One thought on “Reset MySQL root password

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s