19

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)

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)

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.​

Grants for 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'

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

Here's some of my system info

MySQL Version

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

mysqldump version

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

Community
  • 1
  • 1
kas
  • 898
  • 3
  • 10
  • 27

1 Answers1

36

This is a result of mysql server upgrade. Run 

# mysql_upgrade -u root -p --force
# systemctl restart mysqld
synapse
  • 541
  • 5
  • 6
  • I think you're right, but I didn't run this code. I noticed the other week that there were patches for the mysql packages. I upgrade six packages to `5.7.9-1.el7` and that cleared up the issue, specifically `mysql-community-client`, `mysql-community-common`, `mysql-community-libs`, `mysql-community-libs-compat`, `mysql-community-release`, and `mysql-community-server`. I'm not sure why that was happening, but I'm glad it's working now. – kas Nov 05 '15 at 22:42
  • If your local mysql root user doesn't have a password, run `mysql_upgrade -u root --force` – Brendan Nee Feb 27 '16 at 19:52
  • This operation will upgrade columns if needed. I have numerous datetime columns and have lots of "TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format." warnings. – txyoji Mar 03 '17 at 17:47