文档课题：mysql innobackupex备份恢复相关测试.
测试环境：mysql 5.7.21 从节点
系统：rhel 7.3
1、理论知识
大数据量的备份与还原始终是难点，当数据量超10G后用mysqldump的时效会很低.本文介绍xtrabackup工具，相比mysqldump要快很多.
1.1、Xtrabackup介绍
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup很好的替代品.
Xtrabackup有两个主要工具：xtrabackup、innobackupex
A、xtrabackup只能备份InnoDB和XtraDB两种数据表，不能备份MyISAM数据表;
B、innobackupex参考InnoDB Hotbackup的innoback脚本修改而来，innobackupex是perl脚本封装.主要为了可以同时备份InnoDB和MyISAM引擎的表，但在处理MyISAM时需要加一个读锁，且加入可选项，如slave-info可记录备份恢复后作为slave需要的一些信息，根据这些信息可以很方便的利用备份来重做slave.
1.2、Xtrabackup作用
A、在线(热)备份整个库的InnoDB、XtraDB表
B、在xtrabackup上一次整库备份基础上做增量备份(innodb only)
C、以流的形式产生备份，可直接保存到远程机器上(本机硬盘空间不足时很有用)
说明：MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份恢复.
Xtrabackup工具支持对InnoDB存储引擎的增量备份，工作原理如下：
A、首先完成一个完全备份，并记录下此时检查点的LSN(Log Sequence Number);
B、在进程增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是则备份该页，同时记录当前检查点的LSN;
C、在logfile中找到并记录最后一个checkpoint(“last checkpoint LSN”)，然后开始从LSN的位置开始拷贝InnoDB的logfile到xtrabackup_logfile，接着开始拷贝全部的数据文件.ibd，然后在拷贝全部数据文件，结束之后才停止拷贝logfile.
说明：因logfile记录全部的数据修改情况，所以即使在备份过程中数据文件被修改过，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致.innobackupex支持innodb、myisam，因此本文重点介绍innobackupex.
2、安装xtrabackup
安装包下载地址：https://www.percona.com/downloads
2.1、xtrabackup安装步骤
sftp> lcd F:\package
sftp> put percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
sftp> put libev-4.15-7.el7.x86_64.rpm
[root@mysql-leo-perslave ~]# mount /dev/sr0 /mnt
[root@mysql-leo-perslave ~]# cat <<EOF>>/etc/yum.repos.d/local.repo
[local]
name=local
baseurl=file:///mnt
gpgcheck=0
enabled=1
EOF
[root@mysql-leo-perslave yum.repos.d]# yum makecache
[root@mysql-leo-perslave ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
[root@mysql-leo-perslave ~]# yum -y install perl-Digest-MD5 cmake gcc gcc-c++ automake autoconf bzr
[root@mysql-leo-perslave ~]# rpm -ivh libev-4.15-7.el7.x86_64.rpm
warning: libev-4.15-7.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:libev-4.15-7.el7 ################################# [100%]
[root@mysql-leo-perslave ~]# rpm -ivh percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
warning: percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:percona-xtrabackup-24-2.4.12-1.el################################# [100%]
2.2、innobackupex参数
--compress：压缩innodb数据文件备份
--compress-threads：并行压缩worker线程数量
--compress-chunk-size：每个压缩线程worker buffer大小，单位字节，默认64K
--encrypt：通过ENCRYPTION_ALGORITHM算法加密innodb数据文件备份，目前支持的算法有ASE128、AES192、AES256
--encrypt-threads：并行加密worker线程数量
--encrypt-chunk-size：每个加密线程worker buffer大小，单位字节，默认64K
--encrypt-key：使用合适长度加密key，因为会记录到命令行，所以不推荐使用
--encryption-key-file：文件必须是一个简单二进制或文本文件，加密key可通过openssl rand -base64 24命令行命令生成
--include：使用正则表达式匹配表的名字[db.tb]，要求为其指定匹配要备份表的完整名称，即databasename.tablename
--user：备份账号
--password：备份密码
--port：备份数据库的端口.
--host：备份数据库的地址.
--databases：接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开，如"xtra_test dba_test"，在指定某数据库时，也可以只指定其中的某张表，如"mydatabase.mytable".注意该选项对innodb引擎表无效，还是会备份所有innodb表.此外此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象
--tables-file：指定含有表列表的文件，格式为database.table，该选项直接传给--tables-file
--socket：mysql.sock所在位置，以便备份进程登录mysql
--no-timestamp：表示不要创建一个时间戳目录来存储备份，指定到自己想要的备份文件夹
--ibbackup：指定使用具体xtrabackup二进制程序.IBBACKUP-BINARY是运行percona xtrabackup命令.这个选项适用于xtrbackup二进制不在你是搜索和工作目录，如果指定了该选项，innoabackupex自动决定用的二进制程序.
--slave-info：对slave备份时使用，打印master名字和binlog pos，同样将这些信息以change master的命令写入xtrabackup_slave_info文件.可以通过基于此备份启动一个从库.
--safe-slave-backup：为保证一致性复制状态，这个选项停止SQL线程并且等到show status中的slave_open_temp_tables为0的时候开始备份，如果没有打开临时表，bakcup会立刻开始，否则SQL线程启动或者关闭知道没有打开的临时表.如果slave_open_temp_tables在--safe-slave-backup-timeount（默认300秒）秒之后不为0，从库sql线程会在备份完成的时候重启.
--rsync：通过rsync工具优化本地传输，当指定该选项时innobackupex使用rsync拷贝非Innodb文件而替换cp，当有很多DB和表时会快很多，不能与—stream同时使用.
--kill-long-queries-timeout：从开始执行FLUSH TABLES WITH READ LOCK到kill掉阻塞它的这些查询之间等待的秒数.默认值为0，不会kill任何查询，使用该选项xtrabackup需要有Process和super权限.
--kill-long-query-type：表示kill的类型，默认是all，可选select.
--ftwrl-wait-threshold：表示检测到长查询，单位秒，表示长查询阈值.
--ftwrl-wait-query-type：表示获得全局锁之前允许哪种查询完成，默认是ALL，可选update.
--galera-info：表示生成包含创建备份时本地节点状态的xtrabackup_galera_info文件，该选项只适用于备份PXC.
--stream：表示流式备份格式，backup完成之后以指定格式到STDOUT，目前只支持tar和xbstream.
--defaults-file：指定从哪个文件读取MySQL配置，必须放在命令行的第一个选项位置.
--defaults-extra-file：指定在标准defaults-file之前从哪个额外的文件读取MySQL配置，必须在命令行的第一个选项位置.一般用于存备份用户的用户名和密码的配置文件.
--defaults-group：表示从配置文件读取的组，innobakcupex多个实例部署时使用.
--no-lock：表示关闭FTWRL的表锁，只有在所有表都是Innodb表并且不关心backup的binlog pos点，如果有任何DDL语句正在执行或者非InnoDB正在更新时(包括mysql库下的表），都不应该使用该选项，后果是导致备份数据不一致，如果考虑备份因为获得锁失败，可以考虑--safe-slave-backup立刻停止复制线程.
--tmpdir：表示指定--stream时，指定临时文件存在位置，在streaming和拷贝到远程server之前，事务日志首先存在临时文件里.在使用参数stream=tar备份时xtrabackup_logfile可能会临时放在/tmp目录下，如果备份时并发写入较大，xtrabackup_logfile可能会很大(5G+)，很可能会撑满/tmp目录，此时可通过参数--tmpdir指定目录解决该问题.
--history：表示percona server 的备份历史记录在percona_schema.xtrabackup_history表.
--incremental：表示创建一个增量备份，需要指定--incremental-basedir.
--incremental-basedir：表示接受一个字符串参数指定含有full backup的目录为增量备份的base目录，与--incremental同时使用.
--incremental-dir：表示增量备份的目录.
--incremental-force-scan：表示创建一份增量备份时强制扫描所有增量备份中的数据页.
--incremental-lsn：表示指定增量备份的LSN，与--incremental选项一起使用.
--incremental-history-name：表示存储在PERCONA_SCHEMA.xtrabackup_history基于增量备份的历史记录名字.Percona Xtrabackup搜索历史表查找最近(innodb_to_lsn)成功备份并且将to_lsn值作为增量备份启动初始lsn.与innobackupex--incremental-history-uuid互斥，若没有检测到有效的lsn，xtrabackup会返回error.
--incremental-history-uuid：表示存储在percona_schema.xtrabackup_history基于增量备份的特定历史记录的UUID.
--close-files：表示关闭不再访问的文件句柄，当xtrabackup打开表空间通常并不关闭文件句柄，目的是正确的处理DDL操作.若表空间数量巨大，这是一种可以关闭不再访问的文件句柄的方法.使用该选项有风险，会有产生不一致备份的可能.
--compact：表示创建一份没有辅助索引的紧凑备份.
--throttle：该选项表示每秒IO操作的次数，只作用于bakcup阶段有效.apply-log和--copy-back不生效不要一起用.
2.3、注意事项
使用innobackupex备份，注意配置文件my.cnf中[mysqld]下需设置datadir.
[mysql@mysql-leo-perslave etc]$ cat my.cnf
[mysqld]
datadir = /mysql/data
若没有设置datadir，在数据恢复时可能出现如下告警：
xtrabackup: Error: Please set parameter 'datadir'
innobackupex: fatal error: no 'mysqld' group in MySQL options
innobackupex: fatal error: OR no 'datadir' option in group 'mysqld' in MySQL options
3、innobackupex全库备份与还原
3.1、innobackupex全库备份
--全库备份
[root@mysql-leo-perslave ~]# /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --socket=/mysql/data/mysql.sock --user=root --password=mysql --port=3306 --slave-info /mysql/bak/file/
备份日志：
……
230730 22:18:35 Backup created in directory '/mysql/bak/file/2023-07-30_22-18-23/'
MySQL binlog position: filename 'mysql-bin.000007', position '234', GTID of the last change '9621e6df-2ab4-11ee-8438-0050563d0a8c:1-1000010,
e29135f1-2bcb-11ee-bc4b-00505636addc:1-32'
MySQL slave binlog position: master host '192.168.133.116', purge list '9621e6df-2ab4-11ee-8438-0050563d0a8c:1-1000010, e29135f1-2bcb-11ee-bc4b-00505636addc:1-32', channel name: ''
230730 22:18:35 [00] Writing /mysql/bak/file/2023-07-30_22-18-23/backup-my.cnf
230730 22:18:35 [00] ...done
230730 22:18:35 [00] Writing /mysql/bak/file/2023-07-30_22-18-23/xtrabackup_info
230730 22:18:35 [00] ...done
xtrabackup: Transaction log of lsn (7589210575) to (7589210641) was copied.
230730 22:18:36 completed OK!
--检查备份集
[root@mysql-leo-perslave file]# pwd
/mysql/bak/file
[root@mysql-leo-perslave file]# ll
total 0
drwxr-x---. 6 root root 267 Jul 30 22:18 2023-07-30_22-18-23
[root@mysql-leo-perslave file]# du -sch 2023-07-30_22-18-23
3.9G 2023-07-30_22-18-23
3.9G total
3.2、备份单库
[mysql@mysql-leo-perslave ~]$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --socket=/mysql/data/mysql.sock --user=root --password=mysql --database=booksDB --port=3306 --slave-info /mysql/bak/file/
--检查备份集
[root@mysql-leo-perslave file]# pwd
/mysql/bak/file
[root@mysql-leo-perslave file]# ll
total 0
drwxr-x---. 6 root root 267 Jul 30 22:18 2023-07-30_22-18-23
drwxr-x---. 6 mysql mysql 267 Jul 30 22:24 2023-07-30_22-24-34
[root@mysql-leo-perslave file]# du -sch 2023-07-30_22-24-34
3.9G 2023-07-30_22-24-34
3.9G total
3.3、备份并压缩
[mysql@mysql-leo-perslave ~]$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --socket=/mysql/data/mysql.sock --user=root --password=mysql --port=3306 --slave-info --stream=tar --tmpdir=/mysql/bak/full /mysql/bak/file/ 2>/mysql/bak/log_bak/backup.log |gzip ->/mysql/bak/file/192.168.133.117_20230730_3306.tar.gz
-备份日志
[mysql@mysql-leo-perslave log_bak]$ tail -5000f backup.log
……
xtrabackup: Transaction log of lsn (7589210657) to (7589210673) was copied.
230730 22:37:31 completed OK!
--备份集检查
[root@mysql-leo-perslave file]# du -sch *
777M 192.168.133.117_20230730_3306.tar.gz
3.9G 2023-07-30_22-18-23
3.9G 2023-07-30_22-24-34
8.5G total
4、全库还原
4.1、删除文件
mysql> shutdown;
[mysql@mysql-leo-perslave ~]$ mv /mysql/data /mysql/data_bak
[mysql@mysql-leo-perslave ~]$ mkdir -p /mysql/data
4.2、apply-log恢复
--apply-log选项是在一个备份上启动mysql服务.
[root@mysql-leo-perslave 2023-07-30_22-18-23]# innobackupex --defaults-file=/mysql/data/etc/my.cnf --user=root --apply-log /mysql/bak/file/2023-07-30_22-18-23
xtrabackup: recognized server arguments: --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=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=2 --redo-log-version=1
xtrabackup: recognized client arguments: --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=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=2 --redo-log-version=1
230730 22:49:27 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!".
innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: cd to /mysql/bak/file/2023-07-30_22-18-23/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(7589210575)
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 = 1
xtrabackup: innodb_log_file_size = 8388608
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
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: Log scan progressed past the checkpoint lsn 7589210575
InnoDB: Doing recovery: scanned up to log sequence number 7589210641 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 234, file name mysql-bin.000007
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: 5.7.19 started; log sequence number 7589210641
InnoDB: xtrabackup: Last MySQL binlog file position 234, file name mysql-bin.000007
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 7589212694
InnoDB: Number of pools: 1
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 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: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=7589212694
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 7589213196
InnoDB: Doing recovery: scanned up to log sequence number 7589213205 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 234, file name mysql-bin.000007
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: 5.7.19 started; log sequence number 7589213205
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 7589213224
230730 22:49:30 completed OK!
注意：apply-log恢复未测试成功.
4.3、copy-bak恢复
--copy-bak选项是从备份目录拷贝数据、索引、日志到my.cnf文件规定的初始位置.
[root@mysql-leo-perslave file]# /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --user=root --copy-back /mysql/bak/file/2023-07-30_22-18-23
[root@mysql-leo-perslave file]# chown -R mysql:mysql /mysql/data
[mysql@mysql-leo-perslave ~]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
说明：数据库启动后重新设置主从关系以及开启半同步.
5、增量备份与还原
5.1、建测试数据库和表
mysql> create database fruitsDB;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| booksDB |
| fruitsDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> use fruitsDB;
Database changed
mysql> create table fruits
-> (
-> f_id char(10) not null,
-> s_id int not null,
-> f_name char(255) not null,
-> f_price decimal(8,2) not null,
-> primary key(f_id)
-> ) ENGINE=innodb default charset=utf8 auto_increment=1;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into fruits (f_id,s_id,f_name,f_price)
-> values('a1',101,'apple',5.2),
-> ('b1',101,'blackberry',10.2),
-> ('bs1',102,'orange',11.2),
-> ('bs2',105,'melon',8.2),
-> ('t1',102,'banana',10.3),
-> ('t2',102,'grape',5.3),
-> ('o2',103,'coconut',9.2),
-> ('c0',101,'cherry',3.2),
-> ('a2',103,'apricot',2.2),
-> ('l2',104,'lemon',6.4),
-> ('b2',104,'berry',7.6),
-> ('m1',106,'mango',15.7),
-> ('m2',105,'xbabay',2.6),
-> ('t4',107,'xbababa',3.6),
-> ('m3',105,'xxtt',11.6),
-> ('b5',107,'xxxx',3.6);
Query OK, 16 rows affected (0.03 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)
5.2、全库备份
[mysql@mysql-leo-perslave data]$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --socket=/mysql/data/mysql.sock --user=root --password=mysql --port=3306 --slave-info --tmpdir=/mysql/bak/full /mysql/bak/file/ 2>/mysql/bak/log_bak/backup.log
[mysql@mysql-leo-perslave file]$ ll
total 8
drwxr-x---. 7 mysql mysql 4096 Aug 1 23:14 2023-08-01_23-14-21
--查看xtrabackup_checkpoints文件
[mysql@mysql-leo-perslave file]$ cd 2023-08-01_23-14-21
[mysql@mysql-leo-perslave 2023-08-01_23-14-21]$ cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 7589242403
last_lsn = 7589242412
compact = 0
recover_binlog_info = 0
5.3、第一次增量备份
--新增数据
insert into fruits (f_id,s_id,f_name,f_price)
values('c1',201,'pear',5.6),
('c5',201,'pear',5.6),
('c2',202,'pawpaw',3.6),
('c3',203,'grape',6.6),
('c4',204,'strawberry',3.6);
--第一次增量备份
[mysql@mysql-leo-perslave data]$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --socket=/mysql/data/mysql.sock --user=root --password=mysql --port=3306 --slave-info --tmpdir=/mysql/bak/full --incremental-basedir=/mysql/bak/file/2023-08-01_23-14-21 --incremental /mysql/bak/file/ 2>>/mysql/bak/log_bak/backup.log
说明：
--incremental /mysql/bak/file/ 指定增量备份的位置
--incremental-basedir=/mysql/bak/file/2023-08-01_23-14-21 指定上一次全备或增量备份目录
--查看备份集
[mysql@mysql-leo-perslave file]$ ll
total 8
drwxr-x---. 7 mysql mysql 4096 Aug 1 23:14 2023-08-01_23-14-21
drwxr-x---. 7 mysql mysql 4096 Aug 1 23:29 2023-08-01_23-29-52
[mysql@mysql-leo-perslave file]$ du -sch *
3.9G 2023-08-01_23-14-21
2.9M 2023-08-01_23-29-52
--查看第一次增量备份后的xtrabackup_checkpoints文件
[mysql@mysql-leo-perslave 2023-08-01_23-29-52]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7589242403
to_lsn = 7589245996
last_lsn = 7589246012
compact = 0
recover_binlog_info = 0
--第一次增量备份日志
......
MySQL binlog position: filename 'mysql-bin.000005', position '2676', GTID of the last change '9621e6df-2ab4-11ee-8438-0050563d0a8c:1-1000023,
e29135f1-2bcb-11ee-bc4b-00505636addc:1-32'
MySQL slave binlog position: master host '192.168.133.116', purge list '9621e6df-2ab4-11ee-8438-0050563d0a8c:1-1000023, e29135f1-2bcb-11ee-bc4b-00505636addc:1-32', channel name: ''
230801 23:29:59 [00] Writing /mysql/bak/file/2023-08-01_23-29-52/backup-my.cnf
230801 23:29:59 [00] ...done
230801 23:29:59 [00] Writing /mysql/bak/file/2023-08-01_23-29-52/xtrabackup_info
230801 23:29:59 [00] ...done
xtrabackup: Transaction log of lsn (7589245996) to (7589246012) was copied.
230801 23:29:59 completed OK!
5.4、第二次增量备份
--新增数据
insert into fruits (f_id,s_id,f_name,f_price)
values('c6',206,'kiwifruit',3.6),
('c7',207,'muskmelon',1.6);
--第二次增量备份
[mysql@mysql-leo-perslave data]$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --socket=/mysql/data/mysql.sock --user=root --password=mysql --port=3306 --slave-info --tmpdir=/mysql/bak/full --incremental-basedir=/mysql/bak/file/2023-08-01_23-29-52 --incremental /mysql/bak/file/ 2>>/mysql/bak/log_bak/backup.log
--检查备份集
[mysql@mysql-leo-perslave file]$ ls -ltr
total 12
drwxr-x---. 7 mysql mysql 4096 Aug 1 23:14 2023-08-01_23-14-21
drwxr-x---. 7 mysql mysql 4096 Aug 1 23:29 2023-08-01_23-29-52
drwxr-x---. 7 mysql mysql 4096 Aug 1 23:46 2023-08-01_23-46-10
[mysql@mysql-leo-perslave file]$ du -sch *
3.9G 2023-08-01_23-14-21 全备
2.9M 2023-08-01_23-29-52 第一次增量备份产生的目录
2.8M 2023-08-01_23-46-10 第二次增量备份产生的目录
3.9G total
--查看第二次增量备份后的xtrabackup_checkpoints文件
[mysql@mysql-leo-perslave file]$ cd 2023-08-01_23-46-10
[mysql@mysql-leo-perslave 2023-08-01_23-46-10]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 7589245996
to_lsn = 7589246994
last_lsn = 7589247010
compact = 0
recover_binlog_info = 0
6、恢复演练
--现运用增量备份集进行恢复
6.1、损坏数据
mysql> drop database fruitsDB;
Query OK, 1 row affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | ON |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
6.2、开始恢复
mysql> set global super_read_only=off;
Query OK, 0 rows affected (0.00 sec)
$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --user=root --apply-log --redo-only /mysql/bak/file/2023-08-01_23-14-21
$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --user=root --apply-log --redo-only /mysql/bak/file/2023-08-01_23-14-21 --incremental-dir=/mysql/bak/file/2023-08-01_23-29-52
$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --user=root --apply-log --redo-only /mysql/bak/file/2023-08-01_23-14-21 --incremental-dir=/mysql/bak/file/2023-08-01_23-46-10
说明：以上为增量还原，最后还有最重要的一步，就是进行一次全量还原.
--停止数据库
mysql> shutdown;
--删除数据文件目录
$ mv /mysql/data /mysql/databak
$ mkdir -p /mysql/data
--全量恢复
$ /usr/bin/innobackupex --defaults-file=/home/mysql/etc/my.cnf --user=root --copy-back /mysql/bak/file/2023-08-01_23-14-21
说明：增量备份恢复的原理是把增量目录下的数据整合到全量目录下，然后在进行全量还原.总体来说innobackupex速度快，支持innodb、myisam，不过并不是很方便.
6.3、验证数据
[mysql@mysql-leo-perslave data]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
[1] 6001
[mysql@mysql-leo-perslave data]$ mysqld_safe Adding '/usr/local/Percona-Server-5.7.21-20-Linux.x86_64.ssl101/lib/mysql/.1' to LD_PRELOAD for mysqld
2023-08-01T16:07:26.973450Z mysqld_safe Logging to '/mysql/data/mysql_error.log'.
2023-08-01T16:07:27.004172Z mysqld_safe Starting mysqld daemon with databases from /mysql/data
[mysql@mysql-leo-perslave data]$ ps -ef|grep mysql
avahi 818 1 0 Aug01 ? 00:00:00 avahi-daemon: running [mysql-leo-perslave.local]
root 2847 2796 0 Aug01 pts/0 00:00:00 su - mysql
mysql 2848 2847 0 Aug01 pts/0 00:00:00 -bash
root 3821 3783 0 Aug01 pts/1 00:00:00 su - mysql
mysql 3822 3821 0 Aug01 pts/1 00:00:00 -bash
root 4004 3966 0 Aug01 pts/2 00:00:00 su - mysql
mysql 4005 4004 0 Aug01 pts/2 00:00:00 -bash
root 4349 4311 0 Aug01 pts/3 00:00:00 su - mysql
mysql 4350 4349 0 Aug01 pts/3 00:00:00 -bash
mysql 5501 4350 0 00:01 pts/3 00:00:00 iostat 2 -xkd
mysql 6001 3822 0 00:07 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf
mysql 6351 6001 30 00:07 pts/1 00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/etc/my.cnf --basedir=/usr/local/mysql --datadir=/mysql/data --plugin-dir=/usr/local/mysql/lib/mysql/plugin --log-error=/mysql/data/mysql_error.log --pid-file=/mysql/data/mysqld.pid --socket=/mysql/data/mysql.sock
mysql 6380 3822 0 00:07 pts/1 00:00:00 ps -ef
mysql 6381 3822 0 00:07 pts/1 00:00:00 grep --color=auto mysql
[mysql@mysql-leo-perslave data]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
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 |
| booksDB |
| fruitsDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use fruitsDB;
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> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| c1 | 201 | pear | 5.60 |
| c2 | 202 | pawpaw | 3.60 |
| c3 | 203 | grape | 6.60 |
| c4 | 204 | strawberry | 3.60 |
| c5 | 201 | pear | 5.60 |
| c6 | 206 | kiwifruit | 3.60 |
| c7 | 207 | muskmelon | 1.60 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
23 rows in set (0.00 sec)
说明：如上所示，数据成功还原.
参考文档：
https://blog.csdn.net/LiuHuan_study/article/details/81365133
https://www.jb51.net/article/55449.htm?t=1433425901007mysql innobackupex备份恢复相关测试
原创Liujun_Deng 博主文章分类：Mysql ©著作权
文章标签 增量备份 MySQL innobackupex备份恢复 文章分类 MySQL 数据库
©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任
上一篇：mysql在执行innobackupex增量备份时报错cannot open *****/xtrabackup_checkpoints
下一篇：mysql在安装group_replication插件时，报错"version libcrypto.so.10 not defined in file libcrypto.so"
提问和评论都可以，用心的回复会被更多人看到
评论
发布评论
相关文章
-
mysql innobackupex增量备份
mysql innobackupex增量备份备忘
mysql 增量备份 innobackupex -
fsdb文件和vf文件
一切皆为文件，在类 unix 系统中，这一思想影响了一代操作系统，作为著名的 Linux，如何实现这一思想，如何让市面上所有的文件系统能够完美兼容地运行在 Linux 系统中，这背后显然隐藏着一系统复杂的机制，本文将带你窥探其中的奥秘。 假设
fsdb文件和vf文件 VFS super block dentry inode
