Tutorial

How To Reset Your MySQL or MariaDB Root Password

Updated on May 5, 2020
Default avatar

By Mateusz Papiernik

Software Engineer, CTO @Makimo

How To Reset Your MySQL or MariaDB Root Password
Not using Ubuntu 16.04?Choose a different version or distribution.
Ubuntu 16.04

Introduction

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

This tutorial will cover how to reset the root password for older and newer versions of MySQL and MariaDB.

Prerequisites

To recover your root MySQL/MariaDB password, you will need:

  • Access to the Linux server running MySQL or MariaDB with a sudo user.

Step 1 — Identifying the Database Version

Most modern Linux distributions ship with either MySQL or MariaDB, a popular drop-in replacement which is fully compatible with MySQL. Depending on the database used and its version, you’ll need to use different commands to recover the root password.

You can check your version with the following command:

  1. mysql --version

You’ll see some output like this with MySQL:

MySQL output
mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

Or output like this for MariaDB:

MariaDB output
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Make note of which database and which version you’re running, as you’ll use them later. Next, you need to stop the database so you can access it manually.

Step 2 — Stopping the Database Server

To change the root password, you have to shut down the database server beforehand.

You can do that for MySQL with:

  1. sudo systemctl stop mysql

And for MariaDB wtih:

  1. sudo systemctl stop mariadb

After the database server is stopped, you’ll access it manually to reset the root password.

Step 3 — Restarting the Database Server Without Permission Checking

If you run MySQL and MariaDB without loading information about user privileges, it will allow you to access the database command line with root privileges without providing a password. This will allow you to gain access to the database without knowing it.

To do this, you need to stop the database from loading the grant tables, which store user privilege information. Because this is a bit of a security risk, you should also skip networking as well to prevent other clients from connecting.

Start the database without loading the grant tables or enabling networking:

  1. sudo mysqld_safe --skip-grant-tables --skip-networking &

The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.

Now you can connect to the database as the root user, which should not ask for a password.

  1. mysql -u root

You’ll immediately see a database shell prompt instead.

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

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

MariaDB [(none)]>

Now that you have root access, you can change the root password.

Step 4 — Changing the Root Password

One simple way to change the root password for modern versions of MySQL is using the ALTER USER command. However, this command won’t work right now because the grant tables aren’t loaded.

Let’s tell the database server to reload the grant tables by issuing the FLUSH PRIVILEGES command.

  1. FLUSH PRIVILEGES;

Now we can actually change the root password.

For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command.

  1. ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:

  1. SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Make sure to replace new_password with your new password of choice.

Note: If the ALTER USER command doesn’t work, it’s usually indicative of a bigger problem. However, you can try UPDATE ... SET to reset the root password instead.

  1. UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';

Remember to reload the grant tables after this.

In either case, you should see confirmation that the command has been successfully executed.

Output
Query OK, 0 rows affected (0.00 sec)

The password has been changed, so you can now stop the manual instance of the database server and restart it as it was before.

Step 5 — Restart the Database Server Normally

First, stop the instance of the database server that you started manually in Step 3. This command searches for the PID, or process ID, of MySQL or MariaDB process and sends SIGTERM to tell it to exit smoothly after performing clean-up operations. You can learn more in this Linux process management tutorial.

For MySQL, use:

  1. sudo kill `cat /var/run/mysqld/mysqld.pid`

For MariaDB, use:

  1. sudo kill `/var/run/mariadb/mariadb.pid`

Then, restart the service using systemctl.

For MySQL, use:

  1. sudo systemctl start mysql

For MariaDB, use:

  1. sudo systemctl start mariadb

Now you can confirm that the new password has been applied correctly by running:

  1. mysql -u root -p

The command should now prompt for the newly assigned password. Enter it, and you should gain access to the database prompt as expected.

Conclusion

You now have administrative access to the MySQL or MariaDB server restored. Make sure the new root password you choose is strong and secure and keep it in safe place.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors
Default avatar

Software Engineer, CTO @Makimo

Creating bespoke software ◦ CTO & co-founder at Makimo. I’m a software enginner & a geek. I like making impossible things possible. And I need tea.


Default avatar

staff technical writer

hi! i write do.co/docs now, but i used to be the senior tech editor publishing tutorials here in the community.


Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

If you get the following error after running the mysqld_safe command…

2017-02-10T17:05:44.872874Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-02-10T17:05:44.874547Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

Try the following

mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld

Tried on Ubuntu 16.04

I dunno, this didn’t work for me.

For Step 2, the server is using MariaDB but none of the mariadb-specific commands worked. Also it doesn’t know what ‘systemctl’ is. I figured out though that I could stop the database with ‘sudo service mysql stop’ - that definitely stopped the service.

In Step 3, ‘sudo mysqld_safe --skip-grant-tables --skip-networking &’ did something, but it didn’t kick me back to the command line. Although I didn’t have to ctrl-c it either, just when I tried typing the next bit was when the new line appeared. Don’t know if that’s a problem or not, I’m just not used to command line stuff.

In Step 4, ‘FLUSH PRIVELEGES;’ said the query was ok, but 0 rows affected. Then again, I don’t know if this is supposed to affect any rows. Next, the recommended way of changing the password did not work. I used the method where it says it’s indicative of bigger problems (whatever that means), and that did work and affect 1 row. Or it didn’t work because the guide says it should say 0 rows affected?

For Step 5, neither of the sudo kill commands worked. And of course the systemctl commands don’t work to restart the database, so I did ‘sudo service start mysql’. It said MariaDB started.

Then I tried the final step with ‘mysql -u root -p’ but it says the password is wrong. So I don’t know what I changed (if anything) but yeah, the guide didn’t work for me. At least I don’t have the same problems as NUCUTA?

Edit: I managed to change the password following Rackspace’s guide (https://support.rackspace.com/how-to/mysql-resetting-a-lost-mysql-root-password/). It’s almost the same, don’t know if the different order of mysql commands made a difference. I still used my own commands to start/stop the mysql service though - ‘sudo service mysql start’ or ‘sudo service mysql stop’

I’m getting the following error

Can't connect to local server through socket '/opt/bitnami/mariadb/tmp/mysql.sock' (111)

“Was this helpful?” …it was lifesaving mate! Cheers.

Boa Noite Senhoras e senhores da DigitalOcean [representados aqui pelo Mateusz (meu xará!], Muito mas muito obrigado mesmo, por vocês terem aparecido para mim, ótimos tutoriais!! me salvou! vamos que vamos!! saúde, sucesso, paz e amor a todas e a todos!

Doesn’t work.

“The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.”

No it doesn’t. Using latest MariaDB on CentOS8, it’s not in the background. The prompt is stuck in the window.

Also the “skin grant tables” doesn’t help. It doesn’t allow me to change the password.

After I tried to install Plesk (which failed) on my VPS my mysql root password was deleted. Instead an admin account was created with an unknown password. By creating a new password for the admin account I was able to connect to my database.

The op need to put the supported Ubuntu version in the title, this article is for v16.04, it doesn’t work on v18.04.

also this happened $ sudo kill cat /var/run/mysqld/mysqld.pid cat: /var/run/mysqld/mysqld.pid: No such file or directory

my database is not restating back, why?

$ sudo systemctl start mysql Job for mysql.service failed because the control process exited with error code. See “systemctl status mysql.service” and “journalctl -xe” for details.

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel