爱喝兽奶帝天荒

这个屌丝很懒，什么也没留下！

热门标签

mysql GTID看这一篇就够啦_change master auto position

作者：爱喝兽奶帝天荒 | 2024-08-16 18:01:10

踩

change master auto position

GTID看这一篇就够啦

这里讲的gtid是5.7版本的，5.7版本比5.6有一些增强，比如在线开启等

GTID

(Global Transaction ID)是全局事务ID,当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务，比如主库新事务都有一个全局ID，即使此事务被同步到任何从库上并应用都会标记着这个事务

GTID的组成： server_uuid:transaction_id，平时看到的gtid_executed或者Executed_Gtid_Set实际是个集合，m-n的方式，代表执行了事务m 到事务n 这些ID

有了GTID对数据库分布式会有很大的帮助。尤其是复杂主从关系，我们可以省去手工去找偏移量，而是通过CHANGE MASTER TO MASTER_HOST=‘xxx’, MASTER_AUTO_POSITION=1的即可方便的搭建从库，在故障修复中也可以采用MASTER_AUTO_POSITION=‘X’的方式。

MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID，然后再写事务的Binlog，主从同步时GTID_Event和事务的Binlog都会传递到从库，在从库应用Relay Log,从库在执行的时候也是用同样的GTID写binlog

GTID限制语法

1、CREATE TABLE … SELECT 复制表结构，复制数据
2、使用GTID复制模式时，不支持create temporary table 和 drop temporary table。但是在autocommit=1的情况下可以创建临时表，Master端创建临时表不产生GTID信息，所以不会同步到slave，但是在删除临时表的时候会产生GTID会导致，主从中断.
3、在一个事务中或语句中同时更新事务和非事务表，比如在一个事务中更新 innodb表和myisam表。要更新的所有非事务表是临时表除外

GTID在binlog中的结构

请添加图片描述

Previous_gtid_log_event
- Previous_gtid_log_event 在每个binlog 头部都会有
- 每次binlog rotate的时候存储在binlog头部
- Previous-GTIDs在binlog中只会存储在这台机器上执行过的所有binlog，不包括手动设置gtid_purged值。
- 换句话说，如果你手动set global gtid_purged=xx；那么xx是不会记录在Previous_gtid_log_event中的。
GTID和Binlog之间的关系是怎么对应的呢

* 假设有4个binlog: bin.001,bin.002,bin.003,bin.004
* bin.001 : Previous-GTIDs=empty; binlog_event有：1-40  
* bin.002 : Previous-GTIDs=1-40;  binlog_event有：41-80 
* bin.003 : Previous-GTIDs=1-80;  binlog_event有：81-120  
* bin.004 : Previous-GTIDs=1-120;  binlog_event有：121-160  
1. 假设现在我们要找GTID=$A，那么MySQL的扫描顺序为： 从最后一个binlog开始扫描（即：bin.004）      
2. bin.004的Previous-GTIDs=1-120，如果$A=140 > Previous-GTIDs,那么肯定在bin.004中  
3. bin.004的Previous-GTIDs=1-120，如果$A=88 包含在Previous-GTIDs中,那么继续对比上一个binlog文件 bin.003,然后再循环前面2个步骤，直到找到为止
1
2
3
4
5
6
7
8

查看Previous_gtid_log_event

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |      1546 |         123 | Server ver: 5.7.17-log, Binlog ver: 4  |
| mysql-bin.000002 | 123 | Previous_gtids |      1546 |         190 | 635cd94d-5731-11ec-b22e-5e04a6f03178:4 |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |      1546 |         123 | Server ver: 5.7.17-log, Binlog ver: 4                             |
| mysql-bin.000001 | 123 | Previous_gtids |      1546 |         150 |                                                                   |
| mysql-bin.000001 | 150 | Gtid           |      1547 |         211 | SET @@SESSION.GTID_NEXT= '635cd94d-5731-11ec-b22e-5e04a6f03178:4' |
| mysql-bin.000001 | 211 | Query          |      1547 |         270 | BEGIN                                                             |
| mysql-bin.000001 | 270 | Table_map      |      1547 |         322 | table_id: 221 (orch_meta.zjk_test)                                |
| mysql-bin.000001 | 322 | Write_rows     |      1547 |         358 | table_id: 221 flags: STMT_END_F                                   |
| mysql-bin.000001 | 358 | Xid            |      1547 |         385 | COMMIT /* xid=1848 */                                             |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

