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.
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root-password');mysql>
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 Run Shell Script as Cron Job
How to Get Current Directory in Shell Script
How to Create Cron Job Using Shell Script
How to Check Dependencies for Package in Linux
How to Get Current Directory in Python