MySQL基于GTIDs的MySQL Replication
MySQL M-S GTID
基于GTIDs的MySQL Replication
什么是GTIDs以及有什么特定?
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是一个序列数字,自动生成
使用GTIDs的限制条件有哪些?
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命令会出现问题
GTID的生命周期包含以下部分:
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大大的简化了复制的过程,降低了维护的难度
配置基于GTIDs的Replication
在生产环境中,大多数情况下使用的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
相关文章
- MySQL Error number: MY-010561; Symbol: ER_RPL_SLAVE_CANT_INIT_RELAY_LOG_POSITION; SQLSTATE: HY000 报错 故障修复 远程处理
- 如何使用PHP连接MySQL数据库(php如何关联mysql)
- MySQL连接错误:排查与解决(mysql连接错误)
- 提升业务效率:基于Oa系统的MySQL架构(oa系统mysql)
- 型基于C的MySQL类型操作技巧(c操作mysql类)
- 能力MySQL:极限存储容量测评(mysql最大存储)
- MySQL数据库使用JavaScript操作基于JavaScript实现MySQL数据库自动化操作(mysql-js)
- MySQL缓冲池:最佳数据库性能优化(mysql缓冲池)
- 连接MySQL中实现多表连接的技巧(mysql多个表)
- Mastering MySQL: A Guide to Setting Up and Configuring MasterSlave Replication(mysql的主从配置)
- 基于 VC 和 MySQL 创建应用程序的简单教程(vc++ mysql)
- 快速掌握常用Mysql语句,让你的MySQL更强大(常用mysql语句)
- MySQL中使用OR语句进行多条件查询(mysql中使用or)
- 基于C语言技术的Web应用程序开发与MySQL集成(c web mysql)
- MySQL中ID自动增长机制实现(mysql中id自动增长)
- 集群基于CDH和MySQL搭建高可用性分布式集群(cdh mysql搭建)
- 基于arm平台快速安装MySQL服务器(arm平台安装mysql)
- cmd控制台操作MySQL一步一步操作指南(cmd 中操作mysql)
- 基于163镜像站的MySQL数据库管理(163镜像站mysql)
- 基于100060的MySQL数据库解决方案(100060 mysql)
- MYSQL收费了 了解MySQL开源版的最新政策(mysql不再免费了吗)
- GET MYSQL 免费下载并破解MySQL数据库软件(mysql下载和破解)
- 如何在MySQL中创建下拉列表(mysql 下拉列表)
- MySQL上机题测试你的数据库技能(mysql 上机题)
- MySQL数据库不支持外键约束限制,需手动控制关系,考虑引用完整性问题(mysql不支持外链)
- MySQL的分区技术探究一千个分区的优缺点(mysql 一千分区)