如何在MySQL 5.7上重置默认的"root"用户行为 [英] How can I reset default 'root' user behavior on MySQL 5.7

问题描述

我对新的MySQL 5.7"root"用户行为感到满意.它仅允许localhost上的linux根用户以MySQL根用户身份登录MySQL.

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.

我最近导入了旧的用户表，它覆盖了此更改，现在任何人都可以使用密码以本地用户身份在localhost上登录.

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

如何恢复默认的MySQL 5.7 root用户设置?

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

请仔细阅读问题，因为许多功能已从5.5更改为5.7，并且很容易向后回答此问题.

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.

推荐答案

问题:

如果通过导入旧的MySQL 5.5数据库和用户表来覆盖5.7更改，则需要在root用户上手动安装并启用auth_socket模块.

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.

解决方案:

对于用户，我们仍要使用默认值mysql_native_password.对于root，我们要使用auth_socket.

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;

如果您以错误的顺序执行操作(在安装插件之前添加root的auth_socket列)，则由于插件将不会执行身份验证，因此您将无法加载mysql.要以安全模式启动和运行mysql，请使用以下命令:

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

这些修复是在将完整的数据库和用户表从MySQL 5.5导入到MySQL 5.7，从Ubuntu 14.04迁移到Ubuntu 16.04时进行的.

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.

工具:

请参阅插件:show plugins \g

在root用户上显示auth_socket:select auth_socket from mysql.user where user='root';

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

这篇关于如何在MySQL 5.7上重置默认的"root"用户行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！