From 3cf6de437495495ea400ade55f7b13d4077b8cb3 Mon Sep 17 00:00:00 2001 From: Micah Anderson Date: Fri, 2 Nov 2007 00:52:55 +0000 Subject: [PATCH] added nodata option to mysql handler, thanks to Daniel Bonniot (Closes: 408829) --- AUTHORS | 2 +- ChangeLog | 4 ++++ examples/example.mysql | 7 +++++++ handlers/mysql.in | 30 ++++++++++++++++++++++++------ 4 files changed, 36 insertions(+), 7 deletions(-) diff --git a/AUTHORS b/AUTHORS index 541e949..79e338a 100644 --- a/AUTHORS +++ b/AUTHORS @@ -15,7 +15,7 @@ rhatto -- rub handler and patches Patches: cmccallum@thecsl.org -Daniel.Bonniot@inria.fr +Daniel.Bonniot@inria.fr -- mysql ignores and nodata Brad Fritz -- trac patch garcondumonde@riseup.net Martin Krafft madduck@debian.org -- admingroup patch diff --git a/ChangeLog b/ChangeLog index ba68c07..5aa1fd0 100644 --- a/ChangeLog +++ b/ChangeLog @@ -24,6 +24,10 @@ version 0.9.5 -- unreleased mysql: . Fixed case where odd combination of configuration options caused sqldump backups to get overwritten with an empty file (Closes: #402679) + . Added 'nodata' option to enable you to specify tables that you want to omit + the data from a backup, but still backup the table structure. This is very + useful in cases where tables contain large amounts of cache data. See the + example.mysql for options, thanks Daniel Bonniot (Closes: #408829) pgsql: . Support configuring PGSQLUSER for real, and document it a bit; this broken support actually prevented pgsql handler to work for VServers diff --git a/examples/example.mysql b/examples/example.mysql index 25750ad..d7ab8b6 100644 --- a/examples/example.mysql +++ b/examples/example.mysql @@ -53,6 +53,13 @@ compress = yes # which databases to backup. should either be the word 'all' or a # space separated list of database names. # +# nodata = < table1 table2 table3 > (no default) +# only dump the structure for the database tables listed here, this means +# no data contained in these tables will be dumped. This is very useful +# to backup databases that have tables with cache stored in tables that +# isn't necessary to backup, but you still need the structure to exist +# on a restore +# # backupdir = < path/to/destination > (default = /var/backups/mysql) # where to dump the backups. hotcopy backups will be in a subdirectory # 'hotcopy' and sqldump backups will be in a subdirectory 'sqldump' diff --git a/handlers/mysql.in b/handlers/mysql.in index de4e4c3..64b6f49 100644 --- a/handlers/mysql.in +++ b/handlers/mysql.in @@ -6,6 +6,7 @@ getconf backupdir /var/backups/mysql getconf databases all getconf ignores +getconf nodata getconf dbhost localhost getconf hotcopy no getconf sqldump no @@ -47,7 +48,7 @@ fi ## This only works for mysqldump at the moment ignore='' -for i in $ignores; do +for i in $ignores $nodata; do ignore="$ignore --ignore-table=$i" done @@ -243,10 +244,27 @@ then fatal "Authentication problem, maybe user/password is wrong or mysqld is not running?" fi fi -fi + fi for db in $databases do + DUMP_BASE="$MYSQLDUMP $defaultsfile --lock-tables --complete-insert --add-drop-table --quick --quote-names" + + # Dumping structure and data + DUMP="$DUMP_BASE $ignore $db" + + # If requested, dump only the table structure for this database + if echo "$nodata" | grep -E '(^|[[:space:]])'"$db\." >/dev/null + then + # Get the structure of the tables, without data + DUMP_STRUCT="$DUMP_BASE --no-data $db" + for qualified_table in $nodata + do + table=$( expr match "$qualified_table" "$db\.\([^\w]*\)" ) + DUMP_STRUCT="$DUMP_STRUCT $table" + done + DUMP="( $DUMP; $DUMP_STRUCT )" + fi if [ $usevserver = yes ] then # Test to make sure mysqld is running, if it is not sqldump will not work @@ -255,9 +273,9 @@ fi fatal "Either you have an authentication problem, or mysqld doesn't appear to be running!" fi if [ "$compress" == "yes" ]; then - execstr="$VSERVER $vsname exec $MYSQLDUMP $defaultsfile --lock-tables --complete-insert --add-drop-table --quick --quote-names $ignore $db | $GZIP > $vroot$dumpdir/${db}.sql.gz" + execstr="$VSERVER $vsname exec $DUMP | $GZIP > $vroot$dumpdir/${db}.sql.gz" else - execstr="$VSERVER $vsname exec $MYSQLDUMP $defaultsfile --lock-tables --complete-insert --add-drop-table --quick --quote-names $ignore $db -r $vroot$dumpdir/${db}.sql" + execstr="$VSERVER $vsname exec $DUMP -r $vroot$dumpdir/${db}.sql" fi else # Test to make sure mysqld is running, if it is not sqldump will not work @@ -266,9 +284,9 @@ fi fatal "Either you have an authentication problem, or mysqld doesn't appear to be running!" fi if [ "$compress" == "yes" ]; then - execstr="$MYSQLDUMP $defaultsfile --lock-tables --complete-insert --add-drop-table --quick --quote-names $ignore $db | $GZIP > $dumpdir/${db}.sql.gz" + execstr="$DUMP | $GZIP > $dumpdir/${db}.sql.gz" else - execstr="$MYSQLDUMP $defaultsfile --lock-tables --complete-insert --add-drop-table --quick --quote-names $ignore $db -r $dumpdir/${db}.sql" + execstr="$DUMP -r $dumpdir/${db}.sql" fi fi debug "su $user -c \"$execstr\"" -- 2.30.2