User Tools

Site Tools


database:mysql:mysql_backup

MySQL Backup & Restore

Checkout my open source backup and restore application mysqlmb ;-)

Dumping and restore a single database

Dump

mysqldump --opt db_name > backup-file.sql

Restore

mysql db_name < backup-file.sql

Automated Backups

Create a MySQl backup user:

GRANT SELECT, RELOAD, LOCK TABLES ON * TO backup@localhost IDENTIFIED BY 'top_secret';

Backup bash scripts using mysqldump

Option 1: Put all databases in one dump file

#!/bin/bash
 
# MySQL backup script
# v.0.1 | 5.12.2007 | Nik Wolfgramm
 
export PATH="/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin"
 
#------------------------------------------------------------------------------
# Overall Parameters
#------------------------------------------------------------------------------
DATE=`date +%Y-%m-%d`
DATE_LONG=$(date)
MYSQLDUMP=/usr/bin/mysqldump
BACKUPDIR=/var/backups/mysql
MAIL=/bin/mail
KEEP_BACK=30
 
FILE="$BACKUPDIR/$DATE-all-databases.sql"
 
#------------------------------------------------------------------------------
# Connection Parameters
#------------------------------------------------------------------------------
HOST=localhost
USER=backup
PW='top-secret'
 
CONN="--user=$USER --password=$PW"
 
#------------------------------------------------------------------------------
# Mail Parameters
#------------------------------------------------------------------------------
SUBJECT="MySQL backup"
EMAIL="nik.wolfgramm@example.com"
 
#------------------------------------------------------------------------------
# Execute MySQL backup
#------------------------------------------------------------------------------
start_time=$(date +%s)
$MYSQLDUMP $CONN --opt --flush-logs --all-databases > $FILE.tmp
finish_time=$(date +%s)
backup_error="$?"
 
#------------------------------------------------------------------------------
# Delete old Backups
#------------------------------------------------------------------------------
if [ "$backup_error" -eq 0 ]
  then
    find $BACKUPDIR -maxdepth 1 -type f -mtime +$KEEP_BACK -exec rm -vf {} > last_cleanup \;
    cleanup=$(cat last_cleanup)
fi
 
#------------------------------------------------------------------------------
# Send confirmation by mail
#------------------------------------------------------------------------------
if [ "$backup_error" -eq 0 ]
  then
    mv $FILE.tmp $FILE
    bzip2 -f $FILE
    SIZE=$(du -hs $FILE.bz2 | awk '{print $1 '\n'}')
    $MAIL -s "$SUBJECT - successful" $EMAIL << END
----------------------------------------------------------
                MySQL backup on $HOST
                $DATE_LONG
----------------------------------------------------------
 
All databases successfully stored in $FILE.bz2
Time duration: $((finish_time - start_time)) secs.
Backup file size after compression: $SIZE
Old backups $cleanup
Backup retention time: $KEEP_BACK days
END
else
  echo "$SUBJECT - failed" "$DATE: Backup of MySQL databases failed" | $MAIL -s "$SUBJECT" $EMAIL
fi

Option 2: Create a dump for each database on the server (easier to restore)

#!/bin/bash
 
# MySQL backup script
# v.0.1 | 5.12.2007 | Nik Wolfgramm
# - initial version
# v.0.2 | 26.08.2008 | Nik Wolfgramm
# - dump each single database
 
 
export PATH="/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin"
 
#------------------------------------------------------------------------------
# Overall Parameters
#------------------------------------------------------------------------------
DATE=`date +%Y-%m-%d`
DATE_LONG=$(date)
MYSQLDUMP=/usr/bin/mysqldump
BACKUPDIR=/var/backups/mysql
MAIL=/bin/mail
KEEP_BACK=30
 
FILE="$BACKUPDIR/$DATE-all-databases.sql"
 
#------------------------------------------------------------------------------
# Connection Parameters
#------------------------------------------------------------------------------
HOST=localhost
USER=backup
PW='top-secret'
 
CONN="--user=$USER --password=$PW"
 
#------------------------------------------------------------------------------
# Mail Parameters
#------------------------------------------------------------------------------
SUBJECT="MySQL backup"
EMAIL="icc@example.com"
 
#------------------------------------------------------------------------------
# Execute MySQL backup
#------------------------------------------------------------------------------
start_time=$(date +%s)
backup_error=0
for d in $(echo "show databases" | mysql $CONN | grep -v Database); do
  FILE="$BACKUPDIR/$DATE-$d"
  echo $i; $MYSQLDUMP --opt --flush-logs --allow-keywords -q -a -c $CONN $d > $FILE.tmp
  if [ "$?" != "0" ]; then
    backup_error=1
    echo "!! Backup failed on database: ${d}"
  else
    mv $FILE.tmp $FILE
    bzip2 -f $FILE
    echo "++ Successfully backed up database: ${d}"
  fi
done
 
#------------------------------------------------------------------------------
# Delete old Backups
#------------------------------------------------------------------------------
if [ "$backup_error" -eq 0 ]
  echo $DATE > $BACKUPDIR/last_cleanup
  then
    find $BACKUPDIR -maxdepth 1 -type f -mtime +$KEEP_BACK -exec rm -vf {} >> $BACKUPDIR/last_cleanup \;
    cleanup=$(cat $BACKUPDIR/last_cleanup)
fi
 
finish_time=$(date +%s)
#------------------------------------------------------------------------------
# Send confirmation by mail
#------------------------------------------------------------------------------
if [ "$backup_error" -eq 0 ]
  then
    SIZE=$(du -hsc $BACKUPDIR/$DATE-*.bz2 | awk '{print $1 '\n'}' | tail -n 1)
    $MAIL -s "$SUBJECT - successful" $EMAIL << END
----------------------------------------------------------
                MySQL backup on $HOST
                $DATE_LONG
----------------------------------------------------------
 
All databases successfully backed up
Time duration: $((finish_time - start_time)) secs.
Backup file size after compression: $SIZE
Old backups removed: ${cleanup}
Backup retention time: $KEEP_BACK days
END
else
  echo "$SUBJECT - failed" "$DATE: Backup of MySQL databases failed for at least one database" | $MAIL -s "$SUBJECT" $EMAIL
fi

Schedule daily backups

Run the script above with a cronjob once a day. That's what the file /etc/cron.d/mysql-backup could look like:

30 23 * * *  root /root/mysqlbackup.sh

Errors

No. 145

If you get an error like…

mysqldump: Got error: 1016: Can't open file: 'Database-Name' (errno: 145) when using LOCK TABLES

Fixe it with:

mysqlcheck -r -u root -p Database-Name

Restore with mysqlmb

mysqlmb restore --backup-path /var/backups/mysql/ -o 1 --user root --password <password> --date-format %d-%m-%Y -v -d user
/srv/wiki.niwos.com/data/pages/database/mysql/mysql_backup.txt · Last modified: 2009/08/15 19:27 (external edit)