Mysql GTID

GTID是什么?

    GTID 全称A global transaction identifier 全局事物id,是MASTER创建的和事物相匹配的ID号;

  1. 它不仅在本机上是唯一的,而且在其他服务器上也是唯一的;
  2. gitd与事物一一对应
  3. master每次commit事物的时候就会自动创建gtid

     gitd格式是  GTID = source_id:transaction_id (如 04038bcc-fd0c-11e7-9cc5-000c29db6599:1-2)

     source_id 一般是发起事物的uuid, 保存在auto.cnf文件中;

     transaction_id是事物id, 1-2代表第二个事物;第1-n代表n个事物

 

为什么要用GTID?

              在主从复制中,尤其是半同步复制中, 由于Master 的dump进程一边要发送binlog给Slave,一边要等待Slave的ACK消息,这个过程是串行的,即前一个事物的ACK没有收到消息,那么后一个事物只能排队候着; 这样将会极大地影响性能;有了GTID后,SLAVE就直接可以通过数据流获得GTID信息,而且可以同步;

             另外,主从故障切换中,如果一台MASTER down,需要提取拥有最新日志的SLAVE做MASTER,这个是很好判断,而有了GTID,就只要以GTID为准即可方便判断;而有了GTID后,SLAVE就不需要一直保存这bin-log 的文件名和Position了;只要启用MASTER_AUTO_POSITION 即可

             当MASTER crash的时候,GTID有助于保证数据一致性,因为每个事物都对应唯一GTID,如果在恢复的时候某事物被重复提交,SLAVE会直接忽略;

什么时候用GTID?

          一般在主从复制的场景下,如果只有单台就没必要使用;

           GTID不支持: CREATE TABLE...SELECT语句,因为一个事物一个GTID,这个语句有2个事物,一个CREATE,一个INSERT,所以不支持

             CRETAE TEMPORARY TABLE,或者DROP TEMPORARY TABLE不支持

             事物和非事物混合使用; 不支持

如何配置GTID?

       所有主从节点都执行 set global enforce_gtid_consistency=WARN;,mysql 5.7.6之前只有ON和OFF,之后有WARN,即允许执行GTID限制的语句,但是会生成warnings,如果有warnings就要处理这些不支持的语句;否则容易造成数据不一致的问题产生;确认没有告警后,继续执行

mysql> set global enforce_gtid_consistency=WARN;
Query OK, 0 rows affected (0.00 sec)
mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)

所有节点,开启gtid模式,gtid_mode有4中模式:

  1. OFF 不产生GTID, Slave只接受不带GTID的事务

  2. OFF_PERMISSIVE 不产生GTID, Slave即接受不带GTID的事务也接受带GTID的事务

  3. ON_PERMISSIVE 产生GTID, Slave即接受不带GTID的事务也接受带GTID的事务

  4. ON 产生GTID, Slave只接受带GTID的事务

模式之间的切换只能逐步切换,否则会报错:

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 simultaneouslyon all servers. See the Manual for instructions.

mysql>  set global gtid_mode=OFF_PERMISSIVE;
Query OK, 0 rows affected (0.02 sec)

mysql> set global gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.02 sec)

mysql> set global gtid_mode=ON;
Query OK, 0 rows affected (0.02 sec)

查看设置

mysql> show global variables like '%gtid%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                        |
| enforce_gtid_consistency         | ON                                        |
| gtid_executed                    | 04038bcc-fd0c-11e7-9cc5-000c29db6599:1-16 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      |                                           |
| session_track_gtids              | OFF                                       |
+----------------------------------+-------------------------------------------+

重新配置SLAVE: (在所有SLAVE上这行)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_auto_position=1;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql>

配置完成

如何查看当前GTID?

          1,Slave上执行 show slave status 

mysql>  show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.8.135.149
                  Master_User: repl
                  Master_Port: 3358
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000036
          Read_Master_Log_Pos: 194
               Relay_Log_File: localhost-relay-bin.000017
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000036
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                       ..........
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 04038bcc-fd0c-11e7-9cc5-000c29db6599:1-16
            Executed_Gtid_Set: 04038bcc-fd0c-11e7-9cc5-000c29db6599:1-16
                Auto_Position: 1
         Replicate_Rewrite_DB:

        2, master上执行:show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000036 |      584 |              |                  | 04038bcc-fd0c-11e7-9cc5-000c29db6599:1-17 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

        3, show binlog events

mysql> show binlog events in 'mysql-bin.000036';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000036 |   4 | Format_desc    |    135149 |         123 | Server ver: 5.7.19-log, Binlog ver: 4                              |
| mysql-bin.000036 | 123 | Previous_gtids |    135149 |         194 | 04038bcc-fd0c-11e7-9cc5-000c29db6599:1-16                          |
| mysql-bin.000036 | 194 | Gtid           |    135149 |         259 | SET @@SESSION.GTID_NEXT= '04038bcc-fd0c-11e7-9cc5-000c29db6599:17' |
| mysql-bin.000036 | 259 | Query          |    135149 |         332 | BEGIN                                                              |
| mysql-bin.000036 | 332 | Table_map      |    135149 |         380 | table_id: 108 (apple.a)                                            |
| mysql-bin.000036 | 380 | Write_rows     |    135149 |         553 | table_id: 108 flags: STMT_END_F                                    |
| mysql-bin.000036 | 553 | Xid            |    135149 |         584 | COMMIT /* xid=61 */                                                |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+

       4, 查看binlog

#180123 19:24:10 server id 135149  end_log_pos 194 CRC32 0x37c62cd3     Previous-GTIDs
# 04038bcc-fd0c-11e7-9cc5-000c29db6599:1-16
# at 194
#180123 19:32:09 server id 135149  end_log_pos 259 CRC32 0x929f90c1     GTID [commit=no]
SET @@SESSION.GTID_NEXT= '04038bcc-fd0c-11e7-9cc5-000c29db6599:17'/*!*/;
# at 259
#180123 19:32:09 server id 135149  end_log_pos 332 CRC32 0xf9df8eb9     Query   thread_id=6     exec_time=0     error_code=0

 

展开阅读全文

没有更多推荐了,返回首页