Mycat分库分表配置
Mycat分库分表配置
以下为Mycat为三台Mysql数据库分库分表的配置，三台数据库主机名node0-2，数据库名db01-03.
以下是Mycat的schema.xml配置文件：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1"> <!-- auto sharding by id (long) --> <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置--> <table name="users" primaryKey="id" dataNode="dn1" /> <table name="item" primaryKey="id" dataNode="dn2,dn3" rule="rule1" /> <table name="customer" dataNode="dn1,dn2,dn3" rule="auto-sharding-long-custom"> <childTable name="orders" joinKey="customer_id" parentKey="id" /> </table> <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate" /> --> </schema> <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" /> --> <dataNode name="dn1" dataHost="localhost1" database="db01" /> <dataNode name="dn2" dataHost="localhost2" database="db02" /> <dataNode name="dn3" dataHost="localhost3" database="db03" /> <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" /> <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> <dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" /> <dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> --> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="node0:3306" user="root" password="root123"> </writeHost> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM2" url="node1:3306" user="root" password="root123"> </writeHost> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> <dataHost name="localhost3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM3" url="node2:3306" user="root" password="root123"> </writeHost> <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --> </dataHost> |
然后编辑rule.xml：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | <tableRule name="rule1"> <rule> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <tableRule name="auto-sharding-long-custom"> <rule> <columns>id</columns> <algorithm>rang-long</algorithm> </rule> </tableRule> <function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">2</property> </function> <function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong"> <property name="mapFile">auto-sharding-long-custom.txt</property> </function> |
以上配置说明：
mod-long算法，即按余数分配，一共2台。
rang-long，按范围分配。
创建auto-sharding-long-custom.txt文件：
1 2 3 4 | cat auto-sharding-long-custom.txt0-1000=01000-2000=12000-3000=2 |
表示0-1000分配到0号datanode，1000-2000到1号datanode，2000-3000到2号datanode。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-2756123/，如需转载，请注明出处，否则将追究法律责任。