Troubleshooting guide for resolving the Access denied error when using mysqldump with FLUSH TABLES command on MySQL servers
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.
To resolve this issue, follow these steps to verify and update your backup user's privileges:
Connect to your database as an admin user, such as vultradmin:
Verify the backup user's privileges:
Confirm the Host value matches your connection source (commonly % for all hosts unless restricted).
Grant the required global privileges:
Re-check the privileges to confirm the update:
Ensure there is a Y under both Process_priv and Reload_priv.
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.
Granting global privileges like RELOAD and PROCESS should be done cautiously and only to trusted users, as these privileges allow significant control over the server. If granting these privileges is not feasible, consider using Vultr's managed backup solutions or adjusting your mysqldump strategy to minimize the need for FTWRL.
0 Comments
Be the first to comment and share your perspective with the community.