使用mycat实现读写分离

最新推荐文章于 2024-09-12 16:05:45 发布

潇潇灬秋

最新推荐文章于 2024-09-12 16:05:45 发布

阅读量377

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/Limenghui0614/article/details/79398819

数据库专栏收录该内容

8 篇文章

订阅专栏

本文介绍如何在CentOS 6.5环境下通过Mycat实现MySQL的读写分离，包括主从服务器配置、Mycat配置文件修改等步骤，并验证读写分离效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

centos6.5系统
主服务器：192.168.1.84
从服务器：192.168.1.83
一主一从模式
实现读写分离之前需要先进行mysql主从配置，参考msyql主从配置
因为mycat不负责同步数据问题
修改mycat配置文件schema.xml为：

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

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="node04">
                <!-- auto sharding by id (long) -->

        </schema>
        <dataNode name="node04" dataHost="localhost" database="test01" />

        <dataHost name="localhost" 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.1.84:3306" user="tongbu"
                        password="123456">
                <readHost host="hostS1" url="192.168.1.83:3306" user="tongbu" 
                        password="123456">
                </readHost>
                </writeHost>
        </dataHost>

</mycat:schema>

修改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://org.opencloudb/">
        <system>
        <property name="defaultSqlParser">druidparser</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!-- <property name="processorBufferChunk">40960</property> -->
        <!-- 
        <property name="processors">1</property> 
        <property name="processorExecutor">32</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="mutiNodeLimitType">1</property> 0：开启小数量级（默认） ；1：开启亿级>数据排序
                <property name="mutiNodePatchSize">100</property> 亿级数量排序批量
                        <property name="processors">32</property> <property name="processorExecutor">32</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> -->
        <!--黑白名单 
        <firewall>
             <whitehost>
                <host host="指定IP", user="test"></host>
             </whitehost>
             <blacklist check="true">
                <property name="selectAllColumnAllow">false</property>
             </blacklist>
        </firewall>-->
        </system>
        <user name="test">
                <property name="password">test</property>
                <property name="schemas">TESTDB</property>
        </user>

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

</mycat:server>

验证mycat读写分离是否实现：

[root@node04 ~]# mysql -utest -ptest -h192.168.1.84 -P8066 -DTESTDB
mysql> show tables; #test01数据库之前已经创建好，以及数据库表
+------------------+
| Tables_in_test01 |
+------------------+
| grades           |
| student          |
| test             |
+------------------+
3 rows in set (0.00 sec)
mysql> select *from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaa    |
|  2 | bbb    |
|  3 | ccc    |
| 11 | xxx    |
| 12 | zzz    |
| 13 | node03 |
| 14 | node03 |
| 16 | ddd    |
| 17 | eee    |
+----+--------+

在1.83的从库上也可以查看到数据信息：

[root@node03 ~]# mysql -u root -p111111 -Dtest01
mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| grades           |
| student          |
| test             |
+------------------+
3 rows in set (0.00 sec)
mysql> select *from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaa    |
|  2 | bbb    |
|  3 | ccc    |
| 11 | xxx    |
| 12 | zzz    |
| 13 | node03 |
| 14 | node03 |
| 16 | ddd    |
| 17 | eee    |
+----+--------+
mysql> stop slave #停止主从复制，验证读写分离

进入mycat虚拟数据库插入数据进行验证：

mysql> insert into test values(18, "fff");
mysql> select *from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaa    |
|  2 | bbb    |
|  3 | ccc    |
| 11 | xxx    |
| 12 | zzz    |
| 13 | node03 |
| 14 | node03 |
| 16 | ddd    |
| 17 | eee    |
+----+--------+
#发现没有新插入的数据，因为关闭了主从复制，代理层只把数据写到了主库,从库是读端没有复制数据，只显示以前的数据。
[root@node04 ~]# mysql -uroot -p111111 -Dtest01
mysql> select *from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaa    |
|  2 | bbb    |
|  3 | ccc    |
| 11 | xxx    |
| 12 | zzz    |
| 13 | node03 |
| 14 | node03 |
| 16 | ddd    |
| 17 | eee    |
| 18 | fff     |
+----+--------+
#主库里面有

开启从库的主从复制：

mysql> start slave;
mysql> select *from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaa    |
|  2 | bbb    |
|  3 | ccc    |
| 11 | xxx    |
| 12 | zzz    |
| 13 | node03 |
| 14 | node03 |
| 16 | ddd    |
| 17 | eee    |
| 18 | fff     |
+----+--------+

进入mycat代理数据库：

msyql> select *from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | aaa    |
|  2 | bbb    |
|  3 | ccc    |
| 11 | xxx    |
| 12 | zzz    |
| 13 | node03 |
| 14 | node03 |
| 16 | ddd    |
| 17 | eee    |
| 18 | fff     |
+----+--------+