I can't remember the last time I changed the root password for MySQL, and since I use a program called Sequel Pro to manage all my databases, I rarely (if ever) need to type in my root password anymore. But the day finally came when I needed the root password and I couldn't find it anywhere! What to do?
Here are the commands that I used to reset my password in MySQL 5.7
sudo service mysql stop
mysqld_safe --skip-grant-tables &
The only problem was that this returned an error, something like this:
2017-09-21T02:39:03.691092Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2017-09-21T02:39:03.694957Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
Step 3 (Optional, depending on if you get errors in Step 2)
If you get the errors listed above, all you need to do is create the directory and edit the permissions to it:
sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld
Then repeat Step 3
mysql -u root mysql
update user set authentication_string=password('YOUR-NEW-PASSWORD') where user='root';
flush privileges; exit;
sudo service mysql restart