十四、Mycat-纵向分库-crc32slot算法-读写分离

目录

配置schema文件                                                                                                                                            

配置rule文件​​

创建物理库

创建物理表

插入数据

配置读写分离

---

1. 配置schema文件                                                                                          

   <?xml version="1.0"?>
   <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
   <mycat:schema xmlns:mycat="http://io.mycat/">
   	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" >
   		<table name="customer" dataNode="dn1,dn2,dn3" rule="crc32slot"></table>
   	</schema>
   	<dataNode name="dn1" dataHost="host1" database="mycatdb1" /> 
   	<dataNode name="dn2" dataHost="host1" database="mycatdb2" /> 
   	<dataNode name="dn3" dataHost="host1" database="mycatdb3" /> 
   	<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.31.134:3306" user="root"
   				   password="123456">
   		</writeHost>
   	</dataHost>
   </mycat:schema>

                                                           

2. 配置rule文件

3. 创建物理库

   在mysql主库里面先创建物理库，物理库必须和schema里面的配置一致

   

4. 创建物理表

   在mycat里面创建物理表，是根据crc32slot规则分库，会自动创建_slot字段

   CREATE TABLE `customer` (
     `id` int(11) DEFAULT NULL,
     `name` varchar(32) DEFAULT NULL
   ) 

   

5. 插入数据

   在mycat里面插入数据

   insert into customer(id,name) values(1,'a');
   insert into customer(id,name) values(2,'a');
   insert into customer(id,name) values(3,'a');
   insert into customer(id,name) values(4,'a');
   insert into customer(id,name) values(5,'a');
   insert into customer(id,name) values(6,'a');
   insert into customer(id,name) values(7,'a');
   insert into customer(id,name) values(8,'a');
   insert into customer(id,name) values(9,'a');
   insert into customer(id,name) values(10,'a');
   insert into customer(id,name) values(11,'a');
   insert into customer(id,name) values(12,'a');
   insert into customer(id,name) values(13,'a');
   insert into customer(id,name) values(14,'a');
   insert into customer(id,name) values(15,'a');
   insert into customer(id,name) values(16,'a');
   insert into customer(id,name) values(17,'a');
   insert into customer(id,name) values(18,'a');
   insert into customer(id,name) values(19,'a');
   insert into customer(id,name) values(20,'a');
   insert into customer(id,name) values(21,'a');
   insert into customer(id,name) values(22,'a');
   insert into customer(id,name) values(23,'a');

   再查询，会看到数据按照顺序排列，是因为数据分别到了不同的mycat1、2、3下面的customer表。

6. 配置读写分离

   修改schema文件

   注意位置readHost标签是在writeHost标签内部

   

   <?xml version="1.0"?>
   <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
   <mycat:schema xmlns:mycat="http://io.mycat/">
   	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" >
   		<table name="customer" dataNode="dn1,dn2,dn3" rule="crc32slot"></table>
   	</schema>
   	<dataNode name="dn1" dataHost="host1" database="mycatdb1" /> 
   	<dataNode name="dn2" dataHost="host1" database="mycatdb2" /> 
   	<dataNode name="dn3" dataHost="host1" database="mycatdb3" /> 
   	<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
   			  writeType="0" dbType="mysql" dbDriver="native" 
   			  switchType="1"  slaveThreshold="100">
   		<heartbeat>select user()</heartbeat>
   		<writeHost host="hostM1" url="192.168.31.134:3306" user="root"
   				   password="123456">
   			<readHost host="hostS1" url="192.168.192.129:3306" user="root"
   				password="123456">
   			</readHost>
   		</writeHost>
   	</dataHost>
   </mycat:schema>

   往从库插入一条测试数据，通过mycat查询若每次都能查到此测试数据表示每次读取是从从库获取。