How to reset MariaDB root password

Updated   Read time 1 min 50 sec(s)   (1941 views).  Database  

Forgetting passwords happens to the best of us. If you forget or lose the root password to your MariaDB database, you can still gain access and reset the password if you have access to the server.

Step 1 — Stop MariaDB service

First of all, you need to stop MariaDB service on your system. You can do this using the following command.

  • For SysVinit systems 
    # service mariadb stop
    or 
    # /etc/init.d/mariadb stop
  • For Systemd systems 
    # systemctl stop mariadb.service

Step 2 — Start MariaDB in Safe Mode

Now connect to MariaDB in safe more using skip grant and run this command in background.

# mysqld_safe --skip-grant-tables &

Step 3 — Login to MariaDB and Change Password

After starting MariaDB in safe more connect to MariaDB with root user and without any password. After that run following command to change the root user password.

# mysql -u root
MariaDB [(none)]> use mysql;
MariaDB [mysql]> UPDATE user SET authentication_string = password("NeW_pAsSw0rD") where User='root';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> quit;

Step 4 — Stop and Restart MariaDB

After successfully changing the 'root' password, stop and restart the service in normal mode using following commands:

  • For SysVinit systems: 
    # service mariadb stop
# service mariadb start
    or 
    # /etc/init.d/mariadb stop
# /etc/init.d/mariadb start
  • For Systemd systems 
    # systemctl stop mariadb.service
# systemctl start mariadb.service

Step 5 — Login with New password

At this stage you have successfully updated you root MariaDB password, Lets connect to MariaDB using new password.

# mysql -u root -p

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

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

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

MariaDB [(none)]>

You have restored administrative access to your MariaDB server. Make sure the new password you chose is strong and secure and keep it in a safe place.

For more information on user management, authentication mechanisms, or ways of resetting database password for other version of MariaDB, please refer to the official MariaDB documentation.