MySQL高可架构设计方案

1、MySQL高可架构设计方案2.1. 高可用环境高可用（ High Availability）有两种不同的含义，在广义环境中，是指整个系统的高可用特性，在狭义方面，一般指主机的冗余接管，如主机HA。我们目前的产品及相关系统平台主要都倾向于广义上的高可用。一个良好的高可用环境，不仅仅能避免系统本身的问题，还能防止天灾人祸，并且有一个简单可靠的系统维护方法，同时能在最小的成本资源下产生最大的效益。高可用的计算方法一般以年在线率来计算，例如规定整个系统一年之中的可用环境要达到 99.95%，那么 24*365* （ 1-99.95% ） = 4.38 小时（包括计划内维护时间）。另外，子系统的可用性一

2、定会高于整个系统的可用性，如整个系统的可用性为99.95%，则对于子系统，可用性可能就是要求达到99.999%。可用性级别 = 计划外与计划内停机可用性级别每年停机时间99.999%分钟599.99%53分钟99.9%小时8.899.0%87.6小时95.0%小时438图2-1高可用级别对照表在实际产品开发中，很难达到100%的在线能力，即使真的达到，代价会非常大。一般能达到99.9%以上的可用性的环境，都可以认为是比较高的可用环境。成本高可用环境成本业务中断损失在线率99.0%99.9%99.95%99.99%99.999%图 2-2 收益与成本在公司收益与投入成本计算方面取得一个平衡，则是

3、最终所希望的在线效率， 但是收益与成本的计算方法则是决策者与实施者需要着重考虑的问题，适合自己的高可用环境即是最好的，不能盲目地追逐过高的可用性。2.2. 主要风险在一个高可用的环境中，会遇到各种风险，主要的风险如下系统失败或崩溃（ System faults and crashes）?应用层或中间层错误（ Application and middleware failures）网络失败 （ Network failures）介质失效，一般指存放数据的媒体介质故障（ Media failures）人为失误 （ Human Error）分级与容灾 （ Disasters and extended

4、 outages）计划宕机与维护（ Planned downtime, maintenance and management tasks）2.3. 面临的主要问题使用 MySQL+PC服务器来构建高可用的MySQL集群会遇到一些主要的问题，这些问题如果忽略了或者没有去解决好，是会对高可用造成影响的，设置直接影响到整个产品及系统的稳定运行。MySQL会丢数据吗MySQL自身的稳定性怎么样MySQL的性能怎么样MySQL如何快速自动切换MySQL如何进行可靠的容灾MySQL主备库数据的一致性校验MySQL备库同步延迟，备库跟不上主库MySQL在线 DDL锁表（阻塞写）怎么解决相比商业软件成熟的解决

5、方案，MySQL+PC架构其高可用性如何保证3. MySQL 数据可靠性3.1. 背景MySQL实例 Down掉会不会丢数据MySQL服务器 Down掉（比如断电、CPU、内存损坏等）会不会丢数据硬盘坏掉会不会丢数据说明： MySQL丢数据更多地是指，MySQL采用 PC服务器， PC服务器存在硬件损坏的可能性（比如 CPU、内存、硬盘坏掉） ，从而导致丢数据。3.2. 解决方案1、传统思路共享存储2、非共享存储思路可以分开对 MySQL和应用两个方面进行一定的设置和处理，相当于是双保险的方式，使数据不丢失。对于 MySQL设置 innodb_flush_log_at_trx_commit =

6、 1设置为 1：每个事务日志都Flush 到磁盘设置为 2：每个事务刷到log file中，每秒Flush到磁盘设置 sync_binlog = 1设置为 0：事务提交后， MySQL不做 fsync 之类的磁盘同步命令刷新binlog_cache中的数据到磁盘，而让文件系统自行决定什么时候同步，或Cache 满了后才同步到磁盘。设置为 1：事务提交后， MySQL会将 binlog_cache中的数据强制写入磁盘，是最安全的设置。设置 innodb_support_xa = true设置为 1：是否支持分布式事务（默认是打开）设置为 0：不支持分布式事务如果确认应用中不需要使用分布式事务，可

7、关闭该参数Slave 远程 binlog通过 Slave 来保证数据不丢失，binlog实时传送到远程Slave ，如果主备库之间的网络较好的话，一般的（依赖于RTT），备库的时间基本上在毫秒之内。半同步复制（ Semi-Sync ）半同步复制总体上可以保证数据的零丢失，但是可能对性能会有少许影响，会造成约 20%的 TPS下降。说明：1、innodb_flush_log_at_trx_commit、sync_binlog、innodb_support_xa三个参数的设置在保证数据安全性和可靠性的同时，对性能是有一定的牺牲的。innodb_flush_log_at_trx_commit、 sy

