Skip to content

Instantly share code, notes, and snippets.

Last active May 9, 2022 15:43
Show Gist options
  • Save jaygooby/5208373 to your computer and use it in GitHub Desktop.
Save jaygooby/5208373 to your computer and use it in GitHub Desktop.
Call via crontab on whatever schedule suits you; keep n full mysql Percona xtrabackups of your mysql database, with binary logs applied. Also does a full mysqldump that can then have the binary logs applied to restore to a point-in-time backup via the binlogs. Copy all of this (backup, mysqldump, binlogs) to S3.
# Carries out a full mysqldump, calls percona-xtrabackup and then
# copies the sql dump, the percona backup and your mysql bin logs
# to S3 using s3cmd
# TODO: extract out the S3 backup stuff to make it optional, and so
# other s3 programs can replace the s3cmd call.
# TODO: the if [ $? == 0 ] alert blocks should be a function
# TODO: make the if [ $? == 0 ] if [ $? != 0 ] more consistent - test
# for failure first in all cases
# Put me in cron.daily, cron.hourly or cron.d for your own custom schedule
# Running daily? You'll keep 3 daily backups
# Running hourly? You'll keep 3 hourly backups
# Who wants to know when the backup failed
# Your mysql bucket, where the backups and dumps will get
# copied to
# Where you keep your backups
# The folder in $BACKUPDIR where you'll keep your mysqldumps
# How you'll do a mysqldump
# the --master-data=2 means we're able to do point-in-time recovery
# using the binlogs that we'll also backup
MYSQLDUMP_OPTIONS="-u root -h --all-databases --single-transaction --default-character-set=latin1 --skip-set-charset --master-data=2"
# Where your mysql binary logs live
# path to innobackupex
# Add any other files you never want to remove
# The mysql user able to access all the databases
# Shouldn't need to change these...
# The dumps and percona backups we'll be removing
PREV=$(ls $BACKUPDIR | sort | grep -v "$(ls $BACKUPDIR | tail -n $NUM_BACKUPS_TO_KEEP)" | grep -v $DUMPDIR)
PREVDUMP=$(ls $DUMPDIR | sort | grep -v "$(ls $DUMPDIR | tail -n $NUM_DUMPS_TO_KEEP)")
# do a mysqldump
$DUMP | /bin/gzip -9 > $DUMPDIR/$(date +%Y%m%d_%H:%M).sql.gz
# we got a full dump
if [ $? == 0 ]; then
# only remove if $PREVDUMP is set
if [ -n "$PREVDUMP" ]; then
# remove dumps you don't want to keep
cd $DUMPDIR && rm -rf $PREVDUMP
if [ $? != 0 ]; then
echo "Looks like there was a problem deleting $PREVDUMP in $DUMPDIR" | mail $EMAIL -s "Couldn't remove $PREVDUMP in $DUMPDIR"
echo "Couldn't do the mysqldump to $DUMPDIR" | mail $EMAIL -s "mysqldump to $DUMPDIR failed"
# run a backup
if [ $? == 0 ]; then
# we got a backup, now we need to apply the binary logs
MOST_RECENT=$(ls $BACKUPDIR | grep -v $DUMPS | sort | tail -n1)
# ensure the generated cnf file used by xtrabackup has innodb_file_per_table set
# otherwise using the --export switch will fail
echo "innodb_file_per_table" >> $BACKUPDIR/$MOST_RECENT/backup-my.cnf
# binary logs were applied OK, so we have a brand new percona backup
# so remove older backups we don't need
if [ $? == 0 ]; then
# only remove if $PREV is set
if [ -n "$PREV" ]; then
# remove backups you don't want to keep
cd $BACKUPDIR && rm -rf $PREV
if [ $? != 0 ]; then
echo "Looks like there was a problem deleting $PREV in $BACKUPDIR"
# Sync the percona backups to S3
# ionice the s3 sync, so we don't block the mysql server
/usr/bin/ionice -c 2 -n 6 /usr/bin/s3cmd sync --delete-removed $BACKUPDIR/* s3://$S3_BUCKET/backups/
# alert if the s3 sync failed
if [ $? != 0 ]; then
echo "Looks like there was a problem with the mysql backup syncing to S3" | mail $EMAIL -s "Couldn't copy mysql backup to S3"
# now back up the latest binlogs too, we'll need these if we want to do a point-in-time restore (which the mysqldump is prepped for)
/usr/bin/ionice -c 2 -n 6 /usr/bin/s3cmd sync --delete-removed $MYSQL_BIN_LOG_DIR/mysql-bin* s3://$S3_BUCKET/binlogs/
if [ $? != 0 ]; then
echo "Couldn't sync the SQL binlogs to S3 - you won't be able do a point-in-time recovery" | mail $EMAIL -s "Mysql binlogs not copied to S3"
echo "Couldn't apply the binary logs to the backup $BACKUPDIR/$MOST_RECENT" | mail $EMAIL -s "Mysql binary log didn't get applied to backup"
# problem with initial backup :(
echo "Couldn't do a mysql backup" | mail $ALERT -s "Mysql backup failed"
Copy link

Thanks @caot

Copy link

Thanks for this!

I've hacked for a multisystem setup using an option file and sane defaults if it isn't otherwise set. I've also enabled compression to save space which isn't directly compatible with apply logs so I've left that section alone until I need to restore. Also handy is the explicit permissions the backup user needs if you don't want to use root.

Copy link

Nice start, but did have a question - if BACKUPDIR is just created, the PREV logic doesn't allow for you to "get 3 backups" - it creates a backup and then removes it on the next run. To get around this I created my backup directory and then did "mkdir 1 2 3 4 5 6 7" to create 7 initial entries that eventually get fully replaced by 7 backups.

Copy link

This is great and, I may fork it to use with Monit to ensure the backup succeeded.


Copy link

Thankds @caot - updated with your change

Copy link

./ line 87: /backup/ Not a directory
Could not open required defaults file: /backup/
Fatal error in defaults handling. Program aborted
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 4545
main::get_option('innodb_data_file_path') called at /usr/bin/innobackupex line 2631
main::apply_log() called at /usr/bin/innobackupex line 1578
innobackupex: Error: no 'innodb_data_file_path' option in group 'mysqld' in server configuration file '' at /usr/bin/innobackupex line 4545.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment