MySQL主从复制与读写分离

一：MySQL主从复制

1.主从复制原理

MySQL 的主从复制和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。

（1）MySQL支持的复制类型

• 基于语句的复制。在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句，MySQL 默认采用基于语句的复制，效率比较高。
• 基于行的复制。把改变的内容复制过去,而不是把命令在从服务器上执行一遍。
• 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

（2）复制的工作过程中

• 在每个事务更新数据完成之前，Master 将这些改变记录进二进制日志。写入二进制日志完成后，Master 通知存储引擎提交事务。
• Slave 将 Master 的 Binary log 复制到其中继日志(Relay log)。首先，Slave 开始一个工作线程--I/0 线程,I/0 线程在 Master 上打开一个普通的连接，然后开始 Binlog dump process。Binlog dump process 从 Master 的进制日志中读取事件，如果已经跟上 Master，它会睡眠并等待 Master 产生新的事件。I/0 线程将这些事件写入中继日志。
• SQL slave thread(sQL 从线程)处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件而更新 Slave 数据，使其与 Master 中的数据保持一致。只要该线程与 I/0 线程保持一致，中继日志通常会位于0S 的缓存中，所以中继日志的开销很小。复制过程有一个很重要的限制，即复制在Save 上是串行化的，也就是说 Master 上的并行更新操作不能在 Slave 上并行操作。

2.MySQL读写分离原理

简单来说，读写分离(图6.3)就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性查询，而从数据库处理 select 查询。数据库复制被用来把主数据库上事务性查询导致的变更同步到集群中的从数据库。

目前较为常见的MySQL读写分离分为两种

（1）基于程序代码内部实现

在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支;缺点是需要开发人员来实现，运维人员无从下手。

（2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有两个代表性程序。

• MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行 SQL 判断，虽然是 MySQL 官方产品，但是 MySQL 官方并不建议将 MySQL-Pr
  oxy 用到生产环境。
• Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由 Java 语言进行开发，阿里巴巴将其用于生产环境。它不支持事务和存储过程。经过上述简单的比较，通过程序代码实现 MySQL 读写分离自然是一个不错的选择，但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的 Java 应用，如果在程序代码中实现读写分离对代码改动就较大。所以，像这种应用一般会考虑使用代理层来实现。本章后续案例通过 Amoeba 实现。
•  MyCAT 是一款开源的分布式关系型数据库中间件，主要用于解决大规模数据存储和高效查询的需求。它支持分布式 SQL 查询，兼容 MySQL通信协议，能够通过数据分片提高数据查询处理能力。MyCAT的前端用户可以将其视为一个数据库代理，使用 MySQL,客户端工具和命令行访问，而后端则可以通过 MySQL 原生协议与多个 MySQL服务器通信，或者使用JDBC协议与大多数主流数据库服务器通信

二：MySQL主从复制案例

1.案例环境

所有节点时间同步

2.配置master主服务器

（1）再/etc/my.cnf 中增加以下内容

重启服务器

登录MySQL程序，给从服务器授权

授权后查看主服务器状态

3.配置slave从服务器

再slave1，slave2服务器上分别执行下面步骤

（1）再/etc/my.cnf中修改或者增加下面内容，server-id 不能相同

slave1

slave2

（2）重启MySQL服务

（3）登录MySQL，配置同步

（4）启动同步

（5）查看slave状态，确保以下值为YES

3.验证主从复制效果

（1）在主服务器上创建数据库

（2）在两台服务器上分别查看数据库。

三：搭建MySQL读写分离

MyCAT是目前最流行的分布式数据库中间插件，是一个开源的分布式数据库系统，是一个实现了 MySQL协议的服务器。前端用户可以把它看作一个数据库代理，用 MySQL 客户端工具和命令行访问，其后端可以用 MySQL 原生协议与多个MySQL 服务器通信，也可以用 JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端 MySQL服务器里或者其他数据库里。

MyCAT 发展到目前，已经不是一个单纯的 MySQL代理了，它的后端可以支持MySQL、SQL Server、0racle、DB2、PostgreSQL 等主流数据库，也支持 MongoDB 这种新型 NoSQL 方式的存储。未来，它还会支持更多类型的存储。

不过，无论是哪种存储方式，在最终用户看，MyCAT里都是一个传统的数据库表，支持标准的 SQL语句进行数据的操作，这样一来，对前端业务系统来说，可以大幅降低开发难度，提升开发速度。

1.安装mycat2

四个软件包

安装配置mycat软件

为简化输入，可将系统环境变量文件“/etc/profile”最后一行的内容追加 Mycat 安装目录的可执行文件所在绝对路径，更新后的“/etc/profile”文件最后一行的完整内容为:

把依赖包 mycat2-1.21-release-jar-with-dependencies.jar 和 mysql-connector-java-8.0.18.jar,原样移动或者复制到目录"/usr/local/mycat/lib”

为 Mycat 命令添加执行权限

到目前为止，安装的步骤基本上算是完成了，任意命令行下执行指令“mycat -h”验证安装的正确性

2.配置mycat读写分离

（1)创建mycat工作所必需的账号

启动 Mycat2服务，需要有真实的数据库服务器支撑才能运行，因此，需要在 MySQL 服务器(其它被 Mycat2 支持的数据库也如此)创建账号并给账号授权,然后在 Mycat2所在的宿主系统用 MySQL,客户端用创建好的账号远程进行连接，验证账号的有效性和正确性。

在前边的章节，我们已经做好了 MySQL数据库间的主从同步，因此创建 Mycat2 所需账号的操作只需也只能在主数据库上进行，具体的指令如下:

配置完成后刷新权限

（2）启动mycat

与 Mycat1.x版本相比，Mycat2 的配置基本不需要手动去修改配置文件，而是可以在 Mycat2 启动之后，登录 Mycat 管理后台，用 SQL 指令或者客户端工具进行配置。在启动 Mycat2之前，需要对原型库的数据源做相应的修改，修改的项主要是主数据库的连接信息，一个完整的修改过的原型数据源文件“/usr/10cal/mycat/conf/datasources/prototypeDs.datasource.json”的内容如下：

启动

查看端口号

用 MySQL 客户端工具连接 Mycat 的服务端口 TCP 8066、用户名与密码在配置文件“/usr/local/mycat/conf/users/root.user. json“

用命令行连接 Mycat 管理后台的指令为“mysql -uroot -p123456 -P8066h192.168.10.101 ”，进入用户交互界面，表明 Mycat2 运行正常，可在此交互界面进行读写分离配置。

（3）mycat配置读写分离

两种配置 MySQL读写分离的方法，一种是直接在 Mycat 的配置目录“/usr/1ocal/mycat/conf”的子目录编辑相关的文本文件(Mycat1.x版本只用这种方法);另一种登录到 Mycat 交互界面，用特殊语法的 SQL 命令进行配置。本教程采用第一种方法，直接在 Mycat 的交互界面输入命令。

第一步：mycat增加数据源

需要正确输入的数据主要包括:MySQL 主从数据库的IP地址、数据库库名(schema)、数据库账号、数据库密码(生产数据库请使用复杂密码)、实例类型(READ、WRITE 或 READ WRITE)。下边是添加一个主库源和两个从库源的具体指令:

增加主数据库：

增加从库slave1和slave2：

第二步：创建mycat集群

查看数据源信息

查看并修改集群配置信息

修改负载均衡的默认轮询

修改配置后重启mycat

3.验证mycat读写分离

（1）登录mycat集群，创建测试库和测试表

注意：

创建完库以后在 mycat 的 schemas 目录下会有一个以该库明明的文件，要修改这个文件标注上 mycat 的集群，然后重启 mycat。

（2）停止salve1和salve2的主从同步

在两台上执行

（3）在master和slave1，slave2创建测试数据

master上操作

slave1上操作

在server2上操作

（4）测试读操作

登录mycat集群，查询ttt.t1 的数据

（4）测试写操作

但在 slave1 和 slave2 上査询不到,最终只有在 Master 上才能查看到这条语句内容，说明写操作在 Master 服务器上。由此验证，已经实现了 MySQL 读写分离。目前所有的写操作都全部在 Master 主服务器上，用来避免数据的不同步;所有的读操作都分摊给了 Slave 从服务器，用来分担数据库压力。