How to Easily Reset the MySQL Root Password

This guide will focus on showing you how to easily reset the MySQL root password on Ubuntu. This can easily get done by using the --skip-grant-tables option.

Reset the MySQL Root Password

Stop MySQL

To begin, we must stop the MySQL service:

sudo service mysql stop

or

sudo /etc/init.d/mysql stop

Mysqld directory and user group

Ensure that the directory /var/run/mysqld exists, but if not:

sudo mkdir /var/run/mysqld

Let’s asign the necessary user group (mysql) to the newly created directory:

sudo chown mysql /var/run/mysqld

Start MySQL with –skip-grant-tables option

Now, let’s start mysql with the --skip-grant-tables option. Do note that this option starts mysql without using the privilege system. Thus, giving anyone unrestricted access to all databases. Use carefully.

sudo mysqld_safe --skip-grant-tables&

output

linuxify@server:~# sudo mysqld_safe --skip-grant-tables&
[1] 48578
linuxify@server:~# 220708 22:32:47 mysqld_safe Logging to syslog.
220708 22:32:47 mysqld_safe Starting mariadbd daemon with databases from /var/lib/mysql

Now, press ENTER on your keyboard to return to the bash prompt.

Login to the MySQL server

Now, let’s login to the MySQL server:

sudo mysql --user=root mysql

output

linuxify@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 MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.16-MariaDB-1:10.5.16+maria~focal-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mysql]>

Reset the MySQL Root Password

MySQL 8

To change the root password on MySQL 8 or greater:

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

flush privileges;

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

Make sure to replace new_password with your new MySQL root password.

Lastly, flush privileges once again:

flush privileges;

and finally, we can exit

exit;

MySQL 5.7

To change the root password on MySQL 5.7 or older:

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

Change new_password with your new MySQL root password.

Lastly, let’s change the auth plugin to the mysql_native_password, flush privileges, and exit:

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

flush privileges;

exit;

Summary

That’s it! We’ve showed you how to easily reset the MySQL root password whether its MySQL 8 or 5.7.