HOWTO: Reset (Change) MySQL Root Password on Ubuntu 16.04
HOWTO: Reset (Change) MySQL Root Password on Ubuntu 16.04

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

or

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

 

2 thoughts on “HOWTO: Reset (Change) MySQL Root Password on Ubuntu 16.04

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.