基于centos7安装mycat服务配置读写分离

    技术2022-07-10  153

    基于mycat和mysql数据库配置读写分离

    1、mycat读写分离架构思路2、环境准备3、mycat安装4、配置4.1、server.xml 配置4.2、schema.xml 配置4.3、rule.xml 配置 5、功能测试

    1、mycat读写分离架构思路

    mycat官网mycat权威指南

    2、环境准备

    两台服务器都安装了mysql数据库均能正常使用。(192.168.78.230主,192.168.78.235从) 未安装mysql的小伙伴可以参考文章:https://blog.csdn.net/qq_34846877/article/details/107044396一台服务器安装mycat,如果资源不够可以安装在两台服务器中其中一台。(192.168.78.231)mycat安装包地址:Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gzlinux下载命令:wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

    3、mycat安装

    上传安装包到 /usr/local 并解压后得到mycat文件,目录如下图 tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz 随后删除安装包:rm -rf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz 存储不足,各位随意,可以移到其它位置。

    4、配置

    进入到mycat进行配置读写分离,删除多余的配置信息。

    4.1、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/"> <!-- 读写都可用的用户 --> <user name="root" defaultAccount="true"> <property name="password">root</property> <property name="schemas">TESTDB</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> <!-- 只读用户 --> <user name="user"> <property name="password">user</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> </user> </mycat:server>

    4.2、schema.xml 配置

    <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> <!-- auto sharding by id (long) --> <!-- <table name="record" primaryKey="id" autoIncrement="true" subTables="record_$0-3" dataNode="dn1" rule="rule1" splitTableNames ="true"/> --> </schema> <dataNode name="dn1" dataHost="localhost1" database="gi_test" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="192.168.78.230:3306" user="root" password="root"> <!-- 可以配置多个从库 --> <readHost host="hostS1" url="192.168.78.235:3306" user="root" password="root" /> </writeHost> </dataHost> </mycat:schema>

    4.3、rule.xml 配置

    由于schema.xml中注释了对表的配置,故没有采用规则,所有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="rule1"> <rule> <!-- id为真实分表字段,这里为路由字段,表示通过这个字段拆分 --> <columns>id</columns> <!-- 指定路由则规则所使用的算法 --> <algorithm>rang-mod</algorithm> </rule> </tableRule> <!-- 枚举规则 --> <tableRule name="rule2"> <rule> <columns>city</columns> <algorithm>hash-int</algorithm> </rule> </tableRule> <!-- 定义取模算法,name表示名称,class表示所使用的算法类 --> <function name="rang-mod" class="io.mycat.route.function.PartitionByMod"> <!-- count表示分表的个数 --> <property name="count">4</property> </function> <!-- 定义枚举算法,mapFile:指定枚举映射文件 type:字段属性类型,默认值为0,0表示Integer,非零表示String defaultNode:指定默认节点,如果city值没有在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">1</property> </function> </mycat:rule>

    配置完成后启动mycat服务 启动命令:./mycat start 停止命令:./mycat stop 重启命令:./mycat restart 查看相应的日志是否启动成功 查看日志:tailf wrapper.log

    5、功能测试

    分别用不用的用户连接mycat后测试

    user 用户连接 --> 该用户只读。 root 用户连接 --> 插入成功 利用mycat可以很好缓解数据库负载过大引起的缓慢问题,以上就是基于mycat读写分离的配置。自我总结学习并分享给大家。 上一篇:基于centos7安装MySQL8主从复制配置 下一篇:基于mycat整合springboot项目实现读写分离 学习参考:蚂蚁课堂,mycat官网
    Processed: 0.054, SQL: 9