October 1, 2022

WeAnswer

The complete guide to the beginners as well as to the experienced Web Developers

How to reset or change the MySQL root password?

Forgot MySQL password? here step for reset or change the MySQL root password using below steps

 

Which MySQL version are you using?

If you are using MySQL version 8, you will see something like:

mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

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

mysql Ver 14.14 Distrib 5.7.36, for Linux (x86_64)

 Restart MySQL with skip-grant-tables&

we should stop the MySQL service


Make sure the directory /var/run/mysqld exists and the correct owner is set.

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

You should see something similar:
[1] 1283
user@server:~$ 2022-08-09T22:22:59.1272516Z mysqld_safe Logging to syslog.
2022-08-09T22:22:59.1272516Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2022-08-09T22:22:59.1272516Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

 Change MySQL Root Password

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

 1. MySQL 8 – Reset Root Password

For MySQL 8 on Ubuntu, run the following commands.

UPDATE mysql.user SET authentication_string=null WHERE User='root';

flush privileges;

Replace your_password_here with your own.

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

Flush privileges again.

flush privileges;

Exit MySQL.
exit

.

2. MySQL 5.7 – Reset Root Password

For MySQL 5.7 on Ubuntu, run this command to change the root password. Replace your_password_here with your own. (Generate a strong password here)

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

Change the auth plugin to mysql_native_password.

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

Flush privileges.

flush privileges;

Exit MySQL.

exit

3. MySQL 5.6 – Reset Root Password

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

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

Change the auth plugin to mysql_native_password.

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

Flush privileges.
flush privileges;

Exit MySQL.
exit

Test New Root Password

Make sure all MySQL processes are stopped before starting the service again.
sudo killall -u mysql
If you see a message similar to below, press ENTER to continue.
2022-08-09T22:22:59.1272516Z mysqld_safe mysqld from pid file /var/lib/mysql/ubuntu.pid ended
Start MySQL again.
sudo /etc/init.d/mysql start

Log in to MySQL again and you should now be prompted for a password.

sudo mysql -p -u root

Enter your MySQL root password. If correct, you should see something like:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>