How to Reset MySQL 5.7 Root Password (Even If You Don't Know the Old Password)
How to Reset MySQL 5.7 Root Password (Even If You Don't Know the Old Password)
Jason Jason Photo By Jason Jason, Sep 20, 2017

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

Step 1

sudo service mysql stop

Step 2

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

Step 4

mysql -u root mysql

Step 5

update user set authentication_string=password('YOUR-NEW-PASSWORD') where user='root';

Step 6

flush privileges; exit;

Step 7

sudo service mysql restart

References

https://www.vultr.com/docs/reset-mysql-root-password-on-debian-ubuntu

https://stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx


Tags & Categories

MySQL