Home Database How To Reset MySQL Root User Password In Linux
How To Reset MySQL Root User Password In Linux

How To Reset MySQL Root User Password In Linux

By sk
951 Views

Let's imagine this. You setup a LAMP or LEMP stack with MySQL 8. After a few days, you forgot the MySQL root user password. You vaguely remember the password and you try different combinations of passwords. But none of them worked. What you are going to do? No worries! This brief guide explains how to reset MySQL root user password in Linux. The steps given below should also work to reset MariaDB (drop-in replacement for MySQL) root password.

We already posted a guide to reset root (administrative) user password in older MySQL versions, for example 5.7. But, that method didn't work with newer MySQL 8 version. After going through the official mysql documentation guide, I learned the following two workarounds to reset root password in MySQL 8.

Reset MySQL Root User Password In Linux

Like I already mentioned, we can reset mysql root password in two ways.

  • The generic way
  •  Using init_file system variable

Method 1 - The generic way to reset mysql root password

This method will work regardless of the operating system you use. It is a less secure way.

First, stop the mysql service using command:

$ sudo systemctl stop mysql

You can verify if it 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 Wed 2020-06-03 09:00:51 UTC; 7s ago
    Process: 853 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 853 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"

Jun 03 08:23:31 ubuntuserver systemd[1]: Starting MySQL Community Server...
Jun 03 08:24:04 ubuntuserver systemd[1]: Started MySQL Community Server.
Jun 03 09:00:49 ubuntuserver systemd[1]: Stopping MySQL Community Server...
Jun 03 09:00:51 ubuntuserver systemd[1]: mysql.service: Succeeded.
Jun 03 09:00:51 ubuntuserver systemd[1]: Stopped MySQL Community Server.

As you can see, mysql service is not running!

Now, start 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:

020-06-03T09:26:34.529219Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2020-06-03T09:26:34.532724Z 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 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.20-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

mysql>

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 '[email protected]#@!';

In the above command, replace "[email protected]#@!" with your own password. Please note that if have enabled Validate Password component, you may need to use a strong password.

Finally, exit from the mysql prompt:

mysql> exit

Reset MySQL Root User Password In Linux

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 and then mysql root user password that you set in the previous step.

[sudo] password for ostechnix: 
Enter password: 
2020-06-03T10:12:55.737848Z mysqld_safe mysqld from pid file /var/lib/mysql/ubuntuserver.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

Method 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 '[email protected]#@!';

Replace "[email protected]#@!" 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 and update the "[email protected]" 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 (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

Hope this helps.

Resource:

Thanks for stopping by!

Help us to help you:

Have a Good day!!

You May Also Like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More