Skip to content

Instantly share code, notes, and snippets.

@geoffgarside
Created January 24, 2011 14:11
Show Gist options
  • Save geoffgarside/793262 to your computer and use it in GitHub Desktop.
Save geoffgarside/793262 to your computer and use it in GitHub Desktop.
FreeBSD periodic/daily script for running MySQL backups
#! /bin/sh
#
# $FreeBSD$
#
# Maintenance shell script to backup mysql databases
# Put this in /usr/local/etc/periodic/daily, and it will be run
# every night
#
# By Geoff Garside <Geoff.Garside at m247.com>, Mon, Jan 24 13:04:18 GMT 2010
#
# Inspired by postgresql port's backup script by
# Palle Girgensohn <girgen@pingpong.net>
#
# In public domain, do what you like with it,
# and use it at your own risk... :)
#
# Define these variables in either /etc/periodic.conf or
# /etc/periodic.conf.local to override the default values.
#
# ls / # do backup of all databases
# daily_mysql_backup_enable="foo bar db1 db2" # only do backup of a limited selection of databases
#
# Fine tune nightly backup you may use the following
#
# daily_mysql_host (str): Set mysql host to connect to. Default "localhost".
# daily_mysql_user (str): Set mysql user to login with. Default "root".
# daily_mysql_passwd (str): Set mysql user password. Default blank.
# daily_mysql_backupdir (str): Set directory to backup to. Default /var/db/mysql/backups.
# daily_mysql_savedays (str): Number of days to keep backups. Default 7.
# daily_mysql_dumpargs (str): Arguments to be passed to mysqldump. Default "--opt".
if [ -r /etc/defaults/periodic.conf ]
then
. /etc/defaults/periodic.conf
source_periodic_confs
fi
daily_mysql_enable=${daily_mysql_enable:-"NO"}
daily_mysql_host=${daily_mysql_host:-"localhost"}
daily_mysql_user=${daily_mysql_user:-"root"}
daily_mysql_passwd=${daily_mysql_passwd:-""}
daily_mysql_backupdir=${daily_mysql_backupdir:-"/var/db/mysql/backups"}
daily_mysql_savedays=${daily_mysql_savedays:-"7"}
daily_mysql_dumpargs=${daily_mysql_dumpargs:-"--opt"}
eval backupdir=${daily_mysql_backupdir}
daily_mysql_args=""
test -n "$daily_mysql_host" && daily_mysql_args="${daily_mysql_args}-h${daily_mysql_host} "
test -n "$daily_mysql_user" && daily_mysql_args="${daily_mysql_args}-u${daily_mysql_user} "
test -n "$daily_mysql_passwd" && daily_mysql_args="${daily_mysql_args}-p${daily_mysql_passwd} "
rc=0
mysql_backup() {
# daily_mysql_backupdir must be writeable by user mysql
# /var/db/mysql is just that under normal circumstances,
# but this might not be where you want the backups...
if [ ! -d ${backupdir} ] ; then
echo Creating ${backupdir}
mkdir -m 700 ${backupdir}; chown mysql ${backupdir}
fi
echo
echo "MySQL backups"
# Protect the data
umask 077
rc=$?
now=`date +"%Y-%m-%dT%H:%M:%S"`
db=$1
while shift; do
echo -n " $db"
file=${daily_mysql_backupdir}/mysqldump_${db}_${now}
su -l ${daily_mysql_user} -c "umask 077; mysqldump ${daily_mysql_dumpargs} ${daily_mysql_args} ${db} | gzip -9 > ${file}.gz"
test $? -gt 0 && rc=3
db=$1
done
if [ $rc -gt 0 ] ; then
echo
echo "Errors were reported during backup."
fi
# cleaning up old data
find ${backupdir} -name 'mysqldump_*' \
-a -mtime +${daily_mysql_savedays} -delete
echo
}
case "$daily_mysql_backup_enable" in
[Yy][Ee][Ss])
dbnames=""
dbresults=`su -l ${daily_mysql_user} -c "umask 077; mysql ${daily_mysql_args} -e 'show databases'"`
for db in ${dbresults} ; do
test "$db" = "Database" -o "$db" = "backups" && continue
dbnames="${dbnames} ${db}"
done
mysql_backup $dbnames
;;
[Nn][Oo]|"")
;;
*)
mysql_backup $daily_mysql_backup_enable
;;
esac
exit $rc
#!/bin/sh
#
# Shell script to run after mysql_backup to rsync the
# backups to a remote host.
#
# Written by Geoff Garside <geoff.garside@m247.com>
#
# Define these variables in either /etc/periodic.conf or
# /etc/periodic.conf.local to override the default values.
#
# daily_mysql_backup_enable="YES" # do backups
# daily_mysql_rsync_enable="YES" # rsync them
# daily_mysql_rsync_user="root" # user to get keys from
# daily_mysql_rsync_ssh_args="ssh"
# daily_mysql_rsync_target="user@backups:mybackups"
#
daily_mysql_rsync_user="root"
# Copy some vars in from /usr/local/etc/periodic/daily/503.mysql
daily_mysql_backupdir="/var/db/mysql/backups"
if [ -r /etc/defaults/periodic.conf ]; then
. /etc/defaults/periodic.conf
source_periodic_confs
fi
daily_mysql_rsync_flags="-tavz -e ${daily_mysql_rsync_ssh_args} --delete"
eval backupdir=${daily_mysql_backupdir}
rc=0
case "$daily_mysql_backup_enable" in
[Yy][Ee][Ss])
# OK backups are being taken, we can proceed.
case "$daily_mysql_rsync_enable" in
[Yy][Ee][Ss])
# Even better, we want to rsync them too
test "$(ls -A ${backupdir})" || exit $rc
today=`date +"%Y-%m-%d %H:%M:%S"`
echo "[$today]: $0 ran" >> /var/log/mysql-rsync.log
echo "/usr/local/bin/rsync ${daily_mysql_rsync_flags} ${backupdir} ${daily_mysql_rsync_target}" >> /var/log/mysql-rsync.log
su -l ${daily_mysql_rsync_user} -c "/usr/local/bin/rsync ${daily_mysql_rsync_flags} ${backupdir} ${daily_mysql_rsync_target}"
[ $? -gt 0 ] && rc=3
;;
esac
;;
esac
exit $rc
@till
Copy link

till commented Dec 29, 2016

@geoffgarside Thanks for posting this, I've forked it, and fixed a bug in the first script. Do you mind if I add a "proper" license (like BSD-2-Clause) to the repository?

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