The chance of remembering your MySQL root user account password when coming back to an Ubuntu server after a long period of time is pretty much zero – after all, this is seldom a username/password combo that you actually use on a daily basis! Luckily, there is a particularly easy way to reset the root user password for an Ubuntu server…
We’ll simply reconfigure the MySQL package!
First, we need to check the version of the currently installed mysql-server.
apt-cache policy mysql-server
This should give us among all the returned information a line that starts with ‘Installed’. My server instance returned this:
So I have 5.5 installed then. Next, we start the reconfiguration process with:
sudo dpkg-reconfigure mysql-server-*.*
*.* should obviously be replaced with your version, so using my example, I would have ran:
sudo dpkg-reconfigure mysql-server-5.5
This command should then stop the database daemon and a prompt will then appear where you need to enter your new root password (yay!) and then confirm the reconfiguration. On completion, your instance of mysql-server should automatically be started up again.
As simple as that. You should now be able to login as root using your newly set password:
mysql -u root -p