How To Fix the mysqldump "Couldn't execute FLUSH TABLES Access denied" Error?

An update to the MySQL server introduced a fix for a bug where mysqldump with --single-transaction could produce inconsistent backups. This inconsistency occurred because the GTID set was captured before the transaction snapshot, leading to a mismatch between the GTID set and the actual data. The fix implemented the use of FLUSH TABLES WITH READ LOCK (FTWRL) to ensure a consistent snapshot by locking all tables during the backup process.

Executing mysqldump with certain flags causes it to internally run FLUSH TABLES WITH READ LOCK. To successfully perform this operation, the MySQL user needs the global RELOAD and PROCESS privileges (ON *.*). Without these privileges, mysqldump fails with an "Access Denied" error.

Update Backup User Permissions

To resolve this issue, follow these steps to verify and update your backup user's privileges:

1. Connect to your database as an admin user, such as vultradmin:

   console Copy

   $ mysql -h [HOST] -P [PORT] -u vultradmin -p
   

   Explain Code

2. Verify the backup user's privileges:

   sql Copy

   mysql> SELECT User, Host, Process_priv, Reload_priv FROM mysql.user WHERE User = '<username>';
   

   Explain Code

   Confirm the Host value matches your connection source (commonly % for all hosts unless restricted).

3. Grant the required global privileges:

   sql Copy

   mysql> GRANT RELOAD, PROCESS ON *.* TO '<username>'@'%';
   mysql> FLUSH PRIVILEGES;
   

   Explain Code

4. Re-check the privileges to confirm the update:

   sql Copy

   mysql> SELECT User, Host, Process_priv, Reload_priv FROM mysql.user WHERE User = '<username>';
   

   Explain Code

   Ensure there is a Y under both Process_priv and Reload_priv.

5. Retry your mysqldump command.

This will let your backup user to perform FLUSH TABLES WITH READ LOCK successfully, preventing "Access denied" errors when running mysqldump backups.