How do I fix the mysqldump "Couldn't execute FLUSH TABLES Access denied" error?

An update to the mysqldump package fixed a bug with inconsistent backups with --single-transaction. Because the fix was rolled out in a minor version, automated backups pulled in the update and broke backups for some users.

Update Backup User Permissions

Our recommended solution is to update the permissions of the backup user so you can continue using --single-transaction.

Log into the MySQL console, then run the following command to list the users and their relevant permissions:

    
        
             
SELECT User, HOST, Process_priv, Reload_priv FROM mysql.user; 

        
    

Find your backup user, and make sure the host matches what you connect with. It should be % unless you restrict it to a single host.

Run the following command on the correct user:

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

        
    

To confirm the fix, run the command to list users again:

    
        
             
SELECT User, HOST, Process_priv, Reload_priv FROM mysql.user; 

        
    

Confirm that there is a Y in both columns.

Alternative Solutions

As an alternative, you can remove the --single-transaction flag from your mysqldump command. We don’t recommend this option because it could cause your backups to be inconsistent, risking data loss and restoration issues.

You can also downgrade your version of mysqldump. We don’t recommend this option because downgrades lack bug fixes and future improvements.

However, if you are using Amazon Relational Database Service (RDS), you need to use one of these alternative solutions because RDS doesn’t allow super permissions. Learn more about fixing this error on RDS.

Related Topics