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

Thanks very much for replying! :)

On Monday, July 3, 2017 at 8:06:12 PM UTC+5:30, Axel Schwenke wrote:
> On 03.07.2017 15:47, Vaibhaw 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.
>

Yes, I am trying to do what lvmbackup does. But since we are deploying this on EC2 & EC2 provides us simple APIs to do snapshots, we aren't using lvmbackup.

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

That it is not essential for the binary logs to be in sync is what I inferred from reading the documentation as well. Thanks for confirming it for me. :)

So what confused is why some folks recommend the FLUSH operation after FTWRL. It seems like the smart thing to do but it isn't mandatory - the only reason I read was that it makes PITR easy.

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

Yes, I was trying to understand whether or not binary logs must be in sync with InnoDB during a backup.

> > 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.
>
Yeah, what I meant was InnoDB can handle itself and be consistent after the restore of a snapshot. But it won't be consistent with the binary logs. I was concerned about how it would affect the overall consistency of the master/slave cluster.

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

So what I am trying to do is - take a backup on either master or the slave, depending on the need & then be able to restore that one snapshot on each node - master & slaves. My initial impression was that I could simply cleanup all binary/relay logs, rewrite the UUID at auto.cnf and setup slaves to the bin log position recorded during backup and be done.

However, someone pointed me to additional tasks like setting the value of global variable gtid_executed correctly and figure out what happens when binary logs are ahead or behind InnoDB. & I wasn't able to authoritatively answer the later - what will happen when binary logs are ahead or behind of InnoDB and they aren't deleted during restore. Received on Tue Jul 04 2017 - 10:21:45 CEST