Fixing mysqldump: Couldn't execute 'FLUSH TABLES': Access denied

MySQL did an update to their mysqldump dump package for the better, but unfortunately, the change came in a minor patch release and has broken MySQL dump backups for some users.

mysqldump: Couldn't execute 'FLUSH TABLES': Access denied

Table Of Contents

• Fix 1 Add Permissions (Preferred)
• Fix 2 Remove Single Transaction
• Fix 3 Downgrade
• RDS?
• What Changed?

Fix 1 Add Permissions (Preferred)

The ideal fix, as you can still enjoy the benefits of a --single-transaction, is to update the permissions of the backup user.

1) log in to MySQL Console

2) Run the Following Command

select `User`,`HOST`, `Process_priv`,`Reload_priv` from mysql.user;

3) Find your backup user, and make sure the host matches what you connect with; it should be % unless you lock it down to a single host

4) Run the following command on the correct user

GRANT RELOAD,PROCESS ON *.* TO 'backups'@'%';
FLUSH PRIVILEGES;

5) step two again and confirm you can see a 'Y' in both columns

The backup should now run again.

Fix 2 Remove Single Transaction

Remove the --single-transaction flag from your mysqldump command.

This is not recommended, as your backup could not be consistent. It is causing data loss and restoring issues.

Fix 3 Downgrade

Downgrade mysqldump version. Steps of which would be beyond this simple guide as downgrades could have unforeseen issues.

RDS?

AWS RDS is tricky as they don't allow super permissions. Learn More.

You need to downgrade or remove --single-transaction flag from your mysqldump command. We hope that mysql release a fix with a new command flag to ignore this error.

What Changed?

These changes came about when MySQL fixed a bug using single-transaction with --set-gtid-purge=on. This could generate a dump file with inconsistent data if insert queries were sent between MySQL processing both commands, and the data would be missing from the dump. This fix corrects this issue. Learn more

Unfortunately, this fix was rolled out in a minor version causing systems with automated updates to pull it in and break backups.