When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:
mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)
mysql>
Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.
You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.
If you want to just allow anonymous SELECT against that table, first run this:
GRANT USAGE ON *.* TO 'wordpress'@'%';
This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.
You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:
SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
SELECT CONCAT('''',user,'''@''',host,'''') userhost
FROM mysql.user WHERE user='wordpress'
) A;
The documentation is misleading. I read it exactly the same way you do, which isn't what the utility does.
Adding --flush-privileges causes mysqldump to include the following in the backup file, after dumping the mysql schema...
--
-- Flush Grant Tables
--
/*! FLUSH PRIVILEGES */;
...which of course causes the server where the dump is being restored to re-read the potentially-changed grant tables.
And, that's all it does.
Confirmation of this can be found in the source code for mysqldump "10.13" (the version bundled with MySQL Server 5.5.30):
if (flush_privileges && using_mysql_db == 0)
{
fprintf(md_result_file,"\n--\n-- Flush Grant Tables \n--\n");
fprintf(md_result_file,"\n/*! FLUSH PRIVILEGES */;\n");
}
The using_mysql_db == 0 comparison was confusing at first, but it turns out to be the return value from a string comparison function where "0" means "identical."
Best Answer
You need to add the
--set-gtid-purged=OFFflag to your mysqldump statement. This will stop it from writing the set-gtid-purged line in the dump file.