How To Reset MySQL Root User Password In Linux

Have you forgotten your MySQL root user password? No worries! This guide walks you through the steps to reset MySQL root user password in Linux operating systems. The steps given below should also work to reset MariaDB root password as well.

We already posted a guide to reset root (administrative) user password in MySQL older versions, i.e. MySQL 5.7. But, that method didn't work with the new MySQL 8 version. If you wanted to reset root password in MySQL 8, follow any one of the two workarounds given below.

Reset MySQL Root User Password In Linux

As I already mentioned, we can reset MySQL root password in two ways.

• The generic way,
•  Using init_file system variable.

1. The generic way to reset MySQL root password

This method will work regardless of the operating system you use. I warn you that it is a less secure way, because we are going to start MySQL server with the --skip-grant-tables option. This gives anyone with access to the server unrestricted access to all databases.

First, stop the MySQL service using command:

$ sudo systemctl stop mysql

You can verify if MySQL is actually stopped with command:

$ sudo systemctl status mysql

Sample output:

● mysql.service - MySQL Community Server
      Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
      Active: inactive (dead) since Sat 2021-05-29 11:53:08 UTC; 8s ago
     Process: 560 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
     Process: 666 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
    Main PID: 666 (code=exited, status=0/SUCCESS)
      Status: "Server shutdown complete"
 May 29 10:08:33 ubuntu2004.localdomain systemd[1]: Starting MySQL Community Server…
 May 29 10:08:37 ubuntu2004.localdomain systemd[1]: Started MySQL Community Server.
 May 29 11:53:07 ubuntu2004.localdomain systemd[1]: Stopping MySQL Community Server…
 May 29 11:53:08 ubuntu2004.localdomain systemd[1]: mysql.service: Succeeded.
 May 29 11:53:08 ubuntu2004.localdomain systemd[1]: Stopped MySQL Community Server.

Well, the MySQL service is not running.

Now, restart the MySQL server without permission-checking by running the following command:

$ sudo mysqld_safe --skip-grant-tables --skip-networking &

Here, --skip-grant-tables option enables you to connect to the mysql database server without a password and with all privileges. The --skip-networking option is used to prevent the other clients from connecting to the database server. And, the ampersand (&) symbol is used to run the command in background, so you can be able to type the other commands given in the subsequent steps. Please be mindful that the above command is dangerous and your database server becomes insecure and vulnerable to security threats. You should run this command only for a brief period of time to reset the password.

You may the see the following error:

2021-05-29T12:00:19.991826Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2021-05-29T12:00:19.996942Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

If you see this error, create the missing directory (i.e. /var/run/mysqld) and set proper permissions to it like below.

$ sudo mkdir -p /var/run/mysqld

$ sudo chown -R mysql:mysql /var/run/mysqld

Now try again to start mysql server without password and with root privileges:

$ sudo mysqld_safe --skip-grant-tables --skip-networking &

Next, connect to the mysql server using command:

$ mysql

You will be landed in the mysql shell prompt.

 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 7
 Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

 Copyright (c) 2000, 2021, Oracle and/or its affiliates.

 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.

 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Since we logged in without loading grant tables (because we used --skip-grant-tables option), we can't user ALTER USER command to reset password. So let us load grant tables using command:

mysql> FLUSH PRIVILEGES;

Now, run the following command to update the mysql root user password:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd123#@!';

In the above command, replace P@ssw0rd123#@! with your own password. Please note that if have enabled Validate Password component, you must use a strong password.

Finally, exit from the mysql prompt:

mysql> exit

Finally, shutdown the running database server that you started earlier with --skip-grant-tables option. To do so, run:

$ sudo mysqladmin -u root -p shutdown

You will be asked to enter your sudo password followed by the MySQL root user password that you set in the previous step.

[sudo] password for ostechnix: 
Enter password: 
2021-05-29T12:09:38.425737Z mysqld_safe mysqld from pid file /var/lib/mysql/ubuntu2004.localdomain.pid ended
[1]+  Done                    sudo mysqld_safe --skip-grant-tables --skip-networking

If the above command doesn't work, find the .pid file that contains the server's process ID. The PID file will usually be available in /var/lib/mysql/ or /var/run/mysqld/ or /usr/local/mysql/data/ directory depending upon your distribution, host name, and configuration. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.

In my case, it is "/var/lib/mysql/ubuntuserver.pid".

So, I stopped the instance of the database server with command:

$ sudo kill `/var/lib/mysql/ubuntuserver.pid`

Please use the back-tick to mention the file path.

We can also find the MySQL pid using ps and grep commands like below:

$ ps ax | grep mysql

Sample output:

1930 pts/0 S 0:00 sudo mysqld_safe --skip-grant-tables --skip-networking
1931 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables --skip-networking
2091 pts/0 Sl 0:28 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=/var/log/mysql/error.log --pid-file=ubuntuserver.pid

Manually kill it like below:

$ sudo kill <PID>

Or, kill all MySQL services:

$ sudo  killall mysql

Now, start MySQL service normally using command:

$ sudo systemctl start mysql

Now, log in to the MySQL server with new password:

$ mysql -u root -p

Sample output:

Connect to MySQL server with new root password

2. Reset MySQL root password with password file

The another way to reset mysql root password is to use init_file system variable.

Stop MySQL service:

$ sudo systemctl stop mysql

Create a text file, for example ostechnix.txt:

$ vi ostechnix.txt

Add the following line in it:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd123#@!';

Replace P@ssw0rd123#@! with your own password in the above line. If you have enabled Validate Password plugin, you should specify a strong password. Save and close the file.

Now, start the MySQL server with the init_file system variable that points to the ostechnix.txt file:

$ sudo mysqld --init-file=/home/sk/ostechnix.txt &

This command will execute the contents of ostechnix.txt file and update the root@localhost account password with new password mentioned in the ostechnix.txt file and finally start the mysql server.

Now stop and restart mysql server normally:

$ sudo systemctl stop mysql

$ sudo systemctl start mysql

Finally, delete the password file i.e ostechnix.txt.

$ rm /home/sk/ostechnix.txt

Now you can be able to login to mysql server with new password using command:

$ mysql -u root -p

We also posted slightly different method to reset MySQL root password. Check the following guide for details:

>> How To Reset Root Password In MySQL 8 On Ubuntu Linux

Conclusion

In this guide, we have learned two different ways to reset the forgotten MySQL root user password in Linux operating systems. If you lost your database administrative account user password, you can easily reset it in a couple minutes.

Resource:

• MySQL 8.0 Reference Manual