ports/69309: mysql database backup script for periodic/daily

[Andreas Klemm]

>Number:         69309
>Category:       ports
>Synopsis:       mysql database backup script for periodic/daily
>Confidential:   no
>Severity:       non-critical
>Priority:       medium
>Responsible:    freebsd-ports-bugs
>State:          open
>Quarter:        
>Keywords:       
>Date-Required:
>Class:          update
>Submitter-Id:   current-users
>Arrival-Date:   Mon Jul 19 18:20:09 GMT 2004
>Closed-Date:
>Last-Modified:
>Originator:     Andreas Klemm
>Release:        FreeBSD 5.2.1-RELEASE-p8 i386
>Organization:
home
>Environment:
System: FreeBSD titan.klemm.apsfilter.org 5.2.1-RELEASE-p8 FreeBSD 5.2.1-RELEASE-p8 #0: Sun Jul 4 17:11:05 CEST 2004 root at titan.klemm.apsfilter.org:/usr/src/sys/i386/compile/TITAN i386

>Description:

	The mysql port is missing a script for nightly backup
	of all databases.

	Inspired by Palle Girgensons backup script in the postgresql
	port I wrote an enhanced script where you can enable/disable
	the script in /etc/rc.conf as well as finetune the script
	with additional parameters for rc.conf

	Implementation notes:
	1) The script filters db's with names "Database" and "backups" since
	   mysql treats every subdir under "base dbdir" as potential db-dir

	2) The default mysql database directory is possibly not /var/db/mysql
	   But IMHO it would be a good thing

	3) The default mysql "super-user" with all rights is possibly not
	   "root" user. It might be "mysql", I don't remember the defaults.
	
>How-To-Repeat:
>Fix:
	Install this script under
	/usr/local/etc/periodic/daily/503.mysql 

#! /bin/sh
#
# $FreeBSD$
#
# Maintenance shell script to backup mysql databases
#
# by Andreas Klemm <andreas at FreeBSD.org>, Mon Jul 19 12:12:31 CEST 2004
#
# Heavily inspired by postgresql port's backup script
# by Palle Girgensohn <girgen at pingpong.net>
#
# Copyright: do what you like with it and use it at your own risk...
#
######################################################################
#
# Put this script into /usr/local/etc/periodic/daily
#
# Add the following line(s) to /etc/rc.conf
# to enable and fine-tune nightly backup of all mysql databases:
#
# mysql_backup_enable (bool):	Set to "NO" by default
#				Set it to "YES" to enable nightly backup
# mysql_db_user (str):		Set to "root" by default (the mysql "db root")
#				Modify it for your needs (might be "mysql")
# mysql_db_passwd (str):	Set to "dbpasswd" by default
#				Modify it for your needs
# mysql_db_host (str):		Set to "localhost" by default
# mysql_backup_dir (str):	Set to "/var/db/mysql/backups" by default
#				Modify it for your needs
# mysql_backup_save_days (str):	Set to "7" by default
#				Set it to the no. of mysql backup's you want to keep
#
# mysql_dump_args (str):	By default set to:
#			-u$mysql_db_user -p$mysql_db_passwd -h$mysql_db_host
#
######################################################################
#
# Implementation notes:
#
# 1) The script filters db's with names "Database" and "backups" since
#    mysql treats every subdir under "base dbdir" as potential db-dir
# 2) The default mysql database directory is possibly not /var/db/mysql
#    But IMHO it would be a good thing
# 3) The default mysql "super-user" with all rights is possibly not
#    "root" user. It might be "mysql", I don't remember the defaults.
#
######################################################################

DIR=`dirname $0`
progname=`basename $0`
PRG=`cd $DIR; pwd `/$progname

. /etc/rc.conf

# set defaults
mysql_backup_enable=${mysql_backup_enable:-"NO"}
mysql_db_user=${mysql_db_user:-"root"}
mysql_db_passwd=${mysql_db_passwd:-"dbpasswd"}
mysql_db_host=${mysql_db_host:-"localhost"}
mysql_backup_dir=${mysql_backup_dir:-"/var/db/mysql/backups"}
mysql_backup_savedays=${mysql_backup_savedays:-"7"}
mysql_dump_args=${mysql_dump_args:-\
			"-u$mysql_db_user -p$mysql_db_passwd -h$mysql_db_host"}

case $mysql_backup_enable in
	[Nn][Oo]|[Ff][Aa][Ll][Ss][Ee]|[Oo][Ff][Ff]|0)
		exit 1
		;;
esac

# MySQL Hostname
DBHOST='localhost'

# Run as mysql super user
if [ `id -un` != $mysql_db_user ]; then
    su -l $mysql_db_user -c ${PRG}
    exit $?
fi

# $mysql_backup_dir must be writeable by db user
if [ ! -d "$mysql_backup_dir" ] ; then 
    echo "Creating $mysql_backup_dir"
    mkdir $mysql_backup_dir
fi

echo
echo "MySQL maintenance"

# Protect the data
umask 077
dbnames=`mysql ${mysql_dump_args} -e"show databases"`
rc=$?
now=`date "+%Y-%m-%dT%H:%M:%S"`
for db in ${dbnames}; do
	if [ "$db" != "Database" -a "$db" != "backups" ]; then
		echo -n " $db"
		file=$mysql_backup_dir/mysqldump_${db}_${now}.gz
		mysqldump ${mysql_dump_args} ${db} | gzip --best > $file
		[ $? -gt 0 ] && rc=3
	fi
done

if [ $rc -gt 0 ]; then
    echo
    echo "Errors were reported during backup."
fi

# cleaning up old data
find $mysql_backup_dir -name 'mysqldump_*' \
	-a -mtime +$mysql_backup_savedays -delete

exit $rc
>Release-Note:
>Audit-Trail:
>Unformatted: