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.
Stats
OS: Ubuntu 16.04.1 LTS
MySQL: 5.7.15
Sit-rep
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.
Step 1
Shut down Mysql. Try
sudo service mysql stop
or
sudo /etc/init.d/mysql stop
or looking up the PID and killing it. Make sure it's stopped via:
service mysql status
Step 2
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:
--skip-networking
Step 3
In a new terminal, connect to MySQL server with the mysql client. No password is neccessary. Execute the following steps:
use mysql;
UPDATE user SET authentication_string=PASSWORD("securepassword") where User='root';
UPDATE user SET plugin="mysql_native_password";
FLUSH PRIVILEGES;
quit;
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 PASSWORD to 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.
Step 4
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!
Links
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)"
Latest comments (10)
Thank you for taking the time to write this, Erika. For me, knowing how to run MySQL in safe mode was the trick, after which it was trivial to reset the password for my
rootuser. Thanks for sharing!I owe you a beer if I ever meet you! I have spent 5+ hours on this issue and your answer was the only one that worked!
If you just run mysql command under root user you will be granted access without asked for password, because socket authentication enabled for root@localhost.
Short way to solve
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';
Then it disallows connecting via GUI tools as the MySQL Workbench with error:
You must reset your password using ALTER USER statement before executing this statement.To overcome this run
In the mysql shell.
One must go into the
mysqldatabase before applying commands in Step 3Bless you ! May the rain of good luck keep showering upon you. Phewwwww
After upgrading MySQL from the previous version to 5.7, when I had tried to log in to the database as a non-sudo user it shows the following error. But I was enabled to login into the system as sudo user without any issues.
ERROR 1698 (28000): Access denied for user ‘root’@’localhost’.
You can use the following steps to beat MySQL Error 1698 (28000): Access denied for user ‘root’ @ ‘localhost’
In Ubuntu systems running MySQL 5.7 (and later), the root user is authenticated by the auth_socket plugin by default.
$ sudo mysql #No Username to be the provide
mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;
+——————+———————–+
| User | plugin |
+——————+———————–+
| root | auth_socket |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+——————+———————–+
mysql> UPDATE user SET plugin=’mysql_native_password’ WHERE User=’root’;
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ service mysql restart
Right Answer!!
If you want to login to your MySQL server as root from an external program such as phpMyAdmin you have two options.
The first one is to change the authentication method from auth_socket to mysql_native_password. You can do that by running the following command: ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘very_strong_password’;FLUSH PRIVILEGES;
Conclusion: Now you have learned to install troubleshoot the Access Denied error in MySql 5.7 and also you can check detailed solution from expresstechsoftwares.com/mysql-acc...
Wow, this was a lifesaver! Thanks!
Thank you! thank you so much!
Your suggestion worked like a charm. Thanks for the help