How to Reset Root Password in MySQL and MariaDB?

In older versions of MySQL, if you forgot or lost the root password for MySQL, you could view the current password in the my.cnf file. In modern versions of MySQL and MariaDB this trick won’t work. Let’s take a look at how to reset the root password on MySQL and MariaDB on Linux distros (CentOS 7 is used in this example).

The way to reset root password in MySQL is pretty simple:

1. You need shell access to the Linux server console and root privileges (or sudo account);
2. You need to stop the MySQL daemon and start it with a special key;
3. Reset the root password from the MySQL command line;
4. Restart MySQL service.

Connect to the Linux server console (for example, via SSH) and display the current MySQL version number:

$mysql –version

The system will display a message with the db version number. For example,

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

or

mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Now you need to stop the database daemon. For MySQL:

$sudo systemctl stop mysql

For MariaDB:

$sudo systemctl stop mariadb

Make sure the mysql process (mariadb) is stopped:

$ps aux | grep mysql

If there are still mysql processes running, you need to force them to stop using the command:

$sudo killall mysqld

Now you need to run MySQL in the safe mode without loading the grant tables and with disabled network access (for security reasons):

$mysqld_safe --skip-grant-tables --skip-networking

Now you can connect to the server with a mysql client without a password:

$mysql -u root

In the modern MySQL versions the ALTER USER command is used to change the password. But it doesn’t work if the privilege table is disabled. Reload the privilege table:

mysql> FLUSH PRIVILEGES;

Now you need to reset the root password. In MySQL 5.7.6 and MariaDB 10.1.20 and newer versions, use the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewP@ssw0rd';

In previous versions of MySQL and MariaDB use the command:

mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewP@ssw0rd');

You can also reset the root password with the following commands:

mysql> use mysql; 
mysql> update user set password=PASSWORD("NewP@ssw0rd") where User='root'; 
mysql> flush privileges; 
mysql> quit

mysql -u root 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 39 
MariaDB [(none)]> use mysql; 
Database changed 
MariaDB [mysql]> update user set password=PASSWORD("NewP@ssw0rd") where User='root'; 
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

MariaDB [mysql]> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> quit 
Bye

It remains to restart the MySQL server in normal mode with the commands:

$sudo killall mysqld

$sudo service mysqld start

Now you can connect to the MySQL with the new password:

$mysql -u root -p

Enter password:

Welcome to the MySQL monitor