SimpleMySQLBackup

From Mike Neir's Wiki

Premise

This script makes quick and easy backups of MySQL databases, compresses them, and removes backups older than 7 days old. It makes for a quick and easy backup system for MySQL.

Script

#!/bin/bash
HOME=/root
if [ -f /etc/bashrc ]; then
       . /etc/bashrc
fi

BACKUPLOC="/home/dbbackups"
mkdir -p $BACKUPLOC

for db in `mysql -s -B -e "show databases"`; do nice -n 18 mysqldump --opt $db > $BACKUPLOC/$db.`date +%Y%m%d-%H%M%S`.sql; done
nice -n 18 bzip2 -9 $BACKUPLOC/*.sql
chown root:root $BACKUPLOC/* 
chmod 600 $BACKUPLOC/* 

find $BACKUPLOC -mtime +7 | xargs rm -f

Notes

• I had to add the stuff about HOME and source the /etc/bashrc file on my systen because the mysql command line utilities weren't finding my user info in /root/.my.cnf properly. Inserting that stuff seemed to take care of the issue.
• The window of time in which backups are kept can be changed by adjusting the last line.