How To Reset Root Password In MySQL 8 On Ubuntu Linux

Did you forget your MySQL database root user password? No worries! This step by step guide explains how to reset root password in MySQL 8 on Ubuntu 20.04 operating system.

Note: As you may already know, MySQL root user uses auth_socket plugin to authenticate with MySQL server in Ubuntu systems running MySQL 5.7 and later versions. So you can login to MySQL server as root user with sudo mysql command as long as you know your system user's sudo password. In that case, it is not necessary to change the MySQL root password. If you already have changed the authentication method for MySQL root user to either caching_sha2_password or mysql_native_password, follow the below steps to reset the root password of MySQL database.

Reset Root Password In MySQL 8 On Ubuntu Linux

1. First, stop MySQL service using command:

$ sudo systemctl stop mysql

This will stop the MySQL service if it is running. You can verify the status of the MySQL service 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 Mon 2021-05-31 11:01:15 UTC; 1min 15s ago
     Process: 1446 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
    Main PID: 1446 (code=exited, status=0/SUCCESS)
      Status: "Server shutdown complete"
 May 31 07:57:50 ubuntu2004.localdomain systemd[1]: Starting MySQL Community Server…
 May 31 07:57:51 ubuntu2004.localdomain systemd[1]: Started MySQL Community Server.
 May 31 11:01:14 ubuntu2004.localdomain systemd[1]: Stopping MySQL Community Server…
 May 31 11:01:15 ubuntu2004.localdomain systemd[1]: mysql.service: Succeeded.
 May 31 11:01:15 ubuntu2004.localdomain systemd[1]: Stopped MySQL Community Server.

2. Next, start the MySQL server without any permission-checking. To do so, run:

$ sudo systemctl edit mysql

This will open the mysql systemd configuration file your default text editor. In my case, it is nano editor.

Add the following lines in it:

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --skip-grant-tables --skip-networking

After adding the above lines, press CTRL+O and ENTER to save the file and then press CTRL+X to close it.

Here, the --skip-grant-tables option enables you to connect to the MySQL database server without a password and with all privileges. It will also disable account-management statements such as ALTER USER and SET PASSWORD. And the --skip-networking option is used to prevent the other clients from connecting to the database server. Since it disables all remote connections, none of the remote clients can access the database server until you restart the database server normally.

3. Reload systemd configuration using command:

$ sudo systemctl daemon-reload

4. Start MySQL service:

$ sudo systemctl start mysql

This will start MySQL server with --skip-grant-tables and --skip-networking options. You can verify it by checking the MySQL service status:

$ sudo systemctl status mysql
 ● mysql.service - MySQL Community Server
      Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Drop-In: /etc/systemd/system/mysql.service.d
              └─override.conf
      Active: active (running) since Mon 2021-05-31 11:39:18 UTC; 1min 23s ago
     Process: 1882 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Main PID: 1905 (mysqld)
      Status: "Server is operational"
       Tasks: 36 (limit: 2280)
      Memory: 331.0M
      CGroup: /system.slice/mysql.service
              └─1905 /usr/sbin/mysqld --skip-grant-tables --skip-networking
 May 31 11:39:16 ubuntu2004.localdomain systemd[1]: Starting MySQL Community Server…
 May 31 11:39:18 ubuntu2004.localdomain systemd[1]: Started MySQL Community Server.

5. Now, connect to MySQL server as root user without password:

$ sudo mysql -u root

You will be landed inside the MySQL shell prompt immediately.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
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.

mysql> 

6. We logged into the database server without loading grant tables (because we used --skip-grant-tables option). So we can't user ALTER USER command which his needed to reset password. To load grant tables, run the following command from MySQL shell prompt:

mysql> FLUSH PRIVILEGES;

7. Next, run any one of the following command to reset MySQL root password.

If you're using caching_sha2_password plugin, run:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Password123#@!';

If you use mysql_native_password plugin, run this instead:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';

Replace Password123#@! with your own.

After changing the MySQL root password, exit from the MySQL shell prompt:

mysql> exit

8. Revert the modified systemd configuration back to the normal settings using command:

$ sudo systemctl revert mysql

This will remove all modified files.

Removed /etc/systemd/system/mysql.service.d/override.conf.
Removed /etc/systemd/system/mysql.service.d.

9. Reload systemd configuration to take effect the changes:

$ sudo systemctl daemon-reload

10. Finally, restart MySQL server normally:

$ sudo systemctl restart mysql

11. Now you should be able to connect to the MySQL database as root user with the new password using command:

$ mysql -u root -p

Enter the root password to access MySQL shell prompt:

mysql> 

There is also another way to change MySQL root password in Linux. It is slightly different than this method. If you're interested to know, take a look at the following link:

>> How To Reset MySQL Root User Password In Linux

Conclusion

As you can see, resetting MySQL root password is easy! If you followed the above steps carefully, you can recover your MySQL database root password in couple minutes. Don't lose it again. Memorize or save your password in a safe place.