This post shows students and new users steps to reset or change MySQL or MariaDB password when using Ubuntu Linux. One can create and reset the root passwords easily via its command terminal or shell when using MySQL or MariaDB.
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 totally another thing and not fun. And if you did, then this post is show you show you how to recover the root password.
Also, for students and new users learning Linux, the easiest place to start learning is on Ubuntu Linux. Ubuntu is the modern, open source Linux operating system for desktop, servers and other devices.
When you’re ready to reset the root password for MySQL or MariaDB, follow the steps below.
Depending on the MySQL or MariaDB server version you are running, 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
Both MariaDB and MySQL servers come with the root user is set to use the auth_socket authentication method by default.
The auth_socket plugin authenticates users that connect from the localhost through the Unix socket file. This means that you can’t authenticate as a root by providing a password.
However, you can disable auth_socket plugin using the SQL commands below. If you do, then you’ll be required to type the root password to logon.
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 either MySQL or MariaDB, first stop the database server. 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 to connect to the database server without a password and with all privileges granted. However, this can only happen if you’re connecting from the local server terminal console.
For MySQL server, you may need to run these commands to get password 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 logon 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 versions of the database server, then run the commands below 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 is 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 their commands below.
sudo systemctl start mysql sudo systemctl start mariadb
You can now try to login with the newly created password above.
sudo mysql -u root -p
If it works, then you’re all set.
Conclusion:
This post showed you how to reset the root password for MySQL or MariaDB. If you find any error above or have something to add, please use the comment form below.
