How to reset mysql password on Ubuntu 22.04 when you dont know your password

While setting up my mysql root password, I input some thing that made mysql reject my password. I tried to fix it but got it further confused my terminal. Eventially I exited the password sequence, but the password was set… And I didnt know what it was. After trying a for 30 minutes to guess it, I let that go. Then I tried to reinstall mysql.. didnt work either. The password was set. So I had to reset it. Here is how I did it.

1. Stop Mysql

service mysql stop

2. Restart server in safe mode with a special flag.

mysqld_safe --skip-grant-tables &

If like me your mysql install doesnt have the folder required to do this, it might say something like this:

2023-03-08T00:57:36.000190Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2023-03-08T00:57:36.003198Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

In that case, make the folder and give it the correct permissions:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

And if for some reason, your terminal window doesnt run the mysqld_safe --skip-grant-tables & command in the background, just let that command run in the window, start another terminal window, login, and keep it pushing.

3. Login to mysql (in safe mode) without a password

By this point, you should be able to log into mysql without a password by running.

mysql

4. Reset the password

Flush the privelages

mysql> flush privileges;

Select the MYSQL database

mysql> use mysql;

Run the reset command

Replace new-password with your passowrd (and leave the quotes in).

ALTER USER  'root'@'localhost' IDENTIFIED BY 'new-password';

5. Exit and restart mysql

mysql> quit;

sudo killall -u mysql

sudo systemctl restart mysql.service

6. Try to login

sudo mysql -u root -p

Hope that helps, thanks for reading,

-AP