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

公众号: 云原生生态圈

发布于 2020-09-21 15:22:15

4.6K0

发布于 2020-09-21 15:22:15

文章被收录于专栏：云原生生态圈云原生生态圈

MYSQL基于GTID数据同步方式

同步原理

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

GTID常用的重要的参数变量

「gtidexecuted」 : 在当前实例上已执行过的 GTID 集合，实际上包含了所有记录到 binlog 中的事务。设置 set sqllogbin=0 后执行的事务不会生成 binlog 事件，也不会被记录到 gtidexecuted 中。执行 RESET MASTER 可以将gtid_executed变量置空。
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
「gtid_next」会话级变量，指示如何产生下一个GTID。可能的取值如下: 项目说明AUTOMATIC自动生成下一个 GTID，实现上是分配一个当前实例上尚未执行过的序号最小的 GTID。ANONYMOUS设置后执行事务不会产生GTID。显式指定的GTID可以指定任意形式合法的 GTID 值，但不能是当前 gtid_executed 中的已经包含的 GTID，否则下次执行事务时会报错。
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复制的默认值。
binlog中的GTID

同步实现方式

实现单slave通过gtid数据同步

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

创建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 # 必须项且要保证唯一

运行两个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>

创建一个复制账号

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)

创建主从关系

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

验证GTID

mysql_slave>show  global  variables like  '%gtid%';
mysql_slave>show master status;

验证数据

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的数据一致性。

删除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)

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的生命周期"。

运行一个开启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

使用--all-databases进行全量的备份
传输备份文件到新的从库上

scp $FROM_MASTER to $SLAVE

「新从库reset master」 「备份文件信息:」 备份文件里「包含了设置 gtid_purged 的语句,需要reset master，否则会出错」。

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

在从库恢复数据前需要先通过 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关闭。

从新库导入数据

mysql -h172.17.0.6 -P3306 -uroot -p < mysql-all-databackup.sql

查看导入后slave状态

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

show gloabl variables like '%gtid%';
show master status;

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 TABLE和DROP 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 是只读的，要改变状态必须
- 关闭实例
- 修改配置文件
- 重启实例