MySQL Backups

Create a backup user

GRANT LOCK TABLES, SELECT ON mydatabase.* TO 'backup'@'localhost' IDENTIFIED BY 'somecoolpassword';

All MySQL Databases – single backup

#!/bin/sh

BACKUPDIR=/paht/to/backups
USERNAME=
PASSWORD=
DATABASES=( database1 database2 )
#Will display like 2006-06-22-1103
BACKUPFILE=db-backup-$(date +%Y-%m-%d-%k%M)

echo 'Removing old...'
rm -fr $BACKUPDIR/$BACKUPFILE.tar.gz

for DATABASE in ${DATABASES[@]}
do
   echo 'Dumping database' $DATABASE
   /usr/bin/mysqldump -u$USERNAME -p$PASSWORD --opt $DATABASE > $BACKUPDIR/$DATABASE.sql
done

echo 'Arcive temp SQL files...'
cd $BACKUPDIR/
tar -zcvf $BACKUPDIR/$BACKUPFILE.tar.gz *.sql

echo 'Removing temp SQL files...'
rm -fr $BACKUPDIR/*.sql

echo 'Done! You can find the file ' $BACKUPDIR/$BACKUPFILE.tar.gz

Rotating 30-day backups of a single database

Another strategy is to create a 30-day rotating backup script. This script will back up a single database and name the file the day of the month (1-31). Once it reaches the end of the month, it will start replacing files from the beginning of the month.

Since it goes off of day-of-the-month numbers, some backups may actually stick around for 2 months, but that’s just an unintended consequence.

DATE=`date "+%d"`
APP='myapp'
DIR=/var/www/virtual/example.com/backup/$APP

mkdir -p $DIR/mysql
/usr/bin/mysqldump -ubackupuser -ppassword $APP\_production | gzip > $DIR/mysql/mysql-daily-$DATE.sql.gz

Then add it to your Crontab @daily.