CODEWITHSUNDEEP
mysql
robbintt
robbintt

Reputation: 342

How can I reset default 'root' user behavior on MySQL 5.7

I am happy with the new MySQL 5.7 'root' user behavior. It allows only the linux root user on localhost to log in to MySQL as the MySQL root user.

I recently imported my old users table and it wrote over this change, now anyone can login as root on localhost with a password.

How can I restore the default MySQL 5.7 root user settings?

Please read the question carefully, as a lot of this functionality has changed from 5.5 to 5.7 and it is easy to answer this question backwards.

Upvotes: 2

Views: 7989

Answers (1)

robbintt
robbintt

Reputation: 342

Problem:

The auth_socket module needs manually installed and enabled on the root user if you write over the 5.7 changes by importing your old MySQL 5.5 database and user table.

Solution:

For users we want to still use mysql_native_password, the default. For root, we want to use auth_socket.

install plugin auth_socket soname 'auth_socket.so';
use mysql; update user set plugin='mysql_native_password';
update mysql.user set plugin = 'auth_socket' where User='root';
flush privileges;

If you do it in the wrong order (add root's auth_socket column before installing the plugin), then you will be unable to load mysql since the plugin won't perform authentication. To start and run mysql in safe mode use this:

sudo service mysql stop
sudo mysqld_safe --skip-grant-table &
sudo mysql -u root -p -h localhost

These fixes were performed when importing a full database and user table from MySQL 5.5 to MySQL 5.7, migrating from Ubuntu 14.04 to Ubuntu 16.04.

Tools:

See Plugins: show plugins \g

Show auth_socket on root user: select auth_socket from mysql.user where user='root';

Upvotes: 6

Related Questions