MySQL基于GTIDs的MySQL Replication

372次阅读

没有评论

共计 3969 个字符，预计需要花费 10 分钟才能阅读完成。

基于 GTIDs 的 MySQL Replication

1、GTIDs（Global transaction identifiers）全局事务标识符，是 mysql 5.6 新加入的一项技术

2、当使用 GTIDs 时，每一个事务都可以被识别并且跟踪

3、添加新的 slave 或者当发生故障需要将 master 身份或者角色迁移到 slave 上时，都无需考虑是哪一个二进制日志以及哪个 position 值，极大简化了相关操作

4、GTIDs 是完全基于事务的，因此不支持 MYISAM 存储引擎

5、GTID 由 source_id 和 transaction_id 组成：
1>source_id 来自于 server_uuid, 可以在 auto.cnf 中看到
2>transation_id 是一个序列数字，自动生成

1、不支持非事务引擎（MYisam），因为可能会导致多个 gtid 分配给同一个事务
2、create table … select 语句不支持（主库语法报错）
3、create/drop temporary table 语句不支持
4、必须使用 enforce-gtid-consistency 参数
5、sql-slave-skip-counter 不支持(传统的跳过错误方式)
6、GTID 复制环境中必须要求统一开启和 GTID 或者关闭 GTID
7、在 mysql 5.6.7 之前，使用 mysql_upgrade 命令会出现问题

1. A transaction is executed and committed on the master.
This transaction is assigned a GTID using the master’s UUID and the smallest nonzero transaction sequence number not yet used on this server; the GTID is written to the master’s binary log (immediately preceding the transaction itself in the log).

2. After the binary log data is transmitted to the slave and stored in the slave’s relay log, the slave reads the GTID and sets the value of its gtid_next system variable as this GTID. This tells the slave that the next transaction must be logged using this GTID.It is important to note that the slave sets gtid_next in a session context.

3. The slave verifies that this GTID has not already been used to log a transaction in its own binary log. If this GTID has not been used, the slave then writes the GTID, applies the transaction, and writes the transaction to its binary log. By reading and checking the transaction’s GTID first, before processing the transaction itself, the slave guarantees not only that no previous transaction having this GTID has been applied on the slave, but also that no other session has already read this GTID but has not yet committed the associated transaction. In other words, multiple clients are not permitted to apply the same transaction concurrently.

4. Because gtid_next is not empty, the slave does not attempt to generate a GTID for this transaction but instead writes the GTID stored in this variable—that is, the GTID obtained from the master—immediately preceding the transaction in its binary log.

总结：有了 GTID 大大的简化了复制的过程，降低了维护的难度

在生产环境中，大多数情况下使用的 MySQL5.6 基本上都是从 5.5 或者更低的版本升级而来，这就意味着之前的 mysql replication 方案是基于传统的方式部署，并且已经在运行，因此，接下来我们就利用已有的环境升级至基于 GITDs 的 Replication

传统的方案部署参考：https://www.cnblogs.com/yanjieli/p/9831084.html

注意：

1、开启 GITDs 需要在 master 和 slave 上都配置gtid-mode，log-bin，log-slave-updates，enforce-gtid-consistency（该参数在 5.6.9 之前是 –disable-gtid-unsafe-statement）

2、其次，slave 还需要增加 skip-slave-start 参数, 目的是启动的时候，先不要把 slave 起来，需要做一些配置

详细操作步骤：

当前环境是传统的 AB 复制转换成 GTID 模式

master：192.168.1.166

slave：192.168.1.114

1、将 master 和 slave 服务器都设置为 read-only

mysql>set @@global.read_only=ON;

2、停止两台服务器的 mysql 服务

3、配置 master

master：[root@master ~]# vim /etc/my.cnf
log-bin=mysql-bin
gtid-mode=on
log-slave-updates
enforce-gtid-consistency
[root@master ~]# service mysqld restart

4、配置 slave

slave：[root@slave1 ~]# vim /etc/my.cnf 
gtid-mode=on
log-bin
log-slave-updates
enforce-gtid-consistency
skip-slave-start

[root@slave1 ~]# service mysqld restart

mysql> change master to master_host='192.168.1.166',master_port=3306,master_user='slave',master_password='123',master_auto_position=1;

mysql> start slave;

mysql> show slave status \G;
Auto_Position: 1

5、关闭 read-only 模式

mysql> set @@global.read_only=OFF;

6、测试

master 查看:
mysql> select * from db01.table03;
+------+------+
| id   | name |
+------+------+
|    1 | haha |
|    2 | wowo |
|    4 | yoyo |
|    1 | haha |
|    2 | wowo |
|    4 | yoyo |
+------+------+
6 rows in set (0.07 sec)

slave 查看：mysql> select * from db01.table03;
+------+------+
| id   | name |
+------+------+
|    1 | haha |
|    2 | wowo |
|    4 | yoyo |
|    1 | haha |
|    2 | wowo |
|    4 | yoyo |
+------+------+
6 rows in set (0.07 sec)

master 插入数据并查看：mysql> insert into db01.table03 values(5,'ouou');
Query OK, 1 row affected (0.04 sec)

mysql> select * from db01.table03;
+------+------+
| id   | name |
+------+------+
|    1 | haha |
|    2 | wowo |
|    4 | yoyo |
|    1 | haha |
|    2 | wowo |
|    4 | yoyo |
|    5 | ouou |
+------+------+
7 rows in set (0.00 sec)

slave 查看：mysql> select * from db01.table03;
+------+------+
| id   | name |
+------+------+
|    1 | haha |
|    2 | wowo |
|    4 | yoyo |
|    1 | haha |
|    2 | wowo |
|    4 | yoyo |
|    5 | ouou |
+------+------+
7 rows in set (0.00 sec)

并且 master 上面操作后查看 slave 的状态，下面就会有事务产生
mysql> show slave status\G;
Retrieved_Gtid_Set: 5624c184-5b55-11e8-b117-000c293dfd08:1
Executed_Gtid_Set: 5624c184-5b55-11e8-b117-000c293dfd08:1
Auto_Position: 1