mysql-dump-script

#!/bin/bash
CONFDIR=/etc/chem-zfs-backup-server/zfs-rsync.d
SSH="ssh -p ${SSHPORT:-22} -o ConnectTimeout=10"

# to restore a backup of a database taken by this script, begin by 
# copying mysql-files/$DATABASE/*.gz  to /var/lib/mysql-files/$DATABASE
# on the mysql server and gunzip them all. Then you might:
#
# drop database $DATABASE;
# create database $DATABASE;
# for i in /var/lib/mysql-files/$DATABASE/*.sql; do mysql --defaults-file=/etc/mysql/debian.cnf $DATABASE < $i; done
# for i in /var/lib/mysql-files/$DATABASE/.txt; do mysqlimport --defaults-file=/etc/mysql/debian.cnf $DATABASE $i; done
#
# Note: by default mysql is strict about filesystem locations it can export to/import from, and the default is
# /var/lib/mysql-files . The mysqlimport needs to have the a fully-qualified filepath
#
# Also, trigger functions are exported to $tablename.sql, but regular functions end up in the mysql.proc table.
# Determining the best way to restore those is left as an exercise for the interested reader.
# Similar comments apply to users, grants etc - see mysql.user, mysql.tables_priv probably

set -xv
SERVER=$1
if [ -z $SERVER ] ; then
 echo $0 Server
 exit 1
fi

(
$SSH root@$SERVER ls -1 /root/.my.cnf >/dev/null 2>&1
if [ $? -eq 0 ] ; then
  MYSQL=mysql
  MYSQLDUMP=mysqldump
else
  MYSQL="mysql --defaults-file=/etc/mysql/debian.cnf"
  MYSQLDUMP="mysqldump --defaults-file=/etc/mysql/debian.cnf"
fi

$SSH root@$SERVER "
set -e
SQLDIR=/var/lib/mysql-files/

mkdir -p \$SQLDIR
chown mysql:mysql \$SQLDIR

DBS=\$($MYSQL -NB -e \"show databases\")

for DB in \$DBS; do
 DB_DIR=\$SQLDIR/\$DB
 mkdir -p \$DB_DIR
 chown mysql:mysql \$DB_DIR
 rm -f \$DB_DIR/*.gz
 $MYSQLDUMP --single-transaction --tab=\$DB_DIR \$DB
 gzip -r \$DB_DIR
done
"
) >/var/log/chem-zfs-backup-server/${SERVER}-mysql-prepare 2>&1