MyCat的核心概念

1.逻辑数据库 schema.xml
2.分片规则 rule.xml

单库大表拆分

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  5.     <!-- 逻辑库 -->
  6.     <!--
  7.         checkSQLschema=false 处理表名的序号
  8.         sqlMaxLimit = 100 只返回100条
  9.     -->
  10.     <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
  11.         <!-- 逻辑表 -->
  12.         <!--
  13.             name：逻辑表名
  14.             dataNode：节点
  15.             rule：分片规则
  16.                 mod-long：取模
  17.         -->
  18.         <table name="company" subTables="company$1-3" dataNode="dn1" rule="mod-long"/>
  19.     </schema>
  21.     <!-- 数据节点 -->
  22.     <dataNode name="dn1" dataHost="localhost1" database="db" />
  24.     <!-- host配置 -->
  25.     <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  26.               writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
  27.         <heartbeat>select user()</heartbeat>
  28.         <writeHost host="hostM1" url="localhost:3306" user="root"
  29.                    password="123456">
  30.         </writeHost>
  31.     </dataHost>
  32. </mycat:schema>

跨库大表拆分

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  5.     <!-- 逻辑库 -->
  6.     <!--
  7.         checkSQLschema=false 处理表名的序号
  8.         sqlMaxLimit = 100 只返回100条
  9.     -->
  10.     <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" randomDataNode="dn1">
  11.         <!-- 逻辑表 -->
  12.         <!--
  13.             name：逻辑表名
  14.             dataNode：节点
  15.             rule：分片规则
  16.                 mod-long：取模
  17.         -->
  18.         <table name="company" subTables="company$1-3" dataNode="dn1" rule="mod-long"/>
  19.         <table name="record"  dataNode="dn1,dn2,dn1" rule="mod-long"/>
  20.     </schema>
  22.     <!-- 数据节点 -->
  23.     <dataNode name="dn1" dataHost="localhost1" database="db1" />
  24.     <dataNode name="dn2" dataHost="localhost1" database="db2" />
  25.     <dataNode name="dn3" dataHost="localhost1" database="db3" />
  27.     <!-- host配置 -->
  28.     <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  29.               writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
  30.         <heartbeat>select user()</heartbeat>
  31.         <writeHost host="hostM1" url="localhost:3306" user="root"
  32.                    password="123456">
  33.         </writeHost>
  34.     </dataHost>
  35. </mycat:schema>

读写分离

  1. <?xml version="1.0"?>
  2.         <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  6. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
  8. </schema>
  10. <!-- 数据节点 -->
  11. <dataNode name="dn1" dataHost="localhost1" database="db" />
  13. <!-- host配置 -->
  14. <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  15.           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
  16.     <!-- 心跳检测 -->
  17.     <heartbeat>show slave status</heartbeat>
  18.     <writeHost host="hostM1" url="localhost:3306" user="root"
  19.                password="123456">
  20.         <readHost host="localhost1" url="localhost:3306" password="root" user="123456" />
  21.     </writeHost>
  22. </dataHost>
  23. </mycat:schema>

数据库负载均衡：Haproxy

数据库高可用：keepalive