The new bugzilla uses InnoDB tables, for which our mysqlhotcopy backup strategy doesn't work properly. The foundation and rt3 databases on button are also using InnoDB and hence not getting included in our backups.
Review of attached appreciated, tested on my local MySQL databases seems to work fine.
>From 616fcd692b00388767aebf870c06b284bf2fc2c4 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 | 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
_______________________________________________ gnome-infrastructure mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-infrastructure
