Xtrabackup 增量备份和增量还原

测试数据准备

在friends表中录入一条数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| xtra_test          |
+--------------------+
4 rows in set (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_xtra_test |
+---------------------+
| friends             |
+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO friends(id, name) VALUES (1, 'tom' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from friends;
+----+------+
| id | name |
+----+------+
|  1 | jack |
|  1 | tom  |
+----+------+
2 rows in set (0.00 sec)

进行全量备份

[root@node ~]# cd /opt/backup/
[root@node backup]# pwd
/opt/backup
[root@node backup]# ls
full  incr

full 目录存放全量备份的数据
incr 目录存放增量备份的数据

[root@node backup]# innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=234567 --port=3306 /opt/backup/full/
trabackup: recognized server arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
xtrabackup: recognized client arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
181211 15:35:25 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
... ...
181211 15:35:28 Executing UNLOCK TABLES
181211 15:35:28 All tables unlocked
181211 15:35:28 Backup created in directory '/opt/backup/full/2018-12-11_15-35-25/'
MySQL binlog position: filename 'mysql-bin.000001', position '372'
181211 15:35:28 [00] Writing /opt/backup/full/2018-12-11_15-35-25/backup-my.cnf
181211 15:35:28 [00]        ...done
181211 15:35:28 [00] Writing /opt/backup/full/2018-12-11_15-35-25/xtrabackup_info
181211 15:35:28 [00]        ...done
xtrabackup: Transaction log of lsn (1654624) to (1654624) was copied.
181211 15:35:28 completed OK!

备份好了，下面查看

[root@node backup]# ls -l full/2018-12-11_15-35-25/
total 12320
-rw-r----- 1 root root      481 Dec 11 15:35 backup-my.cnf
-rw-r----- 1 root root 12582912 Dec 11 15:35 ibdata1
drwxr-x--- 2 root root     4096 Dec 11 15:35 mysql
drwxr-x--- 2 root root     4096 Dec 11 15:35 performance_schema
-rw-r----- 1 root root       21 Dec 11 15:35 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Dec 11 15:35 xtrabackup_checkpoints
-rw-r----- 1 root root      521 Dec 11 15:35 xtrabackup_info
-rw-r----- 1 root root     2560 Dec 11 15:35 xtrabackup_logfile
drwxr-x--- 2 root root     4096 Dec 11 15:35 xtra_test

第一次增量插入一条数据

mysql> INSERT INTO friends(id, name) VALUES (2, 'winstom' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from friends;
+----+---------+
| id | name    |
+----+---------+
|  1 | jack    |
|  1 | tom     |
|  2 | winstom |
+----+---------+
3 rows in set (0.00 sec)

mysql>

进行第一次增量备份

注意

特别强调

第一次增量备份，要以全量备份为节点，进行备份

增量备份使用--incremental参数加增量备份的目录。

所以 --incremental-basedir 跟的参数是全量备份的目录

[root@node backup]# innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=234567 --incremental /opt/backup/incr/ --incremental-basedir=/opt/backup/full/2018-12-11_15-35-25/
xtrabackup: recognized server arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
xtrabackup: recognized client arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
181211 15:46:22 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
... ...
181211 15:46:24 Executing UNLOCK TABLES
181211 15:46:24 All tables unlocked
181211 15:46:24 Backup created in directory '/opt/backup/incr/2018-12-11_15-46-22/'
MySQL binlog position: filename 'mysql-bin.000001', position '628'
181211 15:46:24 [00] Writing /opt/backup/incr/2018-12-11_15-46-22/backup-my.cnf
181211 15:46:24 [00]        ...done
181211 15:46:24 [00] Writing /opt/backup/incr/2018-12-11_15-46-22/xtrabackup_info
181211 15:46:24 [00]        ...done
xtrabackup: Transaction log of lsn (1654936) to (1654936) was copied.
181211 15:46:24 completed OK!

第一次增量备份完成，下面进行查看

[root@node backup]# ll incr/2018-12-11_15-46-22/
total 132
-rw-r----- 1 root root   481 Dec 11 15:46 backup-my.cnf
-rw-r----- 1 root root 98304 Dec 11 15:46 ibdata1.delta
-rw-r----- 1 root root    44 Dec 11 15:46 ibdata1.meta
drwxr-x--- 2 root root  4096 Dec 11 15:46 mysql
drwxr-x--- 2 root root  4096 Dec 11 15:46 performance_schema
-rw-r----- 1 root root    21 Dec 11 15:46 xtrabackup_binlog_info
-rw-r----- 1 root root   117 Dec 11 15:46 xtrabackup_checkpoints
-rw-r----- 1 root root   589 Dec 11 15:46 xtrabackup_info
-rw-r----- 1 root root  2560 Dec 11 15:46 xtrabackup_logfile
drwxr-x--- 2 root root  4096 Dec 11 15:46 xtra_test

第二次增量插入一条数据

mysql> INSERT INTO friends(id, name) VALUES (3, 'tracey' );
Query OK, 1 row affected (0.00 sec)

mysql> select * from friends;
+----+---------+
| id | name    |
+----+---------+
|  1 | jack    |
|  1 | tom     |
|  2 | winstom |
|  3 | tracey  |
+----+---------+
4 rows in set (0.00 sec)

进行第二次增量备份

注意

特别强调，单独拉了一个菜单

在第二次增量备份的时候，不再是以全量为节点，要以上一次增量备份为节点来进行第二次的增量备份

所以--incremental-basedir参数后面跟的是上一次增量备份的目录；

[root@node backup]# innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=234567 --incremental /opt/backup/incr/ --incremental-basedir=/opt/backup/incr/2018-12-11_15-46-22/
xtrabackup: recognized server arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
xtrabackup: recognized client arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
181211 15:49:41 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".
... ...
181211 15:49:43 Executing UNLOCK TABLES
181211 15:49:43 All tables unlocked
181211 15:49:43 Backup created in directory '/opt/backup/incr/2018-12-11_15-49-41/'
MySQL binlog position: filename 'mysql-bin.000001', position '883'
181211 15:49:43 [00] Writing /opt/backup/incr/2018-12-11_15-49-41/backup-my.cnf
181211 15:49:43 [00]        ...done
181211 15:49:43 [00] Writing /opt/backup/incr/2018-12-11_15-49-41/xtrabackup_info
181211 15:49:43 [00]        ...done
xtrabackup: Transaction log of lsn (1655979) to (1655979) was copied.
181211 15:49:43 completed OK!

备份完成，下面查看

[root@node backup]# ll incr/2018-12-11_15-49-41/
total 228
-rw-r----- 1 root root    481 Dec 11 15:49 backup-my.cnf
-rw-r----- 1 root root 196608 Dec 11 15:49 ibdata1.delta
-rw-r----- 1 root root     44 Dec 11 15:49 ibdata1.meta
drwxr-x--- 2 root root   4096 Dec 11 15:49 mysql
drwxr-x--- 2 root root   4096 Dec 11 15:49 performance_schema
-rw-r----- 1 root root     21 Dec 11 15:49 xtrabackup_binlog_info
-rw-r----- 1 root root    117 Dec 11 15:49 xtrabackup_checkpoints
-rw-r----- 1 root root    589 Dec 11 15:49 xtrabackup_info
-rw-r----- 1 root root   2560 Dec 11 15:49 xtrabackup_logfile
drwxr-x--- 2 root root   4096 Dec 11 15:49 xtra_test

查看xtrabackup_binlog_info中的binlog位置

[root@node backup]# cat full/2018-12-11_15-35-25/xtrabackup_binlog_info 
mysql-bin.000001	372
[root@node backup]# cat incr/2018-12-11_15-46-22/xtrabackup_binlog_info 
mysql-bin.000001	628
[root@node backup]# cat incr/2018-12-11_15-49-41/xtrabackup_binlog_info 
mysql-bin.000001	883

查看xtrabackup_checkpoints中lsn

[root@node backup]# cat full/2018-12-11_15-35-25/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1654624
last_lsn = 1654624
compact = 0
recover_binlog_info = 0
[root@node backup]# cat incr/2018-12-11_15-46-22/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1654624
to_lsn = 1654936
last_lsn = 1654936
compact = 0
recover_binlog_info = 0
[root@node backup]# cat incr/2018-12-11_15-49-41/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1654936
to_lsn = 1655979
last_lsn = 1655979
compact = 0
recover_binlog_info = 0

这里可以看到，全备的lsn是从0开始的，第一次增量备份的lsn是从全量备份的lsn结束的位置开始的，第二次增量备份同理，从第一次增量备份的lsn结束的位置开始的。

模拟数据删除

之间删除xtra_test数据库，来模拟数据丢失等情况

[root@node full]# mysql -uroot -p234567
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| xtra_test          |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop database xtra_test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

本次仅仅是演示，请勿在生产操作次命令；

数据恢复操作

恢复具体步骤

先把xtrabackup备份下来的目录进行再次备份，防止在还原的时候误操作，导致备份不可用；
关闭要回复的数据库实例；
移除需要恢复数据的MySQL实例下的data目录，建议mv移动到别的目录下；
操作全量备份使用--apply-log 和 --redo-only参数对全量备份进行恢复前准备操作，如事物回滚等操作；
操作第一次增量备份将第一次增量备份应用到完全备份，同用使用--apply-log 和 --redo-only参数
操作第二次增量备份将第二次增量备份应用到完全备份，使用--apply-log参数，不要加--redo-only参数。
把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据
使用--copy-back 和 --rsync 参数直接对全量备份操作后的目录进行恢复；
授权MySQL实例下的data目录后，启动数据库
验证恢复数据

第一步备份

[root@node backup]# cd full/
[root@node full]# pwd
/opt/backup/full
[root@node full]# ls
2018-12-11_15-35-25
[root@node full]# cp -a 2018-12-11_15-35-25/ 2018-12-11_15-35-25_bak
[root@node full]# ll
total 8
drwxr-x--- 5 root root 4096 Dec 11 15:35 2018-12-11_15-35-25
drwxr-x--- 5 root root 4096 Dec 11 15:35 2018-12-11_15-35-25_bak
[root@node full]# cd ../incr/
[root@node incr]# ll
total 8
drwxr-x--- 5 root root 4096 Dec 11 15:46 2018-12-11_15-46-22
drwxr-x--- 5 root root 4096 Dec 11 15:49 2018-12-11_15-49-41
[root@node incr]# cp -a 2018-12-11_15-46-22/ 2018-12-11_15-46-22_bak
[root@node incr]# cp -a 2018-12-11_15-49-41 2018-12-11_15-49-41_bak
[root@node incr]# ll
total 16
drwxr-x--- 5 root root 4096 Dec 11 15:46 2018-12-11_15-46-22
drwxr-x--- 5 root root 4096 Dec 11 15:46 2018-12-11_15-46-22_bak
drwxr-x--- 5 root root 4096 Dec 11 15:49 2018-12-11_15-49-41
drwxr-x--- 5 root root 4096 Dec 11 15:49 2018-12-11_15-49-41_bak

第二步关闭MySQL实例

[root@node incr]# /etc/init.d/mysqld  stop
Shutting down MySQL..                                      [  OK  ]
[root@node incr]# ps aux|grep mysql
root     20447  0.0  0.1 112720  2180 pts/1    S+   16:18   0:00 grep --color=auto mysql
[root@node incr]# netstat -lntup | grep 3306
[root@node incr]#

第三步移除要恢复数据的MySQL的data目录

[root@node incr]# ll /opt/mysql/data/
total 286760
-rw-rw---- 1 mysql mysql        56 Dec 11 15:04 auto.cnf
-rw-r----- 1 mysql mysql  12582912 Dec 11 16:17 ibdata1
-rw-r----- 1 mysql mysql 134217728 Dec 11 16:17 ib_logfile0
-rw-r----- 1 mysql mysql 134217728 Dec 11 15:02 ib_logfile1
-rw-r----- 1 mysql mysql  12582912 Dec 11 15:02 ibtmp1
drwxr-x--- 2 mysql mysql      4096 Dec 11 15:02 mysql
-rw-rw---- 1 mysql mysql       906 Dec 11 16:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql        37 Dec 11 15:04 mysql-bin.index
drwxr-x--- 2 mysql mysql      4096 Dec 11 15:02 performance_schema
-rw-r----- 1 mysql mysql        21 Dec 11 15:02 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql       509 Dec 11 15:02 xtrabackup_info
-rw-r----- 1 mysql mysql         1 Dec 11 15:02 xtrabackup_master_key_id
drwxr-x--- 2 mysql mysql      4096 Dec 11 15:02 xtra_test
[root@node incr]# mv /opt/mysql/data /tmp/
[root@node incr]# ls -ld /tmp/data/
drwxr-x--- 5 mysql mysql 4096 Dec 11 15:04 /tmp/data/

第四步准备全量备份

[root@node backup]# innobackupex --apply-log --redo-only /opt/backup/full/2018-12-11_15-35-25/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
181211 16:40:24 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".
... ...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1654633
InnoDB: Number of pools: 1
181211 16:40:26 completed OK!

第五步应用第一次增量备份到全量备份

[root@node backup]# innobackupex --apply-log --redo-only /opt/backup/full/2018-12-11_15-35-25/ --incremental-dir=/opt/backup/incr/2018-12-11_15-46-22/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
181211 16:40:57 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".
... ...
181211 16:40:59 [01] Copying /opt/backup/incr/2018-12-11_15-46-22/performance_schema/events_waits_summary_global_by_event_name.frm to ./performance_schema/events_waits_summary_global_by_event_name.frm
181211 16:40:59 [01]        ...done
181211 16:40:59 [00] Copying /opt/backup/incr/2018-12-11_15-46-22//xtrabackup_binlog_info to ./xtrabackup_binlog_info
181211 16:40:59 [00]        ...done
181211 16:40:59 [00] Copying /opt/backup/incr/2018-12-11_15-46-22//xtrabackup_info to ./xtrabackup_info
181211 16:40:59 [00]        ...done
181211 16:40:59 completed OK!

查看全量备份的xtrabackup_checkpoints

[root@node full]# cat 2018-12-11_15-35-25/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1654936
last_lsn = 1654936
compact = 0
recover_binlog_info = 0

对比之前查看的第一次增量备份的last_lsn位置，在应用第一次增量备份到全量后，可以看到last_lsn已经被应用和第一次全量备份的位置相同了

第六步应用第二次增量备份到全量备份

注意在引用最后一次增量备份到全量时，不需要增加--redo-only参数。

[root@node backup]# innobackupex --apply-log /opt/backup/full/2018-12-11_15-35-25/ --incremental-dir=/opt/backup/incr/2018-12-11_15-49-41/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
181211 16:42:04 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".
... ...
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1655998
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1656332
InnoDB: Doing recovery: scanned up to log sequence number 1656341 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 883, file name mysql-bin.000001
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 1656341
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1656360
181211 16:42:10 completed OK!

查看全量备份的xtrabackup_checkpoints

[root@node full]# cat 2018-12-11_15-35-25/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1655979
last_lsn = 1655979
compact = 0
recover_binlog_info = 0

对比之前查看的第二次增量备份的last_lsn位置，在应用第二次增量备份到全量后，可以看到last_lsn已经被应用和第二次全量备份的位置相同了

第七步把备份整体进行一次apply操作

[root@node full]# innobackupex --apply-log /opt/backup/full/2018-12-11_15-35-25/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=134217728 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 
181211 17:13:33 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".
... ...
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.7.19 started; log sequence number 1656379
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1656398
181211 17:13:36 completed OK!

第八步使用`--copy-back` 参数恢复拷贝到data目录

[root@node full]# innobackupex --defaults-file=/opt/mysql-5.6/my.cnf --copy-back /opt/backup/full/2018-12-11_15-35-25/
xtrabackup: recognized server arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
xtrabackup: recognized client arguments: --datadir=/opt/mysql/data --server-id=1 --innodb_data_home_dir=/opt/mysql/data --innodb_flush_log_at_trx_commit=0 --innodb_log_buffer_size=16M --innodb_buffer_pool_size=256M --innodb_log_file_size=128M --innodb_autoextend_increment=1000 --innodb_open_files=300 --innodb_file_per_table=1 --innodb_checksum_algorithm=0 --open_files_limit=4161 --log_bin=/opt/mysql-5.6/data/mysql-bin 
181211 17:14:21 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".
... ...
181211 17:14:25 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /opt/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm
181211 17:14:25 [01]        ...done
181211 17:14:25 [01] Copying ./performance_schema/mutex_instances.frm to /opt/mysql/data/performance_schema/mutex_instances.frm
181211 17:14:25 [01]        ...done
181211 17:14:25 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /opt/mysql/data/performance_schema/events_waits_summary_global_by_event_name.frm
181211 17:14:25 [01]        ...done
181211 17:14:25 [01] Copying ./xtrabackup_info to /opt/mysql/data/xtrabackup_info
181211 17:14:25 [01]        ...done
181211 17:14:25 [01] Copying ./xtrabackup_master_key_id to /opt/mysql/data/xtrabackup_master_key_id
181211 17:14:25 [01]        ...done
181211 17:14:25 completed OK!

验证操作

[root@node full]# ls -l /opt/mysql/data/
total 286744
-rw-r----- 1 root root  12582912 Dec 11 17:14 ibdata1
-rw-r----- 1 root root 134217728 Dec 11 17:14 ib_logfile0
-rw-r----- 1 root root 134217728 Dec 11 17:14 ib_logfile1
-rw-r----- 1 root root  12582912 Dec 11 17:14 ibtmp1
drwxr-x--- 2 root root      4096 Dec 11 17:14 mysql
drwxr-x--- 2 root root      4096 Dec 11 17:14 performance_schema
-rw-r----- 1 root root        21 Dec 11 17:14 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root       589 Dec 11 17:14 xtrabackup_info
-rw-r----- 1 root root         1 Dec 11 17:14 xtrabackup_master_key_id
drwxr-x--- 2 root root      4096 Dec 11 17:14 xtra_test

第九步授权并启动MySQL

[root@node full]# chown -R mysql.mysql /opt/mysql/data
[root@node full]# ls -l /opt/mysql-5.6/data/
total 286744
-rw-r----- 1 mysql mysql  12582912 Dec 11 17:14 ibdata1
-rw-r----- 1 mysql mysql 134217728 Dec 11 17:14 ib_logfile0
-rw-r----- 1 mysql mysql 134217728 Dec 11 17:14 ib_logfile1
-rw-r----- 1 mysql mysql  12582912 Dec 11 17:14 ibtmp1
drwxr-x--- 2 mysql mysql      4096 Dec 11 17:14 mysql
-rw-rw---- 1 mysql mysql         0 Dec 11 17:19 mysql-bin.index
drwxr-x--- 2 mysql mysql      4096 Dec 11 17:14 performance_schema
-rw-r----- 1 mysql mysql        21 Dec 11 17:14 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql       589 Dec 11 17:14 xtrabackup_info
-rw-r----- 1 mysql mysql         1 Dec 11 17:14 xtrabackup_master_key_id
drwxr-x--- 2 mysql mysql      4096 Dec 11 17:14 xtra_test
[root@node full]# /etc/init.d/mysqld  start
Starting MySQL.                                            [  OK  ]
[root@node full]# ps aux|grep mysql
root     21533  0.6  0.1 113324  3208 pts/2    S    17:19   0:00 /bin/sh /opt/mysql/bin/mysqld_safe --datadir=/opt/mysql/data --pid-file=/opt/mysql/mysqld.pid
mysql    22340 10.4 44.2 2299484 903940 pts/2  Sl   17:19   0:00 /opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/opt/mysql/data --plugin-dir=/opt/mysql/lib/plugin --user=mysql --log-error=/opt/mysql/error.log --open-files-limit=4161 --pid-file=/opt/mysql/mysqld.pid --socket=/tmp/mysql.sock --port=3306
root     22369  0.0  0.1 112720  2400 pts/2    S+   17:19   0:00 grep --color=auto mysql
[root@node full]# netstat -lntup | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      22340/mysqld

第十步验证数据

[root@node full]# mysql -uroot -p234567
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| xtra_test          |
+--------------------+
4 rows in set (0.00 sec)

mysql> use xtra_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_xtra_test |
+---------------------+
| friends             |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from friends;
+----+---------+
| id | name    |
+----+---------+
|  1 | jack    |
|  1 | tom     |
|  2 | winstom |
|  3 | tracey  |
+----+---------+
4 rows in set (0.00 sec)

Xtrabackup 增量备份 和 增量还原

测试数据准备

进行全量备份

第一次增量插入一条数据

进行第一次增量备份

注意

第二次增量插入一条数据

进行第二次增量备份

注意

查看xtrabackup_binlog_info中的binlog位置

查看xtrabackup_checkpoints中lsn

模拟数据删除

数据恢复操作

恢复具体步骤

第一步 备份

第二步 关闭MySQL实例

第三步 移除要恢复数据的MySQL的data目录

第四步 准备全量备份

第五步 应用第一次增量备份到全量备份

查看全量备份的xtrabackup_checkpoints

第六步 应用第二次增量备份到全量备份

查看全量备份的xtrabackup_checkpoints

第七步 把备份整体进行一次apply操作

第八步 使用--copy-back 参数恢复拷贝到data目录

第九步 授权并启动MySQL

第十步 验证数据

Xtrabackup 增量备份和增量还原

第一步备份

第二步关闭MySQL实例

第三步移除要恢复数据的MySQL的data目录

第四步准备全量备份

第五步应用第一次增量备份到全量备份

第六步应用第二次增量备份到全量备份

第七步把备份整体进行一次apply操作

第八步使用`--copy-back` 参数恢复拷贝到data目录

第九步授权并启动MySQL

第十步验证数据