MySQL 5.7 Reset root password

With mysql 5.7 the mysql.user table do not have the Password column, it has been replaced by the authentication_string column. Another notable difference is the plugin column, which allows for different authentication methods.

In Ubuntu 18.04 LTS and later release the default authentication plugin/method for root user is auth_socket. This plugin uses the SO_PEERCRED socket option to obtain information about the user running the client program. If the mysql client is ran by root user it will be authenticated and granted root privileges. There is no password required.

We can change this and revert to the native password authentication method. To do this restart the mysql server with skip-grant-tables option like

[mysqld]
skip-grant-tables

We can then set a password for root as

update user set authentication_string=password('ASECRETPASSWORD') where user='root';

We also need to change the authentication plugin to mysql_native_password

update user set plugin='mysql_native_password' where user='root';

Now restart mysql without the skip-grant-tables.

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Leave a comment

Your email address will not be published. Required fields are marked *