How to Reset MariaDB Root Password

How to Reset MariaDB Root Password

We’ll show you, how to reset MariaDB root password.  Did you forget your MariaDB root password? Do not worry, there is an easy way to reset your password on MariaDB in less than 5 minutes. Please note that you need to have root access to your server to be able to change the password of MariaDB. Resetting your MariaDB root password on your Linux server is an easy task if you carefully follow our tutorial below.

1. Login to your server via SSH

Log in to your Linux virtual server via SSH as user root and stop the running MariaDB daemon:

# service mysql stop
Shutting down MySQL. SUCCESS!

2. Start MariaDB in Safe Mode

Start MariaDB in safe mode with ‘skip-grant-tables’ option. Grant tables store the passwords, so using this option you can login to MariaDB without password:

# mysqld_safe --skip-grant-tables &
[1] 14721

131108 19:06:41 mysqld_safe Logging to '/var/lib/mysql/domain.com.err'.
131108 19:06:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

3. Login to MariaDB

Now, you are able to log in to MariaDB without a password:

# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.33a-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

4. Reset MariaDB Root Password

Once you are logged in, you can reset your MariaDB root password by executing the following SQL commands:

MariaDB [(none)]> use mysql;
MariaDB [(none)]> update user set password=PASSWORD("NEWPASSWORD") where User='root';

Don’t forget to replace ‘NEWPASSWORD’ with your actual password.

5. Flush privileges

Now, flush the privileges and exit from the MariaDB server:

MariaDB > flush privileges;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [(none)]> exit; 
Bye

6. Restart MariaDB

Finally, restart your MariaDB server:

# service mysql restart

Done. Your old MariaDB root password is changed and you have a new password.

Now you can log in to MariaDB using the new password:

# mysql -u root -p

Reset MariaDB Root PasswordIf you use one of our MariaDB Optimized Hosting, you don’t have to reset your MariaDB root password, simply ask our admins, sit back and relax. Our admins will reset MariaDB root password for you immediately.

PS. If you liked this, on how to reset MariaDB root password, post please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.

13 thoughts on “How to Reset MariaDB Root Password”

  1. Brilliant! Simple and direct. Well done. Thank you.

  2. It works perfect. Thanks a lot!

  3. Thanks a lot. This was really helpful!!!

  4. The prompt is wrong, after you execute `use mysql` it’s “MariaDB [mysql]>”

    • That is normal. MariaDB is a drop in replacement for MySQL.

  5. Thanks a lot. simple and detailed steps :)

  6. Hello, sorry to say that but … for me this not working. For some reason when you restart the mariadb not starting.

    • Our tutorial is tested and working without a problem. Have you made sure that you’ve followed the instructions correctly? Please try doing all of the described steps again and be more careful.

  7. service mysql restart does not work unless I kill the mysqld_safe proccess.
    After kill it will work fine. Thanks

  8. Great tutorial! Worked fine!

  9. BRILLIANT! you are a REAL problem solver thanks

  10. Does not work on a fresh Debian Stretch 9.6.0 installation:

    Last login: Mon Dec 10 16:33:28 2018 from 192.168.178.184
    [email protected]:~#
    [email protected]:~#
    [email protected]:~# service mysql stop
    [email protected]:~# mysqld_safe –skip-grant-tables &
    [1] 837
    [email protected]:~# 181210 16:35:39 mysqld_safe Logging to syslog.
    181210 16:35:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

    [email protected]:~# mysql -u root
    Welcome to the MariaDB monitor. Commands end with ; or \g.
    Your MariaDB connection id is 2
    Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

    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)]> 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 password=PASSWORD(“123456”) where User=’root’;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1 Changed: 0 Warnings: 0

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

    MariaDB [mysql]> exit;
    Bye
    [email protected]:~# service mysql stop
    [email protected]:~# service mysql start
    Job for mariadb.service failed because a timeout was exceeded.
    See “systemctl status mariadb.service” and “journalctl -xe” for details.
    [email protected]:~#
    [email protected]:~#

    • Please provide us with the output of ‘systemctl status mariadb.service’ command.

Leave a Comment

To prove you are human please solve the following *