饿了么MySQL多IDC架构设计

关于饿了么MySQL多IDC架构外面材料比较多,而且目前属于上线运行,运行比较好的业务,这里做一个记录。

分区依据: 把数据库首先分Zone,然后依赖于地区ID分布到不同Zone中,同一个Zone依赖于商户ID分布到不同分版中(shard)。每个Zone在不同IDC中进行互备。

底层数据同步依赖于自研的DRC进行数据同步。

使用上面的结构的好处就是两个IDC基本可以做到同时对外提供服务,不好的地方是,基本是原来的数据量直接翻倍的容量。

根据数据使用上不同,把Zone拆分为: 全局数据GlobalZone(单IDC写入,多IDC可读),多活架构(真正多活)。

全局数据GlobalZone

这种架构相于对简单,单节点写入, 容易控制数据的一致性。

多活架构(shardingZone):

数据分维度治理,每个单元的业务,都在自已的IDC中完成,不依赖于别的IDC。读和写都在本地IDC中完成, 正常情况下本地业务只需要本地机房的数据,对其它机房的数据无依赖,跨IDC数据延迟时无影响。

每个机房有独立一块区间的自增区间, 这块估计也是使用的自增的,1,3,5,… or 2,4,6,… 这种样子。 在业务设计上尽量让单个IDC接入的业务在单位IDC中完成,不依赖于其它IDC。 如果发生更新冲突,依赖于表里的DRC字段,判断谁是最新的数据,用最新数据覆盖旧数据。相当于主从复制处理中的1062错误(这块实现感觉并不严格),也是全程无锁设计,对于润秒时,可能有问题。

对于自增这块的,这块设计上,可以考虑用snowflake类似的算法,生成ID,可以让每个Zone保持某个特殊的数开头,这样会更清晰一点,但也要考虑顺序在里面,这也许是饿了么直接采用主建的原因。

饿了么在MySQL多IDC设计中遵循:

  1. 业务内聚,一个订单位的处理过程在一个机房中完成,减少可能的延迟。
  2. 可用性优先,优先保证系统可用,让用户可以下单吃饭,容忍暂时的数据不一致,事后修复。
  3. 保证正确性, 在确保可用的情况下,需要对数据做保护以避免错误
  4. 业务可感,业务团队修改逻辑,能够识别出业务单元的边界,只处理本单元的数据,打造强大的业务状态机,能发现和纠正错误。(这点我觉的才是核心,任何多idc,永远可用的业务,不能做到业务没感知,如果要求业务没感知的DB跨IDC设计都是扯蛋。)

 

如果你有其它感兴趣的内容,可以入群联系我。一同交流。

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

RadonDB架构解析

RadonDB在DTCC大会主会场宣布开源了, 一个期待已久的产品终于走进了开源社区。 感谢青云领导层的对技术贡献的情怀。

做为一个MySQL从业人员,从我对RadonDB关注到使用,将近有半年多时间,这次RadonDB开源,基本也全程参与,在这里开源计划到最终在DTCC展现,也深深感受为开源,公司也需要付出很多很多。

这里了为了能快速的让大家了解RadonDB,我这里对RadonDB架构做一个简单的梳理,本着更容易大家理的态度不夸大,更利于接近于实质, 同时也方便大家深入去学习RadonDB。

RadonDB整体架构

RadonDB官网: http://radondb.io

RadonDB基于Golang开发,由四部分组成:

  •    Radon SQL 路由层 ,Proxy模式  下载地址: https://github.com/radondb/radon
  •    Xenon  MySQL Plus高可用组件 下载地址: https://github.com/radondb/xenon
  •    存储节点 ,官方MySQL或是Percona分支,推荐一主两从,增强半同步
  •    计算节点  ,目前使用的TokuDB做为全量数据存储,推荐优化后的分支: https://github.com/xelabs/tokudb

 

Radon作用:

  •    SQL解析及路由,混合OLTP和OLAP
  •    分布式事务支持
  •    用户验证, 在Radon中验证,不需要MySQL端创建
  •    连接池功能
  •    SQL审计日志记录(默认没开启,开启后会记录全量的请求,用于审计使用)
  •    记录全量SQL的binlog,用于计算节点数据实时复制
  •    原生集群支持,配置在节点间自动同步

Xenon(MySQL Plus)作用:

MySQL的高可用组件,这个也是我一直觉的增加半同步出现后,是MHA的一个最佳替换产品。 还有很多好玩的功能可以去在上面扩展,主要功能如下

  •   MySQL高可用选主
    • 基于Raft(依赖于GTID)选主
    • 数据一致性依赖于增强半同步(semi-sync)
  • 故障切换动作
    • 借助于配置中leader-start-command  & leader-stop-command  调用相应的脚本完成
    • 这一块也可以自已扩展,结合Consul,ZK来玩
  • MySQL故障后切为从节点后,自动拉起并修复制关系,也可以自动重建(需要配置)
  • 集成Xtrabackup备份调度实现

MySQL存储节点:

利用MySQL的增强半同步构建,一主两从。

主要用于存储数据中的某个分片,有点类似于Redis Cluster结构中的一个主从分组。 官方使用三个节点,为了高可用,推荐至少两个节点。 实验环境,也可以使用一个节点(在单节点结构下MySQL Plus不是必须的)

计算节点:

目前利用作者优化过的TokuDB版本存储分库分表后的全量数据,这样复杂的SQL请求可以转到该节点上运行,官方目前该节点配置是三个,也可以是1-2个, 如果复制SQL比较多,这个地方需要增多一点,实现多个从节点上的SQL运算。 官方反馈,这个地方也需在找新的技术替代,如: Greenplum或是ClickHouse,也可能是MariaDB的ColumnDB。

该节点要担任:SQL中无分区Key的查询,join查询等复杂类的操作。 该节点数据主要靠Radon给多写实现。 如果没这部分操作,可以不要计算节点。不过,推荐放置,这样相当于有一个地方有一个全量数据。对数据安全也是一个增强。

同样该节点也可以后续加入,通过https://github.com/xelabs/go-mydumper 全集群某个分片全量迁移,然后在结合Radon记录的GTID信息实现增量同步。

计算节点可以说是RadonDB中的一个亮点,借助于Radon实现了一个全量数据维护,这复杂的查询或是统计的分析都可以到计算机节点实现,这个地方如果将来换成一个OLAP类的数据库,就更回完美。

借用官方的架构图供大家在看一下:

 

RadonDB优点

大致对整个结构有一个了解后,我们再看看几个实质的问题,RadonDB有优秀的地方,大致总结以下几点:

  1.  自动分库分表,透明扩容。

    • 在RadonDB中目前只支持Hash拆分,默认把一个表分成: 4096个slot,实际分配到多个子表中,例如青云产品中配置成32个子表,那么每个子表对应128个Solt(4096/32)。 例如创建一张表:
    • create table zst_user(id int not null, wubx varchar(32))   partition by hash(id)
    • 如果有一个MySQL存储分片,会在这个存储分片上建出来32张实际的表,每个表对应id Hash后的128个Slot
    • 诱明扩容
      • 在RadonDB,可以配置当一个表增长超过多大时,当集群中添加节新的存储节点时,会动态进行数据迁移,默认配置单表超过1G,这个步骤迁移是通过利用go-mydumper导出,记录Radon上该节点的GTID,然后到目标节点导入,再通过Radon上补Binlog方式上后,实现访问路由变更。
  2.  对SQL执行没有限制

    • 带有分区Key的查询,可以路由的相应的存储节点计算
    • 不带分区key的会路由到所有存储节点计算,然后在radon中合并
    • 对于有条件及含聚集函数的查询大多还是在存储节点运算后在Radon上合并返回结果,这块需要进一步分析一下执行情况。
    • 对于join,子查询等复杂类的SQL需要计算节点支持,在计算节点运算后返回给前端。
  3.  自带高可用套件: Xenon (MySQL Plus)

    • 支持MySQL节点上故障快速切换及利用API指令把节点重建(调用的xtrabackup)
    • 在数据一致性,安全性方面,还是依赖于MySQL的增强半同步,所以也推荐三个节点。

RadonDB不足之处:

RadonDB 现在可以说刚出江湖,核心代码1万行左右, 学习Golang的同学不要错过。 加上其它类库引入。 Radon代码11万+, Xenon代码5万行+ 整体来说还是一个轻量级结构。

目前还有一些不足及改进的地方:

1.  Radon 这个Proxy模型中, 目前默认提供单节点读写,支持读IP,业务层需要自已处理读写分离。 这样实质的业务压力还是在Proxy这一层(能不能抗住业务,需要亲测一下),需要考虑多节点同时提供读写能力。(这个其实现在Proxy模型都号称支持多节点同时读写,但对于数据一致性要求强的环境差不多都会出问题)。 这块官方为了金融环境,还是比较保守,实质上多个Radon可以成为无状态对外提共服务,更新冲突,可以让数据库自已来处理。 多个Proxy也相当于更多的连接, 这块实际测试中,可以考虑让多个Radon都对外服务,提高Radon的利用率。

