How to Reset MySQL 8 Root Password on Ubuntu 20.04

If you are provisioning lots of servers, every once in a while you may just get your MaySQL root password wrong or mixed up. It can easily ruin your day, so if you in a bind you can quickly reset the root password. This tutorial assumes you are using MySQL 8.x, and we will do it by using MySQL's --skip-grant-tables option. So here goes:

Step 1: Make sure you are running MySQL 8.x

$ sudo mysql -V

You should see something like this:

mysql  Ver 8.0.28-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

Step 2: Restart MySQL with skip-grant-tables

To continue we will need to stop MySQL from running for a few moments:

$ sudo /etc/init.d/mysql stop

and make sure we have a proper directory and permissions set up:

sudo mkdir /var/run/mysqld
sudo chown mysql /var/run/mysqld

And now we start MySQL with --skip-grant-tables

$ sudo mysqld_safe --skip-grant-tables&

The out output should look similar to this:

[1] 468414
you@yourserver:~$ 2022-02-05T13:53:59.578672Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2022-02-05T13:53:59.589622Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Press [ENTER] to get back to a prompt.

Step 3: Change Your MySQL Root Password

Now you can log into MySQL without the need for a root password.

$ sudo mysql --user=root mysql

and now you should have the mysql> prompt. Run the following commands:

UPDATE mysql.user SET authentication_string=null WHERE User='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOURROOTPASSWORD';

Note above YOURROOTPASSWORD should be replaced with the password you make for your root user. Be sure to use a long and complicated password with a mixture of uppercase, lowercase, numbers and symbols. This password should be extremely strong.

flush privileges;
exit

Step 4: Test Your New MySQL Root Password

We will start by stopping MySQL.

$ sudo killall -u mysql

You should see a message similar to this:

you@yourserver:~$ 2022-02-05T13:56:46.826922Z mysqld_safe mysqld from pid file /var/lib/mysql/cloud.pid ended

Press [ENTER] to get back to a command prompt.

Now start the MySQL service again:

$ sudo /etc/init.d/mysql start

And login to MySQL again:

$ sudo mysql -p -u root

You should now be promoted for a password, and the password you just created for root should let you in, and you will see a mysql> prompt.

That is it, you should now be good to go!




Blog Comments powered by Disqus.