2.MyCat分库分表~通过id取模分片

其实mycat的核心就是3个配置文件，理解了之后就很简单了。一下配置就是按天分片，数据分布到3个机器，6个库里

有3个节点，每个节点上创建两个数据库 节点1  db1,db2 , 节点2 db3，db4,  节点3 ：db5,db6

6个库分别创建一个表：t1

t1表结构为：

create table t1 (   id int(10) unsigned not null,   k int(10) unsigned not null default '0',   c char(120) not null default '',   pad char(60) not null default '',   primary key (id),   key k_1 (k) ) engine=innodb default charset=utf8

schema.xml

<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/">     <schema name="testdb" checkSQLschema="false" sqlMaxLimit="100">         <!-- auto sharding by id (long) -->         <table name="t1" primaryKey="id" dataNode="dn01,dn02,dn03,dn04,dn05,dn06" rule="mod-long" />         </schema>       <dataNode name="dn01" dataHost="node1" database="db1" />         <dataNode name="dn02" dataHost="node2" database="db2" />         <dataNode name="dn03" dataHost="node3" database="db3" />         <dataNode name="dn04" dataHost="node4" database="db4" />     <dataNode name="dn05" dataHost="node5" database="db5" />         <dataNode name="dn06" dataHost="node6" database="db6" />     <dataHost name="node1" maxCon="2000" minCon="10" balance="0"               writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">         <heartbeat>select user()</heartbeat>         <writeHost host="hostM1" url="56.56.56.201:3306" user="my" password="123456"></writeHost>     </dataHost>         <dataHost name="node2" maxCon="2000" minCon="10" balance="0"                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">                 <heartbeat>select user()</heartbeat>                 <writeHost host="hostM1" url="56.56.56.201:3306" user="my" password="123456"></writeHost>         </dataHost>         <dataHost name="node3" maxCon="2000" minCon="10" balance="0"                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">                 <heartbeat>select user()</heartbeat>                 <writeHost host="hostM2" url="56.56.56.204:3306" user="my" password="123456"></writeHost>         </dataHost>         <dataHost name="node4" maxCon="2000" minCon="10" balance="0"                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">                 <heartbeat>select user()</heartbeat>                 <writeHost host="hostM2" url="56.56.56.204:3306" user="my" password="123456"></writeHost>         </dataHost>         <dataHost name="node5" maxCon="2000" minCon="10" balance="0"                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">                 <heartbeat>select user()</heartbeat>                 <writeHost host="hostM3" url="56.56.56.206:3306" user="my" password="123456"></writeHost>         </dataHost>         <dataHost name="node6" maxCon="2000" minCon="10" balance="0"                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">                 <heartbeat>select user()</heartbeat>                 <writeHost host="hostM3" url="56.56.56.206:3306" user="my" password="123456"></writeHost>         </dataHost> </mycat:schema>

rule.xml

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">                 <!-- how many data nodes -->                 <property name="count">6</property> </function>

MySQL [(none)]> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MySQL [testdb]> show tables; +------------------+ | Tables in testdb | +------------------+ | t1               | +------------------+ 1 row in set (0.01 sec) MySQL [testdb]> desc t1; +-------+------------------+------+-----+---------+-------+ | Field | Type             | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id    | int(10) unsigned | NO   | PRI | NULL    |       | | k     | int(10) unsigned | NO   | MUL | 0       |       | | c     | char(120)        | NO   |     |         |       | | pad   | char(60)         | NO   |     |         |       | +-------+------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) MySQL [testdb]> insert into t1(id,k,c,pad) values(1,100,'aaa','aaa'); Query OK, 1 row affected (0.05 sec) MySQL [testdb]> insert into t1(id,k,c,pad) values(2,100,'aaa','aaa'); Query OK, 1 row affected (0.00 sec) MySQL [testdb]> insert into t1(id,k,c,pad) values(3,100,'aaa','aaa'); Query OK, 1 row affected (0.00 sec) MySQL [testdb]> insert into t1(id,k,c,pad) values(4,100,'aaa','aaa'); Query OK, 1 row affected (0.01 sec) MySQL [testdb]> insert into t1(id,k,c,pad) values(5,100,'aaa','aaa'); Query OK, 1 row affected (0.00 sec) MySQL [testdb]> insert into t1(id,k,c,pad) values(6,100,'aaa','aaa'); Query OK, 1 row affected (0.00 sec) root@localhost :db202:55:50>select * from t1; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:    90 Current database: db2 +----+-----+-----+-----+ | id | k  | c  | pad | +----+-----+-----+-----+ |  1 | 100 | aaa | aaa | +----+-----+-----+-----+ 1 row in set (0.00 sec) root@localhost :db203:20:01>use db1; Database changed root@localhost :db103:20:05>select * from t1; +----+-----+-----+-----+ | id | k  | c  | pad | +----+-----+-----+-----+ |  6 | 100 | aaa | aaa | +----+-----+-----+-----+ 1 row in set (0.00 sec)

插入了6条数据，可以看到每个数据库，表里面只有1条数据。