MySQL Backup Script

From Wiki
Jump to: navigation, search
#!/bin/bash

HOME=/root

if [ -f /etc/bashrc ]; then
       . /etc/bashrc
fi

BACKUPLOC="/var/data/backup/sql"
ts=$(date +%Y%m%d-%H%M)

for db in $(mysql -Bse "show databases" | grep -v information_schema); do 
        echo "dumping $db database: "
        nice -n 18 mysqldump --opt $db > $BACKUPLOC/$db.$ts.sql
        if [ $? != 0 ]; then
                echo "error dumping $db database" | mail -s "mysql backup error on $(hostname)" wattersmt@gmail.com
        fi
done

nice -n 18 bzip2 -9 $BACKUPLOC/*.sql
chown root:root $BACKUPLOC/* 
chmod 600 $BACKUPLOC/* 

#find $BACKUPLOC -mtime +7 -exec rm -fv {} \;
Note:
  • I had to add the stuff about HOME and source the /etc/bashrc file on my system 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.