CNDBA社区
1 Xtrabackup 概述
Percona公司是mysql领域比较有名的咨询公司，其对innoDB存储引擎进行了性能增强，被percona增强后的innoDB存储引擎叫做Percona XtraDB，Centos7中默认提供Mariadb5.5数据库，而MariadDB5.5的默认的存储引擎就是XtraDB，因为XtraDB完全兼容InnoDB，所以我们可以无差别的使用他们。
Percona推出了一款mysql innodb数据库备份工具：XtraBackup，XtraBackup是一款免费软件，xtrabackup支持对innodb进行热备、增量备份、差量备份。
与mysqldump不同，xtrabackup是一种物理备份工具，它需要通过协议连接到mysql服务端，然后读取并复制innodb底层的”数据块”，完成所谓的”物理备份”。
当我们开始备份innodb数据时，还需要同时备份对应的事务日志，因为在开始备份时，有些事务已经存在于事务日志中，这些事务可能已经提交了，但是还没有同步到datafile中，所以我们要重放这些已经提交的事务，有些事务可能还未提交，所以我们要回滚这些没有提交的事务。
xtrabackup在备份开始时，同时运作两个线程，一个线程负责备份innodb中的page，另一个线程负责备份innodb的事务日志（redo log），事务日志都会被xtrabackup记录到自己的日志文件中，那么，备份结束后，我们会得到两份文件，一份是不可用的备份文件，一份是备份时的事务日志，备份文件之所以不可用，是因为有一部分不确定的数据可能在事务日志中，而且热备过程中数据也可能会发生改变，所以我们要通过这两份文件，制作出一份可用的备份文件，就是通过应用事务日志的方式，让最终的备份成为一个可用的备份，事务日志会被应用，事务日志文件中已经提交的事务需要replayed，未提交的事务需要roolback，整个过程类似于mysql崩溃后恢复的过程，但是这个过程在xtrabackup中被称为”prepare”，”prepare”操作保证了备份出的数据的一致性，没有经过prepare的备份数据是不可用的。
2 Xtrabackup工具安装
2.1 安装过程
可以使用yum源来安装xtrabackup工具，也可以直接从官网下载rpm或者tar包进行安装：
Linux 平台下 yum 源 配置 手册
http://www.cndba.cn/dave/article/154
官网的下载地址
http://www.percona.com/software/percona-xtrabackup
先安装libev包，这个必须先安装：
https://centos.pkgs.org/6/repoforge-x86_64/libev-4.15-1.el6.rf.x86_64.rpm.html
[root@www.cndba.cn/dave /]# rpm -ivh libev-4.03-3.el6.x86_64.rpm
warning: libev-4.03-3.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing... ########################################### [100%]
1:libev ########################################### [100%]
[root@www.cndba.cn/dave /]#
[root@www.cndba.cn/dave /]# yum localinstall percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
Loaded plugins: product-id, refresh-packagekit, security, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Setting up Local Package Process
Examining percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm: percona-xtrabackup-24-2.4.9-1.el6.x86_64
Marking percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm to be installed
cndba.cn | 4.1 kB 00:00 ...
cndba.cn/primary_db | 3.1 MB 00:00 ...
Resolving Dependencies
--> Running transaction check
---> Package percona-xtrabackup-24.x86_64 0:2.4.9-1.el6 will be installed
--> Processing Dependency: perl(DBD::mysql) for package: percona-xtrabackup-24-2.4.9-1.el6.x86_64
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
======================================================================================================================================================
Package Arch Version Repository Size
======================================================================================================================================================
Installing:
percona-xtrabackup-24 x86_64 2.4.9-1.el6 /percona-xtrabackup-24-2.4.9-1.el6.x86_64 31 M
Installing for dependencies:
perl-DBD-MySQL x86_64 4.013-3.el6 cndba.cn 134 k
Transaction Summary
======================================================================================================================================================
Install 2 Package(s)
Total size: 31 M
Total download size: 134 k
Installed size: 31 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 1/2
Installing : percona-xtrabackup-24-2.4.9-1.el6.x86_64 2/2
Verifying : percona-xtrabackup-24-2.4.9-1.el6.x86_64 1/2
Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 2/2
Installed:
percona-xtrabackup-24.x86_64 0:2.4.9-1.el6
Dependency Installed:
perl-DBD-MySQL.x86_64 0:4.013-3.el6
Complete!
[root@www.cndba.cn/dave /]#
2.2 程序说明
- 安装2.3版本之前的XtraBackup后，有两个主要的备份工具：xtrabackup与innobackupex。
- xtrabackup是一个C程序。
innobackupex是一个perl脚本，它对xtrabackup这个C程序进行了封装，在备份innodb表时，此脚本会调用xtrabackup这个C程序。
如果使用xtrabackup这个C程序进行备份，则只能备份innodb和xtradb的表，不能备份myisam表。
如果使用innobackupex进行备份，则可以备份innodb或xtradb的表，同时也能够备份myisam表。
所以，一般在使用XtraBackup备份工具进行数据备份时，通常会选择使用innobackupex命令进行备份。
但在2.4版本中，innobackupex的功能已经完全整合到了xtrabackup中，为了兼容之前用户的使用习惯，官方保留了innobackupex，并用一个软连接，指向了xtrabackup。
[root@www.cndba.cn/dave /]# which innobackupex
/usr/bin/innobackupex
[root@www.cndba.cn/dave /]# ll /usr/bin/innobackupex
lrwxrwxrwx 1 root root 10 Feb 8 21:29 /usr/bin/innobackupex -> xtrabackup
[root@www.cndba.cn/dave /]#
3 使用xtrabackup备份数据库
3.1 全量备份
下例语句表示备份当前数据库服务器上的所有数据库，对所有数据库进行全量备份，将备份的文件存放在/tmp/backup目录下。
[root@www.cndba.cn/dave tmp]# innobackupex --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password='' /tmp/backup
180208 22:30:11 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!".
180208 22:30:11 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: NO).
180208 22:30:11 version_check Connected to MySQL server
180208 22:30:11 version_check Executing a version check against the server...
180208 22:30:11 version_check Done.
180208 22:30:11 Connecting to MySQL server host: 127.0.0.1, user: root, password: not set, port: 3306, socket: /tmp/mysql.sock
Using server version 10.2.12-MariaDB-log
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
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: Number of pools: 1
180208 22:30:11 >> log scanned up to (3629317)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 12 for mysql/innodb_table_stats, old maximum was 0
180208 22:30:11 [01] Copying ./ibdata1 to /tmp/backup/2018-02-08_22-30-11/ibdata1
180208 22:30:11 [01] ...done
180208 22:30:11 [01] Copying ./mysql/innodb_table_stats.ibd to /tmp/backup/2018-02-08_22-30-11/mysql/innodb_table_stats.ibd
180208 22:30:11 [01] ...done
180208 22:30:11 [01] Copying ./mysql/gtid_slave_pos.ibd to /tmp/backup/2018-02-08_22-30-11/mysql/gtid_slave_pos.ibd
180208 22:30:11 [01] ...done
180208 22:30:11 [01] Copying ./mysql/innodb_index_stats.ibd to /tmp/backup/2018-02-08_22-30-11/mysql/innodb_index_stats.ibd
180208 22:30:11 [01] ...done
180208 22:30:11 [01] Copying ./world/countrylanguage.ibd to /tmp/backup/2018-02-08_22-30-11/world/countrylanguage.ibd
180208 22:30:11 [01] ...done
180208 22:30:11 [01] Copying ./world/city.ibd to /tmp/backup/2018-02-08_22-30-11/world/city.ibd
180208 22:30:11 [01] ...done
180208 22:30:12 [01] Copying ./world/country.ibd to /tmp/backup/2018-02-08_22-30-11/world/country.ibd
180208 22:30:12 [01] ...done
180208 22:30:12 [01] Copying ./cndba/oracle.ibd to /tmp/backup/2018-02-08_22-30-11/cndba/oracle.ibd
180208 22:30:12 [01] ...done
180208 22:30:12 [01] Copying ./cndba/dave.ibd to /tmp/backup/2018-02-08_22-30-11/cndba/dave.ibd
180208 22:30:12 [01] ...done
180208 22:30:12 >> log scanned up to (3629317)
180208 22:30:12 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180208 22:30:12 Executing FLUSH TABLES WITH READ LOCK...
180208 22:30:12 Starting to backup non-InnoDB tables and files
180208 22:30:12 [01] Copying ./performance_schema/db.opt to /tmp/backup/2018-02-08_22-30-11/performance_schema/db.opt
180208 22:30:12 [01] ...done
180208 22:30:12 [01] Copying ./mysql/innodb_table_stats.frm to /tmp/backup/2018-02-08_22-30-11/mysql/innodb_table_stats.frm
180208 22:30:12 [01] ...done
……
180208 22:30:13 Finished backing up non-InnoDB tables and files
180208 22:30:13 [00] Writing /tmp/backup/2018-02-08_22-30-11/xtrabackup_binlog_info
180208 22:30:13 [00] ...done
180208 22:30:13 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '3629308'
xtrabackup: Stopping log copying thread.
.180208 22:30:13 >> log scanned up to (3629317)
180208 22:30:14 Executing UNLOCK TABLES
180208 22:30:14 All tables unlocked
180208 22:30:14 [00] Copying ib_buffer_pool to /tmp/backup/2018-02-08_22-30-11/ib_buffer_pool
180208 22:30:14 [00] ...done
180208 22:30:14 Backup created in directory '/tmp/backup/2018-02-08_22-30-11/'
MySQL binlog position: filename 'mysql-bin.000008', position '4507', GTID of the last change '0-1-174'
180208 22:30:14 [00] Writing /tmp/backup/2018-02-08_22-30-11/backup-my.cnf
180208 22:30:14 [00] ...done
180208 22:30:14 [00] Writing /tmp/backup/2018-02-08_22-30-11/xtrabackup_info
180208 22:30:14 [00] ...done
xtrabackup: Transaction log of lsn (3629308) to (3629317) was copied.
180208 22:30:14 completed OK!
[root@www.cndba.cn/dave tmp]#
查看备份的文件：
[root@www.cndba.cn/dave 2018-02-08_22-30-11]# pwd
/tmp/backup/2018-02-08_22-30-11
[root@www.cndba.cn/dave 2018-02-08_22-30-11]# ll -lh
total 13M
-rw-r----- 1 root root 424 Feb 8 22:30 backup-my.cnf
drwxr-x--- 2 root root 4.0K Feb 8 22:30 cndba
-rw-r----- 1 root root 3.1K Feb 8 22:30 ib_buffer_pool
-rw-r----- 1 root root 12M Feb 8 22:30 ibdata1
drwxr-x--- 2 root root 4.0K Feb 8 22:30 mysql
drwxr-x--- 2 root root 4.0K Feb 8 22:30 performance_schema
drwxr-x--- 2 root root 4.0K Feb 8 22:30 world
-rw-r----- 1 root root 30 Feb 8 22:30 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Feb 8 22:30 xtrabackup_checkpoints
-rw-r----- 1 root root 550 Feb 8 22:30 xtrabackup_info
-rw-r----- 1 root root 2.5K Feb 8 22:30 xtrabackup_logfile
[root@www.cndba.cn/dave 2018-02-08_22-30-11]#
[root@www.cndba.cn/dave 2018-02-08_22-30-11]# cd cndba
[root@www.cndba.cn/dave cndba]# ls
dave.frm dave.ibd db.opt oracle.frm oracle.ibd
[root@www.cndba.cn/dave cndba]#
这些目录与数据库的名称相同，都是各个数据库的数据文件备份目录。
还有一个innodb的共享表空间文件，ibdata1，注意，如果想要使用xtrabackup备份众多数据库中的某一个，那么必须保证在创建这个数据库时，已经开启了innodb_file_per_table参数，否则将无法单独备份数据库服务器中的某一个数据库。
除了这些数据文件，xtrabackup还生成了一些文件：
- backup-my.cnf：此文件中包含了my.cnf中的一些设置信息，但是，并不是my.cnf中的所有信息都会包含在此文件中，此文件中只包含了备份时需要的信息。
- xtrabackup_binlog_info：此文件中记录了备份开始时二进制日志文件的”位置(position)”
- xtrabackup_checkpoints：此文件中记录此次备份属于那种类型的备份，是全量还是增量，备份时起始的LSN号码，结束的LSN号码等信息。
- xtrabackup_info：本次备份的概要信息，此文件中的信息还是比较全面的。
- xtrabackup_logfile：记录了备份过程中的日志，在对数据进行prepare时需要通过日志将数据还原成一致的可用的数据。
[root@www.cndba.cn/dave 2018-02-08_22-30-11]# cat backup-my.cnf
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_checksum_algorithm=crc32
innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=0
server_id=1
redo_log_version=1
[root@www.cndba.cn/dave 2018-02-08_22-30-11]# cat xtrabackup_binlog_info
mysql-bin.000008 4507 0-1-174
[root@www.cndba.cn/dave 2018-02-08_22-30-11]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 3629308
last_lsn = 3629317
compact = 0
recover_binlog_info = 0
[root@www.cndba.cn/dave 2018-02-08_22-30-11]# cat xtrabackup_info
uuid = 93800634-0cdc-11e8-99ef-080027cc9e1b
name =
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --passw /tmp/backup
tool_version = 2.4.9
ibbackup_version = 2.4.9
server_version = 10.2.12-MariaDB-log
start_time = 2018-02-08 22:30:11
end_time = 2018-02-08 22:30:14
lock_time = 0
binlog_pos = filename 'mysql-bin.000008', position '4507', GTID of the last change '0-1-174'
innodb_from_lsn = 0
innodb_to_lsn = 3629308
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
[root@www.cndba.cn/dave 2018-02-08_22-30-11]
3.2 增量备份
在上一节我们进行了一个全备，全量备份是差量与增量的基础，因为差量备份只能针对于上一次全量备份，增量备份则可以针对上一次任何一种备份，上一次备份可以是全量、差量、或者增量。
mysql> use cndba
Database changed
mysql> create table zhixin(id int,name varchar(100));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into zhixin values(1,'www.cndba.cn');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
[root@www.cndba.cn/dave /]# innobackupex --host=localhost --user=root --password='' --incremental --incremental-basedir=/tmp/backup/2018-02-08_22-30-11 /tmp/backup
180208 23:15:57 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!".
180208 23:15:57 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: NO).
180208 23:15:57 version_check Connected to MySQL server
180208 23:15:57 version_check Executing a version check against the server...
180208 23:15:57 version_check Done.
180208 23:15:57 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 10.2.12-MariaDB-log
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
incremental backup from 3629308 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
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: Number of pools: 1
180208 23:15:57 >> log scanned up to (3635975)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 12 for mysql/innodb_table_stats, old maximum was 0
xtrabackup: using the full scan for incremental backup
180208 23:15:57 [01] Copying ./ibdata1 to /tmp/backup/2018-02-08_23-15-57/ibdata1.delta
180208 23:15:57 [01] ...done
180208 23:15:57 [01] Copying ./mysql/innodb_table_stats.ibd to /tmp/backup/2018-02-08_23-15-57/mysql/innodb_table_stats.ibd.delta
180208 23:15:57 [01] ...done
180208 23:15:57 [01] Copying ./mysql/gtid_slave_pos.ibd to /tmp/backup/2018-02-08_23-15-57/mysql/gtid_slave_pos.ibd.delta
180208 23:15:57 [01] ...done
180208 23:15:57 [01] Copying ./mysql/innodb_index_stats.ibd to /tmp/backup/2018-02-08_23-15-57/mysql/innodb_index_stats.ibd.delta
180208 23:15:57 [01] ...done
180208 23:15:57 [01] Copying ./world/countrylanguage.ibd to /tmp/backup/2018-02-08_23-15-57/world/countrylanguage.ibd.delta
180208 23:15:57 [01] ...done
180208 23:15:57 [01] Copying ./world/city.ibd to /tmp/backup/2018-02-08_23-15-57/world/city.ibd.delta
180208 23:15:57 [01] ...done
180208 23:15:57 [01] Copying ./world/country.ibd to /tmp/backup/2018-02-08_23-15-57/world/country.ibd.delta
180208 23:15:57 [01] ...done
180208 23:15:57 [01] Copying ./cndba/zhixin.ibd to /tmp/backup/2018-02-08_23-15-57/cndba/zhixin.ibd.delta
180208 23:15:57 [01] ...done
180208 23:15:57 [01] Copying ./cndba/oracle.ibd to /tmp/backup/2018-02-08_23-15-57/cndba/oracle.ibd.delta
180208 23:15:57 [01] ...done
180208 23:15:57 [01] Copying ./cndba/dave.ibd to /tmp/backup/2018-02-08_23-15-57/cndba/dave.ibd.delta
180208 23:15:57 [01] ...done
180208 23:15:58 >> log scanned up to (3635975)
180208 23:15:58 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
180208 23:15:58 Executing FLUSH TABLES WITH READ LOCK...
180208 23:15:58 Starting to backup non-InnoDB tables and files
180208 23:15:58 [01] Copying ./performance_schema/db.opt to /tmp/backup/2018-02-08_23-15-57/performance_schema/db.opt
180208 23:15:58 [01] ...done
180208 23:15:58 [01] Copying ./mysql/innodb_table_stats.frm to /tmp/backup/2018-02-08_23-15-57/mysql/innodb_table_stats.frm
180208 23:15:58 [01] ...done
......
180208 23:16:00 Finished backing up non-InnoDB tables and files
180208 23:16:00 [00] Writing /tmp/backup/2018-02-08_23-15-57/xtrabackup_binlog_info
180208 23:16:00 [00] ...done
180208 23:16:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '3635966'
xtrabackup: Stopping log copying thread.
.180208 23:16:00 >> log scanned up to (3635975)
180208 23:16:00 Executing UNLOCK TABLES
180208 23:16:00 All tables unlocked
180208 23:16:00 [00] Copying ib_buffer_pool to /tmp/backup/2018-02-08_23-15-57/ib_buffer_pool
180208 23:16:00 [00] ...done
180208 23:16:00 Backup created in directory '/tmp/backup/2018-02-08_23-15-57/'
MySQL binlog position: filename 'mysql-bin.000009', position '724', GTID of the last change '0-1-187'
180208 23:16:00 [00] Writing /tmp/backup/2018-02-08_23-15-57/backup-my.cnf
180208 23:16:00 [00] ...done
180208 23:16:00 [00] Writing /tmp/backup/2018-02-08_23-15-57/xtrabackup_info
180208 23:16:00 [00] ...done
xtrabackup: Transaction log of lsn (3635966) to (3635975) was copied.
180208 23:16:00 completed OK!
[root@www.cndba.cn/dave /]#
上述命令中的—incremental选项表示本次备份是一个增量备份（其实这次备份既可以算是增量，也可以算作差量），本次增量备份将备份至/tmp/backup目录下，本次增量备份是针对于/tmp/backup/2018-02-08_22-30-11的增量。如果想要实现差量备份，只要将上述命令中的—incremental-basedir选项的值每次都设定为全量备份的路径即可，其实这次备份也可以理解为一次差量备份。
[root@www.cndba.cn/dave backup]# cd 2018-02-08_23-15-57/
[root@www.cndba.cn/dave 2018-02-08_23-15-57]# ll -lh
total 636K
-rw-r----- 1 root root 424 Feb 8 23:16 backup-my.cnf
drwxr-x--- 2 root root 4.0K Feb 8 23:16 cndba
-rw-r----- 1 root root 3.1K Feb 8 23:16 ib_buffer_pool
-rw-r----- 1 root root 592K Feb 8 23:15 ibdata1.delta
-rw-r----- 1 root root 44 Feb 8 23:15 ibdata1.meta
drwxr-x--- 2 root root 4.0K Feb 8 23:15 mysql
drwxr-x--- 2 root root 4.0K Feb 8 23:15 performance_schema
drwxr-x--- 2 root root 4.0K Feb 8 23:16 world
-rw-r----- 1 root root 29 Feb 8 23:16 xtrabackup_binlog_info
-rw-r----- 1 root root 117 Feb 8 23:16 xtrabackup_checkpoints
-rw-r----- 1 root root 602 Feb 8 23:16 xtrabackup_info
-rw-r----- 1 root root 2.5K Feb 8 23:16 xtrabackup_logfile
[root@www.cndba.cn/dave 2018-02-08_23-15-57]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3629308
to_lsn = 3635966
last_lsn = 3635975
compact = 0
recover_binlog_info = 0
[root@www.cndba.cn/dave 2018-02-08_23-15-57]#
在进行一次增量备份操作：
mysql> insert into zhixin values(2,'http://www.cndba.cn/dave');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
注意这里选取的增量目录是上次的增量备份：
[root@www.cndba.cn/dave /]# innobackupex --host=localhost --user=root --password='' --incremental --incremental-basedir=/tmp/backup/2018-02-08_23-15-57 /tmp/backup
[root@www.cndba.cn/dave backup]# ll
total 12
drwxr-x--- 6 root root 4096 Feb 8 22:30 2018-02-08_22-30-11
drwxr-x--- 6 root root 4096 Feb 8 23:16 2018-02-08_23-15-57
drwxr-x--- 6 root root 4096 Feb 8 23:25 2018-02-08_23-25-13
[root@www.cndba.cn/dave backup]#
至此，我们得到了3个备份：1个全备，2个增备。
4 使用xtrabackup恢复数据
4.1 全量恢复
恢复涉及2个动作：
- 使用-apply-log 使备份达到一致的状态。
- 使用-copy-back 恢复全备，在恢复全备时，DB是需要停掉的。
以我们的环境为例，步骤如下：
停库：
service mysql stop
通过准备阶段使备份达到一致的状态
innobackupex --apply-log /tmp/backup/2018-02-08_22-30-11/
恢复全量备份
innobackupex --copy-back /tmp/backup/2018-02-08_22-30-11/
目录授权mysql权限
chown -R mysql:mysql /data/mysql
启动数据库
service mysql start
4.2 增量数据恢复
增量数据恢复选项是在全备恢复的基础上加了一个—redo-only选项，这个选项表示在进行准备（应用日志）工作时，只会重做已提交但是未应用的事务，而不会回滚未提交的事务。主要用来合并增量备份，之前全备的恢复不需要合并，所以没有添加。同样，在合并最后一个增量时，也不需要加该选项。
现在利用我们之前的3次备份进行恢复：
首先将全备的日志apply:
innobackupex --apply-log --redo-only /tmp/backup/2018-02-08_22-30-11
再将第一次增量备份的备份合并到之前准备好的完全备份中:
innobackupex --apply-log --redo-only --incremental-dir=/tmp/backup/2018-02-08_23-15-57/ /tmp/backup/2018-02-08_22-30-11
最后将第二次增量与全备整合，因为是最后一次，所以这里不需要添加—redo-only选项：
innobackupex --apply-log --incremental-dir=/tmp/backup/2018-02-08_23-25-13/ /tmp/backup/2018-02-08_22-30-11
在合并的每个阶段都可以查看全量备份中的 xtrabackup_checkpoints
[root@www.cndba.cn/dave 2018-02-08_22-30-11]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 3637099
last_lsn = 3637108
compact = 0
recover_binlog_info = 0
[root@www.cndba.cn/dave 2018-02-08_22-30-11]#
到目前为止，已经把所有增量备份都合并到了最初的第一份全量备份中，现在，只要通过这一份全量备份即可恢复数据库。
[root@www.cndba.cn/dave data]# service mysql stop
Shutting down MySQL.. [ OK ]
[root@www.cndba.cn/dave data]# pwd
/mysql/data
[root@www.cndba.cn/dave data]# ls
aria_log.00000001 Dave.err ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 mysql-bin.index world
aria_log_control Dave.log ibdata1 multi-master.info mysql-bin.000002 mysql-bin.000005 mysql-bin.000008 mysql-bin.state
cndba Dave-slow.log ib_logfile0 mysql mysql-bin.000003 mysql-bin.000006 mysql-bin.000009 performance_schema
[root@www.cndba.cn/dave data]# rm -rf *
[root@www.cndba.cn/dave data]#
完成上述工作后，将准备好的数据还原回对应的数据目录中，没错，只将最后合并完成的那个全量备份还原即可，因为之前的准备工作中，我们已经将所有增量数据都合并到这个全量中了。
[root@www.cndba.cn/dave data]# innobackupex --datadir=/mysql/data --copy-back /tmp/backup/2018-02-08_22-30-11
180209 00:41:37 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!".
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
180209 00:41:37 [01] Copying ib_logfile0 to /mysql/data/ib_logfile0
180209 00:41:37 [01] ...done
180209 00:41:37 [01] Copying ib_logfile1 to /mysql/data/ib_logfile1
180209 00:41:37 [01] ...done
180209 00:41:37 [01] Copying ibdata1 to /mysql/data/ibdata1
180209 00:41:37 [01] ...done
180209 00:41:37 [01] Copying ./performance_schema/db.opt to /mysql/data/performance_schema/db.opt
180209 00:41:37 [01] ...done
...
180209 00:41:39 [01] Copying ./cndba/dave.frm to /mysql/data/cndba/dave.frm
180209 00:41:39 [01] ...done
180209 00:41:39 [01] Copying ./xtrabackup_info to /mysql/data/xtrabackup_info
180209 00:41:39 [01] ...done
180209 00:41:39 completed OK!
[root@www.cndba.cn/dave data]# ll
total 122908
drwxr-x--- 2 root root 4096 Feb 9 00:41 cndba
-rw-r----- 1 root root 3146 Feb 9 00:41 ib_buffer_pool
-rw-r----- 1 root root 12582912 Feb 9 00:41 ibdata1
-rw-r----- 1 root root 50331648 Feb 9 00:41 ib_logfile0
-rw-r----- 1 root root 50331648 Feb 9 00:41 ib_logfile1
-rw-r----- 1 root root 12582912 Feb 9 00:41 ibtmp1
drwxr-x--- 2 root root 4096 Feb 9 00:41 mysql
drwxr-x--- 2 root root 4096 Feb 9 00:41 performance_schema
drwxr-x--- 2 root root 4096 Feb 9 00:41 world
-rw-r----- 1 root root 23 Feb 9 00:41 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 602 Feb 9 00:41 xtrabackup_info
[root@www.cndba.cn/dave data]#
修改文件的所有者：
[root@www.cndba.cn/dave data]# chown mysql:mysql * -R
[root@www.cndba.cn/dave data]# ll
total 122908
drwxr-x--- 2 mysql mysql 4096 Feb 9 00:41 cndba
-rw-r----- 1 mysql mysql 3146 Feb 9 00:41 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 9 00:41 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 9 00:41 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 9 00:41 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 9 00:41 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Feb 9 00:41 mysql
drwxr-x--- 2 mysql mysql 4096 Feb 9 00:41 performance_schema
drwxr-x--- 2 mysql mysql 4096 Feb 9 00:41 world
-rw-r----- 1 mysql mysql 23 Feb 9 00:41 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 602 Feb 9 00:41 xtrabackup_info
[root@www.cndba.cn/dave data]#
最后启动数据库，恢复完成：
[root@www.cndba.cn/dave data]# service mysql start
Starting MySQL.180209 00:43:23 mysqld_safe Logging to '/mysql/log/www.cndba.cn-error.log'.
180209 00:43:23 mysqld_safe Starting mysqld daemon with databases from /mysql/data
[ OK ]
[root@www.cndba.cn/dave data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 10
Server version: 5.5.5-10.2.12-MariaDB-log MariaDB Server
Copyright (c) 2000, 2013, 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 |
+--------------------+
| cndba |
| information_schema |
| mysql |
| performance_schema |
| world |
+--------------------+
5 rows in set (0.00 sec)
注：
在前面的过程中，我们通过增量备份进行了恢复，实际上，生产环境，可能还需要在此恢复的基础上进一步利用binlog 来推进数据。
版权声明：本文为博主原创文章，未经博主允许不得转载。
