MySQL中的增量备份

Question

可能重复： 在Mysql中进行增量备份的最佳方法是什么？

有人能帮我在MySQL中进行增量备份吗？

Answer 1

检查MySQL :：MySQL 5.1参考手册:：6.2数据库备份方法页面并阅读“通过启用二进制日志进行增量备份”部分。我们不能说得比这更清楚:)

如果该网站关闭：

通过启用二进制日志进行增量备份 MySQL支持增量备份:您必须使用--logging选项启动服务器以启用二进制日志；参见5.2.4节“二进制日志”。二进制日志文件为您提供了在执行备份后对数据库进行的更改复制所需的信息。目前，您希望进行增量备份(包含自上次完整备份或增量备份以来发生的所有更改)，您应该使用刷新日志旋转二进制日志。完成后，您需要将所有二进制日志复制到备份位置，从上一次完整备份或增量备份的某一时刻到最后一次备份。这些二进制日志是增量备份；在恢复时，如第7.5节“使用二进制日志进行实时(增量)恢复”所解释的那样应用它们。下次执行完全备份时，还应该使用刷新日志、mysqldump -刷新日志或mysqlhotcopy旋转二进制日志。见4.5.4节，“mysqldump-一个数据库备份程序”和4.6.9节，“mysqlhotcopy-一个数据库备份程序”。

另一种选择是使用MySQLDump，并将新的dumpfile版本区分为旧的(呃)版本，然后只存储diff。请参阅MySQLDump评论部分上的实现示例。

为子孙后代，脚本包括在下面。

#!/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