How to Change Root Password in MySQL

Sometimes you may need to reset root password of your MySQL database if you have forgotten it or want to change it. In this article, we will look at how to change root password in MySQL.

How to Change Root Password in MySQL

Here are the steps to change root password in MySQL.

1. Log into Linux

Open terminal as the user that normally uses MySQL database.

2. Stop MySQL Server

Run the following command to stop MySQL server.

$ sudo service mysql stop
OR
$ /etc/init.d/mysql-server stop

If you have custom MySQL installation, you may also run the following command to stop MySQL server.

$ sudo killall mysqld

3. Create password file

Create a text file to store SQL statement for new password in it using vi editor.

$ sudo vi new-password

Add the following line to this new file. Replace root-password below with new password for your root user.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';

Save and close the file.

Run MySQL server using the above file’s full path as init-file option.

$ mysqld --init-file=/home/new-password

When MySQL server starts this time, it will execute the SQL statement in new-password file and automatically update root password. Once the server has started, you can delete the new-password file.

You may test this password by opening a new terminal or MySQL client and logging in as root user, using new password.

4. Restart MySQL Server

Now you can restart MySQL Server as usual and the changes made earlier will persist.

$ sudo service mysql restart
OR
$ /etc/init.d/mysql-server restart

5. Alternative Way

The above method works, if you have been locked out of your MySQL database. If you are not locked out of MySQL and are logged in, then you can simply run the following SQL command to reset root password. Replace root-password with your root password.

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root-password'); 
mysql> FLUSH PRIVILEGES;

If your MySQL installation is default and not modified, you might be able to log into MySQL without using root password, to change it, using the following command.

$ sudo mysql -u root --skip-password

In this article, we have looked at a couple of ways to change root password in MySQL. You can use the same steps to reset root password in Mariadb also.

How to Change Root Password in MySQL