说明:目的是简单实现一张表的分库分表,以t_order为例,以order_id取模为分表规则
1.环境准备,我有一台笔记本装有mysql(192.168.100.55),一台虚拟机装有mysql(192.168.100.41),mycat也安装在虚机上
2.下载mycat安装包,我用的是Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz版本
3.安装包上传到/home路径下并解压,tar -zxvf Mycat-server-1.6.7.5-release-20200410174409-linux.tar.gz
4.进入mycat的配置文件文件夹修改配置文件,cd /home/mycat/conf
5.修改server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">itest</property>
<property name="defaultSchema">itest</property>
</user>
</mycat:server>
6.修改schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="itest" checkSQLschema="true" sqlMaxLimit="100">
<table name="t_order" dataNode="dn1,dn2" rule="mod_rule" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="itest" />
<dataNode name="dn2" dataHost="host2" database="itest" />
<dataHost name="host1" 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.100.41:3306" user="root"
password="***">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.100.55:3306" user="root"
password="***">
</writeHost>
</dataHost>
</mycat:schema>
7.修改rule.xml,增加以下配置
<tableRule name="mod_rule">
<rule>
<columns>order_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
8.为了方便把两台装有mysql的机器都关闭防火墙,mysql都开启远程访问的权限
9.在两个mysql中都建itest数据库并建t_order表
CREATE TABLE `t_order` (
`order_id` int(11) DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`amount` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
10.启动mycat
cd /home/mycat/bin
./mycat start
11.用工具连接mycat,连接的端口为8066,用户名密码是server.xml里面配置的
12.这时在mycat中会看到此前在两台mysql服务器上建的表t_order,往t_order中插入order_id不同的数据,会看到均匀分布在两个mysql库中,检索不同order_id的数据,mycat会自动定位到不同的库并获取结果就ok了