Mysql基础——Gtid开启方法

一、Gtid简介

GTID transactions are identified by a global transaction identifier (GTID) in the form UUID:NUMBER. Every GTID transaction in a log is always preceded by a Gtid_log_event. GTID transactions can be addressed using either the GTID or using the file name and position.

Anonymous transactions do not have a GTID assigned, and MySQL ensures that every anonymous transaction in a log is preceded by an Anonymous_gtid_log_event. In previous versions, anonymous transactions were not preceded by any particular event. Anonymous transactions can only be addressed using file name and position.

 

由于Gtid需要通过数据库参数variables中开启，不然无法显示，开启需要按照顺序开启，不能直接设置为ON；

mysql> SET GLOBAL gtid_mode=ON_PERMISSIVE;

ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

 二、开启Gtid

1、主库和备库手动开启

mysql> SET GLOBAL gtid_mode=OFF_PERMISSIVE;

Query OK, 0 rows affected (0.00 sec)

 

mysql>  SET GLOBAL gtid_mode=ON_PERMISSIVE;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SET GLOBAL gtid_mode=ON;

ERROR 3111 (HY000): SET @@GLOBAL.GTID_MODE = ON is not allowed because ENFORCE_GTID_CONSISTENCY is not ON.

mysql> show variables like '%gtid%';

+----------------------------------+---------------+

| Variable_name                    | Value         |

+----------------------------------+---------------+

| binlog_gtid_simple_recovery      | ON            |

| enforce_gtid_consistency         | OFF           |

| gtid_executed_compression_period | 1000          |

| gtid_mode                        | ON_PERMISSIVE |

| gtid_next                        | AUTOMATIC     |

| gtid_owned                       |               |

| gtid_purged                      |               |

| session_track_gtids              | OFF           |

+----------------------------------+---------------+

8 rows in set (0.00 sec)

 

mysql> SET GLOBAL enforce_gtid_consistency=ON;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SET GLOBAL gtid_mode=ON;

Query OK, 0 rows affected (0.01 sec)

 

mysql> show variables like '%gtid%';

+----------------------------------+-----------+

| Variable_name                    | Value     |

+----------------------------------+-----------+

| binlog_gtid_simple_recovery      | ON        |

| enforce_gtid_consistency         | ON        |

| gtid_executed_compression_period | 1000      |

| gtid_mode                        | ON        |

| gtid_next                        | AUTOMATIC |

| gtid_owned                       |           |

| gtid_purged                      |           |

| session_track_gtids              | OFF       |

+----------------------------------+-----------+

8 rows in set (0.00 sec)

 

 

2、在master节点/etc/my.cnf中【mysqld】添加配置

vim /etc/my.cnf
enforce_gtid_consistency=on
gtid_mode=on

 

 

参考：(38条消息) 书生笔记-gtid_mode 参数解析_魏书生的技术空间的博客-CSDN博客

(38条消息) mysql数据库管理-GTID详解_数据库gtid_执笔画情ora的博客-CSDN博客