mysql数据库管理-GTID详解

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

一、GTID概述

1 sql线程执行的事件也可以通过log_slave_updates系统变量来决定是否写入自己的二进制文件中这是可以用于级联复制的场景。
  GTID是MYSQL5.6新增的特性GTIDGlobal Transaction Identifier全称为全局事务标示符,用以数据库实例事务唯一标识其组成主要是source_id和transaction_id 即GTID = source_id:transaction_id。其中source_id是数据库启动自动生成的数据库实例唯一标识保存在auto.cnf中而transaction_id则是事务执行的序列号。
二、GTID优缺点
  优点
    复制安全性更高一个事务在每个实例上只执行一次
    故障切换简单可通过设置MASTER_AUTO_POSITION=1而非master_log_file和master_log_pos来建立主从关系
    可根据GTID确定事务最早提交的实例
  缺点
    组复制中必须要求统一开启GTID或者关闭GTID
    不支持复制create table table_name select ... from table_name_xx ;
    不支持create temporary table和drop temporary table
    不支持sql_slave_skip_counter可通过set global gtid_next='' 跳过
    从库和主库都必须设置log_slave_updates
三、GTID工作原理
  1、master更新数据时会在事务前产生GTID一同记录到binlog日志中。
  2、slave端的i/o 线程将变更的binlog写入到本地的relay log中。
  3、sql线程从relay log中获取GTID然后对比slave端的binlog是否有记录。
  4、如果有记录说明该GTID的事务已经执行slave会忽略。
  5、如果没有记录slave就会从relay log中执行该GTID的事务并记录到binlog。
  6、在解析过程中会判断是否有主键如果没有就用二级索引如果没有就用全部扫描。
四、GTID开启和关闭
  gtid_mode=ON(必选)
  log_bin=ON(必选)
  log-slave-updates=ON(必选)
  enforce-gtid-consistency(必选)
  log-bin = /home/mysql/mysql-bin必选
  binlog_format = MIXED必选mixed或者row
  ##  
  change master to master_host = 'ipaddr',master_port = 3306,master_user = 'username',master_password='password',master_auto_position = 1;
五、GTID适用场景
  1、搭建高可用架构方便主从切换后新的从库重新指定主库例如一主二从的结构A为mater,B为SlaveC为SlaveA宕机切换到B后C重新指定主库为B
  2、不经常使用create table table_name select * from table_name/create temporary table/update t1,t2 where ...这种语句的场合
六、GTID相关参数

参数comment
gtid_executed执行过的所有GTID可通过reset master重置
gtid_purged丢弃掉的GTID设置后从而导致slave不会再去master请求这些GTIDs并且Executed_Gtid_Set为空时才可以设置此值
gtid_modegtid模式
gtid_nextsession级别的变量下一个gtid
gtid_owned正在运行的gtid
enforce_gtid_consistency保

1 什么事GTID

    全局事务标识符GTID的英文全称为Global Transaction Identifier是在整个复制环境中对一个事务的唯一标识。它是MySQL 5.6加入的一个强大特性目的在于能够实现主从自动定位和切换而不像以前需要指定文件和位置。使用GTID复制时在主库上提交事务时创建事务对应的GTID从库在应用中继日志时用GTID识别和跟踪每个事务。在启动新从库或因故障转移到新主库时可以使用GTID来标识复制的位置极大地简化了这些任务。由于GTID的复制完全基于事务因此只要在主库上提交的所有事务也在从库上提交两者之间的一致性就能得到保证。GTID支持基于语句或基于行的复制格式但为了获得最佳效果MySQL建议使用基于行的格式。GTID始终保留在主库和从库上这意味着可以通过检查它的二进制日志来确定应用源于哪一个从库的何种事务。而且一旦在指定库上提交了具有给定GTID的事务则该库将忽略具有相同GTID的任何后续事务。因此在主库上提交的事务只会在从库上应用一次这也有助于保证一致性。

1 GTID的格式与存储

1单个GTID

