MySQL利用xtrabackup进行增量备份详细过程汇总

xtrabackup --defaults-file=/etc/my.cnf--user=backup --password="123456" --port=3306 --backup--target-dir=/data/backups/mysql/full_incre_$(date +%Y%m%d)

PS: 2>/tmp/fullbackup.log 将备份信息放入文件中。

备份过程信息如下：

[root@data01 ~]# xtrabackup--defaults-file=/etc/my.cnf --user=backup --password="123456"--port=3306 --backup --target-dir=/data/backups/mysql/full_incre_$(date+%Y%m%d)
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423920168)
xtrabackup: Generating a list of tablespaces
[01] Copying ./IBdata1 to/data/backups/mysql/full_incre_20150319/IBdata1
>> log scanned up to (423920168)
>> log scanned up to (423920168)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/full_incre_20150319/IBdata2
>> log scanned up to (423920168)
>> log scanned up to (423920168)
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_master_info.ibd
[01]       ...done
[01] Copying ./mysql/slave_relay_log_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_relay_log_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_index_stats.ibd
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_worker_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_table_stats.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest1.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest5.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest8.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest4.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest2.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest6.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to /data/backups/mysql/full_incre_20150319/sbtest/sbtest7.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest3.ibd
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/full_incre_20150319/test/t1.ibd
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/full_incre_20150319/test/t3.ibd
[01]       ...done
[01] Copying ./test/t.ibd to/data/backups/mysql/full_incre_20150319/test/t.ibd
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/full_incre_20150319/user_db/t1.ibd
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/full_incre_20150319/user_db/test.ibd
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/full_incre_20150319/user_db/test2.ibd
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/full_incre_20150319/user_db/t3.ibd
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/full_incre_20150319/user_db/UC_USER.ibd
>> log scanned up to (423920168)
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/full_incre_20150319/user_db/t.ibd
[01]       ...done
xtrabackup: The latest check point (forincremental): &#39;423920168&#39;
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423920168)
  
xtrabackup: Transaction log of lsn(423920168) to (423920168) was copied.
[root@data01 ~]#

Copy after login

3，xtrabackup进行增量备份

3.1先录入一张表，实现第一次增量数据：

mysql> create table t4 select &#39;aaa&#39; asa, 1 as b;
Query OK, 1 row affected (0.36 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql>
mysql> insert into t4 select &#39;bbb&#39;,2;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql>

Copy after login

3.2 再进行增量备份1

xtrabackup --defaults-file=/etc/my.cnf--backup --user=backup --password="123456" --port=3306 --target-dir=/data/backups/mysql/incre_20150320/--incremental-basedir=/data/backups/mysql/full_incre_201503192>/tmp/incre_backup_1.log

查看备份日志信息：

[root@data01 ~]# more/tmp/incre_backup_1.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423920168 isenabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423925813)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
[01] Copying ./IBdata1 to/data/backups/mysql/incre_20150320/IBdata1.delta
>> log scanned up to (423925813)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/incre_20150320/IBdata2.delta
>> log scanned up to (423925813)
[01]       ...done
>> log scanned up to (423925813)
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150320/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150320/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150320/test/t3.ibd.delta
[01]       ...done
[01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150320/test/t.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to /data/backups/mysql/incre_20150320/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/incre_20150320/user_db/test.ibd.delta
[01]       ...done
>> log scanned up to (423925813)
[01] Copying ./user_db/test2.ibd to /data/backups/mysql/incre_20150320/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150320/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to /data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150320/user_db/t.ibd.delta
[01]       ...done
xtrabackup: The latest check point (forincremental): &#39;423925813&#39;
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423925813)
  
xtrabackup: Transaction log of lsn(423925813) to (423925813) was copied.

Copy after login

3.3 再建立表2，实现第二天增量数据

建立一张表t_incre_2：

mysql> use test;
Database changed
mysql> create table t_incre_2 select&#39;cc&#39; as a,2 as b;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql> insert into t_incre_2 select&#39;dd&#39;,2;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql> select * from t_incre_2;
+----+---+
| a | b |
+----+---+
| cc | 2 |
| dd | 2 |
+----+---+
2 rows in set (0.05 sec)
  
mysql>

Copy after login

3.4 进行第二天的增量备份

备份命令：

xtrabackup --defaults-file=/etc/my.cnf--backup --user=backup --password="123456" --port=3306 --target-dir=/data/backups/mysql/incre_20150321/--incremental-basedir=/data/backups/mysql/incre_20150320 2>/tmp/incre_backup_2.log

备份信息记录如下：

[root@data01 ~]# more /tmp/incre_backup_2.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423925813 isenabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423931610)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
[01] Copying ./IBdata1 to/data/backups/mysql/incre_20150321/IBdata1.delta
>> log scanned up to (423931610)
>> log scanned up to (423931610)
>> log scanned up to (423931610)
>> log scanned up to (423931610)
>> log scanned up to (423931610)
[01]       ...done
>> log scanned up to (423931610)
[01] Copying ./IBdata2 to/data/backups/mysql/incre_20150321/IBdata2.delta
>> log scanned up to (423931610)
>> log scanned up to (423931610)
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.delta
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
>> log scanned up to (423931610)
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150321/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150321/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150321/test/t3.ibd.delta
[01]       ...done
>> log scanned up to (423931610)
[01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150321/test/t.ibd.delta
[01]       ...done
[01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to /data/backups/mysql/incre_20150321/user_db/test.ibd.delta
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150321/user_db/t.ibd.delta
[01]       ...done
>> log scanned up to (423931610)
xtrabackup: The latest check point (forincremental): &#39;423931610&#39;
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423931610)
  
xtrabackup: Transaction log of lsn(423931610) to (423931610) was copied.

Copy after login

4，xtrabackup进行增量恢复

4.1为了验证比对，先删除两个增量备份前表里面的数据

mysql> delete from t4;
Query OK, 2 rows affected (0.05 sec)
  
mysql> delete from t_incre_2;
Query OK, 2 rows affected (0.04 sec)
  
mysql> select * from t4;
Empty set (0.00 sec)
  
mysql> select * from t_incre_2;
Empty set (0.00 sec)
  
mysql>

Copy after login

4.2开始全备份恢复PREPARE THE BASE BACKUP

备份命令如下：

xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456" --apply-log-only--target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/full_restore.log

备份信息记录如下：

[root@data01 mysql]# xtrabackup--defaults-file=/etc/my.cnf --prepare --user=backup--password="123456" --apply-log-only --target-dir=/data/backups/mysql/full_incre_20150319
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: cd to/data/backups/mysql/full_incre_20150319
xtrabackup: This target seems to be alreadyprepared.
xtrabackup: notice: xtrabackup_logfile wasalready used to &#39;--prepare&#39;.
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
xtrabackup: Starting InnoDB instance forrecovery.
xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count bufferpool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size =100.0M
InnoDB: Completed initialization of bufferpool
InnoDB: Setting log file ./ib_logfile101size to 64 MB
InnoDB: Setting log file ./ib_logfile1 sizeto 64 MB
InnoDB: Setting log file ./ib_logfile2 sizeto 64 MB
InnoDB: Renaming log file ./ib_logfile101to ./ib_logfile0
InnoDB: New log files created,LSN=423920168
InnoDB: Highest supported file format isBarracuda.
  
[notice (again)]
  Ifyou use binary log and don&#39;t use any hack of group commit,
  thebinary log position seems to be:
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423920652
[root@data01 mysql]#

Copy after login

4.3 恢复到第一次增量的时刻

恢复命令：

xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456" --apply-log-only--target-dir=/data/backups/mysql/full_incre_20150319--incremental-dir=/data/backups/mysql/incre_20150320 2>/tmp/restore_incre_1.log

恢复过程：

[root@data01 mysql]# more /tmp/restore_incre_1.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423920168 isenabled.
xtrabackup: cd to/data/backups/mysql/full_incre_20150319
xtrabackup: This target seems to be alreadyprepared.
xtrabackup: xtrabackup_logfile detected:size=2097152, start_lsn=(423925813)
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150320
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Generating a list oftablespaces
xtrabackup: page size for /data/backups/mysql/incre_20150320/IBdata2.deltais 16384 bytes
Applying/data/backups/mysql/incre_20150320/IBdata2.delta to ./IBdata2...
xtrabackup: page size for/data/backups/mysql/incre_20150320/IBdata1.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150320/IBdata1.deltato ./IBdata1...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta to./mysql/slave_worker_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta to./mysql/innodb_table_stats.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta to./mysql/innodb_index_stats.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta is16384 bytes
Applying/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta to./mysql/slave_relay_log_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta to./mysql/slave_master_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta to./sbtest/sbtest4.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta to./sbtest/sbtest3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta to./sbtest/sbtest1.ibd...
xtrabackup: page size for /data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.deltais 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.delta to./sbtest/sbtest7.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta to./sbtest/sbtest6.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.deltato ./sbtest/sbtest8.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta to./sbtest/sbtest2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta to./sbtest/sbtest5.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t3.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/test/t3.ibd.delta to ./test/t3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/test/t.ibd.delta to ./test/t.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t4.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/test/t4.ibd.delta to ./test/t4.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/test/t1.ibd.delta to ./test/t1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t3.ibd.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150320/user_db/t3.ibd.deltato ./user_db/t3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/t.ibd.delta to ./user_db/t.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/test2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/test2.ibd.delta to./user_db/test2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/test.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/test.ibd.delta to./user_db/test.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/t1.ibd.delta to ./user_db/t1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta to./user_db/UC_USER.ibd...
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150320
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance forrecovery.
xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count bufferpool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of bufferpool
InnoDB: Highest supported file format isBarracuda.
InnoDB: The log sequence numbers 423920652and 423920652 in ibdata files do not match the log sequence number 423925813 inthe ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information fromthe .ibd files...
InnoDB: Restoring possible half-writtendata pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
[notice (again)]
  Ifyou use binary log and don&#39;t use any hack of group commit,
  thebinary log position seems to be:
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423925813

Copy after login

4.4 恢复到第二次增量备份前面：

恢复命令：

xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456" --apply-log-only --target-dir=/data/backups/mysql/full_incre_20150319--incremental-dir=/data/backups/mysql/incre_201503212>/tmp/restore_incre_2.log

恢复过程：

[root@data01 mysql]# more/tmp/restore_incre_2.log
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423925813 isenabled.
xtrabackup: cd to/data/backups/mysql/full_incre_20150319
xtrabackup: This target seems to be alreadyprepared.
xtrabackup: xtrabackup_logfile detected:size=2097152, start_lsn=(423931610)
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150321
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Generating a list oftablespaces
xtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata2.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150321/IBdata2.deltato ./IBdata2...
xtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata1.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/IBdata1.delta to ./IBdata1...
xtrabackup: page size for /data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.deltais 16384 bytes
Applying/data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.delta to./mysql/slave_worker_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta to./mysql/innodb_table_stats.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta is 16384bytes
Applying/data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta to./mysql/innodb_index_stats.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta is16384 bytes
Applying/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta to./mysql/slave_relay_log_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.delta is 16384bytes
Applying /data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.deltato ./mysql/slave_master_info.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta to./sbtest/sbtest4.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta to./sbtest/sbtest3.ibd...
xtrabackup: page size for /data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.deltais 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.delta to./sbtest/sbtest1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta to./sbtest/sbtest7.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta to./sbtest/sbtest6.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta to./sbtest/sbtest8.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta to./sbtest/sbtest2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta to./sbtest/sbtest5.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t3.ibd.delta is 16384 bytes
Applying /data/backups/mysql/incre_20150321/test/t3.ibd.deltato ./test/t3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta to./test/t_incre_2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/test/t.ibd.delta to ./test/t.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t4.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/test/t4.ibd.delta to ./test/t4.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/test/t1.ibd.delta to ./test/t1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta to ./user_db/t3.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/t.ibd.delta to ./user_db/t.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta to./user_db/test2.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/test.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/test.ibd.delta to./user_db/test.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta to ./user_db/t1.ibd...
xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta is 16384 bytes
Applying/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta to./user_db/UC_USER.ibd...
xtrabackup: using the following InnoDBconfiguration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150321
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance forrecovery.
xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count bufferpool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size =100.0M
InnoDB: Completed initialization of bufferpool
InnoDB: Highest supported file format isBarracuda.
InnoDB: The log sequence numbers 423925813and 423925813 in ibdata files do not match the log sequence number 423931610 inthe ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information fromthe .ibd files...
InnoDB: Restoring possible half-writtendata pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
[notice (again)]
  Ifyou use binary log and don&#39;t use any hack of group commit,
  thebinary log position seems to be:
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  
xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequencenumber 423931610
[root@data01 mysql]#

Copy after login

4.5 恢复整个库

恢复命令：

xtrabackup --defaults-file=/etc/my.cnf--prepare --user=backup --password="123456"--target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/restore_end_3.log

停止mysql服务：

service mysqld5612 stop

开始rsync数据文件：

cd /data/backups/mysql/full_incre_20150319

rsync -rvt --exclude'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /home/data/mysql/data

授予mysql访问权限：

chown -R mysql:mysql /home/data/mysql/data

启动mysql服务：

service mysqld5612 start

4.6，验证

登录mysql，看到以前在备份之后删除的t4以及t_incre_2表的数据已经通过2次增量备份恢复过来了，如下所示：

mysql> select * from t4;
+-----+---+
| a  | b |
+-----+---+
| aaa | 1 |
| bbb | 2 |
+-----+---+
2 rows in set (0.05 sec)
  
mysql> select * from t_incre_2;
+----+---+
| a | b |
+----+---+
| cc | 2 |
| dd | 2 |
+----+---+
2 rows in set (0.06 sec)
  
mysql>

Copy after login

5，innobackupex先做全量备份

命令如下：

innobackupex --defaults-file=/etc/my.cnf--user=backup --password="123456" --host=192.168.52.129/data/backups/mysql/innobackupex_full_20150319/ --no-timestamp 2>/tmp/innobackupex_full.log

备份过程信息：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

[root@data01 mysql]# more/tmp/innobackupex_full.log
  
InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.
  
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.
  
Get the latest version of PerconaXtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
  
150319 02:31:18  innobackupex: Connecting to MySQL server withDSN &#39;dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;host=192.168.52.129&#39;as &#39;backup&#39;  (using
 password: YES).
150319 02:31:18  innobackupex: Connected to MySQL server
150319 02:31:18  innobackupex: Executing a version checkagainst the server...
150319 02:31:18  innobackupex: Done.
150319 02:31:18  innobackupex: Starting the backup operation
  
IMPORTANT: Please check that the backup runcompletes successfully.
          At the end of a successful backup run innobackupex
          prints "completed OK!".
  
innobackupex:  Using server version 5.6.12-log
  
innobackupex: Created backup directory/data/backups/mysql/innobackupex_full_20150319
  
150319 02:31:18  innobackupex: Starting ibbackup with command:xtrabackup --defaults-file="/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/
innobackupex_full_20150319 --tmpdir=/tmp--extra-lsndir=&#39;/tmp&#39;
innobackupex: Waiting for ibbackup(pid=13118) to suspend
innobackupex: Suspend file&#39;/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2&#39;
  
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0, setto 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423920964)
xtrabackup: Generating a list oftablespaces
[01] Copying ./IBdata1 to/data/backups/mysql/innobackupex_full_20150319/IBdata1
>> log scanned up to (423920964)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/innobackupex_full_20150319/IBdata2
>> log scanned up to (423920964)
>> log scanned up to (423920964)
[01]       ...done
>> log scanned up to (423920964)
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/slave_master_info.ibd
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/innobackupex_full_20150319/mysql/slave_relay_log_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/innodb_index_stats.ibd
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/slave_worker_info.ibd
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/innodb_table_stats.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.ibd
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.ibd
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t1.ibd
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t4.ibd
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t3.ibd
[01]       ...done
[01] Copying ./test/t.ibd to /data/backups/mysql/innobackupex_full_20150319/test/t.ibd
[01]       ...done
[01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.ibd
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t1.ibd
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/test.ibd
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/test2.ibd
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t3.ibd
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.ibd
>> log scanned up to (423920964)
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t.ibd
[01]       ...done
xtrabackup: Creating suspend file&#39;/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2&#39; withpid &#39;13118&#39;
  
150319 02:31:24  innobackupex: Continuing after ibbackup hassuspended
150319 02:31:24  innobackupex: Executing FLUSH TABLES WITHREAD LOCK...
150319 02:31:24  innobackupex: All tables locked and flushedto disk
  
150319 02:31:24  innobackupex: Starting to backup non-InnoDBtables and files
innobackupex: in subdirectories of&#39;/home/data/mysql/data/&#39;
innobackupex: Backing up files&#39;/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}&#39;(53 files)
>> log scanned up to (423920964)
innobackupex: Backing up files&#39;/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}&#39;(74 files)
>> log scanned up to (423920964)
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest3.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest4.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest6.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest8.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest1.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/db.opt&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest2.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest7.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest5.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t_incre_2.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t3.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t4.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t1.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/UC_USER.frm&#39;
innobackupex: Backing up file &#39;/home/data/mysql/data//user_db/t.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/t3.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/test.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/db.opt&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/test2.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/t1.frm&#39;
150319 02:31:26  innobackupex: Finished backing up non-InnoDBtables and files
  
150319 02:31:26  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...
150319 02:31:26  innobackupex: Waiting for log copying tofinish
  
>> log scanned up to (423920964)
xtrabackup: The latest check point (forincremental): &#39;423920964&#39;
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423920964)
  
xtrabackup: Creating suspend file&#39;/data/backups/mysql/innobackupex_full_20150319/xtrabackup_log_copied&#39; with pid&#39;13118&#39;
xtrabackup: Transaction log of lsn(423920964) to (423920964) was copied.
150319 02:31:27  innobackupex: All tables unlocked
  
innobackupex: Backup created in directory&#39;/data/backups/mysql/innobackupex_full_20150319&#39;
innobackupex: MySQL binlog position:filename &#39;mysql-bin.000066&#39;, position 120
150319 02:31:27  innobackupex: Connection to database serverclosed
150319 02:31:27  innobackupex: completed OK!
[root@data01 mysql]#

Copy after login

6，innobackupex做增量备份

6.1做第一次增量备份

先录入增量数据

mysql> create table incre1 select&#39;incre1 table &#39; as incre1, &#39;a&#39; as id;
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql> select * from incre1;
+---------------+----+
| incre1        | id |
+---------------+----+
| incre1 table  | a  |
+---------------+----+
1 row in set (0.00 sec)
  
mysql>

Copy after login

再进行增量备份，命令如下：

innobackupex --incremental/data/backups/mysql/innobackex_incre_1--incremental-basedir=/data/backups/mysql/innobackupex_full_20150319/--user=backup --password="123456" --host=192.168.52.129 --no-timestamp2>/tmp/innobackex_incre_1.log

