MySQL多机房容灾设计(with Multi-Master)
-
Upload
lixun-peng -
Category
Documents
-
view
1.112 -
download
3
Transcript of MySQL多机房容灾设计(with Multi-Master)
![Page 1: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/1.jpg)
MySQL多机房容灾设计
淘宝核心系统研发 彭立勋 �
@plinux �
2012-12-08 �
![Page 2: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/2.jpg)
2
跨机房同步的原因
• 机房更靠近用户,提升用户体验 �
• 容灾,防止整机房崩溃后系统不可用 �
• 机房满载,拓展容量 �
• 不同业务共享、相互推送数据 �
![Page 3: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/3.jpg)
3
跨机房同步的类型
• 一写多读 �
• 多写一读 �
• 到处可写 �
![Page 4: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/4.jpg)
4
跨机房同步的方案
• 应用层: �
程序写多个库,以多数成功后返回 �
• MySQL 层: �
MySQL Replication [Master-Slave] �
中间件 [Tungsten, Otter, Erosa, DRC, Transfer] �
Multi-Source Replication [MariaDB] �
• 引擎层: �
InnoDB Redo Replication [In the further] �
![Page 5: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/5.jpg)
5
一写多读
• 一写多读符合 MySQL Master-Slave 架构模型 �
• 设定一个主机房,接收所有的写请求 �
• 设定多个读机房,接收就近的用户读请求 �
• 场景:读多写少,或写集中的系统 �
• 注意:及时性要求高的读 �
应该发生在Master �
![Page 6: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/6.jpg)
6
多写一读(1)
• 如果备机房机器数与主机房相同,则可以使用 Master-Slave 架构 [资源利用率低] �
• 改进:单机多实例,单机利用率提升 [维护成本也提高] �
• 继续改进:单机可以配置多路 Master [Multi-Master, 中间件] �
• 场景:汇总数据进行分析 (OLAP) �
利用大容量主机进行备份 �
![Page 7: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/7.jpg)
7
多写一读(2)
• 如果不同的 Master 之间存在相同的表名如何? �
• 答:只要表结构相同,没有相同的数据(例如唯一键相同、所有列相同),不会有任何冲突。建议在一个复制网络内的所有实例,ID序列不要相同。 �
• 经过分库后不同的Master库名不同但表名相同如何汇总? �
• 答:Slave可以rewrite库名和表名 �
的映射关系,把不同库/表的操作 �
重写到指定的表 �
![Page 8: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/8.jpg)
8
多处可写(1)
• 最常见的需求是任意机房可读可写 �
• 如何规避数据冲突? �
• 1.划分ID段 [每个实例划分一段] �
• 2.错开ID序列 [每个实例取模不同] �
• 3.业务错开 [每个实例负责不同的表集] �
![Page 9: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/9.jpg)
9
多处可写(2)
• 注意:读取及时性要求高的数据应该发生在写此数据的节点 �
• 场景:利用公用主机作为整集群的在线备用主机,多机房要求写入数据并读取其他机房数据 �
![Page 10: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/10.jpg)
10
Multi-Source Replication
• MariaDB 10.0 以上版本: Link �
• Transfer 2.0 以上版本: Link �
• 独立Patch (5.5.18): Link �
![Page 11: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/11.jpg)
11
Multi-Source Replication
• CHANGE MASTER ["connection_name"] ... • FLUSH RELAY LOGS ["connection_name"] • MASTER_POS_WAIT(....,["connection_name"]) • RESET SLAVE ["connection_name"] • SHOW RELAYLOG ["connection_name"] EVENTS • SHOW SLAVE ["connection_name"] STATUS • SHOW ALL SLAVES STATUS • START SLAVE ["connection_name"...] • START ALL SLAVES ... • STOP SLAVE ["connection_name"] ... • STOP ALL SLAVES ...
![Page 12: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/12.jpg)
12
Multi-Source Replication
• set @@default_master_connection=''; • show status like 'Slave_running'; • set @@default_master_connection='other_connection'; • show status like 'Slave_running';
• 兼容原生单Master命令,default_master_connection
变量可以指定当前操作的通道,使用单通道的命令操作指定通道。
![Page 13: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/13.jpg)
13
广而告之
• Flashback 工具: Link [新增-T参数过滤表] �
• InnoDB 建表设定初始大小: Link [CREATE TABLE 新增 datafile_initial_size 参数] �
• 淘宝MySQL开源站点: mysql.taobao.org • 有功能需求或报告patch bug: [email protected]
![Page 14: MySQL多机房容灾设计(with Multi-Master)](https://reader031.fdocument.pub/reader031/viewer/2022020717/556106f1d8b42aff3f8b58f7/html5/thumbnails/14.jpg)
14
提问时间
�
谢谢大家!