GTID与主库上提交的每个事务相关联。此标识符不仅对发起事务的库是唯一的而且在给定复制拓扑结构中的所有库中都是唯一的。GTID是由冒号分隔的一对坐标来表示的例如
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:23​​
前一部分是主库的server_uuid后面一部分是主库上按提交事务的顺序确定的序列号提交的事务序号从1开始。上面的GTID表示具有8eed0f5b-6f9b-11e9-94a9-005056a57a4e的服务器上提交的第23个事务具有此GTID。MySQL 5.6后使用自动生成的128位server_uuid以避免冲突。数据目录下的auto.cnf文件用来保存server_uuid。MySQL启动的时候会读取auto.cnf文件如果没有读取到则会生成一个server_id并保存到auto.cnf文件中。
在主库上提交客户端事务时如果事务已写入二进制日志则会为其分配新的GTID保证为客户事务生成单调递增且没有间隙的GTID。如果未将客户端事务写入二进制日志例如因为事务已被过滤掉或者事务是只读的则不会在源服务器上为其分配GTID。从库上复制的事务保留与主库上事务相同的GTID。即使从库上未开启二进制日志GTID也会被保存。MySQL系统表mysql.gtid_executed用于保存MySQL服务器上应用的所有事务的GTID但存储在当前活动二进制日志文件中的事务除外。
GTID的自动跳过功能意味着一旦在指定服务器上提交了具有给定GTID的事务则该服务器将忽略使用相同GTID执行的任何后续事务这种情况是可能发生的如手工设置了gtid_next时。这有助于保证主从一致性因为在主库上提交的事务在从库上应用不超过一次。如果具有给定GTID的事务已开始在服务器上执行但尚未提交或回滚则任何在该服务器上启动具有相同GTID的并发事务都将被阻止。服务器既不执行并发事务也不将控制权返回给客户端。一旦先前的事务提交或回滚就可以继续执行在同一个GTID上被阻塞的并发会话。如果是回滚则一个并发会话继续执行事务并且在同一个GTID上阻塞的任何其他并发会话仍然被阻止。如果是提交则所有并发会话都将被阻止并自动跳过事务的所有语句。mysqlbinlog输出中的GTID_NEXT包含事务的GTID用于标识复制中的单个事务。

2 GTID跳过功能测试

C:\Users\Administrator>net stop mysql80
mysql80 服务正在停止.
mysql80 服务已成功停止。


C:\Users\Administrator>net start  mysql80
mysql80 服务正在启动 .
mysql80 服务已经启动成功。

mysql> show master status \G
*************************** 1. row ***************************
             File: log.000018
         Position: 429
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 55390eba-d320-11eb-8e31-00ffaabbccdd:1
1 row in set (0.00 sec)

mysql> set gtid_next='55390eba-d320-11eb-8e31-00ffaabbccdd:1';
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> show master status \G
*************************** 1. row ***************************
             File: log.000018
         Position: 156
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed                    |           |
| gtid_executed_compression_period | 0         |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
9 rows in set, 1 warning (0.01 sec)

mysql> set gtid_next='55390eba-d320-11eb-8e31-00ffaabbccdd:1';
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql>

实验2 相同的gtid 事务1提交事务2 被跳过。

准备两个脚本

set gtid_next='55390eba-d320-11eb-8e31-00ffaabbccdd:2';

begin;

delete from t1 where id=1;

select sleep(10);

COMMIT;

set gtid_next=automatic

set gtid_next='55390eba-d320-11eb-8e31-00ffaabbccdd:2';

begin;

delete from t2 where id=1;

select sleep(10);

COMMIT;

set gtid_next=automatic

mysql> create table t2 as select * From t1;
ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a differe
nt value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanat
ion. Current @@SESSION.GTID_NEXT is '55390eba-d320-11eb-8e31-00ffaabbccdd:1'.
mysql> set gtid_next=automatic;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 as select * From t1;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 

执行

mysql -uroot -123 test< 1.sql

mysql -uroot -123 test< 2.sql  同时执行。  ---事务1提交事务2倍跳过。

实验2 相同的gtid 事务1回滚事务2 被提交。

set gtid_next='55390eba-d320-11eb-8e31-00ffaabbccdd:2';

begin;

delete from t1 where id=1;

select sleep(10);

COMMIT;

set gtid_next=automatic

set gtid_next='55390eba-d320-11eb-8e31-00ffaabbccdd:2';

begin;

delete from t2 where id=1;

select sleep(10);

COMMIT;

set gtid_next=automatic

在sleep期间执行s2事务1回滚前事务2被阻塞事务1 回滚事务2提交。

GTID集合

表示方法

55390eba-d320-11eb-8e31-00ffaabbccdd:1-1000:1002,1003-1000011

GTID集可以包括单个GTID和GTID范围的任意组合甚至它可以包括源自不同服务器的GTID。例如一个存储在从库gtid_executed系统变量中的GTID集可能如下
565a6b0a-6f05-11e9-b95c-005056a5497f:1-20,
​​​​​8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-321​​

mysql> show variables like '%gtid_executed%';
+----------------------------------+------------------------------------------+
| Variable_name                    | Value                                    |
+----------------------------------+------------------------------------------+
| gtid_executed                    | 55390eba-d320-11eb-8e31-00ffaabbccdd:1-2 |
| gtid_executed_compression_period | 0                                        |
+----------------------------------+------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

表示该从库已从两个主库应用了事务也有可能是在从库执行的写操作。当从库变量返回GTID集时UUID按字母顺序排列并且数值间隔按升序合并。
MySQL服务器中很多地方都用到GTID集例如gtid_executed和gtid_purged系统变量存储的值是GTID集START SLAVE的UNTIL SQL_BEFORE_GTIDS和UNTIL SQL_AFTER_GTIDS子句的值是GTID集内置函数GTID_SUBSET()和GTID_SUBTRACT()需要GTID集作为输入等。

4 GTID表mysql.gtid_executed

mysql> desc mysql.gtid_executed
    -> ;
