备份最好是在slave上面进行,这样可以不对主库造成任何影响。使用xtrabackup这个工具能实现在线全备和在线增量备份,步骤如下:
1、先做一个全备份
xtrabackup --backup --datadir=/data/iscsi/db/ --target-dir=/data/backups/mysql/
2、每天增量备份
xtrabackup --backup --target-dir=/data/backups/inc/tue/ --incremental-basedir=/data/backups/mysql/ #Suppose the full backup is on Monday,Create an incremental backup on Tuesday
xtrabackup --backup --target-dir=/data/backups/inc/wed/ --incremental-basedir=/data/backups/inc/tue/ #Create an incremental backup on Wednesday
发生异常需要恢复时:
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/ #Prepare the base backup from Monday
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/ --incremental-dir=/data/backups/inc/tue/ #Roll Monday's data forward to the state on Tuesday
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/ --incremental-dir=/data/backups/inc/wed/ #Roll forward again to the state on Wednesday
你可以用脚本程序和crontab一起来控制全备和增量备份的策略,将备份放在远程的服务器上面(可以在数据库服务器上面连接盘柜),达到数据安全的目的。