Get binary logs and storage engine in sync for backup

[Quoted] [Quoted] I am writing a backup solution for MySQL for our org. The approach we picked is to do volume snapshot after obtaining the read lock using "FLUSH TABLES WITH READ LOCK" (FTWRL). Since we intend to do backups during off peak hours, we are reasonably sure of obtaining the lock quickly.

[Quoted] [Quoted] *However, how do I make sure that the binary log files are in sync with the storage engine (InnoDB) once the tables have been flushed & read lock obtained via the FTWRL command?*

[Quoted] [Quoted] I have been reading through the documentation for days trying to find a definite answer but it seems that the binary log isn't flushed to the disk during the FTWRL cycle and thus a volume snapshot taken at such a time might not have all it's binary logs flushed to disk.

Is my understanding correct? If so, are there any MySQL options to alleviate this i.e. ensure that all the binary logs are flushed (after FTWRL) and InnoDB and binary logs are consistent on the disk.

The reason we wish to ensure that our binary logs are in complete sync is that we are doing GTID based replication & intend to use these snapshots for disaster recovery on both master & slaves. I wish to avoid any inconsistencies between binary logs and InnoDB while taking a backup.

Thanks. Received on Mon Jul 03 2017 - 15:47:03 CEST