技术分享

作者：林靖华

爱可生服务团队成员，负责处理客户在MySQL日常运维中遇到的问题；擅长处理备份相关的问题，对数据库相关技术有浓厚的兴趣，喜欢钻研各种问题。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

前言

在日常数据库运维中，备份是不可缺少的一部分。我们常常用备份集来新建从库或恢复数据不一致的实例等等。但有些时候恢复完实例加回集群后，是有可能会丢失数据的。

实验

环境准备

步骤

1、主从同步验证

先在主库写入一些数据，然后验证数据已经同步到从库

-- master(131)
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> create database test2;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      478 |              |                  | eefac7d8-2370-11e9-bfeb-000c29d74445:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

-- slave(132)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.13.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 478
               Relay_Log_File: 192-168-13-132-relay-bin.000002
                Relay_Log_Pos: 691
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ......
           Retrieved_Gtid_Set: eefac7d8-2370-11e9-bfeb-000c29d74445:1-2
            Executed_Gtid_Set: eefac7d8-2370-11e9-bfeb-000c29d74445:1-2

2、在从库上创建备份

逻辑备份或物理备份都可以，这里以逻辑备份为例

mysqldump -uroot -p123456 --single-transaction --master-data=2 --all-databases > dump.sql

3、向主库写入数据

-- master(131)
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
+--------------------+
7 rows in set (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      640 |              |                  | eefac7d8-2370-11e9-bfeb-000c29d74445:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

-- slave(132)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
+--------------------+
7 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.13.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 640
               Relay_Log_File: 192-168-13-132-relay-bin.000002
                Relay_Log_Pos: 853
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ......
           Retrieved_Gtid_Set: eefac7d8-2370-11e9-bfeb-000c29d74445:1-3
            Executed_Gtid_Set: eefac7d8-2370-11e9-bfeb-000c29d74445:1-3

4、产生故障

假设现在主库存放数据的磁盘发生故障，HA 将原来的从节点提升为主节点来提供数据的写入，然后插入数据

-- new master(132)
mysql> create database test4;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
| test4              |
+--------------------+
8 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| mysql-bin.000001 |      802 |              |                  | b66b4623-207d-11ea-a993-000c29122c12:1,
eefac7d8-2370-11e9-bfeb-000c29d74445:1-3 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5、恢复实例

用之前备份的数据来恢复挂掉的实例

-- old master(131)
mysql -uroot -p123456 < dump.sql

恢复后拥有的数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      478 |              |                  | eefac7d8-2370-11e9-bfeb-000c29d74445:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

6、与新主建立复制关系

-- old master(131)
mysql> change master to
    -> master_host='192.168.13.132',
    -> master_user='repl',
    -> master_password='123456',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test4              |
+--------------------+
7 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| mysql-bin.000001 |      640 |              |                  | b66b4623-207d-11ea-a993-000c29122c12:1,
eefac7d8-2370-11e9-bfeb-000c29d74445:1-2 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

现象

可以观察到，在旧主通过备份恢复数据且加回到集群后，丢失了部分的数据。操作的步骤看起来并没有问题，那么原因是什么呢？

丢失的事务的 GTID 是 eefac7d8-2370-11e9-bfeb-000c29d74445:3，我们先到新主的 binlog 中看下是否还有这个事务的记录，保证 binlog 没有被清理。

#200220 14:19:41 server id 1  end_log_pos 543 CRC32 0x3fa9fb8c  GTID    last_committed=2        sequence_number=3       rbr_only=no
SET @@SESSION.GTID_NEXT= 'eefac7d8-2370-11e9-bfeb-000c29d74445:3'/*!*/;
# at 543
#200220 14:19:41 server id 1  end_log_pos 640 CRC32 0x8427b0a3  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1582179581/*!*/;
create database test3
/*!*/;

新主的 binlog 中是有这个事务的，那接着我们再来看下旧主的 relaylog 中是否也同样记录。但意外的是，relaylog 中并没有相关的记录，说明这个事务根本没有写进 relaylog。

那么我们可以回想一下，在 MySQL 里面，有没有类似的机制或者参数让主库的 binlog 在传输或写入从库的过程中被过滤掉。正好我们发现，开启 binlog 必须要配置的 server_id 就能起到这样的作用。

分析

由于我们在恢复实例的时候使用的备份集不是最新的备份集，这个备份集中只含有 eefac7d8-2370-11e9-bfeb-000c29d74445:1-2 两个事务，那么恢复回来的实例加入集群变成从实例的时候，需要通过新主库的 binlog 来补偿数据。

在传输事务 eefac7d8-2370-11e9-bfeb-000c29d74445:3 的时候，从实例的 io_thread 发现这个事务记录的 server_id 为 1，与自己的 server_id 一致，会认为这个是自己执行过的事务，就会把这段信息给过滤掉，最终这个从实例的数据就会缺少一部分。

总结

由此可见，在这种情况下，更换 server_id 是最简单的解决办法，所以我们在恢复实例的时候，尽可能的去修改 server_id，保证 server_id 不与之前的重复且与集群中的其他实例不相同。

有不少人并没有碰到类似的问题，是因为他们在恢复实例的时候，通常是直接在线备份线上的实例，用最新的备份集来导入数据，在这种情况下，也可以避免以上问题的发生。

另外还有一个值得一提的参数 --replicate-same-server-id，这个参数的作用是开启后即使 io_thread 收到与自己 server_id 相同的 binlog，也会写入 relaylog。虽然启用这个参数也可以避免以上问题，但在大部分情况下都不推荐开启这个参数，默认值 OFF 是为了避免 binlog 在复制中回环。在 5.7 中想开启这个参数的话需要先关闭 log_slave_updates；8.0 则做了改进，当 gtid_mode=ON 的情况下就可以开启。

参考：
https://lefred.be/content/mysql-gtid-restore-a-master-from-a-replicas-backup/ https://www.zhangshengrong.com/p/yOXD5zZM1B/ https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#optionmysqldreplicate-same-server-id

技术分享 | 使用备份恢复实例时存在的坑

于3月 16, 20203月 16, 2020由林靖华发布

技术文章

故障分析 | MySQL 无法修改主键？原来是因为这个参数

行业趋势 | 2023 年的 DBA 有哪些变化？

技术文章

技术译文 | MySQL 8 和 MySQL 5.7 在自增计数上的区别

技术分享 | 使用备份恢复实例时存在的坑

于3月 16, 20203月 16, 2020由林靖华发布

相关文章

技术文章

故障分析 | MySQL 无法修改主键？原来是因为这个参数

技术分享

行业趋势 | 2023 年 的 DBA 有哪些变化？

技术文章

技术译文 | MySQL 8 和 MySQL 5.7 在自增计数上的区别

行业趋势 | 2023 年的 DBA 有哪些变化？