My Blog

How to reset MySQL root password

10.26.2011 | Blog

In this article I’ll go through resetting MySQL root password the easiest way

We are going to stop the MySQL daemon to be able to run it in safe mode, then we are going to skip grant tables, which means we are skipping password authentication, then we will set our new password

First, Let’s stop the mysql service, In Red Hat’s family (Red Hat Enterprise, Fedora, CentOS) the Mysql Daemon is called ‘mysqld‘, but in Debianlike Ubuntu based distributions and OpenSuse its called ‘mysql‘. I’ll be using the debian based naming

sudo service mysql stop

OR

sudo /etc/init.d/mysql stop

Now, we’ll start the server in safe mode and skip grant tables so that it wont ask for the password we had lost when we try to login

sudo mysqld_safe --skip-grant-tables

you should see something like this

111026 11:05:46 mysqld_safe Logging to syslog.
111026 11:05:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Now open a new terminal window and run the mysql console using the main mysql database named ‘mysql‘ using the root user

mysql --user=root mysql

Then we’ll reset the password from the mysql’s users table, flush privieges, then exit the mysql console

UPDATE user SET password=PASSWORD('new-password') WHERE user='root';
flush privileges;
exit;

Start again the mysql daemon

sudo service mysql restart

That’s All :)

Be Sociable, Share!

Responses

[…] start Now, you should have your new MySQL root password! I recommend you write it down this time! Background: Sometimes a system administrator forgets the MySQL root password! It happens, but what t…> or service mysql stop Step 2: Start the MySQL Server mysqld_safe –skip-grant-tables & We do this […]

Stan
11.19.2011

I tried this on a CentOS box running Mysql Ver 14.14 Distrib 5.5.17, for Linux (i686) using readline 5.1

This method does not seem to work.

# mysql -u root -p
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Am I doing something wrong?

Cra
4.25.2013

In Red Hat’s family (Red Hat Enterprise, Fedora, CentOS) the Mysql Daemon is called
‘mysqld‘,
but in Debianlike Ubuntu based distributions and OpenSuse its called
‘mysql‘.
I’ll be using the debian based naming.

Nicolai Krüger
12.06.2013

Oh thank you!

Been looking all over the internet (google)…
Lots and lots of places mentions “mysqld_safe –skip-grant-tables” but not a single one mentions the new terminal window – thats what saved me!
THANK YOU! 😀

Comments