This post shows students and new users the steps to reset or change MySQL or MariaDB password when using Ubuntu Linux. One can easily create and reset root passwords via their command terminal or shell when using MySQL or MariaDB.

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

Forgetting your desktop password is one thing. However, forgetting the root password of your MySQL database in production is totally different and no fun. And if you did, this post will show you how to recover root password.

Also, for students and new users learning Linux, the easiest place to start learning is Ubuntu Linux. 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 the version of the MySQL or MariaDB server you are running, you will need to use different commands to retrieve the root password.

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

mysql --version

Be sure to take notes on the version of the MySQL or MariaDB database server that you are running. You will 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 and MariaDB using root passwords

Both MariaDB and MySQL servers come with the root user configured to use the auth_socket default authentication method.

YOU CAN ALSO READ:   What to do if your Android turns off by itself?

the auth_socket The plugin authenticates users connecting from the local host through the Unix socket file. This means that you cannot authenticate as root by providing a password.

However, you can disable the auth_socket plugin using the SQL commands below. If you do, you will be prompted to enter the root password to log in.

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

To reset the root password for MySQL or MariaDB, first stop the database server. You can do this by running the following commands.

You can do it for MySQL or MariaDB.

sudo systemctl stop mysql
sudo systemctl stop mariadb

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

sudo mysqld_safe --skip-grant-tables &

When the --skip-grant-tables If this 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 are connecting from the local server terminal console.

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

mysql -u root

Then run the following commands if you run MySQL 5.7.6 and later or MariaDB 10.1.20 and then:.

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

If the above SQL commands don’t work, try the next one.

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

If you are running older versions of the database server, run the following commands to reset the password.

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

If all goes well above, you should see the following output without any errors.

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 following commands.

mysqladmin -u root -p shutdown

You will be prompted for the new password you just created. Write it down to continue the shutdown.

YOU CAN ALSO READ:   How to change the layout of the Safari address bar in iOS 15

You can then start any of the servers using its commands below.

sudo systemctl start mysql
sudo systemctl start mariadb

Now you can try to log in with the newly created password above.

sudo mysql -u root -p

If it works, then you are all set.

Conclusion:

In this tutorial we have seen how to reset the root password for MySQL or MariaDB. If you find any errors above or have something to add, use the comment form below.

Write A Comment