Solving mysqldump permission (FLUSH TABLE) issues on AWS RDS Database

We decided to try run cPanel on one instance while running the MySQL server on an external server (AWS RDS). Poor decision in hindsight: don't do it. Just spin off a barebone MySQL server and connect it if you want separate hardware between the Apache and MySQL layers. 

The MySQL database connects just fine. However, we noticed that all the backups resulted in MySQL backups failing: 

[2025-09-15 02:10:24 -0800] mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045)

[2025-09-15 16:07:58 -0800] mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045)

[2025-09-15 16:08:15 -0800] mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045)

We quickly spun up our own script to run mysqldump with additional parameters: 

mysqldump --user=root --skip-lock-tables --single-transaction --events --routines --triggers --set-gtid-purged=OFF [... tables, output path etc.]

Our team pondered if changing the MySQL setting at the very high level to tinker with gtid options would work, but deemed risky/not worth it, especially since we have several InnoDB tables on each website which we understand utilizes gtid & single transaction related functions, unlike traditional MyISAM tables. Note, we are not experts in MySQL server settings etc... maybe somebody else knows better, but not us.

The easier and very safe work-around to fix the cPanel backup function is to simply add a mysqldump directive in the /etc/my.cnf file: 

[mysqldump]
set-gtid-purged=OFF

This method will simply add the set-gtid-purged=OFF option to each mysqldump command which is part of the pkgscript function, and not shake up how your MySQL server operates as a whole, so much safer.

Important to note: We are transferring websites only database I/O is not happening, at late hours, with all scheduled cronjobs turned off. 

This made our migration of websites work smoothly without having to figure out our own MySQL export/import scripts on our new Enhance.com operated Ubuntu servers.