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:
- You need shell access to the Linux server console and root privileges (or sudo account);
- You need to stop the MySQL daemon and start it with a special key;
- Reset the root password from the MySQL command line;
- 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
- How to Connect to Exchange Online Using PowerShell? - September 26, 2020
- Configuring Live Migration in Hyper-V - September 26, 2020
- RDP Error: Remote Desktop Can’t Find the Computer - September 17, 2020
