loading...
Cover image for ACCESS DENIED: Reset MySQL root user password

ACCESS DENIED: Reset MySQL root user password

oneearedmusic profile image Erika Wiedemann ・3 min read

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)"

Stop Using Flush Privileges

You're done!

Posted on by:

oneearedmusic profile

Erika Wiedemann

@oneearedmusic

Canada | Software Engineer in Training (EIT)| Perpetually searching for sleep, sanity, and coffee.

Discussion

markdown guide
 

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...

 

Your suggestion worked like a charm. Thanks for the help

 

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';

 

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 root user. Thanks for sharing!

 

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

SET PASSWORD = PASSWORD('securepassword');

In the mysql shell.

 

One must go into the mysql database before applying commands in Step 3

 

Bless you ! May the rain of good luck keep showering upon you. Phewwwww

 

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!