专栏首页云原生生态圈MYSQL数据同步之基于GTID事务数据同步

MYSQL数据同步之基于GTID事务数据同步

MYSQL基于GTID数据同步方式

同步原理

  1. 客户端发送DDL/DML给master上,master首先对此事务生成一个唯一的gtid,假如为uuid_xxx:1,然后立即执行该事务中的操作。主从复制的情况下,sync-binlog基本上都会设置为1,这表示在每次提交事务时将缓存中的binlog刷盘。所以,在事务提交前,gtid以及事务相关操作的信息都在缓存中,提交后它们才写入到binlog file中,然后才会被dump线程dump出去。即只有提交了的事务,gtid和对应的事务操作才会记录到binlog文件中。记录的格式是先记录gtid,紧跟着再记录事务相关的操作。
  2. 当binlog传送到relay log中后,slave上的SQL线程首先读取该gtid,并设置变量gtid_next的值为该gtid,表示下一个要操作的事务是该gtid。gtid_next是基于会话的,不同会话的gtid_next不同。
  3. 随后slave检测该gtid在自己的binlog中是否存在。如果存在,则放弃此gtid事务;如果不存在,则将此gtid写入到自己的binlog中,然后立刻执行该事务,并在自己的binlog中记录该事务相关的操作。注意,slave上replay的时候,gtid不是提交后才写到自己的binlog file的,而是判断gtid不存在后立即写入binlog file。通过这种在执行事务前先检查并写gtid到binlog的机制,不仅可以保证当前会话在此之前没有执行过该事务,还能保证没有其他会话读取了该gtid却没有提交。因为如果其他会话读取了该gtid会立即写入到binlog(不管是否已经开始执行事务),所以当前会话总能读取到binlog中的该gtid,于是当前会话就会放弃该事务。总之,一个gtid事务是决不允许多次执行、多个会话并行执行的。
  4. slave不生成GTID, slave在重放relay log中的事务时,不会自己生成gtid,所以所有的slave(无论是何种方式的一主一从或一主多从复制架构)通过重放relay log中事务获取的gtid都来源于master,并永久保存在slave上。

GTID常用的重要的参数变量

  1. 「gtidexecuted」 : 在当前实例上已执行过的 GTID 集合,实际上包含了所有记录到 binlog 中的事务。设置 set sqllogbin=0 后执行的事务不会生成 binlog 事件,也不会被记录到 gtidexecuted 中。执行 RESET MASTER 可以将gtid_executed变量置空。
  2. gtid_purgedgtidpurged表示已经purge掉的gtid。只有gtid_executed为空时才能手动设置该变量,此时会同时更新 gtidexecuted 为和 gtidpurged 相同的值。(gtidexecuted 为空意味着要么之前没有启动过基于 GTID 的复制,要么执行过RESET MASTER。执行 RESET MASTER 时同样也会把 gtidpurged 置空,即始终保持 gtidpurged 是 gtidexecuted 的子集。) 在slave上设置该项时,表示稍后启动io线程和SQL线程都跳过这些gtid,slave上设置时应该让此项的gtid集合等于master上 gtidexecuted 的值。 binlog 不可能永远驻留在服务上,需要定期进行清理(通过expire_logs_days定期清理)日志。gtid_purged系统变量(@@global.gtidpurged)用于记录本机上已经执行过,但是已经被清除了的 binlog 事务集合。它是 gtid_executed 的子集。以下类别的GTID在此gtid_purged集合中:
    • 在slave上禁用binlog提交的复制事务的GTID
    • 已写入已清除的binlog的事务的GTID
    • 由语句SET @@global.gtid_purged明确添加到集合中的GTID
  3. 「gtid_next」会话级变量,指示如何产生下一个GTID。可能的取值如下: 项目说明AUTOMATIC自动生成下一个 GTID,实现上是分配一个当前实例上尚未执行过的序号最小的 GTID。ANONYMOUS设置后执行事务不会产生GTID。显式指定的GTID可以指定任意形式合法的 GTID 值,但不能是当前 gtid_executed 中的已经包含的 GTID,否则下次执行事务时会报错。
  4. show slave status中的gtid
    1. Retrieved_Gtid_Set:在开启了gtid复制(即gtid_mode=on)时,slave在启动io线程的时候会检查自己的relay log,并从中检索出gtid集合。也就是说,这代表的是slave已经从master中复制了哪些事务过来。检索出来的gtid不会再请求master发送过来。
    2. Executed_Gtid_Set: 在开启了gtid复制(即gtid_mode=on)时,它表示已经向自己的binlog中写入了哪些gtid集合。注意,这个值是根据一些状态信息计算出来的,并非binlog中能看到的那些。
    3. Auto_Position:开启gtid时是否自动获取binlog坐标。1表示开启,这是gtid复制的默认值。
  5. binlog中的GTID
  • "注意行1"中Previous-GTIDs代表的gtid集合是曾经的gtid,换句话说是被purge掉的事务。
  • "注意行2"是两个事务的gtid信息。它们写在每个事务的前面。
  • "注意行3"设置了GTID_NEXT的值,表示读取到了该事务后,那么必须要执行的是稍后列出的这个事务。
  • "注意行6"是在所有事务执行结束时设置的,表示自动获取gtid的值。它对复制是隐身的(也就是说不会dump线程不会将它dump出去),该行的结尾也说了,这一行是mysqlbinlog添加的。

同步实现方式

实现单slave通过gtid数据同步

本文通过Docker以及mysql5.7 镜像进行基于GTID数据复制的同步实践。

  1. 创建master以及slave节点运行的配置文件
root@master:~# mkdir mysql_gtid
root@master:~# cd mysql_gtid/
root@master:~/mysql_gtid#
root@master:~/mysql_gtid# vim mysqlmaster.cnf
root@master:~/mysql_gtid# cat mysqlmaster.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-bin = mysql-bin # mysql 5.6必须项,mysql 5.7非必须项
sync-binlog = 1 # 建议项
binlog_format = row # 建议项
relay-log = relay-bin # 必须项
server-id = 20200914 # 必须项且要保证唯一
log-error = mysql.err
pid-file = mysql.pid

log-slave-updates = 1 # mysql 5.6必须项,mysql 5.7非必须项,选项会增大了从服务器的IO负载, 在MySQL5.7版本已非必须。
enforce_gtid_consistency = on # 必须项
gtid_mode = on # 必须项

root@master:~/mysql_gtid# vim mysqlslave.cnf
root@master:~/mysql_gtid# diff mysqlslave.cnf mysqlmaster.cnf
8c8
< server-id = 20200814 # 必须项且要保证唯一
---
> server-id = 20200914 # 必须项且要保证唯一
  1. 运行两个mysql实例
docker run -d --name mysqlmaster -v ${PWD}/mysqlmaster.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=admin123 -p 33306:3306 mysql:5.7 # for master
docker run -d --name mysqlslave -v ${PWD}/mysqlslave.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=admin123 -p 33307:3306 mysql:5.7 # for slave

# 服务运行验证
root@master:~/mysql_gtid# docker exec -it mysqlslave mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
...(略)
mysql> show variables like '%server_id%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| server_id      | 20200914 |
| server_id_bits | 32       |
+----------------+----------+
2 rows in set (0.02 sec)

# 为了区分是哪个数据库,修改mysql的终端提示符
mysql> prompt mysql_slave>;
PROMPT set to 'mysql_slave>'
mysql_slave>
mysql_slave>

root@master:~/mysql_gtid# docker exec -it mysqlmaster mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
...(略)
mysql> show variables like '%server_id%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| server_id      | 20200914 |
| server_id_bits | 32       |
+----------------+----------+
2 rows in set (0.02 sec)

# 为了区分是哪个数据库,修改mysql的终端提示符
mysql> prompt mysql_master>;
PROMPT set to 'mysql_master>'
mysql_master>
mysql_master>

  1. 创建一个复制账号
mysql_master> GRANT SUPER,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO repl@'%' identified by 'repl';
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql_master> flush privileges;
Query OK, 0 rows affected (0.08 sec)

  1. 创建主从关系
mysql_slave>change master to master_host='172.17.0.3',master_port=3306,master_auto_position=1;
Query OK, 0 rows affected (0.02 sec)

mysql_slave>start slave user='repl' password='repl';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql_slave>show slave status\G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 172.17.0.3
                 Master_User: repl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000003
         Read_Master_Log_Pos: 1443
              Relay_Log_File: relay-bin.000003
               Relay_Log_Pos: 1656
       Relay_Master_Log_File: mysql-bin.000003
            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: 1443
             Relay_Log_Space: 3073432
             Until_Condition: None
              Until_Log_File:
               Until_Log_Pos: 0
          Master_SSL_Allowed: No
          Master_SSL_CA_File:
          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: 20200914
                 Master_UUID: f0b1184d-f7d2-11ea-a7f5-0242ac110003
            Master_Info_File: /var/lib/mysql/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: f0b1184d-f7d2-11ea-a7f5-0242ac110003:1-10
           Executed_Gtid_Set: f0b1184d-f7d2-11ea-a7f5-0242ac110003:1-10
               Auto_Position: 1
        Replicate_Rewrite_DB:
                Channel_Name:
          Master_TLS_Version:
1 row in set (0.01 sec)

ERROR:
No query specified
  1. 验证GTID
mysql_slave>show  global  variables like  '%gtid%';
mysql_slave>show master status;
  1. 验证数据
mysql_master>use mysql_gtid_test;
Database changed
mysql_master>create table if not exists t1(id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) not null );
Query OK, 0 rows affected (0.05 sec)

mysql_master>insert into t1 values (1,"marionxue");
Query OK, 1 row affected (0.03 sec)

# 同步验证
mysql_slave>show slave status
   -> \G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 172.17.0.3
                 Master_User: repl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000003
         Read_Master_Log_Pos: 2179
              Relay_Log_File: relay-bin.000003
               Relay_Log_Pos: 2392
       Relay_Master_Log_File: mysql-bin.000003
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
...
mysql_slave>use mysql_gtid_test;
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_slave>select * from t1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | marionxue |
+----+-----------+
1 row in set (0.01 sec)

添加新slave到gtid复制集群

**线上环境,往往会定期删除一部分binlog。咱们模似这种情况下从库制作。**当master没有删除过任何binlog时,可以方便地向复制结构中添加新的slave,因为slave会复制所有的binlog到自己relay log中并replay。**即类似上节的命令即可 change master to master_host='xxx',master_port=3xx,master_autoposition=1;

当master删除过一部分binlog后,在向复制结构中添加新的slave时,必须先获取到master binlog中当前已记录的第一个gtid之前的所有数据,然后恢复到slave上。只有slave上具有了这部分基准数据,才能保证和master的数据一致性。

  1. 删除binlog
mysql_master>show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |   3071539 |
| mysql-bin.000003 |      2226 |
| mysql-bin.000004 |       194 |
+------------------+-----------+
4 rows in set (0.02 sec)

mysql_master>purge master logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.01 sec)

mysql_master>show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000004 |       194 |
+------------------+-----------+
1 row in set (0.02 sec)
  1. GTID从库搭建

GTID从库数据同步

假如当前master的gtid为A3,已经purge掉的gtid为"1-->A1",备份到slave上的数据为1-A2部分。

  • 如果A1 = 0,表示master的binlog没有被Purge过。slave可以直接开启gtid复制,但这样可能速度较慢,因为slave要复制所有binlog。也可以将master数据备份到slave上,然后设置 gtid_purged 跳过备份结束时的gtid,这样速度较快。
  • 如果A1 != 0,表示master上的binlog中删除了一部分gtid。此时slave上必须先从master处恢复purge掉的那部分日志对应的数据。上图中备份结束时的GTID为A2。然后slave开启复制,唯一需要考虑的是"是否需要设置 gtid_purged 跳过一部分gtid以避免重复执行"。

备份数据到slave上,方式可以是mysqldump、冷备份、xtrabackup备份都行。由于gtid复制的特性,所需要的操作都很少,也很简单,前提是理解了"gtid的生命周期"。

  1. 运行一个开启gtid的新的从库
mysqldump --all-databases --default-character-set=utf8 --triggers -R -q --events --master-data=2 --single-transaction --host=172.17.0.3 --port=3306 --user=root -padmin123 > mysql-all-databackup.sql
  1. 使用--all-databases进行全量的备份
  2. 传输备份文件到新的从库上
scp $FROM_MASTER to $SLAVE
  1. 「新从库reset master」 「备份文件信息:」 备份文件里「包含了设置 gtid_purged 的语句,需要reset master,否则会出错」

含有GTID信息的备份文件内容

  1. 在从库恢复数据前需要先通过 reset master 清空 gtid_executed 变量**
show master status;
show gloabl variables like '%gtid%';
reset master;
show global variables like '%gtid%';
show master status;

「说明:若没有reset master,执行设置 GTIDPURGED 的 SQL 时会出错误GLOBAL.GTID****PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty」: ❞

如果不希望备份文件中生成设置GTID_PURGED的SQL,可以给 mysqldump传入--set-gtid-purged=OFF关闭。

  1. 从新库导入数据
mysql -h172.17.0.6 -P3306 -uroot -p < mysql-all-databackup.sql
  1. 查看导入后slave状态

❝导入备份数据后,可注意到gtidexecuted, gtidpurged的值都已更改。 ❞

show gloabl variables like '%gtid%';
show master status;
  1. change master
change master to master_host='172.17.0.3',master_user='repl',master_auto_position=1;

同步的优缺点

GTID复制是针对事务来说的,一个事务只对应一个GTID,好多的限制就在于此。

