MySQL/MariaDB Root Password Reset Tutorial

Preface

Forgotten passwords are a problem we often encounter. If you have forgotten or lost the root password to your MySQL or MariaDB database, you can still access and reset the password if you have access to the server and a sudo-enabled user account.

A few months ago, I installed LAMP on Ubuntu 18.04. Today I tried to log into the database as root, but I totally forgot my password. After a bit of Googling and reading some articles, I was able to successfully reset my password. For those who are wondering how to do that, this short tutorial explains how to reset MySQL or MariaDB Root password in Unix-like operating systems. Let’s take a look at the detailed introduction.

Reset MySQL or MariaDB Root Password

First, stop the database.

If you are using MySQL, type the following command and press Enter.

$ sudo systemctl stop mysql

For MariaDB:

$ sudo systemctl stop mariadb

Next, restart the database without permission checks using the following command:

$ sudo mysqld_safe --skip-grant-tables &

Here, the --skip-grant-tables option lets you connect without a password and with all privileges. If you start the server with this option, it also enables the --skip-networking option, which is used to prevent other clients from connecting to the database server. Also, the & symbol is used to run the command in the background, so you can enter additional commands in the following steps. Please note that the above command is dangerous and your database will become insecure. You should only run this command for a short period of time to reset your password.

Next, log in to the MySQL/MariaDB server as the root user:

$ mysql

At the mysql > or MariaDB [(none)] > prompt, run the following command to reset the root user password:

UPDATE mysql.user SET Password=PASSWORD('NEW-PASSWORD') WHERE User='root';

Replace NEW-PASSWORD in the above command with your own password.

Then, enter the following command to exit the mysql console.

FLUSH PRIVILEGES;
exit

Finally, shut down the database that you previously ran with the --skip-grant-tables option. To do this, run:

$ sudo mysqladmin -u root -p shutdown

You will be asked to enter the MySQL/MariaDB user password that you set in the previous step.

Now, start the MySQL/MariaDB service normally using the following command:

$ sudo systemctl start mysql

For MariaDB:

$ sudo systemctl start mariadb

Verify that the password has indeed been changed using the following command:

$ mysql -u root -p

That’s all for today. There's more good stuff. Stay tuned!

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.