Skip to content

Instantly share code, notes, and snippets.

@eqhmcow
Last active December 15, 2015 10:09
Show Gist options
  • Save eqhmcow/5243589 to your computer and use it in GitHub Desktop.
Save eqhmcow/5243589 to your computer and use it in GitHub Desktop.
mysql backup script
#!/bin/bash
# backup mysql tables - does an initial dump and then backs up to final
# location using rdiff-backup
# NOTE: do NOT manually edit or update the rdiff-backup managed final backup
# location. ONLY use rdiff-backup to update files in that backup directory. if
# the files are changed directly, the historical backups that rdiff-backup
# maintains will be corrupted.
# must be nobody
NOBODY_UID=`/usr/bin/id -u nobody`
if [[ "$EUID" != "$NOBODY_UID" ]]; then
if [[ "$EUID" != "0" ]]; then
echo "$0 - you need to be root for this to run correctly"
exit 6
fi
/bin/su -s /bin/bash nobody -- -c "$0 $*"
exit $?
fi
###
# tmpdir for temp files for this script:
backup_tmpdir=`mktemp -d`
# script dumps mysql tables here first:
backup_initial_dir=`mktemp -d -p /data/tmp`
# final backup location, managed by rdiff-backup:
backup_final_dir="/backup/wh1/mysql"
###
# sanity check
if ! [[ -d $backup_final_dir ]]; then
echo "$0: [$(date)] $backup_final_dir does not exist, aborting"
exit 7
fi
###
# grab lock
ZERO_BASENAME=`/bin/basename $0`
LOCKFILE="/tmp/$ZERO_BASENAME.lock"
echo "$0: grabbing local lock - $LOCKFILE"
/usr/bin/dotlockfile -p $LOCKFILE 2> /dev/null
if [[ $? != 0 ]]; then
echo "$0: couldn't obtain lock - lockfile is $LOCKFILE" 1>&2
exit 1
fi
trap "/usr/bin/dotlockfile -pu $LOCKFILE ; exit 4" 1 2 3 13 15
###
# to speed up backup and restore operations, we apply different options to
# mysqldump for innodb tables. to do this, we first generate a list of innodb
# and non-innodb tables:
# generate list of table names with type
echo "$0: [$(date)] dumping table metadata"
time /usr/bin/mysql -u root -paspcms1 -sse 'show databases' |
/bin/egrep -v '_wh$|_wh_loading$|^(test|te?mp|Database|information_schema|mysql)$' |
while read i; do
echo "$0: [$(date)] db $i"
/usr/bin/mysql -u root -paspcms1 -sse 'show tables' "$i" |
while read j; do
echo -n "$j "
/usr/bin/mysql -u root -paspcms1 -sse "show create table $j" $i |
/bin/grep 'ENGINE' |
/usr/bin/perl -p -e 's/^.*ENGINE=(\S+).*$/$1/'
done > $backup_tmpdir/$i
done
# generate list of InnoDB tables in each database
echo "$0: [$(date)] generating list of innodb tables"
/usr/bin/mysql -u root -paspcms1 -sse 'show databases' |
/bin/egrep -v '_wh$|_wh_loading$|^(test|te?mp|Database|information_schema|mysql)$' |
while read i; do
/bin/egrep -e ' InnoDB$' $backup_tmpdir/$i |
/usr/bin/perl -p -e 'chomp; s/ \S+$/ /' > $backup_tmpdir/$i.innodb
if [[ -s $backup_tmpdir/$i.innodb ]]; then
echo >> $backup_tmpdir/$i.innodb
fi
done
# generate list of non-InnoDB tables in each database
echo "$0: [$(date)] generating list of non-innodb tables"
/usr/bin/mysql -u root -paspcms1 -sse 'show databases' |
/bin/egrep -v '_wh$|_wh_loading$|^(test|te?mp|Database|information_schema|mysql)$' |
while read i; do
/bin/egrep -ve ' InnoDB$' $backup_tmpdir/$i |
/usr/bin/perl -p -e 'chomp; s/ \S+$/ /' > $backup_tmpdir/$i.other
if [[ -s $backup_tmpdir/$i.other ]]; then
echo >> $backup_tmpdir/$i.other
fi
done
###
# run mysqldump
# backup tables in each database
echo "$0: [$(date)] dumping mysql data"
/usr/bin/mysql -u root -paspcms1 -sse 'show databases' |
/bin/egrep -v '_wh$|_wh_loading$|^(test|te?mp|Database|information_schema|mysql)$' |
while read i; do
if [[ -s $backup_tmpdir/$i.innodb ]] ; then
echo "$0: [$(date)] dumping innodb tables in db $i"
time /usr/bin/mysqldump -uroot -paspcms1 --max_allowed_packet=512M --opt --events \
--routines --triggers --no-autocommit --single-transaction \
--skip-lock-tables $i \
`/bin/cat $backup_tmpdir/$i.innodb` |
/bin/gzip > $backup_initial_dir/$i.innodb.gz
fi
if [[ -s $backup_tmpdir/$i.other ]] ; then
echo "$0: [$(date)] dumping non-innodb tables in db $i"
time /usr/bin/mysqldump -uroot -paspcms1 --max_allowed_packet=512M --opt --events \
--routines --triggers --no-autocommit $i \
`/bin/cat $backup_tmpdir/$i.other` |
/bin/gzip > $backup_initial_dir/$i.other.gz
fi
done
# backup data dumps with rdiff-backup
echo "$0: [$(date)] backing up mysql dump to $backup_final_dir"
time /bin/nice /usr/bin/rdiff-backup $backup_initial_dir $backup_final_dir
# remove initial backup files
echo "$0: [$(date)] removing temporary mysql dump data"
time /bin/rm -rf $backup_initial_dir
echo "$0: [$(date)] current disk usage:"
/bin/df -h
echo "$0: [$(date)] done"
# release lock
/usr/bin/dotlockfile -pu $LOCKFILE
exit 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment