mysql256次利用_【案例】【MySQL】一次复杂的主从库数据不一致修复

最新推荐文章于 2024-12-25 19:34:15 发布

原创最新推荐文章于 2024-12-25 19:34:15 发布 · 520 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql256次利用

本文详细记录了一次MySQL主从同步数据丢失及不一致的问题处理过程，包括问题定位、解决方案实施、数据恢复及主从关系重建等步骤。

修复操作之前，已写好了修复操作方案、回滚方案和规避建议。但是修复过程中，发生了一些意料之外的状况，根据实际情况不断修改方案，直到完成预定目标。

问题描述:

主库已从2b切换到2a，在新主库2a上查询不到6月份和7月份的数据，8月份1号-4号数据主从库两边数据不一致，后续数据同步正常。

问题分析：

因为没有有效监控，主从机器都有发生磁盘满，导致主库从库发生过频繁切换，数据严重不一致。清理了过期数据腾出部分空间后，从8月5号开始数据正常同步。原主库2b上有6月份和7月份数据，没有同步到2a上。

解决过程

目标：

把差异数据都汇集到2b上，让2b拥有最齐全的数据，然后切换2b为主库，再以2b

为基准，重做2a从库，建立主从同步复制关系。

一. 查看2a和2b磁盘空间是否充足

in 2a,2b:

df -h

/dev/mapper/vg_dbdata-lv_workdbs 987G 404G 535G 44% /opt/mysql/data/workdbs

/dev/mapper/vg_dblog-lv_binlog 375G 618M 356G 1% /opt/mysql/data/binlog

/dev/mapper/vg_dbdata-lv_tmp 79G 56M 75G 1% /opt/mysql/data/tmp

/dev/mapper/vg_dblog-lv_log 40G 72M 38G 1% /opt/mysql/data/log

/dev/mapper/vg_backup-lv_backup 99G 11G 83G 12% /opt/mysql/data/backup

/dev/mapper/vg_dbsys-lv_undo 99G 512M 93G 1% /opt/mysql/data/undo

/dev/mapper/vg_dbsys-lv_redo 40G 8.1G 30G 22% /opt/mysql/data/redo

二.操作前做好操作表的数据备份，2a和2b上的ef_fhk_4q_202008，导出

in 2a,2b:

#mysqldump -uroot -p --set-gtid-purged=off fhk_601 ef_fhk_4q_202008>/opt/mysql/data/backup/import_export/ef_fhk_4q_202008.sql

三.在2a(主库)上，创建表ef_fhk_4q_202008_2a_1_4，插入原表8月1号-4号的记录。根据同步原理，在2b上发现新表ef_fhk_4q_202008_2a_1_4已同步过来

use fhk_601;

create table ef_fhk_4q_202008_2a_1_4 like ef_fhk_4q_202008;

insert into ef_fhk_4q_202008_2a_1_4 select * from ef_fhk_4q_202008 where START_DATE>='2020-08-01 00:00:00' and START_DATE<='2020-08-04 23:59:59';

commit;

select count(*) from ef_fhk_4q_202008_2a_1_4;

四.在2b(从库)，比较新表ef_fhk_4q_202008_2a_1_4数据跟表ef_fhk_4q_202008的8月1号-4号的记录数据。不存在ef_fhk_4q_202008表的8月1号-4号的记录数据，从ef_fhk_4q_202008_2a_1_4插入ef_fhk_4q_202008，这样ef_fhk_4q_202008汇合了全部8月1号-4号数据。

set sql_log_bin=off;

set super_read_only=off;

select count(*) from ef_fhk_4q_202008_2a_1_4 where FHK_ID not in (select FHK_ID from ef_fhk_4q_202008 where START_DATE>='2020-08-01 00:00:00' and START_DATE<='2020-08-04 23:59:59');

insert into ef_fhk_4q_202008 select * from ef_fhk_4q_202008_2a_1_4 where FHK_ID not in (select FHK_ID from ef_fhk_4q_202008 where START_DATE>='2020-08-01 00:00:00' and START_DATE<='2020-08-04 23:59:59');

commit;

set super_read_only=on;

set sql_log_bin=on;

后来执行到第三步遇到了问题：

create table ef_fhk_4q_202008_2a_1_4 like ef_fhk_4q_202008; 卡死，堵住,直到超时

查询Mysql当时会话情况：

select * from information_schema.processlist where command != 'Sleep' order by time desc; 也卡住

监控系统不断警报繁忙。Ctrl+C中断SQL后

显示ERROR 2013 (HY000): Lost connection to MySQL server during query

创建的表已存在，但是访问出错

mysql> select * from ef_fhk_4q_202008_2a_1_4;

ERROR 1030 (HY000): Got error 122 from storage engine

表无效，drop掉

mysql> drop table ef_fhk_4q_202008_2a_1_4;

怀疑主库创建表存储有点问题，改变思路，不在主库上操作，改到从库。先从2a(主库)导出数据，到2b(从库)上导入和合并数据

in 2a(主库)：

mysqldump -uroot -p --set-gtid-purged=off fhk_601 ef_fhk_4q_202008 --where=“START_DATE>='2020-08-01 00:00:00' and START_DATE<='2020-08-04 23:59:59'“ >/opt/mysql/data/backup/import_export/ef_fhk_4q_202008_2a_1_4.sql

scp ef_fhk_4q_202008_2a_1_4.sql to 2b

in 2b(从库)：

chown mysql.msyql ef_fhk_4q_202008_2a_1_4.sql

set sql_log_bin=off;

set global super_read_only=off;

create database fhk_601_2b;

use fhk_601_2b;

source /opt/mysql/data/backup/2a_backup/ef_fhk_4q_202008_2a_1_4.sql;

alter table ef_fhk_4q_202008 rename to fhk_601.ef_fhk_4q_202008_2a_1_4;

stop slave;

use fhk_601;

Query OK, 4466326 rows affected (24 min 3.27 sec)

Records: 4466326 Duplicates: 0 Warnings: 0

注：成功插入4466326条差异数据。

commit;

start slave;

五.切换主库到2b

六.以2b(主库)为基准，把整个2b(主库)全量物理备份到2a(从库)，原2a数据删除，重做从库，配置主从同步复制关系

1.查看空间是否足够

df -h

2.全量备份主库

现网空间不足，所以采用压缩方式备份，而且是最高极限压缩

in 2b(主库)：

mysqlbackup --user=root --password=XXXXXXXX --backup-dir=/opt/mysql/data/backup --backup-image=./dball.mbi --with-timestamp --compress-level=9 --compress-method=zlib --skip-binlog --skip-relaylog backup-to-image

scp dball.mbi root@8.11.111.112:/opt/mysql/data/backup

3.从库全量数据恢复

in 2a(从库):

chown mysql.mysql dball.mbi

su - mysql

mysql.server stop

cd /opt/mysql/data/undo

rm -rf *

cd /opt/mysql/data/redo

rm -rf *

cd /opt/mysql/data/workdbs

rm -rf *

mysqlbackup --backup-image=/opt/mysql/data/backup/dball.mbi --backup-dir=/opt/mysql/data/backup --uncompress copy-back-and-apply-log --force

恢复全量备份的命令操作，在其他环境多次使用，都是正确的，但是在这次恢复的过程中，遇到了问题：

先是解压拷贝文件

最后apply-log，发现有问题

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 不断循环

200901 12:43:51 MAIN INFO: MySQL server version is ‘5.7.21-SR1-enterprise-commercial-advanced-log’

200901 12:43:51 MAIN INFO: Restoring …5.7.21-SR1-enterprise-commercial-advanced-log version

200901 12:43:51 MAIN INFO: Creating 12 buffers each of size 65680.

200901 12:43:51 MAIN INFO: Apply-log operation starts with following threads

1 read-threads 1 process-threads 6 apply-threads

200901 12:43:51 MAIN INFO: Using up to 100 MB of memory.

200901 12:43:51 MAIN INFO: ibbackup_logfile’s creation parameters:

start lsn 2273308335616, end lsn 2274706102651,

start checkpoint 2273308336024.

200901 12:46:26 ALW2 INFO: A thread created with Id ‘46986332837632’

200901 12:46:26 ALW3 INFO: A thread created with Id ‘46986330736384’

200901 12:46:26 ALW6 INFO: A thread created with Id ‘46986324432640’

200901 12:46:26 PCR1 INFO: A thread created with Id ‘46986320230144’

200901 12:46:26 ALW4 INFO: A thread created with Id ‘46986328635136’

200901 12:46:26 RDR1 INFO: A thread created with Id ‘46986322331392’

200901 12:46:26 ALW5 INFO: A thread created with Id ‘46986326533888’

200901 12:46:26 ALW1 INFO: A thread created with Id ‘46986334938880’

200901 12:46:26 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273313578496.

200901 12:46:27 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273318821376.

200901 12:46:27 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273324064256.

200901 12:46:27 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273329307136.

200901 12:46:27 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273334550016.

200901 12:46:27 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273339792896.

200901 12:46:27 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273345035776.

200901 12:46:27 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273350278656.

200901 12:46:27 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273355521536.

200901 12:46:27 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273360764416.

200901 12:46:27 PCR1 INFO: InnoDB: Starting an apply batch of log records to the database…

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

17 18 19 20

21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

38 39 40 41

42 43 44 45 46 47 48

49 50 51 52 53 54 55

56 57 58 59 60 61 62 63

65 66

67 68

69 70 71

72 73 74 75 76 77

78 79 80 81

82 83 84 85 86

87 88 89 90 91 92

93 94 95 96 97

98 99

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273366007296.

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273371250176.

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273376493056.

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273381735936.

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273386978816.

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273392221696.

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273397464576.

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273402707456.

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273407950336.

200901 16:46:15 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273413193216.

200901 16:46:15 PCR1 INFO: InnoDB: Starting an apply batch of log records to the database…

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7

8 9 10

11 12

13 14 15 16

17 18 19 20 21 22 23

24 25

26 27

28 29 30

31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

46 47 48 49 50 51

52 53 54

55 56 57 58 59

60 61 62 63 64 65 66 67 68 69 70 71 72

73 74 75 76 77 78 79 80 81 82 83 84 85

86 87

88 89 90 91 92 93 94 95 96 97 98 99

200901 20:11:21 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273418436096.

200901 20:11:21 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273423678976.

200901 20:11:21 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273428921856.

200901 20:11:21 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273434164736.

200901 20:11:21 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273439407616.

200901 20:11:21 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273444650496.

200901 20:11:22 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273449893376.

200901 20:11:22 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273455136256.

200901 20:11:22 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273460379136.

200901 20:11:22 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273465622016.

200901 20:11:22 PCR1 INFO: InnoDB: Starting an apply batch of log records to the database…

InnoDB: Progress in percent: 0 1

2 3

5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

200901 23:15:11 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273470864896.

200901 23:15:11 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273476107776.

200901 23:15:11 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273481350656.

200901 23:15:12 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273486593536.

200901 23:15:12 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273491836416.

200901 23:15:12 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273497079296.

200901 23:15:12 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273502322176.

200901 23:15:12 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273507565056.

200901 23:15:12 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273512807936.

200901 23:15:12 RDR1 Progress in MB: 200

200901 23:15:12 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273518050816.

200901 23:15:12 PCR1 INFO: InnoDB: Starting an apply batch of log records to the database…

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

200902 02:02:50 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273523293696.

200902 02:02:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273528536576.

200902 02:02:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273533779456.

200902 02:02:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273539022336.

200902 02:02:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273544265216.

200902 02:02:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273549508096.

200902 02:02:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273554750976.

200902 02:02:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273559993856.

200902 02:02:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273565236736.

200902 02:02:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273570479616.

200902 02:02:51 PCR1 INFO: InnoDB: Starting an apply batch of log records to the database…

InnoDB: Progress in percent:0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

200902 04:33:22 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273575722496.

200902 04:33:23 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273580965376.

200902 04:33:23 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273586208256.

200902 04:33:23 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273591451136.

200902 04:33:23 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273596694016.

200902 04:33:23 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273601936896.

200902 04:33:23 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273607179776.

200902 04:33:23 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273612422656.

200902 04:33:23 PCR1 INFO: InnoDB: Starting an apply batch of log records to the database…

InnoDB: Progress in percent:0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

200902 06:12:50 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273617665536.

200902 06:12:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273622908416.

200902 06:12:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273628151296.

200902 06:12:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273633394176.

200902 06:12:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273638637056.

200902 06:12:51 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273643879936.

200902 06:12:52 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2273649122816.

200902 06:12:52 PCR1 INFO: InnoDB: Starting an apply batch of log records to the database…

跑了24小时，还在不断循环读log sequence number，无法生成redo文件。

第二天，再跑一次，问题依旧

第三天，怀疑磁盘存储有问题，坏道或者慢盘。打算把全备image文件复制到其他目录

cp /opt/mysql/data/binlog/dball.mbi /opt/mysql/data/backup

清空数据，再来一次

cd /opt/mysql/data/undo

rm -rf *

cd /opt/mysql/data/redo

rm -rf *

cd /opt/mysql/data/workdbs

rm -rf *

只有数据目录workdbs有足够空间。借用数据目录空间，在里面新建目录，先执行image文件全量解压到目录，再apply-log

mkdir /opt/mysql/data/workdbs/backup

