Last active
December 15, 2015 10:09
-
-
Save eqhmcow/5243589 to your computer and use it in GitHub Desktop.
mysql backup script
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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