SpringBoot+Mycat实现Mysql读写分离、主从复制、单库按年份分表（水平分片）
一.需求分析
在开发一个知识管理系统,主要功能是检索文章,用户每检索一次或浏览一篇文章就需要分别增加一条记录到用户检索记录表和用户浏览表,删除用逻辑删除.那可能一天就会产生很多条检索记录和浏览记录.为了提高性能,决定对mysql实现主从复制,并通过mycat数据库中间件实现读写分离以及按年分表.
二.Mysql主从复制
2.1 什么是主从复制
主数据库一般是准实时的业务数据库,而从数据库通过二进制日志文件同步主数据库,从而建立一个和主数据库完全一样的数据库环境.从数据库通过二进制日志文件同步主数据库数据到本地这一过程就叫主从复制.
2.2 主从复制原理
假设MySQL-A为主数据库(master),MYSQL-B为从数据库(slave)
2.2.1.在MySQL-A执行任何有关写sql操作时,都会写入本地的日志系统A中.然后实时的将变化了的日志系统中的数据库事件操作,在MYSQL-A的3306端口,通过网络发给MYSQL-B.
2.2.2.MYSQL-B收到后,写入本地日志系统B,然后一条条的将数据库事件在数据库中完成.那么,MYSQL-A的变化,MYSQL-B也会变化,这样就是所谓的MYSQL的复制,即MYSQL replication
2.2.3.日志系统A,其实它是MYSQL的日志类型中的二进制日志,也就是专门用来保存修改数据库表的所有动作,即bin log【注意MYSQL会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全】
2.2.4.日志系统B,并不是二进制日志,由于它是从MYSQL-A的二进制日志复制过来的,并不是自己的数据库变化产生的,有点接力的感觉,称为中继日志,即relay log.
2.2.5.通过上面的机制,可以保证MYSQL-A和MYSQL-B的数据库数据一致.
2.3 主从复制作用
利用主从复制可以实现数据库备份,读写分离,数据库负载均衡,高可用等等.
2.4 配置mysql实现主从复制(最好保证主从mysql版本一致)
2.4.1 配置主库:
2.4.1.1 主库里my.ini文件新增以下内容(linux对应my.cnf文件)
#服务器唯一标示,一般取服务器的ip
server_id=54
#启动MySQL二进制日志
log_bin=mysql-bin
2.4.1.2 保存my.ini
2.4.1.3 重启主数据库
2.4.1.4 主库给从库创建授权账号
下面中gmdi为用户名,123456为密码,%表示所有客户端都可能连,可以改成具体ip增加安全性
GRANT REPLICATION SLAVE ON *.* to 'gmdi'@'%' identified by '123456';
2.4.1.5 刷新权限
flush privileges
2.4.1.6 查询master状态,记录File 和 Position 的值
show master status
2.4.2 配置从库:
2.4.2.1 从库my.ini文件新增以下内容(linux对应my.cnf文件)
#服务器id
server_id=55
#开启日志文件
log-bin=mysql-bin
#需要同步的数据库名称
binlog_do_db=tpi
2.4.2.2 重启从数据库
2.4.2.3 在从库新增主库信息
一下步骤一步一步 执行
stop slave
下句对应字段解释:
master_host= 主库所在服务器IP。
master_user=’gmdi’ 是2.4.1.4步骤创建的用户名。
master_log_file=’mysql-bin.000003’ 是2.4.1.6步骤File值
master_log_pos=92613 是2.4.1.6步骤Position值
change master to master_host='10.254.24.54',master_user='gmdi',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=92613;
start slave
2.4.2.4 查询slave状态
show slave status
当Slave_IO_Running和Slave_SQL_Running均为Yes时说明主从复制配置成功
三.读写分离
3.1 什么是读写分离
在数据库集群架构中，让主库负责处理事务性查询，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。当然，主数据库另外一个功能就是负责将事务性查询导致的数据变更同步到从库中，也就是写操作。
3.2读写分离好处
1）分摊服务器压力，提高机器的系统处理效率
读写分离适用于读远比写的场景，如果有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能并不高，而主从只负责各自的写和读，极大程度的缓解X锁和S锁争用；
假如我们有1主3从，不考虑上述1中提到的从库单方面设置，假设现在1分钟内有10条写入，150条读取。那么，1主3从相当于共计40条写入，而读取总数没变，因此平均下来每台服务器承担了10条写入和50条读取（主库不承担读取操作）。因此，虽然写入没变，但是读取大大分摊了，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了，说白了就是拿机器和带宽换性能；
2）增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务
四.mycat实现读写分离
4.1 Mycat简介
Mycat 是一个强大的数据库中间件,可以用作读写分离、以及分表分库、容灾备份等等.
具体介绍可查看mycat官方文档:Mycat权威指南
不适合使用Mycat的场景: Mycat不适合的场景
Mycat常用分片规则介绍: Mycat15种分片规则
Mycat读写分离实现方式:
应用连接Mycat,Mycat拦截应用对数据库的操作请求,如果是写SQL(insert,update,delete…),则路由到主库执行SQL语句,而从库根据二进制日志文件进行同步数据;如果是读SQL(select),Mycat就会直接路由到从库进行SQL查询.
4.2 安装Mycat(windows环境)
4.2.1 下载
从如下地址下载mycat的安装包：mycat安装包下载地址
eg：Mycat-server-1.6.7.1-release-20200209222254-win.tar.gz
4.2.2 解压
解压下载的安装包
4.2.3 安装
安装mycat前需要先安装jdk和mysql.mycat1.6版本建议使用的jdk是1.7以上版本,mysql我用的是5.7.24版本
安装完jdk和mysql后,进入mycat解压目录下的bin目录,如本文的路径如下:
D:\tpi\mycat\bin
用管理员身份打开cmd命令行窗口，执行如下命令安装mycat:
mycat.bat install
4.2.4 启动和停止
可以使用如下命令启动mycat服务
mycat.bat start
启动后可以通过如下命令查看mycat的运行状态:
mycat.bat status
可以使用如下命令停止mycat服务
mycat.bat stop
4.3 Mycat配置Mysql读写分离以及按年份分表(枚举分片规则)
4.3.1采用分片规则原因
查看Mycat常用的分片规则后,只有按日期(天)还有按照自然月进行分表,好像并没有实现按照年份实现分片,而采用枚举分片规则能一定程度上实现按年分表,因此我只创建了2020-2030年份的浏览表,2031年之后的数据是统一存放到一个表的.
4.3.1 Mysql创建好表
4.3.2 修改Mycat的server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
<property name="useHandshakeV10">1</property>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<!-- 配置MyCat ID自增方式，0 为本地文件方式，1 为数据库方式，2 为时间戳序列方式 -->
<property name="sequnceHandlerType">2</property>
<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>-->
<!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit 1开启 0关闭
-->
<property name="useOffHeapForMerge">0</property>
<!--
单位为m
-->
<property name="memoryPageSize">64k</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换 -->
<property name="useZKSwitch">false</property>
<!-- XA Recovery Log日志路径 -->
<!--<property name="XARecoveryLogBaseDir">./</property>-->
<!-- XA Recovery Log日志名称 -->
<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
</system>
<!-- 全局SQL防火墙设置 -->
<!--白名单可以使用通配符%或着*-->
<!--例如<host host="127.0.0.*" user="root"/>-->
<!--例如<host host="127.0.*" user="root"/>-->
<!--例如<host host="127.*" user="root"/>-->
<!--例如<host host="1*7.*" user="root"/>-->
<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
<!--
<firewall>
<whitehost>
<host host="1*7.0.0.*" user="root"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<!-- 添加user -->
<user name="mycat" defaultAccount="true">
<property name="password">123456</property>
<!-- 逻辑数据库名称,schemas.xml要用到-->
<property name="schemas">tpi</property>
</user>
</mycat:server>
4.3.3 修改Mycat的rule.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<!-- 枚举规则 -->
<tableRule name="sharding_by_year">
<rule>
<columns>year</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<!-- 定义枚举算法，
mapFile：指定枚举映射文件
type：字段属性类型，默认值为0，0表示Integer，非零表示String
defaultNode：指定默认节点，如果year值没有在partition-hash-int.txt中配置枚举映射，则会被映射到默认的节点上，
其值从0开始，对应了schema.xml文件<table>的subTables属性值
-->
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property>
<property name="defaultNode">11</property>
</function>
</mycat:rule>
4.3.4 修改Mycat的schemas.xml文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 与server.xml中user的schemas名一致 -->
<!-- 定义一个MyCat的模式，此处定义了一个逻辑数据库名称tpi -->
<!-- “checkSQLschema”：描述的是当前的连接是否需要检测数据库的模式 -->
<!-- “sqlMaxLimit”：表示返回的最大的数据量的行数 -->
<!-- “dataNode="dn1"”：该操作使用的数据节点是dn1的逻辑名称 -->
<schema name="tpi" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
<!-- name：逻辑表名
primaryKey：真实主键
autoIncrement：是否开启主键自增
dataNode：指定数据节点（绑定真实数据库）
subTables：指定所有真实分表，gmdi_browse202$0-9为缩写
rule:指定路由策略,与rule.xml中tableRule的name属性值保持一致
-->
<table name="gmdi_browse" primaryKey="id" autoIncrement="true" dataNode="dn1"
subTables="gmdi_browse202$0-9,gmdi_browse2030,gmdi_browse2031_9999" rule="sharding_by_year"/>
</schema>
<!-- 定义个数据的操作节点，以后这个节点会进行一些库表分离使用 -->
<!-- “dataHost="jdbchost"”：定义数据节点的逻辑名称 -->
<!-- “database="dn1"”：定义数据节点要使用的真实数据库名称 -->
<dataNode name="dn1" dataHost="jdbchost" database="tpi" />
<!-- 定义数据节点，包括各种逻辑项的配置 -->
<!--balance 属性：负载均衡类型，目前的取值有 3 种：
1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
2. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
3. balance="2"，所有读操作都随机的在 writeHost、 readhost 上分发。
4. balance="3"，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有。-->
<!--writeType 属性：负载均衡类型，目前的取值有 3 种：
1. writeType="0", 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
2. writeType="1"，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。-->
<dataHost name="jdbchost" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<!-- 配置真实MySQL与MyCat的心跳 -->
<heartbeat>select user()</heartbeat>
<!-- 配置真实的MySQL的连接信息 -->
<writeHost host="hostMaster" url="10.254.24.54:3306" user="root" password="123456">
<readHost host="hostSlave" url="10.254.24.55:3306" user="root" password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
4.3.5 partition-hash-int.txt 配置
2020=0
2021=1
2022=2
2023=3
2024=4
2025=5
2026=6
2027=7
2028=8
2029=9
2030=10
2031_9999=11
其中2020,2021等为分表列(year)对应的值（应当包括所有可取值,等号右边的值为MyCat subTables属性解析后得到的表名数组的索引值。如我的subTables配置会产生[gmdi_browse2020,gmdi_browse2021…,gmdi_browse2031_9999]这么一个长度为12的数组,其中gmdi_browse2020对应的索引为0,以此类推,gmdi_browse2031_9999的索引值就是11,partition-hash-int.txt 文件等号右边就是写上对应的索引值.
4.4 SpringBoot整合Mycat
4.4.1.将原本mysql连接地址改成mycat的,mycat端口默认为8066
4.4.2.其他的就和正常连接Mysql进行开发是一样的(注意分表的主键类型选bigint)
有个需要特别注意的点:对于进行分片的表,在这个文档里我的对应的是gmdi_browse,由于rule.xml中的sequnceHandlerType选的是2,这个是通过时间戳序列方式生成主键的,所以分表的主键类型要设置为bigint,如果选int插入数据时会报out of range的异常.
4.4.3 网上SpringBoot整合Mycat实现读写分离
网上有些实现Mycat读写分离是在rule.xml配置两个user,一个是可写账号,一个是只读账号,然后再SpringBoot整合多数据源,再通过AOP切面监听service层的方法来决定使用的数据源从而实现读写分离.
而我只是在rule.xml中配置了一个账号,SpringBoot整合Mycat时也没有配置多数据源,但我测试过,插入一条数据后,然后直接到从库手动修改那条记录的其中一个数据,通过接口查询这条记录时,返回的是修改后的数据,也就是Mycat路由到了从库执行select操作,所以当在schema.xml中dataHost节点配置好读写分离,就无需再通过代码去选择具体的数据源来实现读写分离,这个Mycat已经帮我们做好了.(纯属个人理解,有不对的地方还望指正.)
五.参考资料
https://blog.csdn.net/weixin_38652136/article/details/90374535
https://blog.csdn.net/yelllowcong/article/details/79073608
https://www.cnblogs.com/linjiqin/p/11202455.html
https://www.cnblogs.com/shaolixin/p/11484072.html
https://www.cnblogs.com/barrywxx/p/8593268.html
https://blog.csdn.net/zyx7653296/article/details/88555718
上一篇： mycat学习05之水平分表
下一篇： Mycat水平分片策略（水平分表拆分）