xtrabackup<三、增量备份脚本>

#!/bin/bashBEGINTIME=`date +”%Y-%m-%d %H:%M:%S”`format_time=`date +”%Y-%m-%d_%H:%M:%S”`week=`date +%Y-%m-%d`backupbin=/usr/binbackdir=/database/backupfile_cnf=/etc/my.cnfuser_name=backuppassword=”backup”out_log=$backdir/xtrabackup_log_$format_timetime_cost=$backdir/xtrabackup_time.txtif [ -d "$backdir/incr5" ];thengzip -cr $backdir >$backdir.$week.gzrm -rf $backdirmkdir $backdirfi#fullif [ ! -d "$backdir/full" ];thenecho “#####start full backup at $BEGINTIME to directory full” >>$time_cost$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info $backdir/full 1> $out_log 2>&1break;elif [ ! -d "$backdir/incr0" ];thenecho “#####start 0 incremental backup at $BEGINTIME to directory incr0″ >>$time_cost$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$backdir/full $backdir/incr0 1> $out_log 2>&1break;elif [ ! -d "$backdir/incr1" ];thenecho “#####start 1 incremental backup at $BEGINTIME to directory incr1″ >>$time_cost$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$backdir/incr0 $backdir/incr1 1> $out_log 2>&1break;elif [ ! -d "$backdir/incr2" ];thenecho “#####start 2 incremental backup at $BEGINTIME to directory incr2″ >>$time_cost$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$backdir/incr1 $backdir/incr2 1> $out_log 2>&1break;elif [ ! -d "$backdir/incr3" ];thenecho “#####start 3 incremental backup at $BEGINTIME to directory incr3″ >>$time_cost$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$backdir/incr2 $backdir/incr3 1> $out_log 2>&1break;elif [ ! -d "$backdir/incr4" ];thenecho “#####start 4 incremental backup at $BEGINTIME to directory incr4″ >>$time_cost$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$backdir/incr3 $backdir/incr4 1> $out_log 2>&1break;elif [ ! -d "$backdir/incr5" ];thenecho “#####start 5 incremental backup at $BEGINTIME to directory incr5″ >>$time_cost$backupbin/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –incremental –incremental-basedir=$backdir/incr4 $backdir/incr5 1> $out_log 2>&1break;fiENDTIME=`date +”%Y-%m-%d %H:%M:%S”`begin_data=`date -d “$BEGINTIME” +%s`end_data=`date -d “$ENDTIME” +%s`spendtime=`expr $end_data – $begin_data`echo “it takes $spendtime sec for packing the data directory” >>$time_cost

MySQL数据库服务器 192.168.17.80:1、零级备份:mysql> select * from test.compary;+—-+——-+————+———+——+| id | color | birthday | contact | vip |+—-+——-+————+———+——+| 5 | blue | 1988-06-27 | 186 | 1 || 6 | blue | 1988-06-27 | 186 | 1 || 7 | blue | 1988-06-27 | 186 | 1 || 8 | blue | 1988-06-27 | 186 | 1 || 9 | blue | 1988-06-27 | 186 | 1 || 10 | blue | 1988-06-27 | 186 | 1 |sh /root/xtrabackup.shls –ltdrwxr-xr-x 5 root root 4096 Dec 10 09:24 full-rw-r–r– 1 root root 4134 Dec 10 09:24 xtrabackup_log_2013-12-10_09:24:09-rw-r–r– 1 root root 110 Dec 10 09:24 xtrabackup_time.txt2、一级增量备份:mysql> insert into test.compary(`id`,`color`,`birthday`,`contact`,`vip`)values (’21′,’red’,’1988-08-08′,’188′,’2′);mysql> select * from test.compary;+—-+——-+————+———+——+| id | color | birthday | contact | vip |+—-+——-+————+———+——+| 5 | blue | 1988-06-27 | 186 | 1 || 6 | blue | 1988-06-27 | 186 | 1 || 7 | blue | 1988-06-27 | 186 | 1 || 8 | blue | 1988-06-27 | 186 | 1 || 9 | blue | 1988-06-27 | 186 | 1 || 10 | blue | 1988-06-27 | 186 | 1 || 21 | red | 1988-08-08 | 188 | 2 |sh /root/xtrabackup.shlldrwxr-xr-x 5 root root 4096 Dec 10 09:24 fulldrwxr-xr-x 5 root root 4096 Dec 10 09:28 incr0-rw-r–r– 1 root root 4134 Dec 10 09:24 xtrabackup_log_2013-12-10_09:24:09-rw-r–r– 1 root root 4235 Dec 10 09:28 xtrabackup_log_2013-12-10_09:28:34-rw-r–r– 1 root root 230 Dec 10 09:28 xtrabackup_time.txt3、二级增量备份:mysql> insert into test.compary(`id`,`color`,`birthday`,`contact`,`vip`)values (’22′,’black’,’1988-09-09′,’188′,’3′);Query OK, 1 row affected (0.01 sec)mysql> select * from test.compary;+—-+——-+————+———+——+| id | color | birthday | contact | vip |+—-+——-+————+———+——+| 5 | blue | 1988-06-27 | 186 | 1 || 6 | blue | 1988-06-27 | 186 | 1 || 7 | blue | 1988-06-27 | 186 | 1 || 8 | blue | 1988-06-27 | 186 | 1 || 9 | blue | 1988-06-27 | 186 | 1 || 10 | blue | 1988-06-27 | 186 | 1 || 21 | red | 1988-08-08 | 188 | 2 || 22 | black | 1988-09-09 | 188 | 3 |sh /root/xtrabackup.shlldrwxr-xr-x 5 root root 4096 Dec 10 09:24 fulldrwxr-xr-x 5 root root 4096 Dec 10 09:28 incr0drwxr-xr-x 5 root root 4096 Dec 10 09:31 incr1-rw-r–r– 1 root root 4134 Dec 10 09:24 xtrabackup_log_2013-12-10_09:24:09-rw-r–r– 1 root root 4235 Dec 10 09:28 xtrabackup_log_2013-12-10_09:28:34-rw-r–r– 1 root root 4205 Dec 10 09:31 xtrabackup_log_2013-12-10_09:31:38-rw-r–r– 1 root root 350 Dec 10 09:31 xtrabackup_time.txt4、全量恢复测试:假定我们现在需要还原到第一次插入数据时状态。具体操作。打包上传文件到远程服务器tar czvf full.tar full/tar -czvf incr0.tar incr0/tar -czvf incr0.tar incr1/scp full.tar james@192.168.17.82:/databackupscp incr0.tar james @192.168.17.82:/databackupscp incr1.tar james@192.168.17.82:/databackup192.168.17.82：[root@host3 databackup]# lltotal 824-rw-r–r– 1 root root 377468 Dec 10 09:37 full.tar-rw-r–r– 1 root root 222011 Dec 10 09:38 incr0.tar-rw-r–r– 1 root root 222034 Dec 10 09:39 incr1.tar/etc/init.d/mysqld stopcd /usr/local/mysql/mv data/* databackup/cd /databackuptar xzvf full.tartar xzvf incr0.tartar zxvf incr1.tar[root@host3 databackup]# lltotal 836drwxr-xr-x 5 root root 4096 Dec 10 09:24 full-rw-r–r– 1 root root 377468 Dec 10 09:37 full.tardrwxr-xr-x 5 root root 4096 Dec 10 09:28 incr0-rw-r–r– 1 root root 222011 Dec 10 09:38 incr0.tardrwxr-xr-x 5 root root 4096 Dec 10 09:31 incr1-rw-r–r– 1 root root 222034 Dec 10 09:39 incr1.tarinnobackupex –apply-log –redo-only –user=backupuser –password=’123456′–defaults-file=/etc/my.cnf /databackup/full/innobackupex –apply-log –redo-only –user=backupuser –password=’123456′ –defaults-file=/etc/my.cnf /databackup/full/ –incremental-dir=/databackup/incr0/innobackupex –copy-back –user=backupuser –password=’123456′ –defaults-file=/etc/my.cnf /databackup/full/[root@host3 data]# cd data[root@host3 data]# chown -R mysql.mysql ./[root@host3 data]# /etc/init.d/mysqld start[root@host3 data]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.11-log MySQL Community Server (GPL)Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql> select * from test.compary;+—-+——-+————+———+——+| id | color | birthday | contact | vip |+—-+——-+————+———+——+| 5 | blue | 1988-06-27 | 186 | 1 || 6 | blue | 1988-06-27 | 186 | 1 || 7 | blue | 1988-06-27 | 186 | 1 || 8 | blue | 1988-06-27 | 186 | 1 || 9 | blue | 1988-06-27 | 186 | 1 || 10 | blue | 1988-06-27 | 186 | 1 || 21 | red | 1988-08-08 | 188 | 2 |+—-+——-+————+———+——+7 rows in set (0.18 sec)至此确认零一级恢复正常6、增量二级恢复测试:下面测试还原到第二次插入数据时状态具体操作192.168.17.82上：[root@host3 data]# cd /databackup/[root@host3 databackup]# lldrwxr-xr-x 5 root root 4096 Dec 10 10:55 full-rw-r–r– 1 root root 377468 Dec 10 10:51 full.tardrwxr-xr-x 5 root root 4096 Dec 10 10:54 incr0-rw-r–r– 1 root root 222011 Dec 10 10:52 incr0.tardrwxr-xr-x 5 root root 4096 Dec 10 10:55 incr1-rw-r–r– 1 root root 222034 Dec 10 10:52 incr1.tar由于第一次的还原数据已写入FULL,INCR0目录下。所以，此时的这两个目录文件不能再使用了。需要删除，重新解压TAR包。[root@host3 mysql]# /etc/init.d/mysqld stoprm –rf /usr/local/mysql/databackup/*mv data/* databackup/innobackupex –apply-log –redo-only –user=backupuser –password=’123456′ –defaults-file=/etc/my.cnf /databackup/full/innobackupex –apply-log –redo-only –user=backupuser –password=’123456′ –defaults-file=/etc/my.cnf /databackup/full/ –incremental-dir=/databackup/incr0/innobackupex –apply-log –redo-only –user=backupuser –password=’123456′ –defaults-file=/etc/my.cnf /databackup/full/ –incremental-dir=/databackup/incr1/innobackupex –copy-back –user=backupuser –password=’123456′ –defaults-file=/etc/my.cnf /databackup/full/cd /usr/local/mysql/datachown -R mysql.mysql .//etc/init.d/mysqld startmysql> select * from test.compary;+—-+——-+————+———+——+| id | color | birthday | contact | vip |+—-+——-+————+———+——+| 5 | blue | 1988-06-27 | 186 | 1 || 6 | blue | 1988-06-27 | 186 | 1 || 7 | blue | 1988-06-27 | 186 | 1 || 8 | blue | 1988-06-27 | 186 | 1 || 9 | blue | 1988-06-27 | 186 | 1 || 10 | blue | 1988-06-27 | 186 | 1 || 21 | red | 1988-08-08 | 188 | 2 || 22 | black | 1988-09-09 | 188 | 3 |+—-+——-+————+———+——+8 rows in set (0.00 sec)此时二级恢复正确