How To Reset MySQL Root Password

 ·  · 

Easy steps about how to reset your MySQL root password.

 

1. Stop MySQL

$ sudo /etc/init.d/mysql stop

or using service command in Ubuntu:

$ sudo service mysql stop

 

2. Restart With --skip-grant-tables

Our goal is to reset the password, so we need to restart the mysqld demon. Start the mysqld demon again without checking privileges.

# NOT recommend!
$ sudo /usr/sbin/mysqld --skip-grant-tables &

With the --skip-grant-tables option the mysqld demon will be launched without user authentication. It is very dangerous if your MySQL server is exposed to internet under this circumstance.

A better way is to disable networking simultaneously with --skip-networking option.

# recommended way!
$ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

 

3. Reset Password

Open another shell session and connect the mysqld demon without authentication:

$ mysql -u root

Reset the password:

mysql> UPDATE mysql.user SET Password=PASSWORD('your_new_password') WHERE Host = 'localhost' AND User = 'root';

Or if you have account that access your MySQL server remotely:

-- allow any remote host
mysql> UPDATE mysql.user SET Password=PASSWORD('your_new_password') WHERE Host = '%' AND User = 'root';
-- allow specified IP only
mysql> UPDATE mysql.user SET Password=PASSWORD('your_new_password') WHERE Host = 'allowed_host_IP' AND User = 'root';

Or alternatively, change both with one line:

mysql> UPDATE mysql.user SET Password=PASSWORD('your_new_password') WHERE User='root';

 

4. Restart Normally

Flush privileges before restarting:

mysql> FLUSH PRIVILEGES;

Restart your MySQL server:

$ sudo /etc/init.d/mysql stop
$ sudo /etc/init.d/mysql start

Now you can login MySQL using your new password:

$ mysql -uroot -pyour_new_password