MySQL backup script to drop into cron.*

gistfile1.txt

/etc/cron.daily/mysqldumpcron:
#!/bin/bash
#
# Create a MySQL user: GRANT SELECT, RELOAD, REPLICATION CLIENT ON *.* TO backup@localhost identified by 'password';

USER=backup
PASSWORD=password

#
# Dump the mysql and pgsql databases to a daily dump file.
#
# This script is called from /etc/logrotate.d/mysqldump

function syslog()
{
	logger -i -p daemon.info -t mysqldump "$1"
}

# Set umask to stop other users from accessing dumps.
#
umask 077

syslog "Starting database dump run"

# Optionally, empty the target dir.
# /bin/rm -f /srv/mysqldump/*.sql

# Get a re-useable list of databases.
DATABASES="$(mysql -u ${USER} -p${PASSWORD} -B -s -e 'show databases' | grep -v ^information_schema)"

# Dump mysql tables.
#
for db in ${DATABASES}; do
	syslog "Dumping database '${db}'";
	/usr/bin/mysqldump -u ${USER} -p${PASSWORD} --single-transaction --triggers --quick --routines --master-data=2 --result-file "/srv/mysqldump/${db}.sql" --databases "${db}";

	syslog "Dumping database definitions '${db}'";
	/usr/bin/mysqldump -u ${USER} -p${PASSWORD} --no-data --single-transaction --triggers --quick --routines --master-data=2 --result-file "/srv/mysqldump/${db}_frm.sql" --databases "${db}";
done

syslog "Completed database dump run"

-------
/etc/logrotate.d/mysqldumpcron:
/srv/mysqldump/*.sql {
	daily
	rotate 7
	compress
	sharedscripts
}