Incremental Backups in MySQL [duplicate]

This question al开发者_如何学Goready has answers here: Closed 10 years ago.

Possible Duplicate:

What is the best way to do Incremental backups in Mysql?

Can anyone help me to take incremental backup in MySQL?

---

Check the MySQL :: MySQL 5.1 Reference Manual :: 6.2 Database Backup Methods page and read the "Making Incremental Backups by Enabling the Binary Log" section. We can't put it clearer than that :)

In case the site goes down:

Making Incremental Backups by Enabling the Binary Log

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see Section 5.2.4, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog. See Section 4.5.4, “mysqldump — A Database Backup Program”, and Section 4.6.9, “mysqlhotcopy — A Database Backup Program”.

Another option is to use MySQLDump, and diff the new dumpfile version an old(er) one, then only store the diff. See the implementation example at MySQLDump comment section.

For posterity, the script is included below.

#!/bin/sh
# Incremental backup script using rdiff
# Author: Driantsov Alexander
# Requirements:
# * rdiff-backup - http://www.nongnu.org/rdiff-backup/
# * rsync
# * ssh ;)
####

BACKUP_ADMIN_EMAIL="yourmail@yourserver.com"
BACKUP_MAIL_SUBJECT="`hostname`: SQL Backup Synchronization Result"
BACKUP_LOG="/var/log/backup-sql"

BACKUP_PRIO="20" # Priority for the MySQL dump and rdiff-backup Min: 20 Max: -20
BACKUP_TMP_DIR="/var/backup/mysql_tmp" # New dumps will be stored here
BACKUP_DIFF_DIR="/var/backup/hosting/mysql" # Diffs of dumps will be stored there
SYNC_SRV="BAC.KUP.SER.VER" # Remote server for backup storage
SYNC_USER="backup_user" # User at remote storage
SYNC_SPEED="200" # Limit Synchronization Bandwidth to this number of KB/s
SYNC_DIR="/backup/hosting/mysql" #Directory on Remote server to synchronize backups in
MYSQL_USER="admin" # MySQL user
MYSQL_PASSWD=`cat /etc/psa/.psa.shadow` # Password for MySQL. You may obtain password from /etc/psa/.psa.shadow if you are using Plesk on your server.

#Implementaition
RSCONSTR="$SYNC_USER@$SYNC_SRV"
# Dump
echo "Backup Started at `date`" > $BACKUP_LOG
load_average=`uptime|awk {print $10" "$11" "$12}`
echo "Load overage at start: $load_average" >> $BACKUP_LOG
echo "\nBackingUP MySQL:" >> $BACKUP_LOG
for i in `mysql -u$MYSQL_USER -p$MYSQL_PASSWD -Bse "show databases"`; do echo "Backing up database $i..." >> $BACKUP_LOG ; nice -n $BACKUP_PRIO mysqldump --single-transaction --quick --skip-extended-insert -u$MYSQL_USER -p$MYSQL_PASSWD $i > $BACKUP_TMP_DIR/$i.dump; done

echo "MySQL dump completed at `date`" >> $BACKUP_LOG
load_average=`uptime|awk {print $10" "$11" "$12}`
echo "\nLoad overage after MySQL dumps: $load_average\n" >> $BACKUP_LOG
# Diff
echo "Looking for difference in database" >> $BACKUP_LOG
nice -n $BACKUP_PRIO rdiff-backup $BACKUP_TMP_DIR $BACKUP_DIFF_DIR/$HOSTNAME 2>&1 >> $BACKUP_LOG
echo "Done" >> $BACKUP_LOG

echo "Rdiff-backup completed at `date`" >> $BACKUP_LOG
load_average=`uptime|awk {print $10" "$11" "$12}`
echo "\nLoad overage after rdiff-backup: $load_average\n" >> $BACKUP_LOG

# Synchronize

sql_result=`rsync -avz --bwlimit $SYNC_SPEED $BACKUP_DIFF_DIR $RSCONSTR:$SYNC_DIR|tail -n 2`
free_space_info=`ssh $RSCONSTR df -h --sync -t ext3`

echo -en " MySQL backup synchronization:\n $sql_result \nSynchronization completed at `date` \n\nInformation on free space on remote backup server: \n $free_space_info \n\n Backup Log: \n\n`cat $BACKUP_LOG`"| mail -s "$BACKUP_MAIL_SUBJECT" $BACKUP_ADMIN_EMAIL