Have you forgotten your MySQL root password? Don’t worry, it happens to all of us.
In this article, we will show you how to reset the MySQL root password from the command line.
Depending on the MySQL or MariaDB server version you are running on your system, you will need to use different commands to recover the root password.
You can find your server version by issuing the following command:
mysql --version
If you have MySQL installed in your system the output will look something like this:
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
Or output like this for MariaDB:
mysql Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Be sure to make a note of which version of MySQL or MariaDB you’re running.
Follow these steps to reset your MySQL/MariaDB root password:
To change the root password first, you need to stop the MySQL server. To do so type the following command:
sudo systemctl stop mysql
Start the database server without loading the grant tables:
sudo mysqld_safe --skip-grant-tables &
The ampersand &
at the end of the command above will cause the program to run in the background , so you can continue to use the shell.
When the --skip-grant-tables
option is used, anyone can to connect to the database server without a password and with all privileges granted.
Now you can connect to the database server as the root user:
mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;
ALTER USER
statement doesn’t work for you, try to modify the user table directly:UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
FLUSH PRIVILEGES;
In both cases if all goes well, you should see the following output:
Query OK, 0 rows affected (0.00 sec)
#mysql