mysqlbackup --backup-dir=/opt/mysql/data/workdbs/backup --backup-image=/opt/mysql/data/backup/dball.mbi image-to-backup-dir

mysqlbackup --backup-dir=/opt/mysql/data/workdbs/backup --uncompress apply-log

200904 13:35:43 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 13:35:43 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 13:35:45 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 13:35:45 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 13:35:47 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 13:35:47 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 13:35:49 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 13:35:49 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 13:35:51 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 13:35:51 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 13:35:53 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 13:35:53 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 13:35:56 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 13:35:56 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 13:35:58 PCR1 INFO: We were able to parse ibbackup_logfile up to

lsn 2274706102651.

200904 13:35:58 PCR1 INFO: Last MySQL binlog file position 0 727394712, file name mysql-bin.000549

200904 13:35:58 PCR1 INFO: The first data file is ‘/opt/mysql/data/workdbs/backup/datadir/ibdata1’

and the new created log files are at ‘/opt/mysql/data/workdbs/backup/datadir’

200904 13:35:58 MAIN INFO: Apply-log operation completed successfully.

200904 13:35:58 MAIN INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!

mysql@rrsdb2a:~>

成功apply-log了，开始拷贝回数据

mysqlbackup --backup-dir=/opt/mysql/data/workdbs/backup copy-back --force

mysqlbackup completed OK!

成功全量恢复！

4.从库增量数据恢复

前面因为apply-log失败，折腾了几天，目前恢复的全备是几天前数据，所以需要去备份这几天的增量数据过来

in 2b(主库):

mysqlbackup --user=root --password=XXXXXXX --incremental=optimistic --incremental-base=dir:/opt/mysql/data/binlog/2020-08-31_09-17-20 --backup-dir=/opt/mysql/data/binlog/ --backup-image=incremental_image4.mbi --with-timestamp --skip-binlog --skip-relaylog backup-to-image

200904 14:09:55 MAIN INFO: Image Path = /opt/mysql/data/binlog/2020-09-04_13-46-26/incremental_image4.mbi

200904 14:09:55 MAIN INFO: Backup contains changes from lsn 2274706102652 to lsn 2285329040425

200904 14:09:55 MAIN INFO: MySQL binlog position: filename mysql-bin.000561, position 928672897

mysqlbackup completed OK!

mysql@rrsdb2b:~>

增量备份完成！

查看之前全备的LSN位置

mysql@rrsdb2b:~/data/binlog/2020-08-31_09-17-20/meta> cat backup_variables.txt

[backup_variables]

apply_log_done=0

binlog_position=mysql-bin.000549:727394712

consistency_time_utc=1598868578674065

end_lsn=2274706102651

end_time_utc=1598868578681595

gtid_executed=7e631f40-d20b-11e7-bedd-fa163e2f43eb:1-51682279,8a2c650e-d20b-11e7-ad33-fa163e9fef2f:1-158641104:158641106-158641500:158641502-159133133:159133135-159133237:159133239-159133309:159133311-159133461:159133463-171037619:171037622-171037996:171037999-171038036:171038038-171038394:171038396-171052461:171052463-233470074:233470076-233470149:233470151-235190794:235190796-235190865:235190867-235191686:235191688-235191721:235191723-235191746:235191748-235192047:235192049-235192080:235192082-235192109:235192111-238672707:238672709-238672798:238672800-240852057:240852059-240852279:240852281-240855239:240855241-240858224:240858226-282962255:282962257-291985054:291985056-296532459:296532461-296586913

has_external_plugins=1

has_tde_tables=0

is_compressed=1

is_incremental=0

is_incremental_with_redo_log_only=0

is_onlyinnodb=0

is_partial=0

is_skip_unused_pages=0

meb_version=4.1.0

mysql_version=5.7.21-SR1-enterprise-commercial-advanced-log

start_lsn=2273308335616

start_time_utc=1598854640724531

mysql@rrsdb2b:~/data/binlog/2020-08-31_09-17-20/meta>

查看增量备份LSN位置

mysql@rrsdb2b:~/data/binlog/2020-09-04_13-46-26/meta> cat backup_variables.txt

[backup_variables]

apply_log_done=0

binlog_position=mysql-bin.000561:928672897

consistency_time_utc=1599217793024620

end_lsn=2285329040425

end_time_utc=1599217793031754

gtid_executed=7e631f40-d20b-11e7-bedd-fa163e2f43eb:1-52856158,8a2c650e-d20b-11e7-ad33-fa163e9fef2f:1-158641104:158641106-158641500:158641502-159133133:159133135-159133237:159133239-159133309:159133311-159133461:159133463-171037619:171037622-171037996:171037999-171038036:171038038-171038394:171038396-171052461:171052463-233470074:233470076-233470149:233470151-235190794:235190796-235190865:235190867-235191686:235191688-235191721:235191723-235191746:235191748-235192047:235192049-235192080:235192082-235192109:235192111-238672707:238672709-238672798:238672800-240852057:240852059-240852279:240852281-240855239:240855241-240858224:240858226-282962255:282962257-291985054:291985056-296532459:296532461-296586913

has_external_plugins=1

has_tde_tables=0

is_compressed=0

is_incremental=1

is_incremental_with_redo_log_only=0

is_onlyinnodb=0

is_partial=0

is_skip_unused_pages=0

mysql_version=5.7.21-SR1-enterprise-commercial-advanced-log

start_lsn=2274706102652

meb_version=4.1.0

start_time_utc=1599216386398910

mysql@rrsdb2b:~/data/binlog/2020-09-04_13-46-26/meta>

拷贝增量备份文件到从库机器

scp /opt/mysql/data/binlog/2020-09-04_13-46-26/incremental_image4.mbi mysql@2a ip:/opt/mysql/data/backup

in 2a(从库):

mkdir /opt/mysql/data/backup/incremental

互联网上，无法完成增量备份为image文件的恢复，必须恢复增量到全量(image解压，需要足够空间)上，再全量恢复数据，官网资料没有解答。

mysqlbackup --backup-dir=/opt/mysql/data/backup/incremental --backup-image=/opt/mysql/data/backup/incremental_image4.mbi image-to-backup-dir

mysqlbackup --backup-dir=/opt/mysql/data/backup/incremental apply-log

重点: 以下是我本人实践成功的，经过研究mysqlbackup帮助文档，在已全量恢复的实例上，恢复增量image文件

mysqlbackup --backup-image=/opt/mysql/data/backup/incremental_image4.mbi --backup-dir=/opt/mysql/data/backup/incremental --incremental=optimistic copy-back-and-apply-log

100 200 300 400 500 600 700 800 900 1000

200904 14:56:48 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 14:56:48 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 14:56:50 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 14:56:50 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 14:56:52 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 14:56:52 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 14:56:54 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 14:56:54 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 14:56:56 PCR1 INFO: InnoDB: Setting log file size to 1073741824.

200904 14:56:56 PCR1 INFO: InnoDB: Progress in MB:.

100 200 300 400 500 600 700 800 900 1000

200904 14:56:59 PCR1 INFO: We were able to parse ibbackup_logfile up to

lsn 2285329040425.

200904 14:56:59 PCR1 INFO: Last MySQL binlog file position 0 928672897, file name mysql-bin.000561

200904 14:56:59 PCR1 INFO: The first data file is ‘/opt/mysql/data/workdbs/ibdata1’

and the new created log files are at ‘/opt/mysql/data/redo’

200904 14:56:59 MAIN INFO: MySQL server version is ‘5.7.21-SR1-enterprise-commercial-advanced-log’

200904 14:56:59 MAIN INFO: Restoring …5.7.21-SR1-enterprise-commercial-advanced-log version

200904 14:56:59 MAIN INFO: Apply-log operation completed successfully.

200904 14:56:59 MAIN INFO: Incremental backup applied successfully.

mysqlbackup completed OK! with 3 warnings

mysql@rrsdb2a:~/data/workdbs/backup/meta>

增量数据已恢复入从库！

5.建立主从库的同步复制关系

mysql.server start

mysql -uroot -p

reset slave all;

change master to master_host = '2b ip', master_port = 3310, master_user = 'rpl_user', master_password = 'XXXXXXXX', master_auto_position=1 for channel 'rpl1';

start slave;

show slave status\G;

成功同步

6.清理备份文件

cd /opt/mysql/data/workdbs

rm -rf backup

cd /opt/mysql/data/backup/import_export

rm -rf *

cd /opt/mysql/data/backup

rm -rf *.mbi

cd /opt/mysql/data/binlog

rm -rf *.mbi

回滚方案:

有备份了操作之前的2a和2b上的ef_fhk_4q_202008，若是需要回滚，可以删除，导入备份。

后续规避建议：

1.做好主机和从机的数据库的workdbs,binlog,log,tmp,undo等目录的磁盘使用率监控，使用率超过80%就要人工干预。

2.若是发生主从切换，主从不同步等情况，要有报警，报警需要人工进入查看干预。