mysql gtid 主从_基于GTID模式MySQL主从复制

基于GTID模式MySQL主从复制

GTID复制原理：

基于GTID的复制是MySQL 5.6后新增的复制方式

GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID.

在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量position进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致.

而基于GTID的复制中, 从库会告知主库已经执行的事务的GTID的值, 然后主库会将所有未执行的事务的GTID的列表返回给从库. 并且可以保证同一个事务只在指定的从库执行一次.

GTID是由server_uuid和事务id组成，格式为：GTID=server_uuid:transaction_id。server_uuid是在数据库启动过程中自动生成，每台机器的server-uuid不一样。

uuid存放在数据目录的auto.conf文件中，而transaction_id就是事务提交时系统顺序分配的一个不会重复的序列号。

GTID的好处：

(1)GTID使用master_auto_position=1代替了binlog和position号的主从复制搭建方式，相比binlog和position方式更容易搭建主从复制。

(2)GTID方便实现主从之间的failover，不用一步一步的去查找position和binlog文件。

GTID模式复制搭建过程中注意事项：

主从需要设置如下参数(一般直接在配置文件/etc/my.cnf下直接添加)：

a、主库配置：

gtid_mode=on

enforce_gtid_consistency=on

log_bin=on

server-id=33062200(主从不能相同)

binlog_format=row

b、从库配置：

gtid_mode=on

enforce_gtid_consistency=on

log_slave_updates=1

server-id=33062211(主从不能相同)

开启GTID需要启用这三个参数：

#GTID

gtid_mode= on

enforce_gtid_consistency = 1

log_slave_updates = 1

主库上操作：

root@sakila 15:10: [mysql]>create user 'repluser'@'192.168.112.%' identified by 'xxxxxx';

root@sakila 15:10: [mysql]> grant replication slave on *.* to 'repluser'@'192.168.112.%';

主库逻辑备份：

mysqldump -uroot -hlocalhost -p --single-transaction --master-data=2 -A >all.sql

从库上操作：将主库上导出的all.sql导入从库

mysql -uroot -hlocalhost -p

如果出现如下错误：

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

则在从库命令行下执行：reset master

root@sakila 15:20:[mysql]> reset master;

跟普通模式一样，执行change master语句，只不过其中的binlog和position换成master_auto_position=1

CHANGE MASTER TO

MASTER_HOST='192.168.112.220',

MASTER_USER='repluser',

MASTER_PASSWORD='xxxxxx',

MASTER_PORT=3306,

MASTER_AUTO_POSITION=1;

启动slave复制服务

start slave;

查看主从复制状态：

root@sakila 15:23: [mysql]>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:192.168.112.220Master_User: repluser

Master_Port:3306Connect_Retry:60Master_Log_File:on.000002Read_Master_Log_Pos:2538363Relay_Log_File: localhost-relay-bin.000004Relay_Log_Pos:2031371Relay_Master_Log_File:on.000002Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:2538363Relay_Log_Space:2031582Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:33060220Master_UUID: 763c7ef3-5977-11e8-ae7a-000c2936b80f

Master_Info_File: mysql.slave_master_info

SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State: Slave hasread all relay log; waiting formore updates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 763c7ef3-5977-11e8-ae7a-000c2936b80f:1393-6926Executed_Gtid_Set: 763c7ef3-5977-11e8-ae7a-000c2936b80f:1-6926Auto_Position:1Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:1 row in set (0.00 sec)

查主从状态

有上图可知：

Slave_IO_Running: Yes

lave_SQL_Running: Yes

复制主从复制状态OK，进一步查看通过Executed_Gtid_Set查看执行过的GTID：

root@sakila 15:38: [mysql]> show master status;

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

| File | Position | Binlog_Do_sakila | Binlog_Ignore_sakila | Executed_Gtid_Set |

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

| on.000002 | 3200431 | | | 763c7ef3-5977-11e8-ae7a-000c2936b80f:1-8730 |

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

1 row in set (0.00 sec)

root@sakila 15:38: [mysql]>

在MySQL5.7版本之后，gtid_executed这个值持久化了，在MySQL库下新增了一张表gtid_executed

root@sakila 15:38: [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)

root@sakila 15:41: [mysql]> select * from gtid_executed;

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

| source_uuid | interval_start | interval_end |

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

| 763c7ef3-5977-11e8-ae7a-000c2936b80f | 1 | 6 |

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

1 row in set (0.00 sec)

该表会记录执行的GTID集合信息，因为有了该表，就不用再像MySQL 5.6版本时，必须开启log_slave_updates参数，从库才可以进行赋值。

GTID信息会保存在gtid_executed表中，可以关闭从库的binlog，节约binlog的记录开销。在执行reset master时，会清空表内所有的数据。

而MySQL 5.7还有一个参数gtid_executed_compresssion_period参数，用来控制gtid_executed表的压缩，该参数默认值为1000，意思是表压缩在执行完1000个事务之后开始。

root@sakila 15:47: [mysql]> show variables like '%gtid_executed%';

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

| Variable_name | Value |

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

| gtid_executed_compression_period | 1000 |

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

1 row in set (0.00 sec)

从MySQL5.7.6开始，gid_mode支持动态修改，gtid_mode可取值为：

OFF-------不支持GTID事务

OFF_PERMISSIVE----新的事务是匿名的，同时允许复制的事务可以是GTID，也可以是匿名的

ON_PERMISSIVE---- 新的事务使用GTID,同时允许复制的事务可以是GTID，也可以是匿名的

ON------支持GITD的事务

在线上环境中，有可能把传统复制改为GTID的复制模式的需求，这里特意强调一点，gtid_mode虽然支持动态修改，但不支持跳跃式修改。

另外在从库上可以执行show slave status命令来获取接收的gtid(retrieve_gtid_set)和执行的gtid(execute_gtid_set)

[root@localhost mysql]# mysql -uroot -hlocalhost -pxxxxxx -e "use mysql;show slave status\G;"|grep "Gtid_Set"

Retrieved_Gtid_Set: 763c7ef3-5977-11e8-ae7a-000c2936b80f:1393-10758

Executed_Gtid_Set: 763c7ef3-5977-11e8-ae7a-000c2936b80f:1-10758

SQL线程执行过的GTID(Executed_Gtid_Set)

IO线程获取到的GTID(Retrieved_Gtid_Set)

GTID模式复制局限性：

(1)不能使用create table table_name select * from table_name模式的语句

(2)在一个事务中既包含事务表的操作又包含非事务表

(3)不支持CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE语句操作

(4)使用GTID复制从库跳过错误时，不支持sql_slave_skip_counter参数的语法

参考：http://www.cnblogs.com/kindnull/p/9051358.html