[PATCH] Handle InnoDB tables in copy-backup.py

[Owen W. Taylor <otaylor fishsoup net>]

mysqlhotcopy doesn't work for InnoDB tables. If we find any tables
that mysqlhotcopy can't handle, use mysqldump --single-transaction
instead to dump the database.

(We also use mysqldump --single-transaction for the case of mixed
tables, even though it won't give a consistent snapshot. This is
the behavior we want for bugzilla.)
---
 copy-db.py |   61 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
 1 files changed, 60 insertions(+), 1 deletions(-)

diff --git a/copy-db.py b/copy-db.py
index 79d73d1..ac442e9 100755
--- a/copy-db.py
+++ b/copy-db.py
@@ -3,6 +3,15 @@
 
 import os, sys
 
+do_verbose = False
+for a in sys.argv:
+    if a == '--verbose' or a == '-v':
+        do_verbose = True
+
+def verbose(s):
+    if do_verbose:
+        print >>sys.stderr, s
+
 dbs = [] # Databases on the machine, got from MySQL
 uidbs = {} # Databases not to be backed up, read from copy-db.exclude
 
@@ -26,5 +35,55 @@ for i in uidbs:
         dbs.remove (i)
 
 # Backup!
+def shell_quote(s):
+    return "'" + s.replace("'", "'\\''") + "'"
+
 for db in dbs:
-    os.spawnlp(os.P_WAIT, 'mysqlhotcopy', 'mysqlhotcopy', '--quiet', '--allowold', db, '/var/lib/mysql-backup')
+    # mysqlhotcopy only works for MyISAM and ARCHIVE tables. If a database has
+    # only tables of those types, then we use mysqlhotcopy.
+    #
+    # For InnoDB tables we can use mysqldump --single-transaction to get a
+    # consistent snapshot of the database.
+    #
+    # For tables with a mixture of InnoDB and MyISAM tables, neither of the
+    # above methods will work and give a consistent snapshot. We could
+    # use 'mysqldump --lock-tables', but that would keep the entire database
+    # locked for the entire length of the dump. Instead we assume that in
+    # this case, the application doesn't care much about the consistentcy
+    # of the MyISAM tables and use --single-transaction anyways. (This is the
+    # right thing to do for bugzilla where everything but the bugs_fulltext
+    # table is InnoDB. bugs_fulltext is just a mirror of the other tables for
+    # searching purposes.)
+    #
+    # Note that mysqlhotcopy is not necessarily faster than mysqldump - the
+    # compressed dump will typically be much smaller and faster to write to
+    # disk than the copy. The hot copy, on the other hand, may be more rsync
+    # friendly when we rsync the databases to the backup machine (This theory
+    # is untested.)
+    #
+    # Future enhancement would be to extent copy-db.exclude to allow specifying
+    # per-database backup methods.
+
+    # Figure out what types of tables the database has
+    table_status = os.popen("mysql --batch -e 'show table status' %s" % shell_quote(db), 'r')
+    first = True
+    can_hotcopy = True
+    for line in table_status:
+        if first: # skip header line
+            first = False
+            continue
+        fields = line.rstrip().split("\t")
+        table = fields[0]
+        engine = fields[1]
+        if engine != 'MyISAM' and engine != 'ARCHIVE':
+            can_hotcopy = False
+    table_status.close()
+
+    if can_hotcopy:
+        verbose("Backing up %s via mysqlhotcopy"% db)
+        os.spawnlp(os.P_WAIT, 'mysqlhotcopy', 'mysqlhotcopy', '--quiet', '--allowold', db, '/var/lib/mysql-backup')
+    else:
+        verbose("Backing up %s via mysqldump" % db)
+        outfile = os.path.join('/var/lib/mysql-backup/', db + ".dump.gz")
+        os.spawnlp(os.P_WAIT, 'sh', 'sh', '-c',
+                  "mysqldump --single-transaction %s | gzip -c > %s" % (shell_quote(db), shell_quote(outfile)))
-- 
1.6.2.5


--=-2KYO3o4BHxYglSBZ7l1q--