There is a way to start MySQL in a safe mode with the SUDO command, bypassing user authentication. When we start the MySQL Server in this mode, we can enter the mysql shell without a password, and have access privileges to all databases and tables. From there, we'll be able to reset the mysql root user password.
First, stop the existing MySQL Server:
$ sudo service mysql stop
Now this is the key command to run the server in safe mode, enabling us to bypass user authentication:
$ sudo mysqld_safe --skip-grant-tables --skip-networking &
Very important note: For security purposes we also disable networking (via the --skip-networking flag) when entering this mode, otherwise outside users could just access your databases freely during this time. Note that some instructions on the web for resetting the root password do not include the --skip-networking flag in their instruction for entering safe mode, which is a big security risk.
Now that the MySQL Server has been started in this safe mode, try to run the mysql shell with no password.
$ mysql -u root
If this works, then we can run these mysql shell commands to reset the MySQL password:
> FLUSH PRIVILEGES;
> SET PASSWORD FOR root@'localhost' = 'my_new_password';
Sometimes, getting MySQL to start in safe mode does not work, causing error output such as:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
[1]+ Exit 1 sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking
This is due the socket file not being present anymore.
This stackoverflow answer by Pedronalbert, et al., linked below are great instructions on how to try to restore the socket file. This StackOverflow post has saved me multiple times. (note, below I suggest a different last step for setting the new password):
Important StackOverflow Post: https://stackoverflow.com/a/41987711/2529368
Follow all the instructions in that answer, except:
EXCEPT, on the last step, instead of
> SET PASSWORD FOR root@'localhost' = PASSWORD('my_new_password') -- This is bad, causes a warning
do this:
> SET PASSWORD FOR root@'localhost' = 'my_new_password'; -- This is good
Another issue I encountered was that the commands for stopping and restarting the mysql server would just hang indefinitely.
$ sudo service mysql start
==> Hangs indefinitely
$ sudo service mysql stop
==> Hangs indefinitely
This prevented me from succesfully following those instructions referenced in the stackoverflow link (https://stackoverflow.com/a/41987711/2529368) above.
I found the indefinite hanging was due to multiple spurious instances of the mysql server running in the background, which all need to be killed.
I ran this command:
$ sudo ps -A|grep mysql
-- Output --
3631 pts/9 00:00:00 mysql
16473 pts/20 00:00:00 mysqld_safe
16895 pts/20 00:00:00 mysqld
16987 ? 00:00:00 mysqld
16989 ? 00:00:00 mysqld
Which showed a bunch of random mysql server processes running -- this was probably due me trying to stop and restart mysql so many times while debugging.
I then ran this command to kill the processes matching the 'mysqld' pattern:
$ sudo pkill mysqld
After I did that, I found that I could successfully stop and restart the mysql server without it hanging, and follow the https://stackoverflow.com/a/41987711/2529368 instructions by pedronalbert, et al. that I linked above, and start the MySQL server in safe mode, and change the password of root@'localhost'.
When you are done with those instructions, and you've changed the MySQL root password, you'll want to exit the mysql shell.
> exit;
Then stop the server to leave safe mode, and start it again normally:
$ sudo service mysql stop
$ sudo service mysql start
Now try to enter the MySQL shell with the new password you created:
$ mysql -u root -p
Enter password: (enter your newly created password)
Hopefully that confirms a succesful reset of the MySQL root password.
Please leave me a comment if these instructions helped you!!
One day, we will live in a world where resetting the MySQL root password is an easier task...