Handy MySQL backup script
I called this script backup_mysql.bash
Change the permissions of this script,
$ chmod 700 /path/to/backup_mysql.bash
I put this in cron as,
30 * * * * /path/to/backup_mysql.bash
which backups up and zips your database dump once an hour if there are changes.
#!/bin/bash
# Usage: ./backup_mysql.bash [database]
# Database is optional. If left off, all databases will be zipped into one dump.
mysql_username="username"
mysql_password="password"
curdatetime=`date +%Y%m%d%H%M%S`
cd /var/www/mysql_backups
if [[ "${1}" != "" ]]; then
mysql_database="${1}"
mysqldump -u ${mysql_username} --password=${mysql_password} --opt ${mysql_database} > ${curdatetime}.${mysql_database}.mysql
else
mysql_database="all_databases"
mysqldump -u ${mysql_username} --password=${mysql_password} --all-databases > ${curdatetime}.${mysql_database}.mysql
fi
sed '$d' ${curdatetime}.${mysql_database}.mysql > 2; mv -f 2 ${curdatetime}.${mysql_database}.mysql
if [[ -e last.${mysql_database}.mysql ]]; then
curdump=`cat ${curdatetime}.${mysql_database}.mysql`
lastdump=`cat last.${mysql_database}.mysql`
if ! diff ${curdatetime}.${mysql_database}.mysql last.${mysql_database}.mysql >> /dev/null; then
cp -f ${curdatetime}.${mysql_database}.mysql last.${mysql_database}.mysql
gzip ${curdatetime}.${mysql_database}.mysql
chmod 700 ${curdatetime}.${mysql_database}.mysql.gz last.${mysql_database}.mysql
else
rm -f ${curdatetime}.${mysql_database}.mysql
fi
else
cp -f ${curdatetime}.${mysql_database}.mysql last.${mysql_database}.mysql
gzip ${curdatetime}.${mysql_database}.mysql
chmod 700 ${curdatetime}.${mysql_database}.mysql.gz last.${mysql_database}.mysql
fi
[Click to add or edit comments])
Please prepend comments below including a date