错误(1142)使用mysqldump在session_variables表上拒绝SELECT命令 [英] Error (1142) SELECT command denied on session_variables table with mysqldump

问题描述

当我尝试运行mysqldump -u dump -p myschema > dumpfile.sql时，出现以下错误消息:

When I try to run mysqldump -u dump -p myschema > dumpfile.sql I get this error message:

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': SELECT command denied to user 'dump'@'localhost' for table 'session_variables' (1142)

我发现某物有关添加--set-gtid-purged=OFF的类似错误.但是我没有在较旧的MySQL数据库上运行mysqldump的MySQL 5.6版本.但是，当我尝试它时，出现了一个新的但相似的错误:

I found something about adding --set-gtid-purged=OFF for a similar error. But I'm not running the MySQL 5.6 version of mysqldump on an older MySQL database. However, when I try it, I get a new, yet similar, error:

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version'': SELECT command denied to user 'dump'@'localhost' for table 'session_variables' (1142)

我不确定用户为什么需要访问session_variables表(我假设它是information_schema.SESSION_VARIABLES)，因为

I'm not sure why the user needs access to the session_variables table, which I presume is information_schema.SESSION_VARIABLES, since this SO post said that the following permissions were sufficient.​

dump @ localhost的赠款

 GRANT USAGE ON *.* TO 'dump'@'localhost'
 GRANT SELECT, LOCK TABLES ON mysql.* TO 'dump'@'localhost'
 GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON myschema.* TO  'dump'@'localhost'

这是怎么回事，如何使mysqldump工作?

What's going on and how do I make mysqldump work?

这是我的一些系统信息

MySQL版本

服务器版本:5.7.8-rc MySQL社区服务器(GPL)

Server version: 5.7.8-rc MySQL Community Server (GPL)

mysqldump版本

版本10.13 Distrib 5.7.8-rc，用于Linux(x86_64)

Ver 10.13 Distrib 5.7.8-rc, for Linux (x86_64)

推荐答案

这是mysql服务器升级的结果. 运行

This is a result of mysql server upgrade. Run

# mysql_upgrade -u root -p --force
# systemctl restart mysqld

这篇关于错误(1142)使用mysqldump在session_variables表上拒绝SELECT命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！