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