8、nc_binlog都为 0 时，性能比其中一个设置为1高出约几百倍；innodb_flush_log_at_trx_commit、 sync_binlog都为 1 时，性能比其中一个设置为1相差约几倍；sync_binlog为 0 和 1 时的系统写入性能差距可能会达到5 倍或更多对于应用应用双写（写两份）应用将同一记录写两份到不同的库中应用通过记录log 来实现可以通过应用程序（Java 、C+）自己写独立的日志来记录数据，也可以通过开源的消息中间件来实现日志记录。4. MySQL 数据一致性4.1. 背景MySQL主库异常Down掉，会导致主备库之间的数据不一致MySQL主备切换后，备库成

9、为主库，数据存在不一致MySQL的逻辑复制理论上是有风险的，极端情况下可能存在主备数据不一致4.2. 解决方案4.2.1. 常规设置 innodb_flush_log_at_trx_commit = 1设置 sync_binlog = 1设置 innodb_support_xa = true半同步复制（ Semi-Sync ）主备库尽量采用row 模式复制，不要采用statement模式复制主备库定期数据一致性校验数据生命周期内的binlog尽量保存下来4.2.2. 主备切换Master 宕机后，有三个选择Slave 立即提供服务，存在数据不一致风险Slave 不提供服务，等待Master 恢

10、复，保证数据一致Slave 提供部分服务（比如只能新建，不允许修改），等待 Master 恢复后，保持数据一致对于我们的MySQL高可用环境，我们采用的处理策略1、Slave 立即提供服务2、Slave （旧）Master（新）3、Master （旧） Rollback4、Master （旧）Slave（新）5、Master （新） ReplayRollback & ReplayMasterSlaveRollbackReplayRollback Master 回滚，保持与 Slave 一致重新恢复主备复制关系ReplaySlave 重放，减少数据丢失冲突检测机制5. MySQL 容灾5

11、.1. 背景互联网应用以普通的PC服务器为主通过业务功能的写入主库通常只有一个，造成单点意外操作 导致数据丢失会遇到不可抗力因素或异常导致宕机5.2. 解决方案writeApp分布式数据中间层writeRemi-SyncMasterSlaveread-only = offRemi-SyncSlave2应用写入数据时，记录应用日志，日志可以用来恢复丢失的数据MySQL复制模式是M-M-S，切换时只需修改read-onlyMySQL主从采用半同步复制（Remi-Sync ）Slave 作为备库， Slave2 也是备库，作为容灾库6. MySQL 自动切换6.1. 背景互联网应用以普通的PC服务器

12、为主MySQL的主库 Down掉后，需要保持提供高可用的服务人工调整切换时间太长多个 MySQL的主库 Down掉后，需要及时切换6.2. 解决方案6.2.1. 架构方式1、整体架构AppSwitch动态数据源ZookAgAgMasterSlaveAgent1Agent2说明：Switch Manager是页面化操作管理切换，目前暂时不实现，采用App+动态数据源直接与 Zookeeper 进行通信。2、详细架构连接管理器，连接不可用，或者监控到Zookeeper 中的主备地址变化时（通过事件的方式可以获得，无需定时检查），从zookeeper 获取新的数据库Master 地址，建立新的连接/

13、basedbZooKeeper master-Master-:5000-lock=:5000-servers-:5000-:5001Agent监控Master 状态，定期更新Zookeeper 上的锁的时间，声明自己可用。Master1. 监控主从状态2. 主动主从切换（当主管理工具恢复的时候，需要此功能）APPAgent半同步复制业界测试，性能损失 20%，用的比较多Slave定期检查 Zookeeper 上的锁更新时间，如果 Master 更新超时，那么把 Slave 状

14、态变成 master,readonly 属性关闭同时更新 zookeeper 上的 Master 地址，为原来 Slave 的地址3、整体思路主备库构成分布式环境，但是有状态确保 Agent 可以重启，可以任意次重启，但是有超时限制主库切换逻辑可以通过Zookeeper 实现锁的升级实现切换时， MySQL的 read-only的设置很重要切换时，需要将异常的故障节点+App 数据源一起切换1、首先在Zookeeper 初始化，创建对应的节点，写入模块信息、数据库源名称、数据库 IP 、数据库端口信息等，然后写入下面的数据库子节点中，并添加watcher ，增加监视事件。2、创建 lock 子

15、节点，不需要设置 watcher ，如果当前 client的 id 是当前最小的节点，则获得了 lock ，退出。否则继续等待，如果id 不存在，则创建子节点3、当发生异常master 宕机后，则watcher 事件触发，然后从当前id 序列中得到最小的 id, 将该节点置为新的 master ，同时将 DB的 read-only 置为 on，保证可以读写6.2.2. 流程设计流程 1： Agent 启动检查工作环境是否满足工作要求满足退出根据传入的参数在Zookeeper 上注册节点注册成功告警，等待手工处理启动数据库监视器流程 2：数据库监控正常开始修改本地监控状态是否 Master 节点

16、检查是否有lock 节点创建 Lock节点更新实例节点数据结束流程 3： 数据库监控数据库Down开始修改本地监控状态If pre_stat = active then now_stat = active2downIf pre_stat = running then now_stat = running2downElse now_stat = down是否为Active2down是否删除lock节点更新实例节点数据结束流程 4： Master 库正常停止开始删除Master 节点停止 Master 数据库结束流程五：应用启动，初始化数据库连接池d fd Data Flow开始读取数据源组信息z

17、ookeeper是从 ZK 读取数据源信息建立 ZK LOCK 节点的监听否构建数据源组结束流程六：应用监听到Active数据库宕机6.2.3. 应用层切换设计目前我们连接池重建连接的过程是当在连接上执行DB操作时发生特定异常时触发连接池关闭不可用连接，重新向数据源获取连接。在使用Oracle 的 RAC配置特性时， Oracle 在驱动层会自行判断数据源是否可用，若不可用则尝试从另外一个数据源获取连接，Oracle的这个特性可以理解为对等数据源的优先选择。但 MYSQL的复制机制 （非共享存储） 决定了其驱动层不能支持当主库出问题时自动连接到从库上，因此我们考虑使用GroupDataSour

18、ce来实现类似Oracle 驱动做的事情，即数据源组中的首选数据源不可用时，我们尝试同组中的其他数据源来获取连接，对于连接池来说这个过程是透明的。连接池还是保持之前当连接异常时，触发执行关闭不可用连接并重新获取连接即可。主备切换和按权重选择、按优先级选择数据源的选择策略是不一样的，因此设计 DbSelector 来描述数据源的选择策略，不同的选择策略在同一数据源组中会同时存在，一个 GroupDataSource 包括写数据源选择策略、 读数据源选择策略和运行时切换策略， 使用何种具体策略取决于组数据源的配置。 待选择的数据源要求是对等的， 即读数据源选择策略只针对标识为读的数据源，不能把读写

19、数据源混在一起选择。引入了 Zookeeper 之后，我们可以通过 Zookeeper 感知到主数据库的状态。 Zookeeper在完成主备切换后会通知应用程序主数据库发生了变更，应用程序收到通知后，需要关闭连接池中之前已建立的主数据库连接，重新创建新的主库连接。基于Zookeeper 的通知机制，我们在AtomDataSource中接收数据源配置变化的信息, 收到变化通知后更新数据源本身的状态，同时建立listner机制，把数据源状态变化发布给连接池等对象进行相应的处理。1、类图：class DataSource?interface?StateDataSource+ getConnectio

20、n() : Connection+ getState() : DsState+ isAvailable() : boolean+ isReadable() : boolean+ isWritable() : booleanAZkStateDataSource+ getConnection() : Connection+ getState() : DsState+ isAvailable() : boolean+ isReadable() : boolean+ isWritable() : boolean+ addDsListener(DsListener) : void notifyDsLis

21、teners() : voidObject?interface?DataSource+ getConnection() : Connection+ getConnection(String, String) : Connection?interface?NamedDataSource+ getConnection() : Connection+ getConnection(String, String) : Connection+ getName()GroupDataSource+ getConnection() : Connection+ getName()+ addDsListener(D

22、sListener) : voidConnectionPool?interface?DsListener+ onChange(DsChangeEvent) : void?enumeration?DsStateREADWRITEREAD_WRITENA?interface?DsSelector+ select() : DataSourceActiv eDsSelectorZkWatcher+select() : DataSource+process() : void?interface?DsChangeEvent+ getOldValue() : <V>+ getNewValue()

23、 : <V>+ getSource() : DataSourceDsStateChangeEvent<DsState>+ getOldValue() : DsState+ getNewValue() : DsState+ getSource() : DataSource2、获取连接时序图s d Get ConnectionConnectionPoolGroupDataSourceActiveDbSelectorZkStateDataSourcegetConnection()select()*isAvailable():true*isWritable():true:act

24、iveDatasourcegetConnection():connection:connection3、应用监听到Active库发生切换时序图：s d Active Sw itchZkWatcherGroupDataSourceZkStateDataSourceDsListener4PoolConnectionPoolZooKeeperMaster is non avaliable(NA):process()getChangedDs(dbName):zkStateDataSourcenotifyDsListeners()changeDsStateToNA()onChange(dsStateCh

25、angeEvent)notifyCheck()closeNaConnections()Master has switched:process()getChangedDs(dsName):zkStateDataSourcenotifyDsListeners()changeDsStateToWritable()onChange(dsStateChangeEvent)initPool()s d Active SwitchZkWatcherZkStateDataSourceDsListener4PoolConnectionPoolZooKeeperMaster is non avaliable(NA)

26、:process()notifyDsListeners()changeDsStateToNA()onChange(dsStateChangeEvent)notifyCheck()closeNaConnections()Master has switched:process()notifyDsListeners()changeDsStateToWritable()onChange(dsStateChangeEvent)initPool()6.2.4. 切换步骤以下具体切换步骤是按7.2.1中的架构方式为基准进行的宕机分类主库（ Master）宕机1. 维护切换，如换机器、内存维护等2. Mast

27、er异常直接挂掉Agent1将状况汇报给 Zookeeper（如果网络通信，zk 感知）Zookeeper的Agent1节点消失Agent2得知 Watch 事件，记录异常，创建异常节点Agent2创建 lock ，获得序列中最小IDAgent2创建 lock 成功，成为一个新的Master，记录在 Zookeeper主备库切换：推送动态数据源配置，将新主库置为可写（即将新主库的read-only置为 false ）主库切换完成6.2.5. 切换的触发条件1、Agent 异常No异常表现触发动作说明a1异常退出要求在 recv_timeout 的时间需 要 记 住 client端 的内可重启，

28、否则会进行切换session，否则进行自动recover。 无 法设 置read-only，需要第三方a2与 MySQL的通信异常与 MySQL进行读写测试，重若 MySQL正常，通信问题可试机制、重试次数、间隔可以忽略（同一台机器）控制a3与 zk 之间的网络异常通过超时来控制，大于由于 session 的绑定无法恢（设置 read-only）recv_timeount 则切换复，需进行切换a4机器死机与 zk 之间的通信中断，在大（设置 read-only）于 recv_timeout 之后进行自动切换2、MySQL异常No异常表现触发动作说明m1访问异常定期进行读写（设置read-onl

29、y ）主库：插入时间戳（可重试，重试间隔可设置）从库：读取时间戳（同上）若 MySQL连接被 kill掉，重新创建连接若异常，认为 MySQL挂掉，进行切换m2机器死机同 a4m3机器的网络异常同 a3m4所在的整个机房down掉发起自动切换（ Zookeeper 也挂掉，被踢出集群）6.2.6.Zookeeper节点设计固定节点，存放RunTime状态/runtime存放数据库状态databaseZookeeper运行节点设计存放 MySQL 数据库状态mysql数据源名称basedbmaster 信息masterActive 锁节点lock数据库实例目录service实例节点10.10.1

30、2.10:5000basedb0:5001masterlockservice0:5002说明：1、basedb 这一级的节点，当进行分库扩展的时候，就在后面加上数值进行区分，比如basedb1、 basedb2 等6.3. 部署及使用场景6.3.1. 部署方式对 MySQL进行水平切分，拆分成很多套数据库，主备库可以部署在不同机房MySQL的复制模式采用Master （ read-only） Master （ rw ） Slave（read-only）数据库中间层（动态数据源包括在内）部署在程序端，配置推送采用 IP 的方式采用可靠的Zookeeper

31、集群保障， Zookeeper 可以部署在三个机房优势多机房部署可实现IDC 容灾不受限于DNS可以进行全页面操作的方式在人工情况下可以将主库切换到任意备库TipsZookeeper 集群中机器的可靠性可以保障，只要半数以上的机器存活即可，是稳定的第三方。Zookeeper集群为了保证其自身的稳定性，机器的最少数量为3，因此对应的MySQL在一个集群节点中的最少部署数量也为3 个库，两个Master库分别为只读和读写，一个Slave库作为容灾库。6.3.2. 使用场景1、场景 1：单机房部署IDC1 机房主库（ read-only）主库（ rw ）从库（容灾read-only）Agent1Ag

32、ent2Agent3Zookeeper1Zookeeper2Zookeeper3Zookeeper集群2、场景 2：多机房部署IDC1 机房IDC2 机房IDC3 机房主库（ read-only）主库（ rw ）从库（容灾 read-only）Agent1Agent2Agent3Zookeeper1Zookeeper2Zookeeper3Zookeeper集群6.4. 页面化管理及监控6.4.1. 切换管理目前暂时不实现6.4.2.Zookeeper监控Zookeeper 提供一些简单但是功能强大的4 字命令， 通过对这些4 字命令的返回内容进行解析，可以获取不少关于ZK 运行时的信息。用 j

33、mx 也能获取一些运行的信息/doc/r3.4.3/zookeeperJMX.html开源的浏览器查看Zookeeper 插件6.5. 测试场景设计6.5.1. 测试环境测试环境1 台 MySQL主库服务器（2个实例 master1 、 master2 ）硬件环境1 台 MySQL从库服务器（2个实例 slave1 、 slave2 ）1 台 Zookeeper 服务器（ 1个实例）操作系统RedHat6 2.6.32-71 64位软件环境MySQL 5.6.11-log 二进制分发版Zookeeper 3.4.5 stable版6.5.2.

34、 测试用例用例编号ha001测试场景MySQL连接中断场景描述测试目的前提条件Agent 与 MySQL之间的连接中断Agent 的自动重连机制及连接失败后的切换处理1、 Agent 与 Zookeeper 之间的通信正常2、 Agent 与 MySQL之间的通信正常3、 Agent 正常运行4、 Zookeeper 正常运行5、 MySQL正常运行测试方法输入/动作在 MySQL服务器上杀掉Agent 的连接进程在 MySQL中 Kill掉 Agent 的连接进程期望的输出1、 Agent在设置的间隔时间内进行自动重连，连续尝试5 次，如果没有连接成功，则发起自动切换，重连的间隔和时间是可以

35、设置的2、 Agent 如果自动重连成功，则返回成功的消息3、 MySQL的连接如果是被Kill掉了，则需要创建连接用例编号ha002测试场景MySQL连接超时场景描述Agent与MySQL的一次连接超过设置的连接超时时间测试目的前提条件Agent 的自动重连机制及处理策略1、 Agent 与 Zookeeper 之间的通信正常2、 Agent 与 MySQL之间的通信正常3、 Agent 正常运行4、 Zookeeper 正常运行测试方法输入/动作期望的输出用例编号测试场景场景描述测试目的前提条件测试方法输入/动作期望的输出用例编号测试场景场景描述测试目的前提条件测试方法输入/动作期望的输出

36、用例编号测试场景5、 MySQL正常运行将 MySQL的连接超时时间设置的足够小设置 MySQL的 wait_timeout参数1、 Agent 在设置的间隔时间内进行自动重连，连续尝试5 次，如果没有连接成功，则发起自动切换，重连的间隔和时间是可以设置的2、 Agent 如果自动重连成功，则返回成功的消息ha003MySQL主库的单个实例挂掉MySQL主库上的1 个实例直接挂掉了MySQL主库上的实例挂掉后能否及时切换并提供正常的服务1、 Agent 与 Zookeeper 之间的通信正常2、 Agent 与 MySQL之间的通信正常3、 Agent 正常运行4、 Zookeeper 正常运

37、行5、 MySQL正常运行人为停掉MySQL主库上的1 个实例通过 mysqladmin shutdown关闭 MySQL实例通过 kill -9杀掉 MySQL主库的实例1、 Agent 发起自动切换，在较短的时间内将1 个从库置为主库，并提供MySQL服务2、如果在可接受的时间内恢复了MySQL，Agent 不发起自动切换ha004MySQL服务器挂掉MySQL主库服务器直接挂掉不可用MySQL主库服务器挂掉后能否进行正常的MySQL服务1、 Agent 与 Zookeeper 之间的通信正常2、 Agent 与 MySQL之间的通信正常3、 Agent 正常运行4、 Zookeeper 正常运行5、 MySQL正常运行人为关闭MySQL的主库服务器通过 shutdown 命令关掉 MySQL的主库服务器1、 Agent 发起自动切换，在较短的时间内将1 个从库置为主库，并提供MySQL服务2、如果在可接受的时间内恢复了MySQL，Agent 不发起自动切换ha005MySQL主从连接断掉场景描述测试目的前提条件测试方法输入/动作期望的输出用例编号测试场景场景描述测试目的前提条件测试方法输入/动作期望的输出用例编号测试场景场景描述测试目的前提条件MySQL主库的 1 个实例与从库的1 个实例主从复制异常中断MySQL主从复制异常中断能否进行自动切换1、 Agent 与 Zoo