mysql5.7删除了root用户,如何在MySQL 5.7上重置默认的“ root”用户行为

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.

解决方案

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