reset root password mysql

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.

mysql change root password

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

mysql reset root password

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

You may also like:

Complete Arch Linux Install Guide Among the wide variety of GNU/Linux distributions, there is one that combines almost all advantages and disadvantages of these systems (of course, the...
Using Chown and Chmod Commands on Linux Managing access to files in GNU/Linux distributions is a basic procedure in the administration process. The chown and chmod commands are closely relat...
Linux Alias Command The GNU/Linux terminal (namely, the Bash shell) copes with the tasks of automated system management. A user is good enough to know the shell commands ...
How to Use Bash Sort Command? One of the most useful commands in Bash — sort — is also one of the most underrated. It is engaged in sorting incoming data (whether it is a file or t...
Windows Subsystem for Linux Last time Microsoft has been actively involved in supporting the Linux kernel and GNU software components. It all started with financial assistance, a...

Add Your Comment