Percona XtraBackup 2.4 innobackupex全量、增量备份恢复流程
innobackupex是xtrabackup的软连接。
xtrabackup 使用C语言编译的备份工具，可以完整备份MySQL数据库，支持MyISAM、InnoDB和XtraDB存储引擎。
在备份的时候，备份工具主要执行两个任务来完成备份：
①
在后台启动一个日志拷贝线程。这个线程会监视InnoDB日志文件，当日志文件发生改变时，这个线程会将发生变化的数据块拷贝到备份目录下一个名为xtrabackup_logfile的文件中。这个操作是必要的，因为备份可能会持续很长时间，在数据库恢复时，需要所有从备份开始到结束的这些日志文件。
② 拷贝InnoDB数据文件到指定备份目录下。这不是一个简单的拷贝，备份工具打开并读取文件的方式类似InnoDB，通过读取文件目录并以页（page）为单位进行拷贝。
当数据文件拷贝结束时，xtrabackup会停止日志拷贝线程，并在指定备份目录中创建一个名为xtrabackup_checkpoints的文件，这个文件包含备份的类型、开始备份的日志序列号和结束备份的日志序列号。
[root@localhost bin]# ls -trl /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin
total 224028
-rwxr-xr-x. 1 root root 3020 Mar 29 04:36 xbcloud_osenv
-rwxr-xr-x. 1 root root 5070569 Mar 29 04:36 xbstream
-rwxr-xr-x. 1 root root 4999056 Mar 29 04:36 xbcrypt
-rwxr-xr-x. 1 root root 5179178 Mar 29 04:36 xbcloud
-rwxr-xr-x. 1 root root 214139498 Mar 29 04:50 xtrabackup
lrwxrwxrwx. 1 root root 10 Apr 25 01:11 innobackupex -> xtrabackup
innobackupex常用参数
--compact 创建一个不包含第二索引（除了主键之外的索引）的备份
--decompress 解压之前所有以–compress参数备份出来的带有.qp格式的备份文件，--parallel参数会允许同时解锁或解压多个文件。需要安装qpress软件。
--defaults-file=[MY.CNF] 配置文件的路径
--incremental-basedir 以上一次全量或增量备份的路径，作为增量备份的基础。指定这个参数的同时，应该同样指定--incremental参数
--incremental 创建增量备份，当指定这个参数的时候，应该指定--incremental-lsn或--incremental-basedir参数，否则将会备份到--incremental-basedir路径
--apply-log 在备份目录下，通过应用名称为xtrabackup_logfile的交易日志文件来准备备份。同时，创建新的交易日志。
--use-memory=# 指定数据库恢复时使用的内存大小，需要搭配--apply-log参数。
--redo-only 当准备数据库的全备或合并增量备份时，需要指定这个参数。这个参数实际上执行的是，会让xtrabackup跳过回滚节点，只做“redo”步骤。当数据库需要应用增量备份时，需要指定这个参数。
--incremental-dir=DIRECTORY 指定增量备份的目录，需要搭配--incremental参数。
--no-timestamp 这个参数会让xtrabackup在备份的时候不创建带有时间格式的子文件夹。当指定了这个参数，备份会直接创建在指定的备份目录下。
--stream=STREAMNAME 指定流备份的格式。备份将会以指定格式输出到STDOUT。目前支持的格式有tar 和 xbstream。如果指定了这个参数，后面需要接tmpdir目录作为处理流的一个中间目录。
--slave-info 当备份一个作为复制环境的服务器时，这个参数会自动将CHANGE MASTER语句写到备份中，在恢复备份后，不必执行CHANGE MASTER语句。
--tables-file=FILE 这个参数会接受一个字符串，这个字符串指定了一个文件，这个文件包含了要备份的表名，格式如database.table，一行一个。
--use-memory=# 这个参数用于在准备备份时，xtrabackup执行crash recovery所使用的内存大小。这个参数仅和--apply-log搭配时才生效。
--创建备份账户
mysql> create user xtrabk@'localhost' identified by 'Myql#2015';
Query OK, 0 rows affected (0.18 sec)
mysql> grant reload,lock tables,replication client,super on *.* to xtrabk@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.44 sec)
需要注意的是，xtrabackup没有提供日志功能。如果要记录日志的话，需要在备份命令中添加下列命令。
innobackupex --defaults-file=/var/lib/mysql/main_my.cnf \
> --user=${MYSQL_USER} --password=${MYSQL_PASS} \
> --extra-lsndir=/database/backup/2016-08-09/checkpoints/ \
> --compress --compress-threads=8 \
> --stream=xbstream --parallel=4 /tmp \
> 2>> /database/backup/2016-08-09/xtra_full_bak_2016-08-09.log \
> > /database/backup/2016-08-09/xtra_full_bak_2016-08-09.xbstream
--innobackupex全量备份数据库
[root@localhost 20160719]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password='System#2013' /backup/20160716/
160716 01:48:48 [00] Writing xtrabackup_binlog_info
160716 01:48:48 [00] ...done
160716 01:48:48 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186510528'
xtrabackup: Stopping log copying thread.
.160716 01:48:49 >> log scanned up to (186510537)
160716 01:48:49 Executing UNLOCK TABLES
160716 01:48:49 All tables unlocked
160716 01:48:49 [00] Copying ib_buffer_pool to /backup/20160716/2016-07-16_01-48-32/ib_buffer_pool
160716 01:48:49 [00] ...done
160716 01:48:49 Backup created in directory '/backup/20160716/2016-07-16_01-48-32'
MySQL binlog position: filename
'production-bin.000006', position '194', GTID of the last change
'cf291e84-2c89-11e6-b6f0-000c29631605:1-44'
160716 01:48:49 [00] Writing backup-my.cnf
160716 01:48:49 [00] ...done
160716 01:48:49 [00] Writing xtrabackup_info
160716 01:48:49 [00] ...done
xtrabackup: Transaction log of lsn (186510528) to (186510537) was copied.
160716 01:48:49 completed OK!
--查看备份出来的文件
[root@localhost 20160716]# cd 2016-07-16_01-48-32/
[root@localhost 2016-07-16_01-48-32]# ls -trl
total 77876
-rw-r-----. 1 root root 79691776 Jul 16 01:48 ibdata1
drwxr-x---. 2 root root 4096 Jul 16 01:48 fire
drwxr-x---. 2 root root 12288 Jul 16 01:48 sys
drwxr-x---. 2 root root 4096 Jul 16 01:48 performance_schema
drwxr-x---. 2 root root 4096 Jul 16 01:48 mysql
-rw-r-----. 1 root root 68 Jul 16 01:48 xtrabackup_binlog_info
-rw-r-----. 1 root root 2560 Jul 16 01:48 xtrabackup_logfile
-rw-r-----. 1 root root 117 Jul 16 01:48 xtrabackup_checkpoints
-rw-r-----. 1 root root 6566 Jul 16 01:48 ib_buffer_pool
-rw-r-----. 1 root root 428 Jul 16 01:48 backup-my.cnf
-rw-r-----. 1 root root 577 Jul 16 01:48 xtrabackup_info
[root@localhost 2016-07-16_01-48-32]# cat xtrabackup_info
uuid = 1d38a5f2-4b32-11e6-9859-000c29631605
name =
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --user=root --password=... /backup/20160716/
tool_version = 2.4.2
ibbackup_version = 2.4.2
server_version = 5.7.12-log
start_time = 2016-07-16 01:48:32
end_time = 2016-07-16 01:48:49
lock_time = 0
binlog_pos = filename
'production-bin.000006', position '194', GTID of the last change
'cf291e84-2c89-11e6-b6f0-000c29631605:1-44'
innodb_from_lsn = 0
innodb_to_lsn = 186510528
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
[root@localhost 2016-07-19_02-07-35]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 186511470
last_lsn = 186511479
compact = 0
recover_binlog_info = 0
--innobackupex全量备份恢复
--在数据库中创建测试表并插入数据
mysql> use fire
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_fire |
+----------------+
| t1 |
| t2 |
| test |
| v_t1 |
| v_t1_myisam |
+----------------+
5 rows in set (0.00 sec)
mysql> select * from t1;
+------+------+
| f1 | gc |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
+------+------+
5 rows in set (0.00 sec)
mysql> delete from t1;
Query OK, 5 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
Empty set (0.00 sec)
--关闭数据库
[root@localhost fire]# service mysqld stop
--删除数据文件目录下的所有文件
[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# ls
auto.cnf client-key.pem
ib_logfile0 performance_schema production-bin.000003
production-bin.000007 server-cert.pem
ca-key.pem fire
ib_logfile1 private_key.pem production-bin.000004
production-bin.000008 server-key.pem
ca.pem ib_buffer_pool
localhost.localdomain.err production-bin.000001 production-bin.000005
production-bin.index sys
client-cert.pem ibdata1 mysql production-bin.000002 production-bin.000006 public_key.pem
[root@localhost mysql]# rm -rf *
--备份出来的数据文件由于拷贝时间不同，在拷贝数据的过程中，数据可能会发生变化，直接使用这些数据文件，会导致恢复出来的数据文件可能会有数据损坏。准备备份文件的目的，是让数据文件在某时间点上保持一致性。可以在任意机器上执行准备备份文件这一动作，不需要仅在备份的主机上面执行这一动作。准备好备份，使用--apply-log参数并指定带时间的备份子目录。想要加快apply-log的进程，推荐使用--use-memory参数。
[root@localhost
2016-07-16_01-48-32]#
/install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex
--defaults-file=/etc/my.cnf --apply-log
/backup/20160716/2016-07-16_01-48-32/
.....
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support not available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
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.11 started; log sequence number 186511139
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
--当准备备份的操作完成后，会看到下面信息
InnoDB: Shutdown completed; log sequence number 186511158
160716 03:07:07 completed OK!
--恢复已经准备好的备份，停止MySQL服务并在innobackupex命令中附带--copy-back参数，这会将已经准备好的数据拷贝回my.cnf参数文件中指定的数据目录下。
[root@localhost
2016-07-16_01-48-32]#
/install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex
--defaults-file=/etc/my.cnf --copy-back
/backup/20160716/2016-07-16_01-48-32/
.....
160716 03:12:30 [01] Copying ./mysql/engine_cost.frm to /var/lib/mysql/mysql/engine_cost.frm
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/server_cost.frm to /var/lib/mysql/mysql/server_cost.frm
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/time_zone_name.ibd to /var/lib/mysql/mysql/time_zone_name.ibd
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/help_category.frm to /var/lib/mysql/mysql/help_category.frm
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/innodb_table_stats.frm to /var/lib/mysql/mysql/innodb_table_stats.frm
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/slave_master_info.frm to /var/lib/mysql/mysql/slave_master_info.frm
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
160716 03:12:30 [01] ...done
160716 03:12:30 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
160716 03:12:30 [01] ...done
160716 03:12:30 completed OK!
--数据恢复完成之后，需要修改相关文件的权限
[root@localhost 2016-07-16_01-48-32]# chown -R mysql.mysql /var/lib/mysql
--启动数据库
[root@localhost fire]# service mysqld start
--查看删除的数据，已经恢复
mysql> select * from fire.t1;
+------+------+
| f1 | gc |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
+------+------+
5 rows in set (0.00 sec)
--使用流的格式压缩备份全库
[root@localhost bin]#
./innobackupex --defaults-file=/etc/my.cnf --stream=tar /tmp --user
system --password 'Mysql#2015' | gzip ->
/backup/xtra/xtra_fullbackup.tar.gz
--innobackupex增量备份
--进行全量备份
[root@localhost
backup]#
/install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex
--defaults-file=/etc/my.cnf --user=root --password='System#2013'
/backup/20160719/
160719 07:24:33 [01] Copying ./mysql/help_relation.frm to /backup/20160719/2016-07-19_07-24-18/mysql/help_relation.frm
160719 07:24:33 [01] ...done
160719 07:24:33 Finished backing up non-InnoDB tables and files
160719 07:24:33 [00] Writing xtrabackup_binlog_info
160719 07:24:33 [00] ...done
160719 07:24:33 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186521799'
xtrabackup: Stopping log copying thread.
.160719 07:24:33 >> log scanned up to (186521808)
160719 07:24:33 Executing UNLOCK TABLES
160719 07:24:33 All tables unlocked
160719 07:24:33 [00] Copying ib_buffer_pool to /backup/20160719/2016-07-19_07-24-18/ib_buffer_pool
160719 07:24:33 [00] ...done
160719 07:24:33 Backup created in directory '/backup/20160719/2016-07-19_07-24-18'
MySQL binlog position: filename 'production-bin.000001', position '335', GTID of the last change 'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
e240ea67-4dbb-11e6-a7d5-000c29631605:1'
160719 07:24:33 [00] Writing backup-my.cnf
160719 07:24:33 [00] ...done
160719 07:24:34 [00] Writing xtrabackup_info
160719 07:24:34 [00] ...done
xtrabackup: Transaction log of lsn (186521799) to (186521808) was copied.
160719 07:24:34 completed OK!
[root@localhost 2016-07-19_07-24-18]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 186521799
last_lsn = 186521808
compact = 0
recover_binlog_info = 0
--创建测试表一并插入数据
mysql> use fire
Database changed
mysql> create table emp(id int(7), name varchar(15));
Query OK, 0 rows affected (0.53 sec)
mysql> insert into emp values(10, 'Neo');
Query OK, 1 row affected (0.15 sec)
mysql> insert into emp values(20, 'Trinity');
Query OK, 1 row affected (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from emp;
+------+---------+
| id | name |
+------+---------+
| 10 | Neo |
| 20 | Trinity |
+------+---------+
2 rows in set (0.06 sec)
--进行增量备份一，以上面的全量备份目录为基础
[root@localhost
2016-07-19_07-24-18]#
/install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/innobackupex
--defaults-file=/etc/my.cnf --user=root --password='System#2013'
--incremental /backup/20160719/inc1
--incremental-basedir=/backup/20160719/2016-07-19_07-24-18/
.....
160719 07:29:04 [01] Copying ./mysql/proc.MYD to /backup/20160719/inc1/2016-07-19_07-28-54/mysql/proc.MYD
160719 07:29:04 [01] ...done
160719 07:29:04 [01] Copying ./mysql/help_relation.frm to /backup/20160719/inc1/2016-07-19_07-28-54/mysql/help_relation.frm
160719 07:29:04 [01] ...done
160719 07:29:04 Finished backing up non-InnoDB tables and files
160719 07:29:04 [00] Writing xtrabackup_binlog_info
160719 07:29:04 [00] ...done
160719 07:29:04 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186528276'
xtrabackup: Stopping log copying thread.
.160719 07:29:04 >> log scanned up to (186528285)
160719 07:29:04 Executing UNLOCK TABLES
160719 07:29:04 All tables unlocked
160719 07:29:04 [00] Copying ib_buffer_pool to /backup/20160719/inc1/2016-07-19_07-28-54/ib_buffer_pool
160719 07:29:04 [00] ...done
160719 07:29:04 Backup created in directory '/backup/20160719/inc1/2016-07-19_07-28-54'
MySQL binlog position: filename
'production-bin.000001', position '1045', GTID of the last change
'cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
e240ea67-4dbb-11e6-a7d5-000c29631605:1-4'
160719 07:29:04 [00] Writing backup-my.cnf
160719 07:29:04 [00] ...done
160719 07:29:04 [00] Writing xtrabackup_info
160719 07:29:04 [00] ...done
xtrabackup: Transaction log of lsn (186528276) to (186528285) was copied.
160719 07:29:04 completed OK!