Mysql 读写分离（3）之 schema.xml基本配置

最新推荐文章于 2025-07-24 13:59:08 发布

程序猿John

最新推荐文章于 2025-07-24 13:59:08 发布

阅读量975

点赞数 19

CC 4.0 BY-SA版权

分类专栏： Mysql数据库管理与优化文章标签： mysql xml android

本文链接：https://blog.youkuaiyun.com/pandamf/article/details/147412322

Mysql数据库管理与优化专栏收录该内容

8 篇文章

订阅专栏

1.方案说明

当前配置只考虑转发规则，不考虑分库和分表那些规则

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

        <schema name="TESTDB"     checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>


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

        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <writeHost host="hostM1" url="localhost:3306" user="root"
                                   password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.75.133:3306" user="slaveuser" password="123" />
                </writeHost>

                <!-- <writeHost host="hostS1" url="192.168.75.133:3306" user="slaveuser" password="123" />-->
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>

</mycat:schema>

方案1 ：

该方案是balance是1的情况 readHost 负责全部读取， writehost 负责写入

方案2：

如果dataHost节点的balance属性改为2 ，则所有读操作都随机的在 writeHost、 readhost 上分发。

方案3：

在方案1的基础上，修改一下，万一主服务器挂了，从服务器继续负责读和写

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

        <schema name="TESTDB"     checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>


        <dataNode name="dn1" dataHost="localhost1" database="testtest" />
      
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="localhost:3306" user="root"
                                   password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.75.133:3306" user="slaveuser" password="123" />
                </writeHost>

                <writeHost host="hostS1" url="192.168.75.133:3306" user="slaveuser" password="123" />
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>  
</mycat:schema>

mycat管理端，可以看到 3台主机

mysql> show @@heartbeat;
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST           | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | localhost      | 3306 |       1 |     0 | idle   |       0 | 77,75,46     | 2019-07-08 23:48:54 | false |
| hostS1 | mysql | 192.168.75.133 | 3306 |       1 |     0 | idle   |       0 | 76,72,24     | 2019-07-08 23:48:54 | false |
| hostS2 | mysql | 192.168.75.133 | 3306 |       1 |     0 | idle   |       0 | 109,74,25    | 2019-07-08 23:48:54 | false |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+

但是这种方式有一个弊端就是会造成主从不同步，这种情况需要从新配置主从，并把备份文件重新导入从库