mylvmbackup

temporary tables vs. backup

Bug #1217747 reported by Kristian Köhntopp
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
mylvmbackup
In Progress
High
Lenz Grimmer

Bug Description

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.

Revision history for this message
Lenz Grimmer (lenzgr) wrote :

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?

Changed in mylvmbackup:
status: New → In Progress
importance: Undecided → High
assignee: nobody → Lenz Grimmer (lenzgr)
Revision history for this message
Kristian Köhntopp (kris-launchpad) wrote :

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.

Revision history for this message
Lenz Grimmer (lenzgr) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.