How to Reset MySQL or MariaDB Root Password

This post shows steps for students and new users to reset or change MySQL or MariaDB passwords when using Ubuntu Linux. Using MySQL or MariaDB, one can easily create and reset the root passwords via its command terminal or shell.

If you have forgotten the root password, the steps below will show you how to reset it easily without deleting and reinstalling the database server.

Forgetting your desktop password is one thing. However, forgetting the root password to your MySQL database in production is another thing that is not fun. If you did, this post shows you how to recover the root password.

Also, for students and new users learning Linux, Ubuntu Linux is the easiest place to start learning. Ubuntu is the modern, open-source Linux operating system for desktops, servers, and other devices.

When you’re ready to reset the root password for MySQL or MariaDB, follow the steps below.

Depending on your MySQL or MariaDB server version, you will need to use different commands to recover the root password.

So, run the commands below to get the version of MySQL or MariaDB.

mysql --version

Be sure to take notes of the version of the MySQL or MariaDB database server you’re running. You’ll need them later in the post.

Mariadb# mysql  Ver 15.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

............................

MySQL# mysql  Ver 8.0.26-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

MySQL and MariaDB using root passwords

MariaDB and MySQL servers have the root user set to use the auth_socket authentication method by default.

The auth_socket plugin authenticates users that connect from the local host through the Unix socket file. You can’t authenticate as a root by providing a password.

However, you can turn off the auth_socket plugin using the SQL commands below. If you do, you must type the root password to log on.

MySQL:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'type_strong_password_here';

MariaDB:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User='root';

Now you can reset the forgotten password below.

How to reset MySQL or MariaDB root password

First, stop the database server to reset the root password for either MySQL or MariaDB. You can do that by running the commands below.

You can do that for MySQL or MariaDB.

sudo systemctl stop mysql
sudo systemctl stop mariadb

After that, run the commands below to start MySQL in safe mode by bypassing the standard authentication process without loading the grant tables:

sudo mysqld_safe --skip-grant-tables &

When the --skip-grant-tables option is used, anyone can connect to the database server without a password and with all privileges granted. However, this can only happen if you connect from the local server terminal console.

You may need to run these commands for MySQL servers to get passwords for some weird errors.

Then run the commands below to create a new mysqld directory and make mysql user owner.

sudo mkdir /var/run/mysqld/
sudo chown mysql /var/run/mysqld

While on the local server, run the commands below to log on as root to the database server.

mysql -u root

Next, Run the following commands if you run MySQL 5.7.6 and later or MariaDB 10.1.20 and later:

UPDATE mysql.user SET authentication_string = PASSWORD('type_new_password_here')
 WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

If the SQL commands above do not work, try the one below.

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'type_new_password_here';
FLUSH PRIVILEGES;

If you’re running earlier database server versions, run the commands below to reset the password.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('type_new_password_here');
FLUSH PRIVILEGES;

You should see the following output without errors if all goes well above.

Query OK, 0 rows affected (0.00 sec)

Now that the root password has been reset, you can stop the server normally by running the commands below.

mysqladmin -u root -p shutdown

You will be prompted for the new password just created above. Type it to continue with the shutdown.

Next, you can start up either server using the commands below.

sudo systemctl start mysql
sudo systemctl start mariadb

You can now try to log in with the newly created password above.

sudo mysql -u root -p

If it works, then you’re all set.

Conclusion:

• Resetting the root password for MySQL or MariaDB is a straightforward process that can be completed without reinstalling the database server.
• Always take note of your MySQL or MariaDB version as the steps may vary slightly based on the version you are using.
• The default authentication method for root users is auth_socket, and switching to mysql_native_password allows password authentication.
• Always ensure that you execute commands with caution, especially when working in safe mode with --skip-grant-tables.
• Regularly updating your passwords and managing user privileges can contribute to the security of your database.
• If you encounter issues, consult the official documentation or community forums for guidance.