temporary tables vs. backup

[Kristian Köhntopp]

When running mylvmbackup you create a snapshot in time that will recover on a different server to a consistent state. This is equivalent to uncleanly shutting down a slave and restarting it into recovery.

That means that the backup will NOT contain any temporary tables, also no CREATE TEMPORARY TABLES that were in flight within the SQL_THREAD. The backup will not be useful to restore a working slave from it when Slave_open_temp_tables > 0. That is, because if Slave_open_temp_tables > 0, there are open temp tables in the replication stream at the time of the backup with on recovery will not be present, but the replication stream will contain statements referencing such tables. Replication will stop.

mylvmbackup must always check

l1: FLUSH TABLES WITH READ LOCK
is Slave_open_temp_tables == 0?
if not,
  UNLOCK TABLES
  wait a moment
  goto l1
end if

so that the consistent snapshot we create has Slave_open_temp_tables == 0. Only then a valid recoverable backup is being created.

[Lenz Grimmer (lenzgr)]

Hi Kris, thanks a lot for reporting this - very good point indeed. I'll look into this. You don't happen to have a patch for this already?
I assume the value of slave_open_temp_tables ca be queried via the SQL interface?
Do you think it would make sense to have a counter or timer (e.g. number of retries) that restricts how many times this check is performed?

[Kristian Köhntopp (kris-launchpad)]

mysql> select variable_value from information_schema.global_status where variable_name like 'Slave_open_temp_tables';
+----------------+
| variable_value |
+----------------+
| 0 |
+----------------+
1 row in set (0,00 sec)

Restricting the number of retries IMHO makes no sense in that the backup is not fit for PIT recovery in any case.

Restricting the number of retries makes sense when you fail with an error, not making a backup, after x retries.

Retry delay should be configureable.

[Lenz Grimmer (lenzgr)]

Sorry for the delay. There is now a branch that contains code that handles the proper snapshotting of slaves and also supports waiting for open temporary tables: https://code.launchpad.net/~lxu8/mylvmbackup/mylvmbackup-ftwrl