备份过程信息如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

[root@data01 mysql]# more/tmp/innobackex_incre_1.log
  
InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.
  
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.
  
Get the latest version of PerconaXtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
  
150319 02:38:04  innobackupex: Connecting to MySQL server withDSN &#39;dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129&#39; as&#39;backup&#39;  (using password: YES).
150319 02:38:04  innobackupex: Connected to MySQL server
150319 02:38:04  innobackupex: Executing a version checkagainst the server...
150319 02:38:04  innobackupex: Done.
150319 02:38:04  innobackupex: Starting the backup operation
  
IMPORTANT: Please check that the backup runcompletes successfully.
          At the end of a successful backup run innobackupex
          prints "completed OK!".
  
innobackupex:  Using server version 5.6.12-log
  
innobackupex: Created backup directory/data/backups/mysql/innobackex_incre_1
  
150319 02:38:04  innobackupex: Starting ibbackup with command:xtrabackup --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/innobackex_incre_1 --tmpdir=/tm
p --extra-lsndir=&#39;/tmp&#39;--incremental-basedir=&#39;/data/backups/mysql/innobackupex_full_20150319/&#39;
innobackupex: Waiting for ibbackup(pid=13358) to suspend
innobackupex: Suspend file&#39;/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2&#39;
  
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423920964 isenabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423925361)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
>> log scanned up to (423925361)
>> log scanned up to (423925361)
>> log scanned up to (423925361)
>> log scanned up to (423925361)
[01] Copying ./IBdata1 to/data/backups/mysql/innobackex_incre_1/IBdata1.delta
>> log scanned up to (423925361)
[01]       ...done
>> log scanned up to (423925361)
[01] Copying ./IBdata2 to /data/backups/mysql/innobackex_incre_1/IBdata2.delta
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackex_incre_1/mysql/slave_master_info.ibd.delta
[01]       ...done
[01] Copying./mysql/slave_relay_log_info.ibd to /data/backups/mysql/innobackex_incre_1/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackex_incre_1/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackex_incre_1/mysql/slave_worker_info.ibd.delta
[01]       ...done
>> log scanned up to (423925361)
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackex_incre_1/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest6.ibd.delta
[01]       ...done
>> log scanned up to (423925361)
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest3.ibd.delta
[01]        ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/innobackex_incre_1/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/innobackex_incre_1/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/incre1.ibd to/data/backups/mysql/innobackex_incre_1/test/incre1.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/innobackex_incre_1/test/t3.ibd.delta
[01]       ...done
[01] Copying ./test/t.ibd to/data/backups/mysql/innobackex_incre_1/test/t.ibd.delta
[01]       ...done
[01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/innobackex_incre_1/test/t_incre_2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to/data/backups/mysql/innobackex_incre_1/user_db/test.ibd.delta
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackex_incre_1/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackex_incre_1/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t.ibd.delta
[01]       ...done
>> log scanned up to (423925361)
xtrabackup: Creating suspend file&#39;/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2&#39; with pid&#39;13358&#39;
>> log scanned up to (423925361)
  
150319 02:38:15  innobackupex: Continuing after ibbackup hassuspended
150319 02:38:15  innobackupex: Executing FLUSH TABLES WITHREAD LOCK...
150319 02:38:15  innobackupex: All tables locked and flushedto disk
  
150319 02:38:15  innobackupex: Starting to backup non-InnoDBtables and files
innobackupex: in subdirectories of&#39;/home/data/mysql/data/&#39;
innobackupex: Backing up files&#39;/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}&#39;(53 files)
>> log scanned up to (423925361)
innobackupex: Backing up files&#39;/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}&#39;(74 files)
>> log scanned up to (423925361)
innobackupex: Backing up file &#39;/home/data/mysql/data//sbtest/sbtest3.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest4.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest6.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest8.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest1.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/db.opt&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest2.frm&#39;
innobackupex: Backing up file &#39;/home/data/mysql/data//sbtest/sbtest7.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest5.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t_incre_2.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t3.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t4.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/incre1.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t1.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/UC_USER.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/t.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/t3.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/test.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/db.opt&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/test2.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/t1.frm&#39;
150319 02:38:17  innobackupex: Finished backing up non-InnoDBtables and files
  
150319 02:38:17  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...
150319 02:38:17  innobackupex: Waiting for log copying tofinish
  
xtrabackup: The latest check point (forincremental): &#39;423925361&#39;
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423925361)
  
