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:
$ sudo mysql -V
You should see something like this:
mysql Ver 8.0.28-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
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.
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
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!