Last Updated on August 22, 2022 by Vikash Ekka
 |
| 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