On Sun, 2009-08-09 at 15:33 -0400, Owen Taylor wrote: > But I agree that not using the shell is more straightforward, if not > more convenient. I couldn't remember if subprocess was a Python 2.4 or > 2.5 addition, but in a check it seems to be a 2.4 addition and thus OK > on RHEL5.
Here's a new version using subprocess and setting up the pipelines in Python. - Owen
>From 8a8c7a10de94c90d984528c7c96354ee3e7db419 Mon Sep 17 00:00:00 2001 From: Owen W. Taylor <[email protected]> Date: Sun, 9 Aug 2009 14:50:45 -0400 Subject: [PATCH] Handle InnoDB tables in copy-backup.py 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 | 67 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++- 1 files changed, 66 insertions(+), 1 deletions(-) diff --git a/copy-db.py b/copy-db.py index 79d73d1..7b1f06f 100755 --- a/copy-db.py +++ b/copy-db.py @@ -2,6 +2,16 @@ # Determine what databases to backup and do it import os, sys +import subprocess + +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 @@ -27,4 +37,59 @@ for i in uidbs: # Backup! 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 = subprocess.Popen(['mysql', '--batch', '-e', 'show table status', db], + stdout=subprocess.PIPE) + first = True + can_hotcopy = True + for line in table_status.stdout: + 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.stdout.close() + table_status.wait() + + if can_hotcopy: + verbose("Backing up %s via mysqlhotcopy"% db) + hotcopy = subprocess.Popen(['mysqlhotcopy', '--quiet', '--allowold', db, '/var/lib/mysql-backup']) + hotcopy.wait() + else: + verbose("Backing up %s via mysqldump" % db) + outfilename = os.path.join('/var/lib/mysql-backup/', db + ".dump.gz") + outfile = open(outfilename, "w") + dump = subprocess.Popen(['mysqldump', '--single-transaction', db], + stdout=subprocess.PIPE) + gzip = subprocess.Popen(['gzip', '-c'], + stdin=dump.stdout, stdout=outfile) + gzip.wait() + outfile.close() -- 1.6.2.5
_______________________________________________ gnome-infrastructure mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-infrastructure
