How to Reset Root Password in MySQL 8.0
Abstract: Reset Root Password in MySQL 8 Now stop the MySQL server and start it normally. # systemctl stop mysqld.service # for distros using systemdThis
In an unfortunate event of forgetting or losing your MySQL root password, you will surely need a way to recover it somehow. What we need to know is that the password is stored in the users table. This means that we need to figure out a way to bypass the MySQL authentication, so we can update the password record.
Luckily there is an easy to achieve and this tutorial will guide you through the process of recovering or resetting root password in MySQL 8.0 version.
As per MySQL documentation there are two ways to reset the root MySQL password. We will review both.
Reset MySQL Root Password Using –init-fileOne of the ways to reset the root password is to create a local file and then start the MySQL service using --init-file
option as shown.
# vim /home/user/init-file.txt
It is important that you make sure that file is readable by the mysql user. Within that file paste the following:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
In the above change 「new_password」 with the password that you wish to use.
Create File with New MySQL Root PasswordNow make sure that the MySQL service is stopped. You can do the following:
# systemctl stop mysqld.service # for distros using systemd # /etc/init.d/mysqld stop # for distros using init
Then run the following:
# mysqld --user=mysql --init-file=/home/user/init-file.txt --console
This will start the MySQL service and during the process it will execute the init-file that you have created and thus the password for the root user will be updated. Make sure to delete the file once the password has been reset.
Reset MySQL Root PasswordMake sure to stop the server and start it normally after that.
# systemctl stop mysqld.service # for distros using systemd # systemctl restart mysqld.service # for distros using systemd # /etc/init.d/mysqld stop # for distros using init # /etc/init.d/mysqld restart # for distros using init
You should now be able to connect to the MySQL server as root using the new password.
# mysql -u root -pConnect to MySQL with New Root Password Reset MySQL Root Password Using –skip-grant-tables
The second option we have is to start the MySQL service with the --skip-grant-tables
option. This is less secure as while the service is started that way, all users can connect without password.
If the server is started --skip-grant-tables
, the option for --skip-networking
is automatically activated so remote connections will not be available.
First make sure that the MySQL service is stopped.
# systemctl stop mysqld.service # for distros using systemd # /etc/init.d/mysqld stop # for distros using init
Then start the service with the following option.
# mysqld --skip-grant-tables --user=mysql &
Then, you can connect to the mysql server by simply running.
# mysql
Since account-management is disabled when the service is started with --skip-grant-tables
option, we will have to reload the grants. That way we will be able to change the password later:
# FLUSH PRIVILEGES;
Now you can run the following query to update the password. Make sure to change 「new_password」 with the actual password you wish to use.
# ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_passowrd';Reset Root Password in MySQL 8
Now stop the MySQL server and start it normally.
# systemctl stop mysqld.service # for distros using systemd # systemctl restart mysqld.service # for distros using systemd # /etc/init.d/mysqld stop # for distros using init # /etc/init.d/mysqld restart # for distros using init
You should be able to connect with your new password.
# mysql -u root -pLogin to MySQL with New Root Password
You might also like to read these useful following MySQL related articles.
- How to Install MySQL 8 in CentOS, RHEL and Fedora
- 15 Useful MySQL Performance Tuning and Optimization Tips
- 12 MySQL Security Practices for Linux
- 4 Useful Commandline Tools to Monitor MySQL Performance
- MySQL Database Administartion Commands
In this article you learned how to reset lost root password for the MySQL 8.0 server. I hope the process was easy.