xtrabackup: Creating suspend file&#39;/data/backups/mysql/innobackex_incre_1/xtrabackup_log_copied&#39; with pid &#39;13358&#39;
xtrabackup: Transaction log of lsn(423925361) to (423925361) was copied.
150319 02:38:18  innobackupex: All tables unlocked
  
innobackupex: Backup created in directory&#39;/data/backups/mysql/innobackex_incre_1&#39;
innobackupex: MySQL binlog position:filename &#39;mysql-bin.000066&#39;, position 120
150319 02:38:18  innobackupex: Connection to database server closed
150319 02:38:18  innobackupex: completed OK!
[root@data01 mysql]#

Copy after login

6.2 基于全备和第一个增量备份来做第二次增量备份

先录入增量数据录入

mysql> create table incre2 select&#39;incre2&#39; as a,3 as id;
Query OK, 1 row affected (0.12 sec)
Records: 1 Duplicates: 0  Warnings: 0
  
mysql>
mysql> select * from incre2;
+--------+----+
| a     | id |
+--------+----+
| incre2 | 3 |
+--------+----+
1 row in set (0.00 sec)
  
mysql>

Copy after login

开始进行第二次增量备份，备份命令：

innobackupex --incremental/data/backups/mysql/innobackex_incre_2 --incremental-basedir=/data/backups/mysql/innobackex_incre_1/--user=backup --password="123456" --host=192.168.52.129 --no-timestamp2>/tmp/innobackex_incre_2.log

