How to Reset MySQL Root Password in Ubuntu

For any reason, you may have forgotten the MySQL root password and need to reset it. You can reset it if you have access to the server with sudo user privileges. Follow this simple guide and know how to reset MySQL root user password in Ubuntu.

In this guide, we use Ubuntu version 20.04, but it should also work with other similar Linux distributions such as future versions of Ubuntu and CentOS. Also, we cover the commands for resetting the root password of MariaDB as well.

Check unix_socket Authentication Plugin

It’s important that you know the version of your database server as the commands can vary depending on it. You can use any one of these commands to know the version:

> mysql --version

> mariadb --version

If you have the unix_socket authentication plugin active for the MySQL root user, then you should be able to log in to MariaDB or MySQL server using the operating system’s sudo user credentials. Once you are logged in to the command line client, you can simply run an SQL query to set or change the root user password.

To check if unix_socket is active for the root user, run the following command:

For MySQL:

> sudo mysql

For MariaDB:

> sudo mariadb

If you don’t see an error “Access denied for user ‘root’@’localhost’ (using password: NO)” and are able to log in, then use the following command to change the root password:

(none)> use mysql;

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('YOURNEWPASSWORD');

mysql> FLUSH PRIVILEGES;

mysql> EXIT;

Now, you should be able to log in using the command:

For MySQL:

> mysql -u root -p

For MariaDB:

> mariadb -u root -p

In case you see an error “Access denied for user ‘root’@’localhost’ (using password: NO)”, then the unix_socket is not active, then you can continue to follow the instructions below to still be able to log in to MariaDB or MySQL.

First of all, make sure you are logged in with a sudo user and have a console window opened. Now, we need to stop MySQL service in case it is running.

> sudo service mysql stop

For MariaDB, you can run this command instead:

> sudo service mariadb stop

Create MySQL Service Directory

Next, we need to make a service directory that is used by the MySQL process to access and store the socket file.

> sudo mkdir /var/run/mysqld

Now, we need to give “mysql” user ownership of this service directory so it can write to it.

> sudo chown mysql: /var/run/mysqld

Start MySQL Without Grant Tables

Next, start the service manually using the “mysqld_safe” command. Here, we start it without permission checks or networking. Using the “–skip-grant-tables” flag, anyone can log in to the database server with all privileges and without the password.

> sudo mysqld_safe --skip-grant-tables --skip-networking &

For MariaDB 10.4.6 or later, you may also use this command instead:

> sudo mariadbd-safe --skip-grant-tables --skip-networking &

The & ampersand which closes the command above allows the program to operate in the background and grants us access to the database server shell or command line client where we can run SQL queries to change the password.

Now, leave this console window open to keep the process running and open another console. Here, log in to MySQL without the password using this command:

> mysql -uroot mysql

For MariaDB, you would run:

> mariadb -uroot mysql

Reset the Root Password

After login, you can run the following command to reset the password:

For newer versions of MySQL (5.7.6 or later) and MariaDB (10.4 or later), you can use the following commands:

mysql> FLUSH PRIVILEGES;

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('YOURNEWPASSWORD');

mysql> FLUSH PRIVILEGES;

In the above commands, we first need to run “FLUSH PRIVILEGES;” to tell the database server to reload the grant tables.

For older versions of MySQL (5.7.5 or before) and MariaDB (10.3 or before), you may also use the following command instead:

> mysql> UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';

Output:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

This changes the password. Now, you can exit from it.

mysql> EXIT;

Stop And Start Service Normally

Next, turn off the running service.

> sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown

In case you see an error “connect to server at ‘localhost’ failed” when running the above command, then you can provide the sudo user password if prompted, also the newly set MySQL root password as follows:

> mysqladmin -S /var/run/mysqld/mysqld.sock shutdown -u root -p

For MariaDB 10.4.6 or later, you can run any one of these commands instead:

> sudo mariadb-admin -S /var/run/mysqld/mysqld.sock shutdown

> mariadb-admin -S /var/run/mysqld/mysqld.sock shutdown -u root -p

Note that if you haven’t started MySQL or MariaDB without grant tables in the first place (the steps in Start MySQL Without Grant Tables), then you can skip the above step.

And finally, start the service normally.

> sudo service mysql start

For MariaDB, you would start the service as follows:

> sudo service mariadb start

To verify if the root password has changed, you can log in to your database server with the new password using the command:

> mysql -u root -p

> mariadb -u root -p

Now, specify the new password that you set above and press ENTER to log in to your MySQL or MariaDB database server.

In conclusion, there are a few steps involved depending on the version of the database server. Using this guide, you get the idea of how you can change or reset the root password. If you still face issues when resetting or changing the root password, then you can state the issue and reach out to us through our contact form.