You would think resetting or changing your mySQL root user password would be an easy process but all the guides out there seem to really suck at explaining things properly. I had to do a reset of a mysql root password this morning on Ubuntu 16.04 Xenial and had some issues getting through the process as its been a while since I last did it.
The guides out there, even an Ubuntu wiki seem to miss just one little bit of information and this leads to you not being able to reset your mySQL root password and then following people’s stupid suggestions on forums which ends up making things even worse. Very frustrating indeed, but I finally figured out the proper way of doing it without doing stupid unnecessary things and for my own future sanity I am documenting the steps here not only to help you but also for me to recall at a later date.
First off please DO NOT listen to people on StackExchange, ServerFault, StackOverflow and AskUbuntu sites telling you to purge your mySQL and reinstall it starting from scratch. That is not only STUPID but a seriously bad suggestion and only a very last resort if you have completely messed up mySQL beyond repair. Seriously there is some people on those sites with very little knowledge about many things nor proper solutions to simple problems.
This guide deals with changing or resetting the mySQL root password which does NOT require you to uninstall, purge and reinstall mySQL as it is just not necessary to go to such extremes.
First off stop mySQL
sudo service mysql stop
Now manually create the socket directory for MySQLD to be able to start up and give mysql permissions to it. (THIS is the most important step that all guides fail to mention leading people into doing very stupid thing when they cannot get mysqld to start)
sudo mkdir /var/run/mysqld; sudo chown mysql /var/run/mysqld
Now start mysql with the –skip-grant-tables option because you are not checking user privs at this point
sudo mysqld_safe --skip-grant-tables &
Now log into mysql as root
mysql -u root
Now run the following commands in the mysql console
mysql> use mysql; mysql> FLUSH PRIVILEGES; mysql> SET PASSWORD FOR root@'localhost' = PASSWORD('yournewpassword'); mysql> FLUSH PRIVILEGES; mysql> exit
Now stop mySQL and Restart it
sudo service mysql stop sudo service mysql start
sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start
Check if mySQL started properly by running
sudo service mysql status
Now to make sure everything is OK reboot your server and after reboot run
sudo service mysql status
Now you can test logging into mySQL with your new password by running
mysql -u root -p