This post was originally written September 10, 2016 during a stressful time in my life - I couldn't access MySQL root. I'm migrating all my personal blog posts onto dev.to.
This is an aggregation of various StackOverflow posts, user tutorials, and official docs that helped me figure out how to reset my local MySQL root password.
Thanks to an early engineering assignment, my initial database setup got really screwed up. Being the middle of a semester, I didn't have the time to fix it. Now, a couple of updates later, I had to face the beast and get it working. For future reference for myself and for anyone else stuck, this is a brief post on the steps I took that worked for my system. All the tutorials I found during my fix-it session were either incomplete or not quite right.
OS: Ubuntu 16.04.1 LTS
You've installed, and re-installed, and tried to repair an old MySQL installation. Entering
mysql -u root -p
followed by your password, returns an
Access denied for user 'root'@'localhost'
error, even though you're sure it's the right login. You've also already tried the official documentation on How to reset the root password, which failed. Maybe you even tried to repackage the deb file with
sudo dpkg-reconfigure mysql-server-5.7
which failed. Have no fear - where there's a terminal, there's a way.
Shut down Mysql. Try
sudo service mysql stop
sudo /etc/init.d/mysql stop
or looking up the PID and killing it. Make sure it's stopped via:
service mysql status
Start MySQL in safe mode without a password:
mysqld_safe --skip-grant-tables &
Caution: this is insecure! I had no data in any of my tables, which meant I wasn't worried about malicious reads. The 'skip' option enables anyone to connect without a password with full privileges. If you have any concerns about your tables, you should also diable any remote access with:
In a new terminal, connect to MySQL server with the
mysql client. No password is neccessary. Execute the following steps:
UPDATE user SET authentication_string=PASSWORD("securepassword") where User='root';
UPDATE user SET plugin="mysql_native_password";
Brief explanation: The second line is where you set your password. One difference between MySQL 5.6 or older, and the latest 5.7+ is the column name switch from
AUTHENTICATION_STRING. Older tutorials use the former.
If you miss the third 'set plugin' statement, you'll successfully update your password but still won't be able to connect to your server. My system, for example, had the plugin value set to 'auth socket.' Even with the right login details, my server threw errors about my missing socket, and I needed to shut down, restart in safe mode, and switch both values again.
Finally, the 'flush privileges' command reloads the server's in-memory copy of the grant tables. Modifying the user table with UPDATE doesn't load the changes into the tables immediately, unlike the higher-level GRANT or SET commands.
Stop your safe mysqld, and start the mysql server normally. You should be able to connect with your new password via
mysql -u root -p
Enjoy your new database access!
MySQL Reference Manual: How to reset the root password
Stack Overflow: MySQL User DB does not have password columns
Stack Overflow: How to stop mysqld
Stack Overflow: MySQL Fails on: mysql "ERROR 1524 (HY000)"