How to Change MySQL Root Password Simple Trick

How to change MySQL Root Password on Ubuntu or Debian or CentOS. Ever forgotten your MySQL root password or fixing a MySQL installation on a new server, we have all been there.

In this guide you are going to learn how to change MySQL root password or Reset it in case you forget it for MySQL 8, MySQL 5.7 and MySQL 5.6.

This guide is tested on MySQL versions running on virtual machine created on Google cloud with Ubuntu.

Prerequisites

Root access to server or a user with sudo privileges.

Check MySQL version

SSH into your server to check the version of MySQL server installed. Each version has their own method of resetting passwords.

To check the MySQL version use the following command.

mysql -V 

If you are on MySQL 8.0 you will get an out put similar to this.

mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

If you are on MySQL 5.* you will get an out put similar to this.

mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Start MySQL server with skip-grant-table

Now you can stop your MySQL server using the following command.

sudo systemctl stop mysql

Now start MySQL server with the --skip-grant-tables option. The & at the last is required here in the command below.

sudo mysqld_safe --skip-grant-tables&

You should see something similar to the one below.

[email protected]:~$ 2021-02-02T04:45:12.861506Z mysqld_safe Logging to syslog.
2021-02-02T04:45:12.868517Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2021-02-02T04:45:12.911492Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Hit ENTER to return to your terminal.

Login to MySQL without Password

Now you can login to MySQL without password normally.

sudo mysql -u root mysql

Now you will see the mysql> prompt to execute mysql commands.

Change MySQL Root Password v8.0

Execute the following commands for MySQL 8.0.

UPDATE mysql.user SET authentication_string=null WHERE User='root';

FLUSH PRIVILEGES;

Replace new_password with your own password.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

FLUSH PRIVILEGES;

Exit MySQL.

exit;

Now you can skip to stop and restart MySQL.

Change MySQL Root Password v5.7

Execute the following commands for MySQL 5.7.

UPDATE user SET authentication_string=PASSWORD('new_password') WHERE user='root';

UPDATE user SET plugin="mysql_native_password" WHERE User='root';

FLUSH PRIVILEGES;

Exit MySQL.

exit;

Change MySQL Root Password v5.6

Execute the following commands for MySQL 5.7.

UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';

UPDATE user SET plugin="mysql_native_password" WHERE User='root';

FLUSH PRIVILEGES;

Exit MySQL.

exit;

Stop and Restart MySQL

Stop all running MySQL process using killall command.

sudo killall -u mysql

If you see any MySQL output with ended message hit ENTER to continue.

Start MySQL.

sudo systemctl start mysql

Login to MySQL using the new password.

sudo mysql -u root -p

Enter the new password you set before when prompted.

That’s it, you are done.

Conclusion

Now you have learned how to reset MySQL password on Linux.

Thanks for your time. If you face any problem or any feedback, please leave a comment below.