优点

  • GTID带来最方便的一点就是主从复制的搭建过程了。它跟异步复制、半同步复制类似,只不过不再利用传统复制模式的binlog文件和position号了,而是在从库“change master to”时使用master_auto_position=1的方式进行搭建,这就让操作变得更加方便和可靠。
  • 根据GTID可以知道事务最初是在哪个实例上提交的
  • GTID的存在方便了Replication的Failover

缺点

  • 非事务性存储引擎的更新: 同一事务中不能同时更新事务表与非事务表(MyISAM),建议都选择 Innodb 作为默认的数据库引擎
  • CREATE TABLE … SELECT语句: create table table_name select * from table_name对于基于语句的复制是不安全的。使用基于行的复制时,此语句实际上记录为两个单独的事件 - 一个用于创建表,另一个用于将源表中的行插入刚刚创建的新表中。当在事务中执行此语句时,在某些情况下,这两个事件可能会接收相同的事务标识符,这意味着slave将跳过包含插入的事务。因此,使用基于GTID的复制时不支持CREATE TABLE … SELECT
  • 临时表: 使用GTID时(即,enforce_gtid_consistency系统变量设置为ON时),事务,过程,函数和触发器内不支持CREATE TEMPORARY TABLEDROP TEMPORARY TABLE语句。可以在启用GTID的情况下使用这些语句,但仅限于任何事务之外,并且仅使用autocommit = 1。
  • 防止执行不受支持的语句: 要防止执行会导致基于GTID的复制失败的语句,必须在启用GTID时使用–enforce-gtid-consistency选项启动所有服务器。
  • 跳过事务: 使用GTID时不支持sql slave skip counter。如果您需要跳过事务,请使用master的gtid_executed变量的值; 有关详细信息,https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-failover.html#replication-gtids-failover-empty
  • GTID 模式实例和非GTID模式实例是不能进行复制的,要求非常严格,要么都是GTID,要么都不是
  • gtid_mode 是只读的,要改变状态必须
    • 关闭实例
    • 修改配置文件
    • 重启实例

参考骏马金龙老师博客:https://www.junmajinlong.com/

本文分享自微信公众号 - 云原生生态圈(CloudNativeEcoSystem),作者:Marionxue

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-09-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Nginx专辑|05 -如何使用Nginx配置正向代理

    在之前的编译nginx的基础上,我们对nginx二进制增加新的模块ngx_http_proxy_connect_module[1]

    公众号: 云原生生态圈
  • Cortex: 高可用和水平扩展Prometheus监控系统

    Prometheus已成为cloud-native世界中的默认监控应用程序和系统。对于真实使用案例,Prometheus应该是高可用的,这是有挑战的。一旦在高可...

    公众号: 云原生生态圈
  • 带命令行控制台的轻量级日志分析工具-GoAccess

    对于想在短时间内快速的生成小站点访问情况的统计数据,一般会选择使用sed、awk、grep三剑客,但是对于要对数据的展示就需要填充到已有开发面板中,比较耗时费力...

    公众号: 云原生生态圈
  • GTID,你了解多少?

    全局事务标识符(Global Transaction Identifier,GTID)是MySQL5.6版本开始在主从复制方面推出的重要特性,它是一个已提交事务...

    MySQL数据库技术栈
  • 大型网站架构系列:电商网站架构案例(2)

    电网网站架构案例系列的第二篇文章。主要讲解网站架构分析,网站架构优化,业务拆分,应用集群架构,多级缓存,分布式Session。

    数商云
  • 叮铃哐当,一大波机器人开始入侵厨房

    安妮 编译自 Wired 量子位 出品 | 公众号 QbitAI 有人在厨房忙碌着,等你回家吃饭。 Gordon在煮咖啡,Sally正忙着往沙拉里面加你喜欢的千...

    量子位
  • 【玩转linux命令】风一样的awk

    自Aho、Weinberger、Kernighan三位大神发明了awk以来,使用者玩出了各种花样,今天来介绍一下awk的内置函数; 主要分为以下三类:算数函数...

    软测小生
  • SAP S/4HANA生产订单抬头字段的维护

    生产订单的初始化状态 is CRTD & once released will have REL. In addition to these there are...

    Jerry Wang
  • python动态柱状图图表可视化:历年软科中国大学排行

    本来想参照:https://mp.weixin.qq.com/s/e7Wd7aEatcLFGgJUDkg-EQ搞一个往年编程语言动态图的,奈何找不到数据,有数据...

    绝命生
  • 使用node-inspector调试nodejs程序<nodejs>

    1.npm install -g node-inspector  // -g 导入安装路径到环境变量 一般是c盘下AppData目录下 2.node-inspe...

    用户1148881

扫码关注云+社区

领取腾讯云代金券