Re: Get binary logs and storage engine in sync for backup

On 03.07.2017 15:47, vabu.vayu_at_gmail.com wrote:
> 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.

In short: you are re-inventing http://www.lenzg.net/mylvmbackup/ I suggest you google "mysql lvm backup" as well. And then read.

> *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?*

I don't see the point in that question. There is no need to snapshot the binlog at the same time when you snapshot the database. It is perfectly enough to record the master binlog position (or GTID) after you have acquired the global lock. You might also issue a FLUSH command to rotate the binlog at that point in time.

[Quoted] And then there is of course the sync_binlog server variable. If you want your binlog to be synchronized with InnoDB redo logging, you must set it to 1.

> 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.

That is again not the point. When you acquire the global lock, this doesn't guarantee that everything is flushed to disk anyway. Not for InnoDB at least. The snapshot you take will be "dirty" from InnoDB's point of view, requiring InnoDB to do a recovery (just like after a crash). InnoDB will then roll to the last transaction that was flushed in the redo log. Depending on your setting of innodb_flush_log_at_trx_commit that will be the last committed transaction or some earlier.

[Quoted] The binlog will contain that transaction (with sync_binlog=1), but might contain additional events. When InnoDB recovers, it takes care of the binlog itself. However: for backup/recovery there is no point in saving the binlogs. If you want to recover the master, it can continue perfecly without the old binlogs. And when you clone a slave from the snapshot, all you need is the master binlog position. Received on Mon Jul 03 2017 - 16:36:08 CEST