数据库MySQL-读写分离

第 1 章 读写分离

1.1 MySQL 主从复制的几种方案

数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。

从数据库的角度来说，对于大多数应用来说，从集中到分布，最基本的一个需求不是数据存储的瓶颈，而是在于计算的瓶颈，即 SQL 查询的瓶颈，我们知道，正常情况下，Insert SQL 就是几十个毫秒的时间内写入完成，而系统中的大多数 Select SQL 则要几秒到几分钟才能有结果，很多复杂的 SQL，其消耗服务器 CPU 的能力超强，不亚于死循环的威力。在没有读写分离的系统上，很可能高峰时段的一些复杂 SQL 查询就导致数据库服务器 CPU爆表，系统陷入瘫痪，严重情况下可能导致数据库崩溃。因此，从保护数据库的角度来说，我们应该尽量避免没有主从复制机制的单节点数据库。

对于 MySQL 来说，标准的读写分离是主从模式，一个写节点 Master 后面跟着多个读节点，读节点的数量取决于系统的压力，通常是 1-3 个读节点的配置，如下图所示：

在这里插入图片描述

MySQL 支持更多的主从复制的拓扑关系，如下图所示，但通常我们不会采用双向主从同步以及环状的拓扑：

在这里插入图片描述

MySQL 主从复制的原理如下：

第一步是在主库上记录二进制日志（稍后介绍如何设置）。在每次准备提交事务完成数 据更新前，主库将数据更新的事件记录到二进制日志中。MySQL 会按事务提交的顺序 而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引 擎可以提交事务了。 下一步，备库将主库的二进制日志复制到其本地的中继日志中。首先，备库会启动一个工作线程，称为 I/O 线程，I/O 线程跟主库建立一个普通的客户端连接，然后在主库上启 动一个特殊的二进制转储(binhg dump、线程（该线程没有对应的 SQL 命令），这个二 进制转储线程会读取主库上二进制日志中的事件。它不会对事件进行轮询。如果该线程追赶上了主库，它将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会 被唤醒，备库 I/O 线程会将接收到的事件记录到中继日志中。

备库的 SQL 线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现 备库数据的更新。当SQL 线程追赶上 I/O 线程时，中继日志通常已经在系统缓存中，所 以中继日志的开销很低。SQL 线程执行的事件也可以通过配置选项来决定是否写入其自 己的二进制日志中，它对于我们稍后提到的场景非常有用。这种复制架构实现了获取事件和重放事件的解耦，允许这两个过程异步进行。也就是说 I/o 线程能够独立于 SQL 线程之外工作。但这种架构也限制了复制的过程，其中最重要 的一点是在主库上并发运行的査询在备库只能串行化执行，因为只有一个 SQL 线程来重 放中继日志中的事件。后面我们将会看到，这是很多工作负载的性能瓶颈所在。虽然有一些针对该问题的解决方案，但大多数用户仍然受制于单线程。MySQL5.6 以后，提供了基于 GTID 多开启多线程同步复制的方案，即每个库有一个单独的(sql thread)进行同步复制，这将大大改善 MySQL 主从同步的数据延迟问题，配合 Mycat 分片，可以更好的将一个超级大表的数据同步的时延降低到最低。此外，用 GTID 避免了在传送 binlog 逻辑上依赖文件名和物理偏移量，能够更好的支持自动容灾切换，对运维人员来说应该是一件令人高兴的事情，因为传统的方式里，你需要找到 binlog和 POS 点，然后 change master to 指向，而不是很有经验的运维，往往会将其找错，造成主从同步复制报错，在 mysql5.6 里，无须再知道 binlog 和 POS 点，需要知道 master 的 IP、端口，账号密码即可，因为同步复制是自动的，mysql 通过内部机制 GTID 自动找点同步。

即使是并发复制机制、仍然无法避免主从数据库的数据瞬间不同步的问题，因此又有了一种增强的方案，即galera for mysql、percona-cluster 或者 mariadb cluster 等集群机制，他们是一种多主同步复制的模式，可以在任意节点上进行读写、自动控制成员，自动删除故障节点、自动加入节点、真正给予行级别的并发复制等强大能力!

下图是其原理图，通常是采用 3 个 MySQL 节点作为一个 Cluster，即提供了 3 倍的数据库读的并发能力.galera for mysql 集群这种方式，是牺牲了数据的写入速度，以换取最大程度的数据并发访问能力，类似Mycat 里的全局表，并且保证了数据同时存在几个有效的副本，从而具有非常高的可靠性，因此在某种程度上，可以替代 Oracle 的一些关键场景，**目前开源中间件中，只有 Mycat 很完美的支持了 galera for mysql 集群模式。

在这里插入图片描述

1.2 MySQL 主从复制的几个问题

MySQL 主从复制并不完美，存在着几个由来已久的问题，首先一个问题是复制方式：

• 基于 SQL 语句的复制(statement-based replication, SBR)；
• 基于行的复制(row-based replication, RBR)；
• 混合模式复制(mixed-based replication, MBR)；
• 基于 SQL 语句的方式最古老的方式，也是目前默认的复制方式，后来的两种是 MySQL 5 以后才出现的复制方式。

RBR 的优点：

• 任何情况都可以被复制，这对复制来说是最安全可靠的；
• 和其他大多数数据库系统的复制技术一样；
• 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多。

RBR 的缺点：

• binlog 大了很多；
• 复杂的回滚时 binlog 中会包含大量的数据；
• 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题；
• 无法从 binlog 中看到都复制了写什么语句。

SBR 的优点：

• 历史悠久，技术成熟；
• binlog 文件较小；
• binlog 中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况；
• binlog 可以用于实时的还原，而不仅仅用于复制；
• 主从版本可以不一样，从服务器版本可以比主服务器版本高。

SBR 的缺点：

• 不是所有的 UPDATE 语句都能被复制，尤其是包含不确定操作的时候；
• 复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁；
• 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响；
• 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错；
• 执行复杂语句如果出错的话，会消耗更多资源。

选择哪种方式复制，会影响到复制的效率以及服务器的损耗，甚以及数据一致性性问题，目前其实没有很好的客观手手段去评估一个系统更适合哪种方式的复制，Mycat 未来希望能通过智能调优模块给出更科学的建议。

第二个问题是关于主从同步的监控问题，Mysql 有主从同步的状态信息，可以通过命令 show slave status获取，除了获知当前是否主从同步正常工作，另外一个重要指标就是 Seconds_Behind_Master，从字面理解，它表示当前 MySQL 主从数据的同步延迟，单位是秒，但这个指标从 DBA 的角度并不能简单的理解为延迟多少秒，感兴趣的同学可以自己去研究，但对于应用来说，简单的认为是主从同步的时间差就可以了，另外，当主从同步停止以后，重新启动同步，这个数值可能会是几万秒，取决于主从同步停止的时间长短，我们可以认为数据此时有很多天没有同步了，而这个数值越接近零，则说明主从同步延迟最小，我们可以采集这个指标并汇聚曲线图，来分析我们的数据库的同步延迟曲线，然后根据此曲线，给出一个合理的阀值，主从同步的时延小于阀值时，我们认为从库是同步的，此时可以安全的从从库读取数据。Mycat 未来将支持这种优化，让应用更加可靠的读取到预期的从库数据。

1.3 Mycat 支持的读写分离

1. 配置 mysql 端主从的数据自动同步，mycat 不负责任何的数据同步问题。
2. Mycat 配置读写分离，具体参数参加前面章节。

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" 
writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="localhost2:3306" user="root" password="123456" 
weight="1" />
</writeHost>
</dataHost>
或者
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" 
writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
</writeHost>
<writeHost host="hostS1" url="localhost:3307" user="root" password="123456">
</writeHost>
</dataHost>

以上两种取模第一种当写挂了读不可用，第二种可以继续使用，事务内部的一切操作都会走写节点，所以读操作不要加事务，如果读延时较大，使用根据主从延时的读写分离，或者强制走写节点：

1.3.1 应用强制走写：

一个查询 SQL 语句以/*balance*/注解来确定其是走读节点还是写节点。
1.6 以后添加了强制走读走写处理：
强制走从：
/*!mycat:db_type=slave*/ select * from travelrecord
/*#mycat:db_type=slave*/ select * from travelrecord
强制走写：
/*#mycat:db_type=master*/ select * from travelrecord
/*!mycat:db_type=master*/ select * from travelrecord

1.3.2 根据主从延时切换：

1.4 开始支持 MySQL 主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

MyCAT 心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType="2" 与slaveThreshold="100"，此时意味着开启 MySQL 主从复制状态绑定的读写分离与切换机制，Mycat 心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running","Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及 Seconds_Behind_Master 主从复制时延，当Seconds_Behind_Master>slaveThreshold 时，读写分离筛选器会过滤掉此 Slave 机器，防止读到很久之前的旧数据，而当主节点宕机后，切换逻辑会检查 Slave 上的 Seconds_Behind_Master 是否为 0，为 0 时则表示主从同步，可以安全切换，否则不会切换。

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" 
writeType="0" dbType="mysql" dbDriver="native" switchType="2"
slaveThreshold="100">
<heartbeat>show slave status </heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="localhost:3306" user="root" 
password="123456">
</writeHost>
<writeHost host="hostS1" url="localhost:3316" user="root" 
password="123456" />
</dataHost>

1.4.1 开始支持 MySQL 集群模式，让读更加安全可靠，配置如下：
MyCAT 心跳检查语句配置为 show status like ‘wsrep%’
，
dataHost 上定义两个新属性： switchType="3"
此时意味着开启 MySQL 集群复制状态状态绑定的读写分离与切换机制，Mycat 心跳机制通过检测集群复制时延时
如果延时过大或者集群出现节点问题不会负载改节点。
dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" 
writeType="0" dbType="mysql" dbDriver="native" switchType="3" >
<heartbeat> show status like ‘wsrep%’</heartbeat>
<writeHost host="hostM1" url="localhost:3306" 
user="root"password="123456">
</writeHost>
<writeHost 
host="hostS1"url="localhost:3316"user="root"password="123456" ></writeHost>
</dataHost>