备份过程信息如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

[root@data01 innobackex_incre_1]# more/tmp/innobackex_incre_2.log
  
InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.
  
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.
  
Get the latest version of PerconaXtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
  
150319 02:51:09  innobackupex: Connecting to MySQL server withDSN &#39;dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129&#39; as&#39;backup&#39;  (using password: YES).
150319 02:51:09  innobackupex: Connected to MySQL server
150319 02:51:09  innobackupex: Executing a version checkagainst the server...
150319 02:51:09  innobackupex: Done.
150319 02:51:09  innobackupex: Starting the backup operation
  
IMPORTANT: Please check that the backup runcompletes successfully.
          At the end of a successful backup run innobackupex
          prints "completed OK!".
  
innobackupex:  Using server version 5.6.12-log
  
innobackupex: Created backup directory/data/backups/mysql/innobackex_incre_2
  
150319 02:51:10  innobackupex: Starting ibbackup with command:xtrabackup --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/innobackex_incre_2 --tmpdir=/tm
p --extra-lsndir=&#39;/tmp&#39;--incremental-basedir=&#39;/data/backups/mysql/innobackex_incre_1/&#39;
innobackupex: Waiting for ibbackup(pid=13694) to suspend
innobackupex: Suspend file&#39;/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2&#39;
  
xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 423925361 isenabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/data/mysql/data
xtrabackup: open files limit requested 0,set to 1024
xtrabackup: using the following InnoDBconfiguration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:  innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 67108864
>> log scanned up to (423929601)
xtrabackup: Generating a list oftablespaces
xtrabackup: using the full scan forincremental backup
>> log scanned up to (423929601)
[01] Copying ./IBdata1 to/data/backups/mysql/innobackex_incre_2/IBdata1.delta
>> log scanned up to (423929601)
[01]       ...done
[01] Copying ./IBdata2 to/data/backups/mysql/innobackex_incre_2/IBdata2.delta
[01]       ...done
[01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackex_incre_2/mysql/slave_master_info.ibd.delta
[01]       ...done
>> log scanned up to (423929601)
[01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/innobackex_incre_2/mysql/slave_relay_log_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackex_incre_2/mysql/innodb_index_stats.ibd.delta
[01]       ...done
[01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackex_incre_2/mysql/slave_worker_info.ibd.delta
[01]       ...done
[01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackex_incre_2/mysql/innodb_table_stats.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest1.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest5.ibd to /data/backups/mysql/innobackex_incre_2/sbtest/sbtest5.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest8.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest4.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest2.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest6.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest7.ibd.delta
[01]       ...done
[01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest3.ibd.delta
[01]       ...done
[01] Copying ./test/incre2.ibd to/data/backups/mysql/innobackex_incre_2/test/incre2.ibd.delta
[01]       ...done
[01] Copying ./test/t1.ibd to/data/backups/mysql/innobackex_incre_2/test/t1.ibd.delta
[01]       ...done
[01] Copying ./test/t4.ibd to/data/backups/mysql/innobackex_incre_2/test/t4.ibd.delta
[01]       ...done
[01] Copying ./test/incre1.ibd to/data/backups/mysql/innobackex_incre_2/test/incre1.ibd.delta
[01]       ...done
[01] Copying ./test/t3.ibd to/data/backups/mysql/innobackex_incre_2/test/t3.ibd.delta
[01]       ...done
>> log scanned up to (423929601)
[01] Copying ./test/t.ibd to/data/backups/mysql/innobackex_incre_2/test/t.ibd.delta
[01]       ...done
[01] Copying ./test/t_incre_2.ibd to /data/backups/mysql/innobackex_incre_2/test/t_incre_2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t1.ibd.delta
[01]       ...done
[01] Copying ./user_db/test.ibd to /data/backups/mysql/innobackex_incre_2/user_db/test.ibd.delta
[01]       ...done
[01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackex_incre_2/user_db/test2.ibd.delta
[01]       ...done
[01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t3.ibd.delta
[01]       ...done
[01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackex_incre_2/user_db/UC_USER.ibd.delta
[01]       ...done
[01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t.ibd.delta
[01]       ...done
>> log scanned up to (423929601)
xtrabackup: Creating suspend file&#39;/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2&#39; with pid&#39;13694&#39;
  
150319 02:51:16  innobackupex: Continuing after ibbackup hassuspended
150319 02:51:16  innobackupex: Executing FLUSH TABLES WITHREAD LOCK...
150319 02:51:16  innobackupex: All tables locked and flushedto disk
  
150319 02:51:16  innobackupex: Starting to backup non-InnoDBtables and files
innobackupex: in subdirectories of &#39;/home/data/mysql/data/&#39;
innobackupex: Backing up files&#39;/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}&#39;(53 files)
>> log scanned up to (423929601)
innobackupex: Backing up files&#39;/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}&#39;(74 files)
>> log scanned up to (423929601)
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest3.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest4.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest6.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest8.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest1.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/db.opt&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest2.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest7.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//sbtest/sbtest5.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t_incre_2.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t3.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t4.frm&#39;
innobackupex: Backing up file &#39;/home/data/mysql/data//test/incre2.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/incre1.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//test/t1.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/UC_USER.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/t.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/t3.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/test.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/db.opt&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/test2.frm&#39;
innobackupex: Backing up file&#39;/home/data/mysql/data//user_db/t1.frm&#39;
150319 02:51:17  innobackupex: Finished backing up non-InnoDBtables and files
  
150319 02:51:17  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...
150319 02:51:17  innobackupex: Waiting for log copying tofinish
  
xtrabackup: The latest check point (forincremental): &#39;423929601&#39;
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423929601)
  
xtrabackup: Creating suspend file&#39;/data/backups/mysql/innobackex_incre_2/xtrabackup_log_copied&#39; with pid &#39;13694&#39;
xtrabackup: Transaction log of lsn(423929601) to (423929601) was copied.
150319 02:51:18  innobackupex: All tables unlocked
  
innobackupex: Backup created in directory&#39;/data/backups/mysql/innobackex_incre_2&#39;
innobackupex: MySQL binlog position:filename &#39;mysql-bin.000066&#39;, position 120
150319 02:51:19  innobackupex: Connection to database serverclosed
150319 02:51:19  innobackupex: completed OK!

Copy after login

7，innobackupex做增量恢复

7.1先删除两次增量数据，用来查看验证恢复结果

mysql> delete from incre1;
Query OK, 1 row affected (0.05 sec)
  
mysql> delete from incre2;
Query OK, 1 row affected (0.01 sec)
  
mysql> select * from incre1;
Empty set (0.00 sec)
  
mysql> select * from incre2;
Empty set (0.00 sec)

Copy after login

7.2 开始做恢复，恢复全备份

备份命令如下：

innobackupex --user=backup--password="123456" --host=192.168.52.129 --apply-log/data/backups/mysql/innobackupex_full_20150319/2>/tmp/innobackex_restore_full.log

备份过程信息如下：

......
InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
 
xtrabackup: st

Copy after login

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

R.E.P.O. Best Graphic Settings

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Assassin's Creed Shadows: Seashell Riddle Solution

2 weeks ago By DDD

R.E.P.O. How to Fix Audio if You Can't Hear Anyone

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

WWE 2K25: How To Unlock Everything In MyRise

4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7491

CakePHP Tutorial

1377

What is the format of the account name of steam

win11 activation key permanent

nyt connections hints and answers

Related knowledge

How to backup Google Chrome extensions Jan 30, 2024 pm 12:36 PM

How to backup Google Chrome extension? For most Google Chrome users, more or less plug-ins are installed during daily use. The existence of plug-ins can improve our usage experience. When we reinstall the system or browser, these plug-ins cannot be retained, and it is troublesome to download and install them again. So is there a way to back up the currently installed plug-ins? Here’s how to do it. The tutorial method of backing up chrome plug-ins first opens Google Chrome, click the menu in the upper right corner, and select More Tools - Extensions. Click Package extension above the extensions page. In C:UsersAdministratorAppDataLocalGoogleChromeUserDataDe

How to delete startup backup in Windows 11's File Explorer Feb 18, 2024 pm 05:40 PM

If you wish to hide the "Start Backup" option in Windows 11's File Explorer, here's what you can do. There are several ways to disable or hide the startup backup option in File Explorer, and we'll briefly list some methods to help you accomplish this task quickly. Before you get started, you need to understand that this option is closely tied to OneDrive. Once you open a library folder (such as Document, Pictures, Music, etc.), it will immediately appear in the file explorer's path. How to delete startup backup in Windows 11’s File Explorer To delete startup backup in Windows 11’s File Explorer, follow the steps below

How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to restore the deleted hosts file Feb 22, 2024 pm 10:48 PM

Title: How to restore the hosts file after deletion Summary: The hosts file is a very important file in the operating system and is used to map domain names to IP addresses. If you accidentally delete the hosts file, you may be unable to access certain websites or have other network problems. This article will introduce how to recover accidentally deleted hosts file in Windows and Mac operating systems. Text: 1. Restore hosts file in Windows operating system. Hosts file in Windows operating system

How to backup system with ghost-ghost backup tutorial Mar 06, 2024 pm 04:30 PM

Recently, many friends have asked the editor how to back up the system with ghost. Next, let us learn the tutorial on how to back up the system with ghost. I hope it can help everyone. 1. After running Ghost, click "OK", as shown in the figure. 2. Click "Local" → "Partition" → "ToImage" (meaning: local → partition → to image file), as shown in the figure. 3. The Select Local Hard Disk window appears, click the hard disk where the partition to be backed up is located, and then click "OK", as shown in the figure. 4. The Select Source Partition window appears (the source partition is the partition you want to back up), click on the partition where the system is located (usually Zone 1, be sure to get it right), and then click "OK", as shown in the figure. 5. Play at this time

How to install, uninstall, and reset Windows server backup Mar 06, 2024 am 10:37 AM

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

How to perform system backup and restore on Kirin OS? Aug 07, 2023 pm 02:22 PM

How to perform system backup and restore on Kirin OS? Kirin operating system is an open source operating system independently developed in China and is widely used in various scenarios. System backup and recovery is a very important task when using Kirin operating system. Backup systems can prevent data loss due to malfunctions or misoperations, while system recovery can quickly restore normal functionality in the event of a system crash. This article will introduce in detail how to perform system backup and recovery on Kirin operating system, and attach relevant code examples. Backing Up the System In order to backup the entire system we can

GitLab's code base backup and recovery functions and implementation steps Oct 20, 2023 pm 12:04 PM

GitLab is an open source code hosting platform that provides rich features, including code base backup and recovery. Code base backup is one of the important steps to ensure the security of the code and it can help us recover the data when unexpected things happen. This article will introduce GitLab's code base backup and recovery functions, and provide corresponding implementation steps and code examples. GitLab's code base backup function GitLab provides two types of backup: incremental backup and full backup. Incremental backup: Incremental backup means backing up only the latest changed data

See all articles