mysql reset root password

How to Reset MySQL Root Password

Sometimes you may need to change root password or reset root password in MySQL, in case you forgot root password. Here’s how to reset root password in MySQL. You can use these steps to reset root password in Windows, Mac, Ubuntu, CentOS, and other Linux systems.

How to Reset MySQL Root Password

Here are the steps to reset root password in MySQL in Ubuntu, CentOS, Windows, Mac and other systems.

1. Stop MySQL Server

Open terminal and run the following command to stop MySQL Server.

CentOS / Fedora

# service mysqld stop

Ubuntu / Debian

$ service mysql stop

Bonus Read : How to Delete Stored Procedure in MySQL

2. Restart MySQL Server with –skip-grant-tables

Next, restart MySQL Server with –skip-grant-tables option so that you can login without a password and with all privileges.

# mysqld_safe --skip-grant-tables &

You may need to include ‘sudo’ keyword before the above command in some Ubuntu/Debian systems.

Make sure to include ‘&’ at the end of your command so that MySQL server will run in the background.

Please note, it is highly insecure to start mysql with –skip-grant-tables option and should be used only for a short amount of time.

After we change root password in MySQL, we will immediately restart MySQL server the normal way.

Bonus Read : How to Change User Password in MySQL

3. Log into MySQL

Run the following command to log into MySQL.

# mysql -uroot

4. Change Root Password in MySQL

Run the following command at mysql> prompt. Replace new_password in command below with your new password.

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

Bonus Read : MySQL CAST vs CONVERT

5. Flush Privileges

Flush privileges to apply changes

mysql> exit;

Bonus Read : MySQL Rollback Query

6. Stop MySQL Server

Run the following command in terminal prompt to stop the MySQL server, since it is not secure.

# mysqladmin -u root -p shutdown

You will be asked to enter root password. Enter your new root password.

Bonus Read : How to Use MySQL Rollup

7. Start MySQL Server

Start MySQL Server normally.

CentOS / Fedora

# service mysqld start

Ubuntu / Debian

$ service mysql start

Hopefully, the above article will help you reset root password in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!