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-table
s 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.
4 comments
Hi,
Thanks a lot.
Excellent, thanks.
Access denied for user ‘root’@’localhost’ (using password: NO)
What to do in this case? I’m in ubuntu 20.4, mysql 8.0
Did you give the password? The output says “Access is denied using “NO” password”.