MySQL基于GTID的复制

GTID(global transaction identifiers)复制是完全基于事务的复制，即每个在主库上执行的事务都会被分配一个唯一的全局ID并记录和应用在从库上 
这种复制方式简化了建立slave和master/slave之间切换的工作，因为其完全不需要找当前执行的bin log和log中的位置完成切换

一个GTID是master上执行的任何commit事务所分配的全局唯一ID标示，其由两部分组成 
GTID = source_id:transaction_id 
Source_id代表主库的server_uuid，transaction_id代表事务按顺序提交的ID，比如第一个提交则是1，第十个提交的事务就是10GTID集合代表一组GTID

1. 当一个事务在主库提交时，该事务就被赋予了一个GTID，并记录在主库的binary log
2. 主库的binary log会被传输到从库的relay log中，从库读取此GTID并生成gtid_next系 
   统参数
3. 从库验证此GTID并没有在自己的binary log中使用，则应用此事务在从库上 
   MySQL5.6的GTID复制模式， slave必须开启bin-log和log_slave_updates参数，否则启动就报错，因为需要在binlog找到同步复制的信息（UUID:事务号）

注：开启log_slave_updates参数，是把relay-log里的日志内容再记录到slave本地的binlog里。

但在MySQL5.7里， 官方做了调整，用一张gtid_executed系统表记录同步复制的信息 
（UUID:事务号），这样就可以不用开启log_slave_updates参数，减少了从库的压力

从MySQL5.7.4版本开始， GTID会存放在mysql系统库的gtid_executed表中

1 2 3 4 5 6 7 8 9

mysql> desc gtid_executed; +----------------+------------+------+-----+---------+-------+ | Field          | Type       | Null | Key | Default | Extra | +----------------+------------+------+-----+---------+-------+ | source_uuid    | char(36)   | NO   | PRI | NULL    |       | | interval_start | bigint(20) | NO   | PRI | NULL    |       | | interval_end   | bigint(20) | NO   |     | NULL    |       | +----------------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

　　

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | + ------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000004 | 475 | | | 9eae8f34-47b6-11e7-8087- 000c298d7ee3:1-25 | +------------------+----------+--------------+------------------+-------------------------------------------+ mysql> select * from mysql.gtid_executed; +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 1 | 1 | | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 2 | 2 | ……… | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 23 | 23 | | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 24 | 24 | | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 25 | 25 | +--------------------------------------+----------------+--------------+

　　

搭建GTIB的复制

创建复制流程 
假定两个数据库实例间的主从关系已经通过传统模式创建好了 
将主库和从库都设置为read only，确保两者之间的数据都完全同步

 

1	mysql> SET @@global.read_only = ON;

　　

关闭主库和从库

1	shell> mysqladmin -uusername -p shutdown

　　

设置主从库GTID后启动 并暂时关闭slave进程

1 2 3 4 5

[mysqld] gtid-mode=on enforce-gtid-consistency=on skip-slave-start=1 Enforce-gtid-consistency参数是确保只有对gtid复制机制安全的语句才会被log

　　

重新设置主从库的复制关系

1 2 3 4 5 6

mysql> CHANGE MASTER TO MASTER_HOST = host, MASTER_PORT = port, MASTER_USER = user, MASTER_PASSWORD = password, MASTER_AUTO_POSITION = 1;

　　

启动slave进程

1	mysql> START SLAVE;

　　

关闭主库和从库的read only模式

 

1	mysql> SET @@global.read_only = OFF;

　　

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

Master上执行： mysql> insert into temp values(3,'c'); Query OK, 1 row affected (0.00 sec) Slave上执行： mysql> select * from temp; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c |   mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.44.128 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 414 Relay_Log_File: vmware1-relay-bin.000002 Relay_Log_Pos: 627 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Retrieved_Gtid_Set: 9eae8f34-47b6-11e7-8087-000c298d7ee3:1 Executed_Gtid_Set: 9eae8f34-47b6-11e7-8087-000c298d7ee3:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:

　　

 

使用GTID复制的限制条件： 
由于GTID复制是依赖于事务的，所以MySQL的一些属性不支持

当一个事务中既包含对InnoDB表的操作，也包含对非事务型存储引擎表(MyISAM)的操作时，就会导致一个事务中可能会产生多个GTID的情况；或者是当master和slave的表使用的存储引擎不一样时，都会导致GTID复制功能不正常

create table…select语句在基于语句复制的环境中是不安全的，在基于行复制 
的环境中，此语句会被拆分成两个事件，一是创建表，二是insert数据，在某 
些情况下这两个事件会被分配相同的GTID，而导致insert数据的操作被忽略， 
所以GTID复制不支持create table … select语句

create/drop temporary table语句在GTID复制环境中不能放在事务中执行， 
只能单独执行，并且autocommit要开启 
sql_slave_skip_counter语句是不支持的，如果想要跳过事务，可以使用gtid_executed变量

如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> create table temp2 select * from temp; ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT. mysql> create table temp2(id int,name varchar(10)) engine=myisam; Query OK, 0 rows affected (0.02 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into temp2 select * from temp; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update temp set name='abc'; Query OK, 3 rows affected (0.02 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> insert into temp2 select * from temp; ERROR 1785 (HY000): Statement violates GTID consistency: Updates to nontransactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.