How to reset MariaDB root password
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 S
ystemd
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.