mycat 入门例子

---》需求按
某种类型把表 分库

---》机器：
* mysql1

操作系统版本 : centos6.5 x64
数据库版本 : mysql­5.1.73
数据库名实例 : db1
hostname:hadoop1

* mysql2

操作系统版本 : centos6.5 x64
数据库版本 : mysql­5.1.73
数据库名实例 : db2
hostname:hadoop2

* mycat
操作系统版本 : win7
mycat版本 ：1.5 release

---》配置

* server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
</system>
<!-- 逻辑用户名 、密码 、实例-->
<user name="test">
<property name="password">test</property>
<property name="schemas">test</property>
</user>
</mycat:server>

* schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<schema name="test" checkSQLschema="false" sqlMaxLimit="100">
<table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" />
</schema>
<!-- db1 db2 为实际存在的实例-->
<dataNode name="dn1" dataHost="hadoop1" database="db1" />
<dataNode name="dn2" dataHost="hadoop2" database="db2" />

<dataHost name="hadoop1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="hadoop1:3306" user="root"
password="root">
</writeHost>
</dataHost>
<dataHost name="hadoop2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="hadoop2:3306" user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>

* rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">

<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>

<function name="hash-int"
class="org.opencloudb.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
</mycat:rule>

* partition-hash-int.txt
10000=0
10010=1

---》客户端测试
* 客户端连接 比如sqlyog、navicat 连接mycat
和mysql连接相似，但是端口号需要改成8066（mycat默认端口）

ip ：127.0.0.1
端口：8066
库：test
用户\密码：test\test （server中配置的）

* 建立表
CREATE TABLE employee (id INT NOT NULL PRIMARY KEY,NAME VARCHAR(100),sharding_id INT NOT NULL);

* 插入数据
INSERT INTO employee(id,NAME,sharding_id) VALUES(1,'leader us',10000)
INSERT INTO employee(id,NAME,sharding_id) VALUES(2, 'me',10010);
INSERT INTO employee(id,NAME,sharding_id) VALUES(3, 'mycat',10000);
INSERT INTO employee(id,NAME,sharding_id) VALUES(4, 'mydog',10010);

* 查询测试
explain select * from employee ;
explain select * from employee where sharding_id=10000 ;

---》Jdbc测试
* 程序
@Test
public void testUpdatePoint3() throws SQLException {

String url = "jdbc:mysql://127.0.0.1:8066/test?user=test&password=test";
Connection con = DriverManager.getConnection(url);
String sql = "select id,NAME,sharding_id from employee";
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int col = rs.getMetaData().getColumnCount();
System.out.println("============================");
while (rs.next()) {
for (int i = 1; i <= col; i++) {
System.out.print(rs.getString(i) + "\t");
if ((i == 2) && (rs.getString(i).length() < 8)) {
System.out.print("\t");
}
}
System.out.println("");
}
System.out.println("============================");


pstmt.close();
con.close();
}

* 结果http://ynp.iteye.com

============================
3 mycat 10000
1 leader us 10000
4 mydog 10010
2 me 10010
============================

---》与Spring 多数据源比较
mycat可以不用再维护多个数据源，且代码修改量小；