Yes. I committed the cardinal sin. I lost my root password to mysql and phpmyadmin. Consequently I could not log in as root in order to change the permissions for a user. Of course I hadn’t noticed I had lost the damn thing because normally I never-ever log in as root to anything.
It was only when I need to fiddle about with other users when I typed in what I thought it was – only to find my memory was crap. Of course, I had written it down somewhere safe. I always do. The problem was (crap memory again) I couldn’t find where.
I always obfuscate saved passwords – just in case some nasty person raids my desk and steals my documents. So it is probably staring me in the face. On the plus side I suppose it shows my obfuscation works pretty well. Hey Ho.
Check Your Mysql Version
Here’s what I did to reset the lost root password to mysql and phpmyadmin. This worked for me. I’m running mysql ver 8.0.30 Check this with:
mysql -V
Mine come back with this.
mysql Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
This may not be compatible with all other versions but I expect it should be with this or later versions.
This method (after trying and failing with many others) is based largely around this post on devanswers.co. So major Hat Tip to them. There are a few extra steps here mainly surrounding clean up before actually changing the password. But if you are running mysql 5 you would probably be better off at devanswers as they deal with that earlier version as well as mysql 8. But you may still want to do the mysql killing and clean up as described here.
If, like me you got to this point after many failed attempts to fix this we need to check what sort of dismal state we are in.
We need to kill mysql. Make absolutely sure it really is dead. It’s worth checking that you haven’t got multiple versions running. This may (especially) happen if you’ve been faffing around with other failed attempts to crack in to mysql (like I had).
Cleanup: Kill all Mysqls
sudo ps -A | grep mysql – if this shows you one or more mysql or mysqld processes running then….
sudo pkill mysql
sudo pkill mysqld
sudo ps -A | grep mysql – make sure this time nothing comes back.
Now we have to make sure the following directory (and associated user) exists. It may well not.
If you try and push ahead without having this directory properly set you will get something like this:
chalkie@chalkies-box:~$ mysql -u root mysql
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
[1]+ Exit 1 sudo mysqld –skip-grant-tables
If you already have this error message START AGAIN. Go back and check ALL instances of mysql are dead THEN create the directory as below.
ls -l /var/run/mysqld
If it is not there and the owner is not mysqld:mysqld then…
sudo mkdir /var/run/mysqld
sudo chown mysql /var/run/mysqld
check it again with
ls -l /var/run/mysqld
Now restart mysql in safe mode like so..
sudo mysqld_safe –skip-grant-tables&
You’ll get something like this back..
[1] 100757
chalkie@chalkies-box:/var/run$ 2022-08-21T12:57:00.853461Z mysqld_safe Logging to ‘/var/log/mysql/error.log’.
2022-08-21T12:57:00.907704Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
now we can login as root like so…
sudo mysql –user=root mysql
You now should be logged in as root the prompt should now be mysql>
Now you are logged in as Root
From here you can now do all sorts of damage. Or even change the lost password for root as so..
First remove the old password with this…(notice the trailing semi colons and ignore the mysql> prompt)
mysql>UPDATE mysql.user SET authentication_string=null WHERE User=’root’;
mysql>flush-privileges;
mysql>ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘a-new-pw-i-promise-never-to-forget’;
mysql>flush-privileges;
And that’s it. Log out and kill the safe mode mysql process
mysql>exit;
sudo killall -u mysql
Now restart it:
sudo /etc/init.d/mysql start
CHECK the new password for root works by logging into mysql and/or phpmyadmin with it!!!
I hope that helps. My other Ubuntu style fixes are listed HERE