mycat配置分库分表

青旅半醒 2023-06-24 08:28 5阅读 0赞

# 实验环境 #

mysql5.6

mycat1.6

## 第一步：配置数据库和表 ##

新建数据库db4

新建数据库db5

在db4和db5中新建用户表

CREATE TABLE `t_user` (
     
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
     
    `name` varchar(256) NULL,
     
    PRIMARY KEY (`id`) 
     
    );

##    第二步：配置mycat的server.xml ##

配置mycat的逻辑数据库名和账号密码

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70][]

## 第三步：配置mycat的schema.xml ##

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
    	<schema name="MyCatDB" checkSQLschema="false" sqlMaxLimit="100">
    	<table name="t_user" primaryKey="ID"  dataNode="dn4,dn5" autoIncrement="false" rule="mod-long" />
    	</schema>
    
    	<dataNode name="dn4" dataHost="localhost1" database="db4" />
    	<dataNode name="dn5" dataHost="localhost2" database="db5" />
    
    	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
    			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		
    		<writeHost host="hostM1" url="你的db4数据库ip和端口" user="root"
    				   password="root">
    		</writeHost>
    	
    	</dataHost>
    	<dataHost name="localhost2" maxCon="1000" minCon="10" balance="3"
    			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		
    		<writeHost host="hostM1" url="你的db5数据库ip和端口" user="root"
    				   password="root">
    		</writeHost>
    	</dataHost>
    	
    </mycat:schema>

## 第四步：启动mycat验证 ##

启动mycat

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 1][]

用navicate连接mycat

然后往mycat的逻辑库MyCatDB的t\_user表插入两条记录

INSERT INTO `t_user` (`id`, `name`) VALUES (1, "ddd");
    INSERT INTO `t_user` (`id`, `name`) VALUES (2, "ddd");

![20191222133527877.png][]

实验结果：db4和db5的t\_user表各一条数据，验证完毕。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 3][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191222133026869.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191222133344483.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA=,size_16,color_FFFFFF,t_70
[20191222133527877.png]: https://img-blog.csdnimg.cn/20191222133527877.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191222133631348.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2019122213385978.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA=,size_16,color_FFFFFF,t_70