mysql reset root password with fix for Socket problem

I don't reset mysql's root password often enough to remember how to do it. So, I google and go through endless hassle because all the examples are old or incomplete. It's maddening.

The main problem is that nobody includes the stuff below referring to /var/run/mysqld. I don't know why. Perhaps it was not needed in the past. However, it sure is now. You can tell if you do by seeing this:

mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists (2)

when you try 'mysql -uroot mysql' without it.

The sequence below works on my Ubuntu 16 installation. 100%. I did it a few times because I wanted to make sure I had done it correctly and repeatedly.

#mysql: reset root user password bash commands
sudo service mysql stop
sudo mkdir /var/run/mysqld
sudo chown mysql: /var/run/mysqld
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -uroot mysql

#in msyql:
UPDATE mysql.user
SET
  authentication_string=PASSWORD('PUT_NEW_PASSWORD_HERE'),
  plugin='mysql_native_password'
WHERE User='root' AND Host='localhost';
exit;

#and back in bash
sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown
sudo service mysql start

mysql -uroot -pPUT_NEW_PASSWORD_HERE

(Of course, mysql will beef that you put your password in the command line. Don't do it if your bash_history or logs could be accessed.)


By the way, I got this information from this website. Obviously this person is a genius. Props.

https://coderwall.com/p/j9btlg/reset-the-mysql-5-7-root-password-in-ubuntu-16-04-lts