简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Home > Database > Mysql Tutorial > body text

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

WBOY
Release： 2016-06-01 13:01:32
Original
850 people have browsed it

1，创建mysql备份用户

mysql -uroot --password="" -e"CREATE USER 'backup'@'192.168.%' IDENTIFIED BY '123456'";

mysql -uroot --password="" -e"GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, SUPERON *.* TO 'backup'@'192.168.%'";

 

2，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): '423920168'
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 'aaa' asa, 1 as b;
Query OK, 1 row affected (0.36 sec)
Records: 1 Duplicates: 0  Warnings: 0
 
mysql>
mysql> insert into t4 select 'bbb',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): '423925813'
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'cc' 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'dd',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): '423931610'
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 '--prepare'.
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'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'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'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

备份过程信息：

[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 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;host=192.168.52.129'as 'backup'  (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='/tmp'
innobackupex: Waiting for ibbackup(pid=13118) to suspend
innobackupex: Suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2'
 
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'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2' withpid '13118'
 
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'/home/data/mysql/data/'
innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)
>> log scanned up to (423920964)
innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)
>> log scanned up to (423920964)
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest3.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest7.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'
innobackupex: Backing up file '/home/data/mysql/data//user_db/t.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'
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): '423920964'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423920964)
 
xtrabackup: Creating suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_log_copied' with pid'13118'
xtrabackup: Transaction log of lsn(423920964) to (423920964) was copied.
150319 02:31:27  innobackupex: All tables unlocked
 
innobackupex: Backup created in directory'/data/backups/mysql/innobackupex_full_20150319'
innobackupex: MySQL binlog position:filename 'mysql-bin.000066', 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'incre1 table ' as incre1, 'a' 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

备份过程信息如下：

[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 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129' as'backup'  (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='/tmp'--incremental-basedir='/data/backups/mysql/innobackupex_full_20150319/'
innobackupex: Waiting for ibbackup(pid=13358) to suspend
innobackupex: Suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2'
 
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'/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2' with pid'13358'
>> 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'/home/data/mysql/data/'
innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)
>> log scanned up to (423925361)
innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)
>> log scanned up to (423925361)
innobackupex: Backing up file '/home/data/mysql/data//sbtest/sbtest3.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'
innobackupex: Backing up file '/home/data/mysql/data//sbtest/sbtest7.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/incre1.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'
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): '423925361'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423925361)
 
xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_log_copied' with pid '13358'
xtrabackup: Transaction log of lsn(423925361) to (423925361) was copied.
150319 02:38:18  innobackupex: All tables unlocked
 
innobackupex: Backup created in directory'/data/backups/mysql/innobackex_incre_1'
innobackupex: MySQL binlog position:filename 'mysql-bin.000066', 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'incre2' 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

备份过程信息如下：

[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 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129' as'backup'  (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='/tmp'--incremental-basedir='/data/backups/mysql/innobackex_incre_1/'
innobackupex: Waiting for ibbackup(pid=13694) to suspend
innobackupex: Suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2'
 
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'/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2' with pid'13694'
 
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 '/home/data/mysql/data/'
innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files)
>> log scanned up to (423929601)
innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files)
>> log scanned up to (423929601)
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest3.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest7.frm'
innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm'
innobackupex: Backing up file '/home/data/mysql/data//test/incre2.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/incre1.frm'
innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt'
innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm'
innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm'
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): '423929601'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (423929601)
 
xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_log_copied' with pid '13694'
xtrabackup: Transaction log of lsn(423929601) to (423929601) was copied.
150319 02:51:18  innobackupex: All tables unlocked
 
innobackupex: Backup created in directory'/data/backups/mysql/innobackex_incre_2'
innobackupex: MySQL binlog position:filename 'mysql-bin.000066', 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
Related labels：
backup process
source：php.cn
Previous article：Mysql事务处理_MySQL Next article：mysql 1366错误：字符集冲突导致插入数据异常，解决方案_MySQL
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
Latest Articles by Author
Latest Issues
What is the format of the variables in the return value? I am a new learner of php. I found a piece of code: if($x<time()){return[false,'error']...
From 2024-04-06 21:55:20
0
1
778
Dynamically modify inline CSS styles in Javafx I'm trying to change the background image of a pane when the application is maximized. My ...
From 2024-04-06 20:57:16
0
1
487
SQL Count() also requires 0 I have only one table and I want to count each entry of a tag that starts with 0. Now I tr...
From 2024-04-06 19:48:53
0
1
449
MySQL: "Select text from... as <variable here or subquery>" I have the following table with the following data: id text language 1 german text german ...
From 2024-04-06 19:25:54
0
2
529
Using variables defined in methods in templates This is my first time using Vue (v2 not v3) and I've been trying to use variables inside t...
From 2024-04-06 18:10:25
0
2
513
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn：Public welfare online PHP training，Help PHP learners grow quickly！