MySQL读写分离-海口c网

GTID一主多从搭建==异步复制== ==半同步复制==

| 机器   | ip             |
| ------ | -------------- |
| master | 192.168.66.143 |
| slave1 | 192.168.66.144 |
| slave2 | 192.168.66.145 |


> 将144的数据复制到145去，删除数据目录下的auto.cnf过程省略读写分离读写分离，从字面理解就是将对数据库的读操作与写操作分离的一种优化手段。其最早起源于互联网快速发展时期，面对海量用户访问问题，通过这一技术来解决数据库性能瓶颈问题。目前已经成为非常常见的一种数据库访问优化技术。在互联网的系统应用是一个读多写少的应用，比如电商系统中，商品浏览的次数是比下单要多的。数据库承载压力大，主要是由这些读的请求造成的，那么我们是不是可以把读操作和写操作分开，让所有读的请求落到专门负责读的数据库上，所有写的操作落到专门负责写的数据库上，写库的数据同步到读库上，这样保证所有的数据修改都可以在读取时，从读库获得。
如果系统的读请求比较多的话，读库可以多部署几台，这样读请求就可以均摊到多台读库上，降低每一个读库上的压力。但是在写数据的时候，数据要落在一个确定，且唯一的写库中。上图中，咱们的写库只有一个，你当然可以部署多个写库。比如：商户发布商品时，将这个商品的数据落在了写库上，同时，写库将这条数据同步给两个读库，买家在网站浏览商品时，会从读库将这个商品数据读取，至于从哪个读库取出数据，那就要看这个请求在当前的路由情况了。读写分离的优缺点好处- 提高访问性能 通过引入读写分离技术，将之前集中于单点的访问压力，分散到更多节点。即可利用更多的资源，支撑业务系统，可有效提升整体访问性能。
- 提高稳定性 通过将读取与写入操作的分离，可有效规避由于异常操作所带来的风险。常见如一个大查询语句，因访问数据规模巨大占用大量CPU资源。通过承载端分离，可避免影响更为重要的写入操作。
- 提高资源利用率 为了更好地保护数据，数据库系统通常采用多副本技术冗余保护数据，但其备用副本如无法提供业务访问，将是一种资源浪费，而读写分离可有效利用只读副本，提升整体资源利用率。
- 提高可用性 通过引入更多节点来承载读写操作，结合负载均衡与高可用探查技术，可避免单点故障引发可用性问题。
- 提高访问效率 通过利用不同节点分别承载读取与写入，还可缓解因为锁带来的争用问题，提高单节点的访问效率。
- 更大优化空间 针对读取操作的特殊性，可通过分离后的独立资源采取特有的优化技术，进一步提升访问效率。引入数据访问代理的好处是源程序不需要做任何改动就可以实现读写分离，坏处是由于多了一层中间件做中转代理，性能上会有所下降，数据访问代理也容易成为性能瓶颈，并且还存在一定维护成本。还有另一种方式，是将数据访问代理层前置到应用侧，通过SDK方式与应用集成在一起，可避免独立一层所带来的性能损耗和维护成本高的问题。但这种方式对开发语言有一定要求，存在适用性问题。缺点目前业界流行的读写分离方案，通常都是基于上述主从模式的数据库架构，通过引入数据访问代理层，来实现访问动作的读写分离。数据从写入到数据库，到从数据库取出，读写分离的架构多了一个同步的操作，同步的操作时间是多少，延迟如果太大对系统有没有影响，如果同步挂了怎么办？例如：电商个人中心的订单列表页，功能挺简单的，只需要将订单数据取出来，在页面上展示就可以了，但是在做的时候，订单以及订单相关的数据都是从读库取出的，其中就包括支付状态，这个用户非常敏感的字段。就在某一天的某一时段，突然接到了用户大量的投诉，说用户已经付了钱了，但是订单的状态还是未支付，去数据库里查询，发现订单状态就是未支付，没有问题。为了保险起见，去写库再查一下这个订单吧，发现写库的订单状态确实是已支付，这下完了，写库和读库取出来的数据不一致，问题根源就是同步挂掉了。当同步挂掉，或者同步延迟比较大时，写库和读库的数据不一致，这个数据的不一致，用户能不能接受，订单支付状态这个不一致当然是不能接受的了，其他的业务场景能不能接受呢？这个要对不同的业务场景做具体的分析了mycat介绍MyCAT是一款开源的MySQL数据库中间件，它可以实现MySQL数据库的水平扩展、读写分离等功能。MyCAT主要由以下几个组件组成：- SQL解析器：解析客户端发送的SQL语句，并生成相应的执行计划。
- 数据节点：负责处理SQL语句的执行，并将结果返回给客户端。
- 配置管理器：负责管理集群配置，包括数据节点、读写分离策略等。MyCAT工作原理1. SQL解析：客户端发送SQL语句到MyCAT，MyCAT解析SQL语句并生成执行计划。
2. 路由决策：根据执行计划，MyCAT决定将SQL语句发送到哪个数据节点。
3. 数据执行：数据节点执行SQL语句，并将结果返回给MyCAT。
4. 结果返回：MyCAT将执行结果返回给客户端。读写分离实现==mycat服务器不要安装mysql====确保主从是正常且可用==

