重复(一)(二)的操作, 搭建出如下的数据库结构
(一)Linux下MySQL 5.7.x版本单实例+多实例部署
(二)Linux下MySQL 5.7.x版本主从数据库搭建配置
修改配置server.xml文件
设置连接mycat时的用户名和密码, 定义三个逻辑库:
<!--MyCat服务器的用户名、密码、逻辑数据库-->
<user name="root" defaultAccount="true">
<property name="password">root</property>
<property name="schemas">USER_DB,ORDER_DB,RED_DB</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">USER_DB,ORDER_DB,RED_DB</property>
<property name="readOnly">true</property>
</user>
配置schema.xml文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--对于USER_DB,我们需要水平切分、E-R表、全局表 配置-->
<schema name="USER_DB" checkSQLschema="true" sqlMaxLimit="1000">
<!--全局表-->
<table name="sys_region" type="global" dataNode="dn_usersdb3306,dn_usersdb3307" primaryKey="region_id"/>
<!-- 要实现分库分表,那么就需要在<schema>标签下配置表了,现在是user_info表水平切分 -->
<table name="user_info" primaryKey="id" dataNode="dn_usersdb3306,dn_usersdb3307" rule="mod-long">
<!--E-R表-->
<childTable name="user_address" joinKey="uid" parentKey="id" primaryKey="id"/>
</table>
</schema>
<!--对于ORDER_DB,我们需要水平切分、E-R表 配置-->
<schema name="ORDER_DB" checkSQLschema="true" sqlMaxLimit="1000">
<!-- 要实现分库分表,那么就需要在<schema>标签下配置表了,现在是order_info表水平切分 -->
<table name="order_info" primaryKey="oid" dataNode="dn_ordersdb3306,dn_ordersdb3307" rule="oid-mod-long">
<!--E-R表-->
<childTable name="order_detail" joinKey="oid" parentKey="oid" primaryKey="id"/>
<childTable name="order_express" joinKey="oid" parentKey="oid" primaryKey="id"/>
</table>
</schema>
<!--对于RED_DB,我们只需要读写分离即可-->
<schema name="RED_DB" checkSQLschema="true" sqlMaxLimit="1000" dataNode="dn_redsdb3306">
<!--如果只实现读写分离,此schema标签下不需要配置其他信息-->
</schema>
<!--配置了一个真实的数据库usersdb(用户数据库)-->
<dataNode name="dn_usersdb3306" dataHost="localhost_usersdb3306" database="usersdb" />
<dataNode name="dn_usersdb3307" dataHost="localhost_usersdb3307" database="usersdb" />
<!--配置了一个真实的数据库ordersdb(订单数据库)-->
<dataNode name="dn_ordersdb3306" dataHost="localhost_ordersdb3306" database="ordersdb" />
<dataNode name="dn_ordersdb3307" dataHost="localhost_ordersdb3307" database="ordersdb" />
<!--配置了一个真实的数据库redsdb(营销数据库,目前主要是红包信息)-->
<dataNode name="dn_redsdb3306" dataHost="localhost_redsdb3306" database="redsdb" />
<!--配置用户库3306,balance="1"表示开启读写分离,0表示不开启读写分离-->
<dataHost name="localhost_usersdb3306" 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="hostM3306" url="192.168.197.130:3306" user="root" password="root">
<!--读库(从库)-->
<readHost host="hostS4406" url="192.168.197.130:4406" user="root" password="root"/>
</writeHost>
</dataHost>
<!--配置用户库3307,balance="1"表示开启读写分离,0表示不开启读写分离-->
<dataHost name="localhost_usersdb3307" 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="hostM3307" url="192.168.197.130:3307" user="root" password="root">
<!--读库(从库)-->
<readHost host="hostS4407" url="192.168.197.130:4407" user="root" password="root"/>
</writeHost>
</dataHost>
<!--配置订单库3306,balance="1"表示开启读写分离,0表示不开启读写分离-->
<dataHost name="localhost_ordersdb3306" 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="hostM3306" url="192.168.197.131:3306" user="root" password="root">
<!--读库(从库)-->
<readHost host="hostS4406" url="192.168.197.131:4406" user="root" password="root"/>
</writeHost>
</dataHost>
<!--配置订单库3307,balance="1"表示开启读写分离,0表示不开启读写分离-->
<dataHost name="localhost_ordersdb3307" 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="hostM3307" url="192.168.197.131:3307" user="root" password="root">
<!--读库(从库)-->
<readHost host="hostS4407" url="192.168.197.131:4407" user="root" password="root"/>
</writeHost>
</dataHost>
<!--配置营销库,balance="1"表示开启读写分离,0表示不开启读写分离-->
<dataHost name="localhost_redsdb3306" 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="hostM3306" url="192.168.197.132:3306" user="root" password="root">
<!--读库(从库)-->
<readHost host="hostS4406" url="192.168.197.132:4406" user="root" password="root"/>
</writeHost>
</dataHost>
</mycat:schema>
配置role.xml文件, 在tableRule 平级,加入一个新的自定义规则
<!--订单水平切分规则-->
<tableRule name="oid-mod-long">
<rule>
<columns>oid</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
测试配置:
按照(三)Linux下Mycat安装与读写分离配置的测试方法,进行
测试SQL参照 :
-- 会员信息
insert into user_info (id,phone,nick,email) values ('next value for MYCATSEQ_GLOBAL','13699060652','voayc1','1@1.com');
insert into user_info (id,phone,nick,email) values ('next value for MYCATSEQ_GLOBAL','13699060653','voayc2','2@2.com');
insert into user_info (id,phone,nick,email) values ('next value for MYCATSEQ_GLOBAL','13699060654','voayc3','3@3.com');
-- 会员地址
insert into user_address (id,uid,province,city,area,address) values ('next value for MYCATSEQ_GLOBAL','10004','四川','成都','武侯区','七里晓月');
insert into user_address (id,uid,province,city,area,address) values ('next value for MYCATSEQ_GLOBAL','10005','四川','成都','双流','川大');
-- 跨分片查询
/*!mycat:catlet=io.mycat.catlets.ShareJoin */select * from user_info u left jion red_record rr on rr.uid = u.id