How to reset the MySQL Root Password on Ubuntu 22.04

Have you failed / forgotten to remember the root password for MySQL database? Stress not, it works out, and there is an answer!

In this article, we will reset the MySQL root password in Ubuntu 22.04 LTS by starting MySQL with the –skip-grant-tables option.

It is a simple guide that works with any modern Linux distribution like CentOS 7 and Ubuntu 18.04, 20.04 LTS

Before moving on to the solutions, it is assumed that you have the MYSQL 8.0 version of the MySQL database for Ubuntu.

How to Reset the MySQL Root Password on Ubuntu 22.04 LTS

Step 1. Check Mysql Version.

First, you must confirm the version of MySQL on Ubuntu that you perform because the commands will be different.

MySQL Version Output:

admin@server:~$ mysql -V

If on MySQL version 8, you will see something like:

admin@server:~$ mysql Ver 8.0.29-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

If you are on MySQL version 5, you will see something similar to:

admin@server:~$ mysql Ver 14.14 Distrib 5.7.36, for Linux (x86_64) using EditLine wrapper

Step 2. Stop MySQL Server.

To change the Root MySQL password, you must first stop the MySQL server, run the following command:

admin@server:~$ sudo /etc/init.d/mysql stop

Step 3. Check MySQL Status.

Check the status of the MySQL server to verify. Run the following command:

admin@server:~$ sudo /etc/init.d/mysql status

OUTPUT:

admin@server:~$ sudo /etc/init.d/mysql status
○ mysql.service - MySQL Community Server
 Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
 Active: inactive (dead) since Thu 2022-06-30 17:35:46 IST; 48min ago
 Process: 41280 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Process: 41288 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 41288 (code=exited, status=0/SUCCESS)
 Status: "Server shutdown complete"
 CPU: 1.439s

 Jun 30 17:33:56 KELLGGNLPTP01038 systemd[1]: Starting MySQL Community Server...
 Jun 30 17:33:57 KELLGGNLPTP01038 systemd[1]: Started MySQL Community Server.
 Jun 30 17:35:45 KELLGGNLPTP01038 systemd[1]: Stopping MySQL Community Server...
 Jun 30 17:35:46 KELLGGNLPTP01038 systemd[1]: mysql.service: Deactivated successfully.
 Jun 30 17:35:46 KELLGGNLPTP01038 systemd[1]: Stopped MySQL Community Server.
 Jun 30 17:35:46 KELLGGNLPTP01038 systemd[1]: mysql.service: Consumed 1.439s CPU time.

Now we will restart MySQL with skip-grant-table command

Step 4. Make sure we have the directory /var/run/mysqld exists and have correct owner set.

admin@server:~$ sudo mkdir /var/run/mysqld

admin@server:~$ sudo chown mysql /var/run/mysqld

Step 5. Starting the MySQL database server without loading grant tables.

Now start MySQL with the –skip-grant-tables option. The & is required here.

admin@server:~$ sudo mysqld_safe --skip-grant-tables&

OUTPUT:

admin@server:~$ 2022-06-30T12:06:55.799610Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2022-06-30T12:06:55.819458Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Now press ENTER to return to the Linux BASH prompt.

Step 6. Now it’s time to change MySQL root password.

You can now connect to the Root MySQL account without password.

admin@server:~$ sudo mysql --user=root mysql

Once logged in, you will see the mysql> prompt.

admin@server:~$ sudo mysql --user=root mysql 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
Welcome to the MySQL monitor. Commands end with ; or g. 
Your MySQL connection id is 7 
Server version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu) 
Copyright (c) 2000, 2022, 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>

MySQL 8 – Change Root Password on Ubuntu

For MySQL 8 on Ubuntu, run following commands.

Update command will help you to update the password.

mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root'; 
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

Now, flush the privileges first. Run the following command:

mysql> flush privileges;

Replace your_password_here with your own

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';

Flush privileges again.

mysql> flush privileges;

Now exit Mysql

mysql> exit 
Bye

Now Start the Mysql Server  and login with new password

admin@server:~$ sudo /etc/init.d/mysql start

admin@server:~$ mysql -u root -p 

MySQL 5.7 – Change Root Password on Ubuntu

For MySQL 5.7 on Ubuntu, run this command to change the root password. Replace your_password_here with your own.

mysql> update user set authentication_string=PASSWORD('your_password_here') where user='root';

Now run this below command to change the auth plugin to mysql_native_password.

mysql> update user set plugin="mysql_native_password" where User='root';

Now, flush the privileges first. Run the following command:

mysql> flush privileges;

Now exit Mysql

mysql> exit 
Bye

Now Start the Mysql Server  and login with new password

admin@server:~$ sudo /etc/init.d/mysql start
vikash@server:~$ mysql -u root -p 

MySQL 5.6 – Reset Root Password on Ubuntu

For MySQL 5.7 on Ubuntu, run this command to change the root password. Replace your_password_here with your own.

mysql> update user set Password=PASSWORD('your_password_here') where user='root';

Now run this below command to change the auth plugin to mysql_native_password.

mysql> update user set plugin="mysql_native_password" where User='root';

Now, flush the privileges first. Run the following command:

mysql> flush privileges;

Now exit Mysql

mysql> exit 
Bye

Now Start the Mysql Server  and login with new password

admin@server:~$ sudo /etc/init.d/mysql start
admin@server:~$ mysql -u root -p

If you are facing any issue then follow the following below OUTPUT box

Command executed in single interface.

admin@server:~$ sudo /etc/init.d/mysql stop 
Stopping mysql (via systemctl): mysql.service.

admin@server:~$ sudo mkdir /var/run/mysqld 

admin@server:~$ sudo chown mysql /var/run/mysqld 

admin@server:~$ sudo mysqld_safe --skip-grant-tables& [1] 41711 

admin@server:~$ 2022-06-30T12:06:55.799610Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2022-06-30T12:06:55.819458Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 

admin@server:~$ sudo mysql --user=root mysql 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
Welcome to the MySQL monitor. Commands end with ; or g. 
Your MySQL connection id is 7 
Server version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu) 
Copyright (c) 2000, 2022, 
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> UPDATE mysql.user SET authentication_string=null WHERE User='root'; 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 

mysql> flush privileges; 
Query OK, 0 rows affected (0.01 sec) 

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Root@123'; 
Query OK, 0 rows affected (0.02 sec) 

mysql> flush privileges; 
Query OK, 0 rows affected (0.01 sec) 

mysql> exit 
Bye 

admin@server:~$ mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g. 
Your MySQL connection id is 9 
Server version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu) 
Copyright (c) 2000, 2022, 
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> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| mysql | 
| performance_schema | 
| sys | 
+--------------------+ 
4 rows in set (0.00 sec) 

mysql> exit 
Bye

Conclusion:

Congratulation you have successfully  reset your MySQL password on Ubuntu.

Let us know if you are facing any issue while reset password in comment section, we will happy to help you.