How to change MySQL root password

To change your MySQL root password, you need to use either the ALTER USER or SET PASSWORD statement as follows:

ALTER USER root@localhost IDENTIFIED BY 'newPassword';
-- or
SET PASSWORD FOR root@localhost = 'newPassword';

If you’re currently logged in as the root account, you can even omit the FOR clause in your SET PASSWORD statement as shown below:

-- change the current user account password
SET PASSWORD = 'newPassword';

If you forgot your current root password and can’t connect to the MySQL server, then you need to reset the root password with another method.

Resetting root password for Windows and Unix

Resetting a root password requires you to restart your MySQL server and add the --init-file option to the sqld command.

The --init-file option is used to provide an initialization file. The file should contain SQL statements that you want to run during the startup process.

If you’re using Windows, then you need to create an init.txt file with the following content:

ALTER USER root@localhost IDENTIFIED BY 'newPassword';

Next, stop the currently running MySQL service.

After that, you need to restart the service using mysqld --init-file=init.txt command. Keep in mind that you need to provide the absolute path to the init file.

The full working example may look as follows:

mysqld --init-file=C:\\init.txt

Once the server has started, you can test connecting to the MySQL server using the root account.

Don’t forget to delete the init.txt file as well to prevent others from finding out your root password.

The method above should work both for Windows and Unix-like operating systems.

Resetting root password using –skip-grant-tables option

Alternatively, you can use the --skip-grant-tables option to reset your root password when you forgot it.

The --skip-grant-tables option is used to start the MySQL server without loading the grant tables, allowing anyone to connect to your MySQL server without username and password.

To reset your root password using this option, you need to:

• Restart your MySQL server with --skip-grant-tables enabled
• Load the grant table so that you can change the root password with ALTER USER statement
• Alter the password for root@localhost

First, turn off your MySQL server and run it again using the mysqld command with --skip-grant-tables added:

sudo service mysql stop
mysqld --skip-grant-tables

That should restart MySQL server with --skip-grant-tables option enabled.

Next, use the mysql command to log into the server from the command line:

mysql

Once inside, load the grant tables using the following command:

FLUSH PRIVILEGES;

Finally, alter the password for root user with the ALTER USER statement

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

You can change NewPassword with your actual desired password.

And now you’re done. Stop the running mysqld command with CTRL + C shortcut and run it normally:

sudo service mysql start

Now that MySQL server is started normally, you should be able to login using root user with the new password you’ve set using the ALTER USER statement.

You have learned how to reset MySQL root password. Nice work!