How to reset a root password in MariaDB on Linux

You’ve lost the root password for your MariaDB database – now what do you do? You can’t recover your existing password, but you can get into your database to create a new root password. You just need root privileges on the Linux server that is running MariaDB. If you’re running Debian Linux (and its derivatives, such as Ubuntu and Mint) you can use a back door trick to reset your password.

First, stop your database if it’s running. On Red Hat Linux and its various offshoots, such as Fedora, CentOS, and Scientific Linux, use the systemctl command:

# systemctl stop mariadb.service

On Debian, Ubuntu, Linux Mint, and other Debian derivatives, stop it this way:

# /etc/init.d/mysql stop

Next, restart MariaDB with unrestricted access, so you don’t need a password to log in. The mysqld_safe script is the safest way to start MariaDB. It logs errors, automatically restarts the database if MariaDB dies unexpectedly, flushes caches, and sets environment variables. Use it with the option --skip-grant-tables to start the server with no user restrictions, so it’s wide open:

# mysqld_safe --skip-grant-tables --skip-networking &
[1] 22411
# 140603 11:57:04 mysqld_safe Logging to syslog.
140603 11:57:04 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

The --skip-networking option is insurance against someone sneaking in over the network while your database is open to everyone. Don’t use this if you’re logging in remotely.

Now you can reset the root password by using the mysql command shell. Log in to your database as root, with no password needed, select the mysql database, reset the MariaDB root password, and then exit:

$ mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("new-password") where User='root';
mysql> flush privileges;
mysql> exit

Now try logging in with your new password:

$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.37-MariaDB-0ubuntu0.14.04.1 (Ubuntu)

Routine password changes

Now that you’re back in, the next time you want to change the root password use this one-liner:

$ mysqladmin -u root -p 'old-password' password 'new-password'

You can do this for any user. And just who are your database users anyway? The user table in the mysql database contains all users with access to your MariaDB server. Log into your mysql shell as root, select the mysql database, and take a look at your users:

$ mysql -u root -p
password:

MariaDB [(none)]> use mysql;
Database changed

MariaDB [mysql]> SELECT user, host, password FROM user;
+--------------+------------+-------------------------------------------+
| user         | host       | password                                  |
+--------------+------------+-------------------------------------------+
| root         | localhost  | *F6FE8C5834229895BF8EE752077629654CB5DC17 |
| root         | virtubuntu | *F6FE8C5834229895BF8EE752077629654CB5DC17 |
| root         | 127.0.0.1  | *F6FE8C5834229895BF8EE752077629654CB5DC17 |
| root         | ::1        | *F6FE8C5834229895BF8EE752077629654CB5DC17 |
| debian-sys-maint | localhost  | *21B2FE94E7B81E6A1100BF426B0996E877D5870C |
+--------------+------------+-------------------------------------------+
5 rows in set (0.00 sec)

The passwords are encrypted. Who are all those root users? All of them are necessary, so don’t delete them. (Don’t muck with anything in the mysql database unless you really really know what you’re doing, as it controls user access and lot of core functionality.) All Linux distributions have multiple MariaDB root users for different host addresses: localhost, the server’s hostname, the IPv4 address, and sometimes the IPv6 address. This is a simple workaround for avoiding DNS hassles.

Debian has a special MariaDB user, debian-sys-maint, that has similar permissions to the root user, and is used in init scripts to avoid giving up the root password for startup and shutdown. It also provides something akin to a back door to MariaDB because the password is stored in clear text in /etc/mysql/debian.cnf:

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = c4BblmobrL98VcWK

If you have the permissions set correctly on this file, only root can read it. If not, you have a big security hole. But if you lose your MariaDB root password on a Debian, Ubuntu, or Mint system, you can log in to your database as the debian-sys-maint user and reset the root password:

$ mysql -u debian-sys-maint -p
Enter password:
MariaDB [mysql]> update user set password=PASSWORD("new-password") where User='root';