If you are a system administrator, you may have forgotten your mysql root password, don't worry, it happens to all of us. In this post, I will show you how to recover mysql root password for Linux system.
First, by running the following command to identify the version of MySQL or MariaDB, because we will use different commands to recover the root passwords:
$ mysql --version
After you've known your version of MySQL or MariaDB, following the steps to recover the root passowrd.
1. Stop the MySQL or MariaDB Service
$ systemctl stop mysql
2. Start the MySQL or MariaDB server without loading the grant tables
$ 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 MySQL or MariaDB server without a password and with all privileges granted.
3. Log in to the MySQL Server
$ mysql -u root
4. Set a new root password
Be careful, you need to run different commands to reset the root password according to the version, run the following commands if you run MySQL 5.7.6 and later or MariaDB 10.1.20 and later, replace NEW_PASSWORD with your own new password:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';
mysql> FLUSH PRIVILEGES;
Run the following commands if you have MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NEW_PASSWORD');
mysql> FLUSH PRIVILEGES;
5. Stop and Start the database server normally
$ mysqladmin -u root -p shutdown
You will be prompted to enter the new root password, then start the service normally by running the following if you are using MySQL:
$ systemctl start mysql
Run the following commands if you are using MariaDB:
$ systemctl start mariadb
That's all and you have recovered the root password of MySQL or MariaDB successfully.