How to Reset the MySQL Root Password on Ubuntu 22.04 | vetechno

How to Reset the MySQL Root Password on Ubuntu 22.04 | vetechno
How to Reset the MySQL Root Password on Ubuntu 22.04 | vetechno
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.

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:
vikash@vetechno:~$   mysql -V
If on MySQL version 8, you will see something like:
vikash@vetechno:~$   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:

vikash@vetechno:~$   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:

vikash@vetechno:~$   sudo /etc/init.d/mysql stop
Step 3. Check MySQL Status.
Check the status of the MySQL server to verify. Run the following command:
vikash@vetechno:~$   sudo /etc/init.d/mysql status
OUTPUT:

vikash@vetechno:~$   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.
vikash@vetechno:~$   sudo mkdir /var/run/mysqld
vikash@vetechno:~$   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.
vikash@vetechno:~$   sudo mysqld_safe --skip-grant-tables&
OUTPUT:
vikash@vetechno:~$   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.
vikash@vetechno:~$   sudo mysql --user=root mysql
Once logged in, you will see the mysql> prompt.
vikash@vetechno:~$    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 – Reset 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
vikash@vetechno:~$   sudo /etc/init.d/mysql start
vikash@vetechno:~$   mysql -u root -p 

MySQL 5.7 – 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 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
vikash@vetechno:~$   sudo /etc/init.d/mysql start
vikash@vetechno:~$   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
vikash@vetechno:~$   sudo /etc/init.d/mysql start
vikash@vetechno:~$   mysql -u root -p

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

Command executed in single interface.

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

vikash@vetechno:~$ sudo mkdir /var/run/mysqld

vikash@vetechno:~$ sudo chown mysql /var/run/mysqld

vikash@vetechno:~$ sudo mysqld_safe --skip-grant-tables&
[1] 41711

vikash@vetechno:~$ 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

vikash@vetechno:~$ 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

vikash@vetechno:~$ 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> 
mysql> 
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.

Also read

You may Also like

Leave a Reply

Your email address will not be published.