How to Change/Reset MySQL or MariaDB root Password

Filed Under: SQL
Change Reset Mysql Mariadb Root Password

Recently, I changed my website MySQL root user password. Then I thought what will happen if I forget the MySQL root password?

Is there an easy way to reset the MySQL or MariaDB root password?

I looked through some online tutorials, but none of them seem to be complete enough to differentiate between changing the password and resetting the password. They seemed lacking the details about the MySQL table where user passwords are stored and in which columns.

In this tutorial, we will learn the following topics.

  1. How to Change MySQL/MariaDB root Password
  2. How to Reset MySQL/MariaDB root Password

I will try to make it as complete as possible, and hopefully after reading this, you will get through this task easily without the need of any further help.

What is the difference between changing and resetting the password?

If you know the root password, you can connect to the database as root user and then change the password very easily. You can change the root password as well as any other user password.

If you have forgotten the root password, means you can’t connect to the MySQL server as root user. The root user has the highest privileges and you can’t change its password through other accounts. We have to perform some additional steps in this case to reset the MySQL root password.

Steps for MySQL and MariaDB are Same?

MariaDB is built on top of MySQL. It’s very popular for web hosting requirements. In fact, JournalDev and all my websites are using MariaDB database. Any command that works for MySQL will work for MariaDB as well.

The only adjustment in the commands you might have to make is to stop and start of MySQL server. I am using Ubuntu for this tutorial and I use systemctl to start/stop services. You can also use /etc/init.d/mysql to perform the same operations.

If you are on Windows OS, then please use mysqld or mysqladmin from the command prompt to start or stop the MySQL server. They are located in the MySQL installation bin folder.

How to Change MySQL or MariaDB root Password

I am using MariaDB database, we can use –version option to find out its version.

# mariadb --version
mariadb  Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
# 

1. Connect to MySQL as root user

# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

2. Change the password and authentication_string value in mysql.user table

MySQL user passwords are stored in mysql.user table password and authentication_string columns in the encrypted form. We can use the PASSWORD() function to convert a plain-text string to the encrypted value and set these columns.

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select password, authentication_string from user where User = 'root' AND Host = 'localhost';
+-------------------------------------------+-------------------------------------------+
| password                                  | authentication_string                     |
+-------------------------------------------+-------------------------------------------+
| *E510A8BC6807F8BF4913D893620792C432FCBA5B | *E510A8BC6807F8BF4913D893620792C432FCBA5B |
+-------------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [mysql]> UPDATE user SET authentication_string = PASSWORD('qwerty2021') WHERE User = 'root' AND Host = 'localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> UPDATE user SET password = PASSWORD('qwerty2021') WHERE User = 'root' AND Host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> select password, authentication_string from user where User = 'root' AND Host = 'localhost';
+-------------------------------------------+-------------------------------------------+
| password                                  | authentication_string                     |
+-------------------------------------------+-------------------------------------------+
| *6F168491676C70E51CB8D0F14D6B581D1322A77A | *6F168491676C70E51CB8D0F14D6B581D1322A77A |
+-------------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

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

MariaDB [mysql]> exit
Bye
root@localhost:~# 

Let’s understand what’s happening in the above queries.

  • First of all, we are changing the database to ‘mysql’
  • Then, we are setting ‘authentication_string’ and ‘password’ column values for ‘root’@’localhost’ user with the new password.
  • Then we are reloading the grant tables using the FLUSH PRIVILEGES command.
  • Then quit the MySQL session. The root password has been changed successfully.

NOTE: I tried to use ALTER USER command to change the root password, but it didn’t work.

MariaDB [mysql]> ALTER USER root@localhost IDENTIFIED BY 'qwerty2022';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER root@localhost IDENTIFIED BY 'qwerty2022'' at line 1
MariaDB [mysql]>

3. Verify the root user login using new password

# mysql -uroot -pqwerty2021
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

That’s it. We have successfully changed the MySQL/MariaDB user password.

How to Reset the MySQL/MariaDB root Password

If you have forgotten the root password, then we need to perform one extra step so that we can login to the MySQL terminal without providing the password.

1. Stop the MySQL Server

# systemctl stop mysql

You can also run systemctl stop mariadb, the effect will be the same.

2. Starting the MySQL Server without permission checking setting

The idea is to start the MySQL server without loading the grant tables information, so that we can login as root user without providing password.

It’s a security risk to run MySQL server like this, so it must be done briefly and shut down immediately after resetting the root password.

We can start the MySQL server in safe mode and pass –skip-grant-tables option to skip loading grant tables that store the user privileges settings.

# sudo mysqld_safe --skip-grant-tables --skip-networking &
[1] 11734
root@localhost:~# 200427 20:05:40 mysqld_safe Logging to syslog.
200427 20:05:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
#

It is important to run the command ending with & so that it runs in the background. I am also passing –skip-networking option to skip networking that prevents other clients from connecting to the MySQL server.

3. Connect to MySQL Server as root without passing password

# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Notice that we are not providing root password, but still we are able to connect to MySQL server.

4. Reset the root password in mysql.user table

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> UPDATE user SET authentication_string = PASSWORD('qwerty2022') WHERE User = 'root' AND Host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> UPDATE user SET password = PASSWORD('qwerty2022') WHERE User = 'root' AND Host = 'localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

MariaDB [mysql]> exit
Bye
# 
Mysql Reset Root Password
Mysql Reset Root Password

5. Stop and Start the MySQL server

First of all, we will kill the running MySQL server. The PID is present in the /var/run/mysqld/mysqld.pid file.

# cat /var/run/mysqld/mysqld.pid
11891
# sudo kill 11891
# 

Now, start the MySQL server in the normal mode.

# systemctl start mysql

6. Verify by logging as root user with new password

# mysql -uroot -pqwerty2022
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

If you will try to login as root without password, it will throw “Access denied” error.

# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# 

Conclusion

MySQL root user is just like any other user. However, changing or resetting its password is a bit tricky because it’s the super user and we can’t change root password from another user login.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages