Mycat读写分离实践

最新推荐文章于 2021-02-05 07:24:53 发布

finn9527

最新推荐文章于 2021-02-05 07:24:53 发布

阅读量165

点赞数

文章标签：读写分离 Mycat配置

本文链接：https://blog.csdn.net/qq_41660467/article/details/85259900

版权

安装mycat

解压mycat的安装包

(可以省略)设置mycat的环境变量

vi /etc/profile

使配置文件立即生效

source /etc/profile

修改server.xml

<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">basemytest</property>
</user>

<user name="user">
<property name="password">user</property>
<property name="schemas">basemytest</property>
<property name="readOnly">true</property>
</user>

修改schema.xml,如果分库分表,增加相应的node节点即可

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="basemytest" checkSQLschema="false" sqlMaxLimit="100">
<table name="tb_user" dataNode="dn1" rule="rule1" />
</schema>

<dataNode name="dn1" dataHost="localhost1" database="mytest" />

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.226.128:3306" user="root"
password="123456">
<readHost host="hostS1" url="192.168.226.130:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>

table的rule属性是分片规则

dataNode的database是实际物理数据库的名字

配置分片规则rule.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>

<tableRule name="rule2">
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>

<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<tableRule name="latest-month-calldate">
<rule>
<columns>calldate</columns>
<algorithm>latestMonth</algorithm>
</rule>
</tableRule>

<tableRule name="auto-sharding-rang-mod">
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
</tableRule>

<tableRule name="jch">
<rule>
<columns>id</columns>
<algorithm>jump-consistent-hash</algorithm>
</rule>
</tableRule>

<function name="murmur"
class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property>
<property name="count">2</property>
<property name="virtualBucketTimes">160</property>


</function>

<function name="crc32slot"
class="io.mycat.route.function.PartitionByCRC32PreSlot">
</function>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">

<property name="count">3</property>
</function>

<function name="func1" class="io.mycat.route.function.PartitionByLong">
<property name="partitionCount">8</property>
<property name="partitionLength">128</property>
</function>
<function name="latestMonth"
class="io.mycat.route.function.LatestMonthPartion">
<property name="splitOneDay">24</property>
</function>
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2015-01-01</property>
</function>

<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
<property name="mapFile">partition-range-mod.txt</property>
</function>

<function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
<property name="totalBuckets">3</property>
</function>
</mycat:rule>

关闭防火墙或者修改防火墙,允许mycat的端口被外界访问

关闭防火墙指令:

service iptables stop

或者

编辑:

vi /etc/sysconfig/iptables

-A INPUT -m state --state NEW -m tcp -p tcp --dport 8066 -j ACCEPT

-A INPUT -m state --state NEW -m tcp -p tcp --dport 9066 -j ACCEPT

然后重启防火墙

service iptables restart

运行启动命令

cd /usr/local/mycat/bin/

./mycat start

查看mycat是否启动成功

./mycat status

查看启动日志：

cat ../logs/wrapper.log

如果报Jvm的堆内存大小异常Invalid maximum heap size: -Xmx4G；可以如下：

vi ../conf/wrapper.conf 修改36行的-Xmx4G可以修改为-Xmx1G

测试

接下来我们测试一下读写分离

使用mysql客户端连接或者navicat来连接mycat,密码是server.xml中的密码,端口是8066

查看mycat日志

cd /usr/local/mycat/logs/

tail -f mycat.log

先测试一下读操作

我们连接到mycat发送一句select *命令试试

可以看到select 操作被路由到了192.168.226.130也就是我们的slave(读)节点

那么我们执行多次看看

接下来我们测试一下写操作

可见插入被路由到了master节点

最后我们看看master的数据是否成功

被同步到slave

记录成功的同步过来了，可见读写分离搭建成功。

finn9527

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mycat读写分离实践

安装mycat解压mycat的安装包 (可以省略)设置mycat的环境变量 vi /etc/profile 使配置文件立即生效source /etc/profile 修改server.xml&lt;user name="root" defaultAccount="true"&gt; &lt;property name="pas...
复制链接

扫一扫