```
#创建mycat用户
mysql> create user 'mycat'@'%' identified with mysql_native_password by '123456';#给slave授权
mysql> GRANT ALL PRIVILEGES  on *.* to 'mycat'@'%';FLUSH PRIVILEGES;
```

| 服务器 | ip             |
| :----- | :------------- |
| mycat  | 192.168.66.138 |
| master | 192.168.66.143 |
| slave1 | 192.168.66.144 |
| slave2 | 192.168.66.145 |初始化环境```
#每台服务器上都初始化，关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
```


安装jdk1. 先下载jdk压缩包2. 解压压缩包3. 配置系统环境变量

 ```# vim /etc/profileexport JAVA_HOME=/root/jdk1.8.0_151export JRE_HOME=$JAVA_HOME/jreexport CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib/rt.jarexport PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin# source /etc/profile# java -version```


下载mycat[MYCAT官方网站](https://www.mycat.org.cn/)安装mycat

```
tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gzmv mycat /usr/local/# 创建专门运行mycat账号
adduser mycat# 将文件权限赋给mycat账号
chown -R mycat:mycat /usr/local/mycat
```


配置环境变量

```
# 配置环境变量并添加 
vim /etc/profile
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin# 使配置生效
source /etc/profile
```


mycat目录结构

| 目录/文件   | 说明                                                         |
| ----------- | ------------------------------------------------------------ |
| bin         | Mycat 的运行目录，包含启动、重启、停止等脚本命令。           |
| catlet      | Mycat 的扩展功能模块，用于支持额外的功能开发和集成。         |
| conf        | Mycat 的配置信息目录，重点关注该目录下的配置文件（如 `server.xml`,`rule.xml`,`schema.xml` 等）。 |
| lib         | Mycat 引用的 jar 包目录，Mycat 是基于 Java 开发的，依赖这些 jar 包运行。 |
| logs        | 日志文件目录，包含 Mycat 启动日志和运行日志，便于排查问题和监控运行状态。 |
| version.txt | Mycat 版本说明文件，记录当前 Mycat 的版本信息。              |mycat相关命令| 指令      | 作用                  | 使用场景                             |
| --------- | --------------------- | ------------------------------------ |
| `console` | 控制台模式启动 Mycat  | 开发、测试环境，或需要实时查看日志时 |
| `start`   | 后台模式启动 Mycat    | 生产环境或需要长期运行 Mycat 服务时  |
| `stop`    | 停止 Mycat 服务       | 需要关闭 Mycat 服务时                |
| `restart` | 重启 Mycat 服务       | 更新配置文件后重新加载配置           |
| `status`  | 查看 Mycat 的运行状态 | 确认 Mycat 是否正常运行              |
| `dump`    | 导出线程堆栈信息      | 排查性能问题或死锁问题时             |Mycat的日志文件都在logs目录里面- wrapper.log ：启动日志
- mycat.log ：详细工作日志

配置文件解释在Mycat中，`server.xml`、`schema.xml`与`rule.xml`是三个非常重要的配置文件，它们各自负责不同的配置方面，以确保Mycat能够正确地进行数据库分片、读写分离等功能。#### server.xml端口设置| 参数          | 端口 | 用途                                      | 连接方式                              |
| ------------- | ---- | ----------------------------------------- | ------------------------------------- |
| `serverPort`  | 8066 | 客户端连接 Mycat，进行正常的 SQL 查询操作 | 客户端通过 JDBC 或 MySQL 客户端连接   |
| `managerPort` | 9066 | 管理员连接 Mycat，执行管理命令            | 管理员通过 MySQL 客户端或其他工具连接 |防火墙设置| 标签          | 说明                                                         |
| ------------- | ------------------------------------------------------------ |
| `<firewall>`  | 定义防火墙规则，包含白名单和黑名单。                         |
| `<whitehost>` | 白名单规则，只有符合规则的客户端允许访问，优先级高于黑名单。 |
| `<host>`      | 定义白名单的具体规则，包括客户端 IP 地址范围和用户名。       |
| `<blacklist>` | 黑名单规则，禁止特定客户端或操作，仅在白名单未启用且 `check="true"` 时生效。 |> 如果不需要访问控制，可以不配置 `<whitehost>` 和 `<blacklist>`，或者将 `<blacklist>` 的 `check` 设置为 `false`。
``` | 配置项 | 说明 | | ---------------- | ----------------------------------------------- | | `<schema>` | 定义逻辑库，包括名称、SQL检查、最大行数限制等。 | | `checkSQLschema` | 是否自动去掉表名前缀的数据库名称。 | | `sqlMaxLimit` | 查询结果的最大行数限制，默认添加 `LIMIT`。 | | `dataNode` | 默认数据节点，用于未定义分片规则的表。 | ``` <dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" />
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">name="TESTDB"：定义了一个逻辑库名称为 TESTDB。这是 Mycat 对外暴露的数据库名称，客户端连接时会使用这个名称。
checkSQLschema="true"：如果设置为 true，Mycat 会在 SQL 解析时自动去掉表名前缀的数据库名称。如果客户端发送的 SQL 是：SELECT * FROM TESTDB.travelrecord;Mycat 会自动处理为：SELECT * FROM travelrecord;这个功能可以避免客户端在 SQL 中硬编码数据库名称，方便迁移和维护。
sqlMaxLimit="100"：设置查询结果的最大行数限制。如果客户端未指定 LIMIT，Mycat 会自动添加 LIMIT 100，防止返回过多数据导致性能问题。客户端执行 SELECT * FROM travelrecord;Mycat 实际执行的是：SELECT * FROM travelrecord LIMIT 100;
dataNode="dn1"：如果某个表没有明确指定分片规则，则默认将数据存储到 dn1 数据节点上。这是一个备用规则，用于处理未定义分片规则的情况。

```| 配置项           | 说明                                            |
| ---------------- | ----------------------------------------------- |
| `<schema>`       | 定义逻辑库，包括名称、SQL检查、最大行数限制等。 |
| `checkSQLschema` | 是否自动去掉表名前缀的数据库名称。              |
| `sqlMaxLimit`    | 查询结果的最大行数限制，默认添加 `LIMIT`。      |
| `dataNode`       | 默认数据节点，用于未定义分片规则的表。          |```
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />


name 属性定义数据节点的名称。定义了两个数据节点：dn1 和 dn2。数据节点名称会在其他配置文件（如 schema.xml）中被引用，用于指定逻辑表的数据存储位置。
dataHost 属性指定该数据节点所属的物理数据库主机。在此例中，dn1 和 dn2 都属于 localhost1 主机。dataHost 的具体信息需要在 schema.xml 文件中通过 <dataHost> 标签进行定义。
database 属性指定该数据节点对应的物理数据库名称。dn1 对应的物理数据库是 db1。dn2 对应的物理数据库是 db2。Mycat 会根据 dataNode 的配置，将逻辑表的数据路由到对应的物理数据库中。

```| 属性       | 说明                                                         |
| ---------- | ------------------------------------------------------------ |
| `name`     | 数据节点的名称，用于在其他配置文件中引用。                   |
| `dataHost` | 数据节点所属的物理数据库主机名称，需与 `<dataHost>` 标签中的 `name` 属性一致。 |
| `database` | 数据节点对应的物理数据库名称。                               |```

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">name="localhost1"定义数据主机的名称。数据主机名称会在 <dataNode> 配置中被引用，用于指定逻辑表的数据存储位置。
maxCon="1000"设置该数据主机的最大连接数。在此例中，最大连接数为 1000。最大连接数应根据物理数据库的性能和硬件资源合理设置，避免超出数据库的承载能力。
minCon="10"设置该数据主机的最小连接数。在此例中，最小连接数为 10。Mycat 会预先创建这些连接以提高性能。最小连接数应根据实际需求设置，过大会浪费资源，过小可能导致连接延迟。
balance="0"定义读写分离的负载均衡策略。可选值：0：不开启读写分离，所有读操作都发送到主库（writeHost）。1：开启读写分离，读操作优先发送到从库（readHost），如果从库不可用，则发送到主库。2：开启读写分离，读操作随机发送到主库或从库。3：所有读操作都发送到从库，主库只负责写操作。
writeType="0"定义写操作的分发策略。可选值：0：写操作只发送到第一个主库（writeHost）。1：写操作可以轮询发送到多个主库（适用于多主架构）。
dbType="mysql"指定数据库类型。
在此例中，数据库类型为 mysql。dbDriver="native"指定数据库驱动类型。在此例中，使用的是 Mycat 内置的原生 MySQL 驱动（native）。
switchType="1"定义主从切换策略。可选值：1：自动切换。当主库不可用时，Mycat 会自动切换到从库。-1：不自动切换。2：基于 MySQL 主从同步状态进行切换。3：基于 Galera Cluster 的切换。
slaveThreshold="100"定义从库延迟阈值（单位为秒）。在此例中，延迟超过 100 秒的从库将不会被分配读请求。避免从库因延迟过高而导致数据不一致。

```| `name`           | 数据主机的名称，用于在 `<dataNode>` 中引用。   |
| ---------------- | ---------------------------------------------- |
| `maxCon`         | 最大连接数，限制该数据主机的并发连接数。       |
| `minCon`         | 最小连接数，预先创建的连接数以提高性能。       |
| `balance`        | 读写分离的负载均衡策略。                       |
| `writeType`      | 写操作的分发策略。                             |
| `dbType`         | 数据库类型，如 `mysql`。                       |
| `dbDriver`       | 数据库驱动类型，如 `native` 或 `jdbc`。        |
| `switchType`     | 主从切换策略。                                 |
| `slaveThreshold` | 从库延迟阈值，超过该值的从库不会被分配读请求。 |```
<heartbeat>select user()</heartbeat>


定义心跳检测 SQL。
Mycat 会定期执行该 SQL 来检测数据库的可用性。
在此例中，心跳 SQL 是 select user()，这是一个轻量级的查询，用于检查数据库是否正常运行。

``````
<writeHost host="hostM1" url="localhost:3306" user="root" password="123456"></writeHost>host="hostM1"定义主库的名称。
url="localhost:3306"指定主库的连接地址。在此例中，主库运行在本地主机（localhost），端口为 3306。
user="root" 和 password="123456"指定连接主库的用户名和密码。在此例中，用户名为 root，密码为 123456。
``````
<writeHost host="hostM1" url="localhost:3306" user="root" password="123456"><readHost host="hostS1" url="localhost:3307" user="root" password="123456"/><readHost host="hostS2" url="localhost:3308" user="root" password="123456"/>
</writeHost>hostM1 是主库。
hostS1 和 hostS2 是从库，分别运行在 localhost:3307 和 localhost:3308 上。
主库和从库共同组成了一个读写分离的架构。
```| 属性          | 说明                                                         |
| ------------- | ------------------------------------------------------------ |
| `<heartbeat>` | 心跳检测 SQL，用于检查数据库的可用性。                       |
| `<writeHost>` | 主库的连接信息，包括名称、地址、用户名和密码。               |
| `<readHost>`  | 从库的连接信息，嵌套在 `<writeHost>` 标签内，用于实现读写分离。 |