+----------------+----------+------+-----+---------+-------+
| Field          | Type     | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| source_uuid    | char(36) | NO   | PRI | NULL    |       |
| interval_start | bigint   | NO   | PRI | NULL    |       |
| interval_end   | bigint   | NO   |     | NULL    |       |
+----------------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
表记录的是服务器上已经执行过的GTID集合。

mysql.gtid_executed表供MySQL服务器内部使用。

当从库禁用二进制日志时用该表记录GTID或者当二进制日志丢失时可从该表查询GTID状态。RESET MASTER命令将重置mysql.gtid_executed表清空表数据。和所有系统表一样用户不要修改该表。
仅当gtid_mode设置为ON或ON_PERMISSIVE时GTID才存储在mysql.gtid_executed表中。存储的GTID值取决于是否启用二进制日志
•  对于从库如果禁用了二进制日志记录skip-log-bin或log_slave_updates则服务器将在该表中存储每个事务的GTID。
•  如果启用了二进制日志记录当刷新二进制日志或重启服务器时服务器都会将当前二进制日志中所有事务的GTID写入mysql.gtid_executed表。这种情况适用于主库或启用了二进制日志记录的从库。
启用二进制日志记录时mysql.gtid_executed表并不保存所有已执行事务GTID的完整记录该信息由gtid_executed全局系统变量的值提供每次提交事务后更新。如果服务器意外停止则当前二进制日志文件中的GTID集不会保存在mysql.gtid_executed表中。在MySQL实例恢复期间这些GTID将从二进制日志文件添加到表中。即使服务器处于只读模式MySQL服务器也可以写入mysql.gtid_executed表这样二进制日志文件仍然可以在只读模式下轮转。如果无法访问mysql.gtid_executed表时进行二进制日志文件轮转则继续使用二进制日志文件存储GTID同时在服务器上记录警告信息

前面已经提到mysql.gtid_executed表的记录可能并不是完整的已执行GTID而且有不可访问的可能性例如误删除此表因此建议始终通过查询@@global.gtid_executed来确认MySQL服务器的GTID状态而不是查询mysql.gtid_executed表。mysql.gtid_executed表可能随着事务量的增多而快速膨胀存储了源自同一个服务器的大量不同的单个GTID这些GTID构成一个范围例如

为了节省空间MySQL服务器定期压缩mysql.gtid_executed表方法是将每个这样的行集替换为跨越整个事务标识符间隔的单行如下所示

通过设置gtid_executed_compression_period系统变量可以控制压缩表之前允许的事务数从而控制压缩率。此变量的默认值为1000指的是在每1000次事务之后执行表的压缩。把gtid_executed_compression_period设置为0将不执行压缩。注意启用二进制日志时不使用gtid_executed_compression_period的值并在每个二进制日志轮转时压缩mysql.gtid_executed表。mysql.gtid_executed表的压缩由名为thread/sql/compress_gtid_table的专用前台线程执行。此线程未在SHOW PROCESSLIST的输出中列出但可以从performance_schema.threads中查询到

通常该线程都处于暂停状态只有当满足条件时被唤醒如达到gtid_executed_compression_period或发生了二进制日志轮转如flush logs等时。
 

1 reset master对 master 主机GTID的严重影响。

从库上执行reset master只是清空从库的gtid_executed随着复制的继续其gtid_executed的值也将随之变化对复制和主从数据一致性没有影响。下面继续实验看一下在主库上执行reset master会产生哪些影响。

6在主库上执行以下语句

 

7在上一步执行期间开启一个新会话在主库上执行reset master。
8查看从库的复制状态。从show slave status的输出中可以看到复制的I/O线程已停止并报以下错误

 

由于主库正在执行事务中间进行了reset master从库无法读取主库的二进制日志而报错。更有甚之这些二进制日志的丢失是永久性的结果很可能需要从头重建复制。由此实验得出的结论是作为一条基本原则不要随意在主库上执行reset master这样做极有可能导致复制停止或造成主从数据不一致等严重后果而且不易恢复。

6 GTID生命周期

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
标签: mysql数据库
返回列表

上一篇：Linux常用命令——smbclient命令

下一篇：《Buildozer打包实战指南》实战打包numpy

“mysql数据库管理-GTID详解” 的相关文章

Windows安装mysql详细步骤（通俗易懂，简单上手）_windows安装mysql1年前 (2023-02-02)
数据库大作业-学生宿舍管理系统1年前 (2023-02-02)
java连接informix数据库1年前 (2023-02-02)
MySQL读写分离1年前 (2023-02-02)
如何理解MySQL的索引？1年前 (2023-02-02)
MySQL的锁机制：MyISAM 表锁、InnoDB行锁1年前 (2023-02-02)
Web开发学习之路--Eclipse＋Tomcat＋mysql之初体验1年前 (2023-02-02)
MySQL主备切换1年前 (2023-02-02)
mysql查看、修改数据库字符集1年前 (2023-02-02)
mysql基础1年前 (2023-02-02)

阿里云国际版