我向我的 mysql-db 添加了一个用户，并授予从多个主机访问的权限，例如:

GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host1';
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host2';
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host3';
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host4';
....

完全删除用户的最短方法是什么？我试过:

drop user 'dbuser'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'dbuser'@'%'

drop user 'dbuser';
ERROR 1396 (HY000): Operation DROP USER failed for 'dbuser'@'%'

show grants for 'dbuser';
ERROR 1141 (42000): There is no such grant defined for user 'dbuser' on host '%'

我想，% 将被视为通配符。但是唯一的方法似乎是删除每个主机的用户，例如:

drop user 'dbuser'@'host1';
drop user 'dbuser'@'host2';
drop user 'dbuser'@'host3';
...

有没有更方便的删除用户的方法？

最佳答案

MySQL <=>

select user,host,password from mysql.user;

MySQL >=5.7.x

select user,host,authentication_string from mysql.user;

以上内容你将需要创建一个回滚计划，相信我，如果删除大量用户并且你想节省时间，你将始终需要一个。

现在回答你的问题:

select concat("DROP USER ","'",user,"'@'",host,"';") from mysql.user where host like "127.0.%" or host like "192.168%";

根据您的平台，探索如何将输出粘贴到文件中并执行

关于Mysql drop user with grants for multiple hosts，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/22250870/