Mycat实现MySQL的分库分表、读写分离、主从切换

发布时间：2023-05-09 13:01:04 所属栏目：MySql教程来源：

导读：配置Mycat
server.xml：

<!DOCTYPE mycat:server SYstem "server.dtd">
<mycat:server xmlns:mycat="">
<system>
<property name="usesqlStat">1</property>
<property name="

配置Mycat
server.xml：

<!DOCTYPE mycat:server SYstem "server.dtd">
<mycat:server xmlns:mycat="">
<system>
<property name="usesqlStat">1</property>
<property name="useGlobleTableCheck">0</property>
<property name="defaultsqlParser">druidparser</property>
<property name="sequnceHandlerType">2</property>
<property name="processorBufferPoolType">0</property>
<property name="serverPort">3310</property> 
<property name="managerPort">3311</property> 
<property name="handledistributedTransactions">0</property>
<property name="uSEOffheapForMerge">1</property>
<property name="memoryPageSize">1m</property>
<property name="spillsfilebufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">389m</property>
</system>
<user name="root">
<property name="password">123456</property>
<property name="schemas">db</property>
<property name="readOnly">false</property>
</user>
</mycat:server>
schema.xml：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYstem "schema.dtd">
<mycat:schema xmlns:mycat=">

<schema name="db" checksqlschema="false" sqlMaxLimit="100">
<table name="t1" datanode="dn1,dn2" rule="mod-long" /> 
</schema>

<datanode name="dn1" dataHost="shard1" database="db1" />
<datanode name="dn2" dataHost="shard2" database="db2" />

<dataHost name="shard1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="MysqL" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user();</heartbeat>
<writeHost host="db1-M1" url="10.20.8.126:3306" user="root" password="123456">
<readHost host="db1-M2" url="10.20.8.126:3307" user="root" password="123456" />
</writeHost>

<writeHost host="db1-M2" url="10.20.8.126:3307" user="root" password="123456">
</writeHost>
</dataHost>
<dataHost name="shard2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="MysqL" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user();</heartbeat>
<writeHost host="db2-M1" url="10.25.80.7:3307" user="root" password="123456">
</writeHost>
</dataHost>
</mycat:schema>
修改rule.xml中下列配置项：

<tableRule name="mod-long">
<rule>
<columns>id</columns> 
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">

<property name="count">2</property> 
</function>
配置文件中各标签的含义可参考文章：MyCat关键配置说明

启动Mycat:

[root@SZB-L0059021 bin]# ./mycat start
Starting Mycat-server...
[root@SZB-L0059021 bin]# ./mycat status
Mycat-server is running (27020).
[root@SZB-L0059021 bin]# MysqL -uroot -p123456 -Ddb -h227.0.0.1 -P3310
分库分表验证：

MysqL> show tables;
+--------------+
| Tables in db |
+--------------+
| t1 |
+--------------+
1 row in set (0.00 sec)
MysqL> desc t1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| db_name | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
MysqL> insert into t1(id,db_name) values(1,database());
Query OK, 1 row affected (0.01 sec)
MysqL> insert into t1(id,db_name) values(2,database());
Query OK, 1 row affected (0.03 sec)
MysqL> select * from t1;
+------+---------+
| id | db_name |
+------+---------+
| 2 | db1 | --id=2，对2取模为0，所以插入dn1
| 1 | db2 | --id=1，对2取模为1，所以插入dn2
+------+---------+
2 rows in set (0.01 sec)
上述查询结果可知，两次插入的数据分落入了db1、db2上，实现了分库

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!