Posted by tony on September 28, 2017
How to Change the Root Password on MariaDB
If you’re a sysadmin, you know how scary losing a root password can be. Don’t worry, though. If you lose or forget the password to your MariaDB or MySQL database, you can reset it in a few easy steps.
First, you’ll need a few things:
- A Linux server running MariaDB
- A sudo-enabled user account for the server
Stop the Database Server
In order to reset the root password, you’ll need to stop the database service. Make sure all users have saved their work, then run this command to shutdown the database:
$ sudo systemctl stop mariadb
Note: If the above command fails with systemctl: command not found, you may be running an older system or a distribution that doesn’t support systemd yet. Don’t worry, though. You can run this command instead to stop the database:
$ sudo service mariadb stop
After you’ve stopped the database, you’ll need to access it manually to reset the MariaDB password.
Restart the Database Server in Safe Mode
MySQL (and MariaDB, by extension) have a safe mode whereby you can log in to the database without checking permissions. You can enable this by running the following command:
$ sudo mysqld_safe --skip-grant-tables --skip-networking &
There are a couple of different parts to this command, so we’ll go through them in order:
- mysqld_safe refers to the “safe mode” for MySQL and MariaDB.
- --skip-grant-tables tells the database to skip loading the tables that contain user permission information. This means you won’t need a password to log in, because the database has not loaded information about users and passwords.
- --skip-networking means that no one else will be able to access the database while you are resetting the password. While this parameter is not necessary, it is useful because running the database in skip permissions mode can be a security risk.
- The & at the end tells the command to run in the background. That means the command will execute and you’ll get a new terminal prompt on the next line. Alternatively, you could open a new shell session while MariaDB runs in this one.
After executing this command, you should be able to connect to the database as the root user:
$ mysql -u root
Since we’ve disabled permission checking, the MariaDB prompt should appear.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
Good news. We’ve accessed the database and have root privileges. Now we can reset the MariaDB password.
Change the Root Password
Once you’re at the database prompt, you’ll need to go through a couple of steps to reset the root password in MariaDB.
- Run FLUSH PRIVILEGES; to reload the grant tables we disabled on login. This will reload the user and password settings so you can edit them.
- To edit the password for the root user, you can use the update user command. Here are the steps you’ll need to take:
- use mysql;
- update user set password=PASSWORD(“astrongpasswordhere”) where User=’root’;
- flush privileges;
This sequence of commands updates the root user’s profile to use the new password, reloads the permissions table, and then disconnects from the database. At this point, the MariaDB root password should be reset. The hard part is over; now you just need to restart the server and test that it works.
Restart the Database Server
Once you’ve reset the password successfully, you’ll need to bring the server back up and then try to log into it as root to make sure the new password has taken effect.
Bring up the server the same way you brought it down, using one of the two commands below depending on your Linux installation:
$ sudo systemctl start mariadb
$ sudo service mariadb start
Then you can run mysql -u root -p and it should prompt you for the new password. Enter the password you set in the previous steps, and you should be back into your database.
Misplacing or improperly configuring the root password on MariaDB doesn’t have to be the end of the world. Using these simple steps, you can get your database up and running again.
Have we missed something? Have a question not covered here? Be sure to let us know in the comments.