因为mysql-bin.000001是第一个binlog，所以没有Previous_gtids，第二个binlog是从第一个轮转过来的，轮转时会添加上Previous_gtids

参数解析

GTID_MODE 这个参数要主备库对应着开启的比如我把主库设置为ON，备库是OFF，备库的slave就中断了，主库是OFF_PERMISSIVE，备库是ON也中断(因为主库不产生GTID，备库只接受GTID所以备库就中断了)
- OFF ：不产生GTID，Slave只接受不带GTID的事务
- OFF_PERMISSIVE ：不产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
- ON_PERMISSIVE ：产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
- ON ：产生GTID，Slave只能接受带GTID的事务。
- 所以在线打开gtid必须遵从这几个步骤，不能跳过步骤
  - set gtid_mode=OFF_PERMISSIVE
  - set gtid_mode=ON_PERMISSIVE 这时候要检查主备复制，确定没有延迟
  - set gtid_mode=ON
ENFORCE_GTID_CONSISTENCY 强一致检查
- WARN：可以检查日志，看看有没有问题，没有的话再设置为ON
- ON：开启gtid的一些安全限制，开启后执行上面限制语法将会报错

开启GTID依赖参数

log-bin=mysql-bin binlog
binlog_format=row 格式
log_slave_updates=1 备库上启用binlog
- 在5.7中不是强制选项，其中最重要的原因在于5.7在mysql库下引入了新的表mysql.gtid_executed把gtid_executed这个值持久化，5.7之前是内存值，所以slave重启后就不知道当前已经运行到的GTID位置，所以必须让备库也记录binlog，当实例启动时扫描最后一个binlog获取当前执行到的GTID位置信息
- 引入该解决方案之后又带来了新的问题，那就是在gtid_executed里面的数据会越来越多，如何精简管理呢，MySQL引入了一个新的线程和参数来进行管理。
  - 线程为：thread/sql/compress_gtid_table，可以查询performance_schema.threads来查看。
  - 参数为 gtid_executed_compression_period ，主要用于控制每执行多少个事务，对表gtid_executed进行压缩，默认值为：1000 详细看下面关于gtid_executed白能量的介绍。
- 在5.7中用户在备库上还是开启了binlog的情况下，mysql.gtid_executed是如何更新的呢？
  - 若从MySQL服务器启用了二进制日志，则表mysql.gtid_executed的更新仅在二进制轮转时发生，因为发生重启等情况依旧可以通过扫描二进制日志判断得知当前运行的GTID位置。所以平时我们通过select * from mysql.gtid_executed;看到的值其实是上个binlog的值，不是准确的。
gtid_mode=ON
enforce_gtid_consistency=ON

在线开启步骤

主备库 set global ENFORCE_GTID_CONSISTENCY = ON;
主备库 set global gtid_mode=OFF_PERMISSIVE;
主备库 set global gtid_mode=ON_PERMISSIVE;
备库检查复制是否一致主备检查 SHOW STATUS LIKE ‘ONGOING_ANONYMOUS_TRANSACTION_COUNT’;是否为0
主备库 set global gtid_mode=ON;
备库
- stop slave;
- CHANGE MASTER TO MASTER_AUTO_POSITION = 1 ; 先开启再设置，不然找不到，就是jtid可以使用0/1,非jtid只能用0
- start slave;

在线回滚

备库 STOP SLAVE;
备库 CHANGE MASTER TO MASTER_AUTO_POSITION = 0; 先关闭再设置，不然关闭时候主备就报错了
备库 START SLAVE;
主备库 SET GLOBAL GTID_MODE = ON_PERMISSIVE;
主备库 SET GLOBAL GTID_MODE = OFF_PERMISSIVE;
主备库 SET GLOBAL GTID_MODE = OFF;
主备库 set global enforce_gtid_consistency=OFF;

新SQL语法

START SLAVE [thread_types] [until_option] [connection_options]
thread_types:
    [thread_type [, thread_type] ... ]
thread_type: 
    IO_THREAD | SQL_THREAD
until_option:
    UNTIL {   {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set
          |   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
          |   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
          |   SQL_AFTER_MTS_GAPS  }
* 举个栗子：  
1. START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS = 3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56   
表示，当SQL_thread 执行到3E11FA47-71CA-11E1-9E33-C80AA9429562:10 的时候停止，下一个事务是11  
2. START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS = 3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56  
表示，当SQL_thread 执行到3E11FA47-71CA-11E1-9E33-C80AA9429562:56 的时候停止，56是最后一个提交的事务。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

show slave status

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.170.35.100
                  Master_User: replic
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 869
               Relay_Log_File: res-4vder6u3-0-relay-bin.000002
                Relay_Log_Pos: 1074
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 869
              Relay_Log_Space: 1282
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
       Replicate_Ignore_Table: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1535
                  Master_UUID: 3b59d3a8-567a-11ec-b9dc-b6cf24031444
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 3b59d3a8-567a-11ec-b9dc-b6cf24031444:1,
ff4738fd-5678-11ec-ae90-7e370957d2bd:10-11
            Executed_Gtid_Set: 3b59d3a8-567a-11ec-b9dc-b6cf24031444:1,
ff4738fd-5678-11ec-ae90-7e370957d2bd:1-11
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
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

其中四个参数与GTID相关：

Master_UUID：主库的UUID
Auto_Position： start slave时自动寻找要拉取的GTID，也就是change master 时候有没有设置master_auto_position=1，开启GTID时必须开启，不然在备库重启时极端情况可能会出现数据不一致的情形
Retrieved_Gtid_Set：从库已经接收到主库的事务编号，就是IO线程拉取到放到relaylog中的事务编号，IO会拉取此值+1的GTID事务，只会记录从上一次reset slave到现在的值
Executed_Gtid_Set：所有执行过了的GTID。可能会有多个，因为只要执行过的GTID都会记录在此，eg1: A(主ff4738fd-5678-11ec-ae90-7e370957d2bd)->B(备3b59d3a8-567a-11ec-b9dc-b6cf24031444)->C(备3e726a5b-567a-11ec-b87d-1edc0754deed)，此库为C，那C库下Executed_Gtid_Set的值为ff4738fd-5678-11ec-ae90-7e370957d2bd:N，当我把A与B断开，并把B设置为主库，在B中插入事务(以B的uuid产生新的GTID 备3b59d3a8-567a-11ec-b9dc-b6cf24031444:N)，此时C下Executed_Gtid_Set就会多了B的GTID 3b59d3a8-567a-11ec-b9dc-b6cf24031444:N。eg2: A(主)<-B(备)，B中有A的GTID，当发生了主备切换，B变为主库，A变为从库了，此时A show slave status和show master status中就有两个GTID，一个是当时自己的，一个是现在从B中传过来的。
- 按照以上，如何在C中去掉原来A的GTID呢？
  - stop slave; 记录show slave status中Executed_Gtid_Set中B的GTID信息 3b59d3a8-567a-11ec-b9dc-b6cf24031444:N
  - show master status; 查看Executed_Gtid_Set 是否跟B中一致
  - reset master; 重置C的binlog信息，该操作会清空C上的mysql.gtid_executed表中的内容，注意不要在主库上执行这种操作，会引起下游拉取binlog失败，主备同步失败。
  - set global gtid_purged=‘3b59d3a8-567a-11ec-b9dc-b6cf24031444:N’ 重置C的gtid_purged的值
  - start slave;
  - show slave status; 发现Executed_Gtid_Set就只有一个了

show master status

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 3e726a5b-567a-11ec-b87d-1edc0754deed |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 855
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 3b59d3a8-567a-11ec-b9dc-b6cf24031444:1,ff4738fd-5678-11ec-ae90-7e370957d2bd:1-11
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Executed_Gtid_Set：跟备库show slave status中的Executed_Gtid_Set同样的意思，从上面看也是有两个值，且没有自己，证明此库自己未执行过事务，可以排除此库之前是主库过，证明此库的主库之前挂载到其他库上过是：A->B->C这种架构

注意：如果此时你手动gtid_purged时候，只保留了上面两个中的一个，此时start slave会有问题，因为向主库请求时，主库有执行ff4738fd-5678-11ec-ae90-7e370957d2bd:1-11相关，发现备库没有，以为备库没执行过，会重新发送此部分log，但是此部分binlog如果已被清除则复制中断，如果未被清除就会有重复消费的问题。

GTID复制协议

GTID采用com_binlog_dump_gtid来dump binlog

备库向主库发送dump binlog请求，携带Retrieved_Gtid_Set 和Executed_Gtid_Set信息
主库首先匹配自己的gtid_purged是不是备库发送的子集，检查下主库的binlog是否已经purge了
主库判断备库都还有哪些GTID未执行，发送未执行的binlog，找到未执行binlog后还是需要判断下是不是被purge了
- 这里报错概率很大，因为从备库的Executed_Gtid_Set信息中可以找到哪些GTID是未执行的，比如A主库的Executed_Gtid_Set=UUID1:N,UUID2:N,其中UUID1是主库自己，UUID2是很久前从另外的库上同步来的早就purge了；B库的Executed_Gtid_Set=UUID1:N，这时候把B master_auto_position=1搭到A上，A就可以判断B上缺少了UUID2:N的GTID，此时就会找这部分GTID事务传给B，但是没有，就报错了。

跳过一个ID

如果明确知道可以跳过某个事务时，可以执行下面操作注入一个新的事务，前提是当前slave已经中断了，比如报错主键冲突了

以下备库上执行

stop slave;
set gtid_next='xxxxx:N'; 指定下一个事务执行的版本，即想要跳过的GTID
begin;commit; 注入一个空事物
set gtid_next='AUTOMATIC' --自动的寻找GTID事务
start slave;
1
2
3
4
5

还有一种方法是直接重置自己的gtid_purged，让其从最新的位置开始拉取数据，这种方法会清空所有binlog并重置为从头开始，操作时要注意

stop slave;
show master status; 记录gtid_executed
reset master;
set global gtid_purged=xxx;
start slave;
1
2
3
4
5

MySQLdump备份与恢复开启GTID的库

mysqldump 参数–set-gtid-purged 解析
- 默认AUTO，开启GTID的库会自动设为ON，不开启GTID的库设为OFF
- 为OFF，备份文件中不添加额外内容，导入时会记录binlog，使用自己的GTID，适用于恢复的库为主库,下游可能有备库，不会搭到备份库上的场景
- 为ON，在备份文件中会添加两行，SET @@SESSION.SQL_LOG_BIN= 0;(不记binlog), SET @@GLOBAL.GTID_PURGED=xx(沿用备份库的GTID)，适用于恢复的库为备库，会重新搭到备份库上

mysqldump --set-gtid-purged=ON的记录，发现多了SET @@SESSION.SQL_LOG_BIN= 0; SET @@GLOBAL.GTID_PURGED=‘635cd94d-5731-11ec-b22e-5e04a6f03178:1-4’;两行

-- MySQL dump 10.13  Distrib 5.7.17, for linux-glibc2.5 (x86_64)
--
-- Host: 10.170.1.218    Database: 
-- ------------------------------------------------------
-- Server version       5.7.17-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup 
--
SET @@GLOBAL.GTID_PURGED='635cd94d-5731-11ec-b22e-5e04a6f03178:1-4';
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=190;
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

下面对几种场景进行分析

未开启GTID的库，果断设置–set-gtid-purged=OFF，毫无疑问，下面只分析开启GTID的库

–set-gtid-purged=OFF

新建主备A->B： 1.未开启GTID把A B拉起(可能会有一些初始化)，2.开启GTID又对A进行一些初始化，3.用mysqldump备份A数据导入B，4.master_auto_position=1搭建B到A

1.AB都不会记录GTID信息
2.A会记录GTID信息了，如果不切binlog，此时mysql.gtid_executed还是空，因为未刷新此表，切了的话会刷入此表值,A的信息如下

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 2389
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 1be70bfd-57d7-11ec-927a-8683ca7e0e5a:1-6   这是在2时候生成的
1 row in set (0.00 sec)
mysql> select *from mysql.gtid_executed;
Empty set (0.00 sec)
mysql> select @@gtid_Mode;
+-------------+
| @@gtid_Mode |
+-------------+
| ON          |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.mysqldump导入B，相当于B执行了很多数据，会记B的binlog并产生B的GTID，此时会同步mysql.gtid_executed表，此表值为空

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 762535
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 1db37736-57d7-11ec-92a6-968a0ae0bc3b:1-137  这是步骤3时候mysqldump生成的自己的GTID
1 row in set (0.00 sec)
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 1db37736-57d7-11ec-92a6-968a0ae0bc3b |
+--------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4.搭建B到A的复制链路，因为mysql.gtid_executed表为空，所以相当于第一次搭建链路，所以会同步A:1-6的事务，这部分事务已经在mysqldump中同步过了，所以这里如果有冲突就会报错，因为相当于1-6重复执行了。就算是gtid_executed表不为空，那也不一定准确，所以还是会出现重复同步的问题。

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 762535
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 1be70bfd-57d7-11ec-927a-8683ca7e0e5a:1-6,  这是第4时候把B搭到A上同步过来A的
1db37736-57d7-11ec-92a6-968a0ae0bc3b:1-137  这是步骤3时候mysqldump生成的自己的GTID
1 row in set (0.00 sec)
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 1db37736-57d7-11ec-92a6-968a0ae0bc3b |
+--------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

5.此步骤就算是搭建成功了复制没啥问题，如果发生A-B主备切换问题就很大了，因为如第4步所示B的Executed_Gtid_Set有两个1be70bfd-57d7-11ec-927a-8683ca7e0e5a:1-6,1db37736-57d7-11ec-92a6-968a0ae0bc3b:1-137，A的只有一个1be70bfd-57d7-11ec-927a-8683ca7e0e5a:1-6，当B成为主库A成为备库时候，A会去B拉binlog，此时B发现A少了1db37736-57d7-11ec-92a6-968a0ae0bc3b:1-137这些，会把这些数据发给A，这些数据可是当时MySQLdump导入B的，再应用到A就出现了数据的回退如下所示，A上的mysql.gtid_executed又被重新回退为空了其他数据也会回退，非常危险。

   Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 1db37736-57d7-11ec-92a6-968a0ae0bc3b:1-137
            Executed_Gtid_Set: 1be70bfd-57d7-11ec-927a-8683ca7e0e5a:1-6,
1db37736-57d7-11ec-92a6-968a0ae0bc3b:1-137
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
mysql> select *from mysql.gtid_executed;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6.这种新建主备的正常过程应该是什么样子呢
- 1.未开启GTID把A B拉起(可能会有一些初始化)，
- 2.开启GTID又对A进行一些初始化，
- 3.用mysqldump --set-gtid-purged=ON备份A数据导入B，
- 4.master_auto_position搭建B到A
- 这种方式搭建的主备，第三步数据同步完后AB的gtid_executed一样，这样无论是否切换都不会有什么问题

基于以上下面看下A->B备库重建场景：
- 1.A B已开GTID并运行了好久，
- 2.清空B的数据并拉起新实例，
- 3.用mysqldump备份A数据导入B，像这种要导入A全部数据的重建跟第一种新建一样都要用mysqldump --set-gtid-purged=ON备份A数据导入B，这样可以确保恢复的B跟A的GTID一样
- 4.master_auto_position=1搭建B到A

下面看个新场景已有A->B 新建个C->D 并且C的全量数据来自于A，增量数据也要跟A同步，最后把CD作为新实例，这就是一般的实例迁移场景

1.A B已开GTID并运行了好久，A的show master status如下：

*************************** 1. row ***************************
               File: mysql-bin.000001
           Position: 3124
       Binlog_Do_DB: 
   Binlog_Ignore_DB: 
  Executed_Gtid_Set: 08348f5a-57fc-11ec-9291-4a45cefb566c:1-151  A的UUID
  1 row in set (0.00 sec)
1
2
3
4
5
6
7

2.新建C->D，CD的新建跟第一个场景一模一样，新建后C的show master status如下

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 764430
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 0196c665-5801-11ec-be27-1a69be8e5285:1-5  C的UUID
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

3.用mysqldump备份A数据导入到C，且C导入的数据会记录binlog并传给D(之所以这样是可以减少一次拷贝数据)
- 要想实现以上，就需要在A备份数据时候设置mysqldump --set-gtid-purged=OFF才会记录binlog
- –set-gtid-purged=OFF这样记录了binlog就无法知道gtid_purged的位点
- 导入数据后C的位点信息如下：
```
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 764430
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 0196c665-5801-11ec-be27-1a69be8e5285:1-146  记录binlog自己增长了
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
```

4.把C搭到A上增量同步数据根据mysqldump中的数据可以知道file和position

第3步不知道gtid_purged的位点，所以CA增量同步可以使用master_auto_position=0指定master_log_file=, master_log_pos=的方式同步数据
A的位点如下：

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 3124
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 08348f5a-57fc-11ec-9291-4a45cefb566c:1-152  A的UUID  比第一步增加了一个事务
1 row in set (0.00 sec)
1
2
3
4
5
6
7

C的位点如下：

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 764430
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 0196c665-5801-11ec-be27-1a69be8e5285:1-146,  记录binlog自己增长了
08348f5a-57fc-11ec-9291-4a45cefb566c:152   搭到A上后，A执行了一个152事务，这里就同步过来了
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

5.切换CD为新实例

上面实例迁移的场景还可以用另一种方法，已有A->B 新建个C->D 并且CD的全量数据来自于A，C跟A建立增量同步，最后把CD作为新实例
- 1.A B已开GTID并运行了好久
- 2.新建C->D，CD的新建跟第一个场景一模一样
- 3.用mysqldump备份A数据导入到C和D，不记binlog方式导入，mysqldump --set-gtid-purged=ON，这样就会导两次数据
  - 导完数据后C和D都会执行gtid_purged，数值跟A一模一样
- 4.把C搭到A上增量同步数据 master_auto_position=1
- 5.切换CD为新实例

注意
- mysqldump --set-gtid-purged=ON 备份的数据，在导入库前需要先进行reset master，不然可能报错，因为dump文件中有set global gtid_purge语句，此语句上面提到过，重新赋值的条件是gtid_executed为空
- 出现问题：导入数据后发现gtid_executed变量和mysql.gtid_executed表不一致，这个可以理解，因为上面提到过这个表的更新不是实时的，需要在滚动日志时才会更新，在mysqldump备份时会把mysql.gtid_executed表也备份了，此时备份库的这个表和备份时的gtid_executed变量就是不一致的，所以恢复数据后也会不一致。不一致就会出现一个很大的问题，如果恢复后立马重启(恢复后的库没有同步新数据，此时就算手动flush logs也不会更新mysql.gtid_executed表，比较奇怪，猜测更新mysql.gtid_executed表不是无脑把gtid_executed变量刷表，还要结合binlog记录，因为没有新数据过来时候此时binlog里根本就没有任何gtid信息，两个动作会更新1.reset master,然后重新执行set global gtid_purged 2.有新数据同步过来且flush logs，如果没有数据同步过来只手动flush logs是不会更新的)，则gtid_executed变量会立马回到mysql.gtid_executed表的数据,此时如果设置主备关系就会导致重复消费数据了
  - 解决此问题的一个方法是在mysqldump是添加上-F参数也就是–flush-logs，此参数会在备份前先进行flush logs，这时就会同步mysql.gtid_executed表，我们知道mysqldump备份实际上备份的是备份前的数据，所以备份前flush-logs，记住位点和gtid点，然后开启事务开始备份数据，所以添加-F可以解决此问题。

xtrabackup备份恢复开启GTID的库

使用xtrabackup备份恢复后需要注意的点是恢复的实例拉起后mysql.gtid_executed表中的数据可能不是最新的,由于所有数据库服务器都配置开启log_bin参数和log_slave_updates参数，因此每次执行事务不会更新mysql.gtid_executed表。xtrabackup备份过程中执行的FLUSH操作不会导致binlog rotation所以不会更新gtid_executed表。

xtrabackup备份数据恢复后不增量同步的场景
xtrabackup备份数据恢复后+备份的binlog做时间点同步的场景
xtrabackup备份数据恢复后搭建mysql复制链路做增量同步的场景

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/爱喝兽奶帝天荒/article/detail/989315