2. Xenon(MySQL Plus)相对独立,没有和Radon有更多的交互, 这个是一个亮点,Xenon后续也可以用到不同的分布式结构下面。但做为产品中的一个组件,还是需要考虑和Radon有更多的交互,如:复制延迟情况, MySQL节点故障后,新选举出来的主,可以同步给Radon,把现有的VIP方案去掉。  这种分离的结构,也给了我们使用者更多灵活的空间,例如结构Consul来玩。感觉有利有弊,有很大的优空间。非常看好Xenon(MySQL Plus),绝对的一个高可用的利器。

3. 某些方面还有待提高,例如计算节点添加, 借助于Radon的Binlog实现,这一块也可以借助于官方的复制实现。需要进一步讨论。 这块可以通过后续DBA的运维手段改善。全量数据维护这块, 也是后续中维护的一个难点,需要提前规划好。

4. 集群中现在成员资源使用绝对不饱合,估计使用在30%以下。 Radon目前单节点提供服务,存储节点,也可只有从节点提供服务。  这块也是金融级高可用的通命,特别是两地三中心架构中,更加浪费。

总的感受

RadonDB  整体非常不错,设计方面,非常独道,属于一个多年开发经验,理解开发中痛点的一个数据库产品MyNewSQL,代码也比较精简,觉的也是学习Golang不错的项目。对于理解数据库架构设计也是一不错的东西。知数堂准备把RadonDB放置到课堂中做为教学内部中的一部分。也欢迎大家一块来交流RadonDB相使使用经验。 欢迎加知数堂-王者侠谷中交流:

 

 

 

新一代MySQL高可用:MySQL Plus

在基于MySQL传统复制的时代(MySQL版本低于5.5)MHA在MySQL高可用中可以说是独领风骚。在MySQL 5.6及GTID的出现后,MHA在这方面就显的不给力,和MHA作者交流,作者基本放弃该软件的维护,MHA作者现就职在Facebook,也没在使用MHA,他也认为在GTID环境下MHA存在的价值不大,不过你如果你还在使用传统复制,还是可以考虑使用MHA做主从的高可用(太老了,建议升级)。

下面我们围绕以下几点来讨论一下:

  1. 在MySQL 5.7 后为什么不需要MHA
  2. MySQL Plus 是什么,能解决什么问题
  3. MySQL Plus看他们如何搞定金融支持

MySQL 5.7后为什么不需要MHA

基于MySQL 5.7 GTID复制已经成熟,另外基于MySQL5.7的增强半同步性进一步提升: 所以在使用MySQL 5.7的复制可以使用: MySQL 5.7+GTID+增强半步, 在该结构中, 不存在会丢数据的问题。 所以MHA在这个结构基本失去了存在的意义。

但使用: MySQL 5.7+GTID+增强半步,也意为着新的知识,可能需要DBA同学们也要更新一下知识。而且在MySQL 5.7中引入binlog group commit, 又是对复制的一个加速。 所以说MySQL5.7 在复制完整性及性能上都有较大的提升,建议没升级的同学尽快升级了。

官方对MySQL 5.7的测试传送门: https://www.mysql.com/why-mysql/benchmarks/

MySQL Plus是什么,能解决什么问题

在3306π北京活动中 青云的蒙哲分享了青云RDS中高可用组件: MySQL Plus。 MySQL Plus是基于一套Raft构建的MySQL中自动选主及维护主从的套件,整体结构如下:

在该结构中Xenon之间会进行通信,在该结构中推荐三个节点的MySQL构建复制,听作者讲也支持两个节点的MySQL构建集群。

在MySQL Plus主要解决:

  1.  集群切换的强一致性(从上面架构看,更多的依赖于MySQL增强半同步,MySQL Plus在控制切换时,会做复制完成校验,从而且保证数据一致)
  2. 主从秒级别切换
  3. 无中心化自动选主

MySQL Plus看他们如何搞定金融环境

MySQL Plus 可以简单的理解是一个MySQL 5.7 GTID增强半同步复制的高可用管理组件。 在MySQL半同步配置方面,为了支持金融业务,青云给的配置如下:

  • rpl_semi_sync_master_wait_no_slave=ON
  • rpl_semi_sync_master_timeout=1000000000000000000
  • rpl_semi_sync_master_wait_point=AFTER_SYNC

看到这个配置我才想明白为什么他们建议是三个节点,在rpl_semi_sync_master_timeout配置上可以说不允许退化到异步复制, 另外RadonDB负责人交流,在MySQL Plus架构中主节点上至少要求一个Slave给半同步应答。 所以2个节点对架构的稳定性也是一个保证。 另外在金融环境中,作者推荐所有请求都在主库上完成, 免的存在复制延迟造成交易数据异常。

在金融架构中,青云也提供一套基于MySQL Plus之上构建建的分库分表机制, 基于MySQL的事务强一致性约,在该平台支持OLTP和OLAP更感觉有点NewSQL的感觉。

下面是官方给的一个总结:

重大消息

如果想了解更多3306π是青云MySQL Plus PPT信息可以关注 3306pai公众号,回复“bj”获取 3306π北京的PPT打包下载。

MySQL Plus 官方要开源了,希望通过MySQL Plus给MySQL 5.7 GTID复制提供一个新的高可用方案。

更重大消息: QingCloud RDS : RadonDB也要开源了,大家期待一下吧。 如果感兴趣提前体验或是更多的关注 RadonDB发展,也可以加到QQ群:748415432  和群主及 RadonDB负责人直接对喊话。   技术在于相互沟通,你的建议也需是将来一个非常不错的功能。

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

招商银行为什么使用MySQL

      对于金融行业使用MySQL可以说也比较早,例如腾讯的财富通从开始到现都是基于MySQL构建,但对于传统银行企业使用MySQL我也是一直存在好奇的想法。 这次在3306π北京站有幸听了招商银行王龙的分享也解决了我几个疑问,特Mark一下。

  1. 招商银行在走向开源的道路为什么选择了MySQL,而不是其它数据库?
  2. 招商银行在使用MySQL大概的规模及情况是什么?
  3. 招商银行是如何管理他们的MySQL?
  4. 他们成功的心得是什么?

招商银行为什么选择了MySQL?

在这一点上,听作者讲他们内部也做了大量的调研,例如PostgreSQL,TiDB,MySQL等。 最终选择了MySQL,不是说MySQL最优秀,也不是其它DB不优秀, 更重要的MySQL可以对他们的业务模型的支撑上更方便一点,同时团队的更加容易上手。具体的原因如下:
  1. 明确业务模型,不为可能需要的功能买单. MySQL足以支持现有的业务, 而且基准测试性能不弱。
  2. 本着简单的原则,不选复杂。 更多的人熟悉,更利于团队开发。
  3. 组建分布式DB,更方便快速扩容。
  4. 通过主从复制,读写分离等技术,更方便的实现多地多活技术。
  5. 选择MySQL更利用云服务化和DevOPS开发。

招商银行在MySQL使用上大概的规模

目前招商银行在使用MySQL将近两年, 目前作者讲有单业务集群分片在上百规模。 按这个分片来推算可以说是我听过最大的MySQL分布式系统。 该集群主要用于银行帐单查询相关业务。

招商银行如何管理他们的MySQL

这个直接引入原文中的PPT吧。 真的不能小看招商银行两年时间在MySQL云化平台做的相当出色。
上面可以看出来他们在资源申请上分三个类型,对于资源对齐方面确实做的不错。 想做MySQL平台的同学,仔细看看上面的两个图片。

招商银行在MySQL大规模使用上成功的心得

在这点招商银行总结金融架构13条(请阅读PPT),也很赞。 这里限于篇幅不在逻列。 我更多的关注对方在MySQL上的使用,这里看一个他们的分库分表决策: 分库常见几个基本问题: 在分库分表中作者见解也比较独到。也可以说是经过实际项目磨练出来的经验。 PPT已经发布在3306π的公众号上,关注3306pai公众号,回复“bj” 获取全部PPT。 作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

MGR监控及优化点

mysql group replication官方在监控及优化方面文档较少,为了在教学中方便使用,总结如下:

监控点

可用性监控

本节点是不是online:
select member_state from replication_group_members where member_id=@@server_uuid;

当前节点是不是可以写:
select * from performance_schema.global_variables where variable_name in ('read_only', 'super_read_only');

节点是Online表示属于集群中,正常工作。 节点不可写,表示是Single-master中的非Master节点。

性能监控

复制是不是存在延迟:
对比获得到的GTID和本节点执行的GTID是不是一致:
获取的GTID:
SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier';

本节点执行的GTID:
select @@gtid_executed;

远程获取的GTID - 本节点执行的GTID = 延迟的GTID数

本节点执行队列是不是有堆积(大于0表示有延迟):
select count_transactions_in_queue from replication_group_member_stats where member_id=@@server_uuid;

流控(flow control)

在MGR中如果节点落后集群中其它成员太多,就会发起让其它节点等他完成在做的控制,这个叫流控。
当启用: group_replication_flow_control_mode=QUOTA 是表示启用流控。 流控默认通过两个参数控制:
group_replication_flow_control_applier_threshold (默认: 25000)
group_replication_flow_control_certifier_threshold (默认: 25000)

也就说默认延迟在25000个GTID时,会对整个集群Block住写操作。
当然,也可以允许,节点延迟,就如同我们主从结构,从节点延迟,不往上面发请求就可以。
关闭Flow control:
set global group_replication_flow_control_mode='DISABLED';

提示: 关闭流控制,注意查看是不是存在延迟,如果延迟,自已控制阀值不向上面发请求即可。 多IDC结构的MGR,建议关闭流控。

MGR调优参数

因为基本复制结构,所有的数据复制,还是逻辑的重放,所以优化也是复制优化点。
更改:
slave_parallel_type -> LOGICAL_CLOCK

增强sql_thread个数:
slave_parallel_workers -> 2-8

如果CPU瓶颈,网络没问题,减少CPU压缩:
group_replication_compression_threshold = 1000000 -> 2000000
由原来的1M变成2M,再进行压缩(主要针对大事务传述优化)

备注: 需要MGR安装文档及更多交流 入QQ群: 579036588 联系群主。

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

讨论数据库减库存几种方法V1

MySQL在电子商务网站使用越来越多,做为第一梯队做MySQL的从业人员,也经常被问到MySQL对于减库存在这块有什么好的处理方法?对于这个问题,想想在课上分享及朋友中交流讲的不下百次。 所以也有了把这个总结一下的想法。
对于减库存的操作,我们需要先从一个更高的Level来看一下,先不用讨论update减1的问题。我们可以把减库存形为简单的分成以下几种:

  • 团购类减库存(库存无限,卖的越多越好) 这个可能大家见的也比较多,就不用多说,这类业务,多卖一些老板反而高兴。
  • 限时活动类(24小时活动产品)
  • 秒杀抢购类活动
  • 正常商品售卖

当然还别的形式,可能有的减订单操作是一个复合型的业务: 如 12306这样的业务,含秒杀,抢购,限时活动于一身,难度空前绝后:)

那么我们再来看一下订单系统里几个难题:
1. 超卖,听说某米是允许在商品接近预警时自动关闭交易(商品下架),并允许一定的超售现象。
2. 羊毛党下单不支付,活动结束,一个也没卖出去。 有的电商的做法是,付款后再减库存,不付款不减,可能出现的问题是,用户犹豫一下,去付款时,告诉你没货了,体验不好。
3. 高并发的抢购,打压到DB中,把DB打死的问题。比较常见的一个问题,早些年某电商还发布过把MySQL死锁去掉的分支,用于支撑抢购里业务,现在MySQL 8.0天生支持,另外也可以考虑在在业务层Nginx上控制。

那么我再回过来看一下减库存的形式,我们可以把减库存,分为两种:

  • 正常商品售卖类减库存
  • 活动促销类减库存

问题分为:是否允许超卖 ,要不要考虑羊毛党的一些形为。 处理好这些问题的情况下支撑高并发业务。另外给老板们提个醒,系统优化是一个全面工作,不只是这里有瓶颈。

我分成几种情况来讨论:

  1. 用户下单减库存 如果允许超卖的情况下, 外加一个预警商品下架(缺货)逻辑来处理,基本可以很快速的跑起来。 业务逻辑最简单适合正常商品逻辑。库存大于多少可以上架之类的一些处理(后台人工调配) 如果不允许超卖, 例如车票这类,处理的办法不在是简单的库存结构设计,可以把每个坐位做为一个商品上架构, 只是车票商品上的属性有自已是那个车,挨着谁之类的信息即可。下单后锁定30分钟… 可以目前各大电商的主流逻辑,只是不同的平台,锁时时间不一样。 防止用户下单后几天后还不支付,让商品可以回归库存,不支付订单的失效过程。 下单减库存也是大家最容易接受的一个形为,但这里把最难的问题抛给了平台,一个商品多少没支付返回库存,目前来看不同平台处理的形为不一样。 不适用于抢购,秒杀点业务。
  2. 支付减库存
    抢购,秒杀,谁先付款谁得的逻辑。 这样可以避免羊毛党下单后不支付的形为,例如,本来只有10个特价产品,羊毛党手快,下单成功,但种种原因没支付,提示商品已售完,但活动结束了,一个商品也没卖出去,老板花的活动费白花了。
  3. 高并发的抢购业务
    例如某米周二中午的抢购,10万个商品,可能是100万人在抢购,这种形为,如果都打到DB,可能是对DB的锁也是一个挑战,可能出现的形为就是不该慢的也慢了,去除检查也是一个好办法。
    现在聪明的人类想好了更好的办法,利用Nginx+LUA引入接入在接层引入摇号方法,基于Cookie在Nginx层拿到了可以购买的资格再往后面放,如果没拿到这个资格就在Nginx上看看,提示你商品已经售完就行。这样每次扩容,只用扩一下Nginx接入层就Ok了,其它不用扩了。

问题分类后,现在来看,对于减库存,如果分类处理后,你会发现,还是挺容易处理的,这里面里也不要再来找我聊用MySQL的乐观锁控制来减库存了,如果你在知数堂的课上学习过,一定会知道多点写入的乐观锁控制会造成更新丢失的现象,同样来讲,单节点的同时并发,不是串行的情况,这事没戏,我确时见过有的公司在这块用了串行事务,他们也跑的挺好,业务量不大。祝你玩的开心。也祝愿你们早日在减库存这块遇到瓶颈,让老板高兴痛苦的给你加薪。

 

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

基于MySQL 5.7多源复制+Keepalived搭建高可用

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

说明

本内容来源于知数堂 公开课 : 《MySQL 5.7 高可用新玩法》–吴炳锡 相关视频推荐:https://ke.qq.com/course/172600

本些分享视频地址: http://pan.baidu.com/s/1mia6MZu

基本环境准备

使用Centos 6.X 64位系统 MySQL 使用 MySQL-5.7.17-x86_64 版本,去官方下载mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz 版本

机器名 操作系统 IP
node1 centos-6.8 192.168.11.100
node2 centos-6.8 192.168.11.101
node3 centos-6.8 192.168.11.102

对应的VIP: 192.168.11.110

特别提示: 关闭iptables chkconfig –del iptables /etc/init.d/iptables stop

关闭:selinux setenforce 0 vim /etc/sysconfig/selinux SELINUX=permissive 更改为: SELINUX=disabled

下载MySQL :

 

mkdir /data/Soft
cd /data/Soft
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

###MySQL部署约定 二进制文件放置: /opt/mysql/ 下面对应的目录 数据文件全部放置到 /data/mysql/ 下面对应的目录 原始二进制文件下载到/data/Soft/

MySQL基本安装

以下安装步骤需要在node1, node2, node3上分别执行。

  1. mkdir /opt/mysql
  2. cd /opt/mysql
  3. tar zxvf /data/Soft/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
  4. ln -s /opt/mysql/mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql
  5. mkdir /data/mysql/mysql3309/{data,logs,tmp} -p
  6. groupadd mysql
  7. useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -M mysql
  8. chown -R mysql:mysql /data/mysql/
  9. chown -R mysql:mysql /usr/local/mysql
  10. cd /usr/local/mysql/
  11. ./bin/mysqld –defaults-file=/data/mysql/mysql3309/my3309.cnf –initialize
  12. cat /data/mysql/mysql3309/data/error.log |grep password
  13. /usr/local/mysql/bin/mysqld –defaults-file=/data/mysql/mysql3309/my3309.cnf &
  14. echo “export PATH=$PATH:/usr/local/mysql/bin” >>/etc/profile
  15. source /etc/profile
  16. mysql -S /tmp/mysql3309.sock -p #输才查到密码进入MySQL
  17. mysql>alter user user() identified by ‘wubxwubx’
  18. mysql>grant replication slave on . to ‘repl’@’%’ identified by ‘repl4slave’;
  19. mysql>grant all privilegs on . to ‘wubx’@’%’ identified by ‘wubxwubx’ # 一会测试使用的帐号
  20. mysql>reset master

每个节点按上面进行,遇到初始化和启动故障请认真阅读/data/mysql/mysql3309/data/error.log 信息。 my3309.cnf 可以从相应的目录下载或是加入QQ群: 579036588 下载,有问题入裙讨论。

搭建主从结构

 

node1上执行:

mysql -S /tmp/mysql3309.sock -pwubxwubx

mysql>change master to master_host=’192.168.11.101′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_101_3309′;

mysql>change master to master_host=’192.168.11.102′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_102_3309’;

mysql>start slave; mysql>show slave status\G; #确认同步OK

node2上执行:

mysql -S /tmp/mysql3309.sock -pwubxwubx

mysql>change master to master_host=’192.168.11.100′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_100_3309′;

mysql>change master to master_host=’192.168.11.102′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_102_3309’;

mysql>start slave; mysql>show slave status\G; #确认同步OK

node3上执行:

mysql -S /tmp/mysql3309.sock -pwubxwubx

mysql>change master to master_host=’192.168.11.100′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_100_3309′;

mysql>change master to master_host=’192.168.11.101′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_101_3309’;

mysql>start slave;

mysql>show slave status\G; #确认同步OK

安装keepalived

node1, node2, node3 上分别执行: 安装keepalived

yum install keepalivled

安装python依赖模块:

yum install MySQL-python.x86_64
yum install python2-filelock.noarch

keepalived配置

配置文件放置在: /etc/keepalived/keepalived.conf 内容如下:

vrrp_script vs_mysql_82 {
    script "/etc/keepalived/checkMySQL.py -h 127.0.0.1 -P 3309"
    interval 15
}
vrrp_instance VI_82 {
    state backup
    nopreempt
    interface eth1
    virtual_router_id 82
    priority 100
    advert_int 5
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        vs_mysql_82
    }
    notify /etc/keepalived/notify.py
    virtual_ipaddress {
        192.168.11.110
    }
}

##Keepalived启动 node1, node2, node3分别执行:

/etc/init.d/keepalived start

观查每个系统上的/var/log/messages 内容输出

##测试用例 在其它机器上使用:

mysql -h 192.168.11.110 -P 3309 -uwubx -pwubxwubx -e "select @@hostname"

自已触发一下切换看看能不能完成自动化的切换。

关注知数堂公从号 第一时间参加技术交流

知数堂公众号

Oracle嘉年华2105分享

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

今年还是受邀参加了一下Oracle技术嘉年华2015, 把课程中去如何做好运维的整体概述给大家分享一下。需要PPT的猛击下面的连接下载。

MySQL运维那些事

http://pan.baidu.com/s/1pJGh7D5

 

varchar和text说不清的那些事

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

最近有几个同学问我varchar和text有啥别吗,这个问题,以前说真的也没太多的整理,以前遇到text在设计中就是尽可能的拆到另一个表中,保持主表尽量的瘦小,可以让innodb bp缓存更多的数据。

今天借次机会系统整理一下,主要从存储上,最大值,默认值几个方面进行比较。

BTW: 从ISO SQL:2003上讲VARCHAR是一个标准型,但TEXT不是(包括tinytext).varchar在MySQL 5.0.3之前只支持0-255byte, 在5.0.3之后才支持到0-65535byte.

从存储上讲:

- text 是要要进overflow存储。 也是对于text字段,不会和行数据存在一起。但原则上不会全部overflow ,
会有768字节和原始的行存储在一块,多于768的行会存在和行相同的Page或是其它Page上。

- varchar 在MySQL内部属于从blob发展出来的一个结构,在早期版本中innobase中,也是768字节以后进行overfolw存储。

- 对于Innodb-plugin后: 对于变长字段处理都是20Byte后进行overflow存储
(在新的row_format下:dynimic compress)

说完存储后,说一下使用这些大的变长字段的缺点:

- 在Innobase中,变长字段,是尽可能的存储到一个Page里,这样,如果使用到这些大的变长字段,会造成一个Page里能容纳的行
数很少,在查询时,虽然没查询这些大的字段,但也会加载到innodb buffer pool中,等于浪费的内存。
(buffer pool 的缓存是按page为单位)(不在一个page了会增加随机的IO)

- 在innodb-plugin中为了减少这种大的变长字段对内存的浪费,引入了大于20个字节的,都进行overflow存储,
而且希望不要存到相同的page中,为了增加一个page里能存储更多的行,提高buffer pool的利用率。 这也要求我们,
如果不是特别需要就不要读取那些变长的字段。

那问题来了? 为什么varchar(255+)存储上和text很相似了,但为什么还要有varchar, mediumtext, text这些类型?
(从存储上来讲大于255的varchar可以说是转换成了text.这也是为什么varchar大于65535了会转成mediumtext)

我理解:这块是一方面的兼容,另一方面在非空的默认值上varchar和text有区别。从整体上看功能上还是差别的。

这里还涉及到字段额外开销的:

- varchar 小于255byte  1byte overhead
- varchar 大于255byte  2byte overhead

- tinytext 0-255 1 byte overhead
- text 0-65535 byte 2 byte overhead
- mediumtext 0-16M  3 byte overhead

- longtext 0-4Gb 4byte overhead

备注 overhead是指需要几个字节用于记录该字段的实际长度。

从处理形态上来讲varchar 大于768字节后,实质上存储和text差别不是太大了。 基本认为是一样的。
另外从8000byte这个点说明一下: 对于varcahr, text如果行不超过8000byte(大约的数,innodb data page的一半) ,overflow不会存到别的page中。基于上面的特性可以总结为text只是一个MySQL扩展出来的特殊语法有兼容的感觉。

默认值问题:

- 对于text字段,MySQL不允许有默认值。
- varchar允许有默认值

总结:

根据存储的实现: 可以考虑用varchar替代tinytext
如果需要非空的默认值,就必须使用varchar
如果存储的数据大于64K,就必须使用到mediumtext , longtext
varchar(255+)和text在存储机制是一样的

需要特别注意varchar(255)不只是255byte ,实质上有可能占用的更多。

特别注意,varchar大字段一样的会降低性能,所以在设计中还是一个原则大字段要拆出去,主表还是要尽量的瘦小

源码中类型:

+--Field_str (abstract)
 |  +--Field_longstr
 |  |  +--Field_string
 |  |  +--Field_varstring
 |  |  +--Field_blob
 |  |     +--Field_geom
 |  |
 |  +--Field_null
 |  +--Field_enum
 |     +--Field_set

(末完待续,也希望大家一块讨论一下)

参考:
http://yoshinorimatsunobu.blogspot.com/2010/11/handling-long-textsblobs-in-innodb-1-to.html

MySQL: TEXT vs. VARCHAR Performance


http://www.pythian.com/blog/text-vs-varchar/

 

测试SQL及方法

 

create table tb_01(
c1 varchar(255),
c2 varchar(255),
c3 varchar(255),
c4 varchar(255),
c5 varchar(255),
c6 varchar(255),
c7 varchar(255),
c8 varchar(255),
c9 varchar(255),
c10 varchar(255),
c11 varchar(255)
)engine=Innodb;

insert into tb_01(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11) values(repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255));
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

(testing)root@localhost [wubx]> set global innodb_file_format=BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

(testing)root@localhost [wubx]> alter table tb_01 row_format=dynamic;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

(testing)root@localhost [wubx]> insert into tb_01(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11) values(repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255));
Query OK, 1 row affected (0.00 sec)


set global innodb_file_format=Antelope;
create table tb_02(
c1 varchar(2000),
c2 varchar(2000),
c3 varchar(2000),
c4 varchar(2000),
c5 varchar(2000),
c6 varchar(2000),
c7 varchar(2000),
c8 varchar(2000)
)engine=Innodb;

insert into tb_02(c1, c2, c3,c4,c5,c6,c7,c8) values(repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000),repeat('吴',2000) );


create table tb_03(
c1 text,
c2 text,
c3 text,
c4 text,
c5 text,
c6 text,
c7 text,
c8 text,
c9 text,
c10 text,
c11 text
)engine=Innodb;
insert into tb_03(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11) values(repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255));

(testing)root@localhost [wubx]> insert into tb_03(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11) values(repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255),repeat('吴',255));
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

set global innodb_file_format=BARRACUDA;
alter table tb_03 row_format=dynamic;

MySQL 二进制日志格式基础(一)

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.
MySQL二制进日志用于记录数据库的变更记录,这里从结构上讨论一下日志的格式。

每个日志都包含4个字节的magic number 和event的描述包

  1. 日志有前四个字节是magic number: oxfe ox62 0x69 0x6e = 0xfe ‘b”i”n’ 转成整数:1852400382  用处就是读4个字节对比不是这个数,说明就不是二进制日志,就不用处理了。
      log_event.sh中可以查到
      /* 4 bytes which all binlogs should begin with */
         #define BINLOG_MAGIC        "\xfe\x62\x69\x6e"
    
  2. 每个event的header大概如下:
     - 每个event中包含: event的类型,什么时间,由哪版本的MySQL产生的
     - 从header头能找到该event的大小及一些变更信息
    
  3. 第一个event称为:format descriptor event(Event描述结构:FDE) 用于说明日志的格式
  4. 其它event就是依赖于描述结构不同,用不同的结构记录数据
  5. 最后一个event是: 日志切换事件(log-rotation event)用于指点定下个日志的文件名

每个event的结构大概如下:

+===================+
| event header      |
+===================+
| event data        |
+===================+

现在大多使用的V4结构,从MySQL 5.0起,具体如下:

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+

第一个event是FDE结构没有extra_headers部分,所以固定为19个字节。

FDE的event_data中定长部分为:

  • 2字节的的日志格式版本,从MySQL 5.0后都是4
  • 50字节 用于记录MySQL的版本号 如:5.6.16-64.2-rel64.2-log 不够50字节用0x00填充
  • 4字节 日志产生的时间
  • 1字节 header长度。一般是19,如果大于19,则下面的event都有extra_header字段
    对于FDE变长部分一般为空

其它Event计算

  • header length = x byte
  • data length = (event_lenth -x )byte
  • 数据区里定长部分长度
      fixed_part = y byte
      variable_part = (event_length - (x+y)) byte
    

如果给定的X不是19,则存extra_header里面有内存
Y依赖于event_type有不同的大小,需要参考不同的event进行特别处理
参考:http://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html