基于amoeba的mysql分布式数据库学习（1）

基于amoeba的mysql分布式数据库学习（一）

一、下载 amoeba 代码

首先先到网站（ http://sourceforge.net/projects/amoeba ）上下载 amoeba for Mysql 代码。然后解压到

C:/amoeba 目录。增加系统环境变量： amoeba.home = C:/amoeba

二、准备 mysql 数据库

Server1 ： localhost schema: test table: test_table2

Server2 ： 10.2.224.241 schema: test table: test_table2

表名称 test_table1 结构为：

`ID` INTEGER(11) NOT NULL,

`NAME` VARCHAR(250) COLLATE gbk_chinese_ci DEFAULT NULL,

先插入一些模拟数据。

三、修改配置文件

找到amoeba.xml配置，修改mysql代理服务器配置信息：

Java代码

<server>
<property name="port" > 8066 </property>
<property name="ipAddress" > 127.0 . 0.1 </property>
<property name="readThreadPoolSize" > 20 </property>
<property name="clientSideThreadPoolSize" > 30 </property>
<property name="serverSideThreadPoolSize" > 30 </property>
<property name="netBufferSize" > 100 </property>
<property name="tcpNoDelay" > true </property>
<property name="user" >root</property>
<property name="password" >admin</property>
</server>

我设置的监控端口为：8066。

配置两个数据库服务器地址，分别对应Server1和Server2。

修改查询规则文件：

Java代码

<tableRule name= "test_table2" schema= "test" defaultPools= "server2,server1" >
<rule name="rule1" >
<parameters>ID</parameters>
<expression><![CDATA[ ID <= 20 ]]></expression>
<defaultPools>server1</defaultPools>
<readPools>server1</readPools>
<writePools>server1</writePools>
</rule>
<rule name="rule2" >
<parameters>ID</parameters>
<expression><![CDATA[ ID > 20 ]]></expression>
<defaultPools>server2</defaultPools>
<writePools>server2</writePools>
<readPools>server2</readPools>
</rule>
</tableRule>

规则：以ID=20为界。

OK，配置文件配置好了以后，我们可以执行：${amoeba.home}/bin/amoeba.bat ，启动代理服务器。

四、编写查询代码

Java代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DataAccess {
private String CONNECTION_STRING = "jdbc:mysql://localhost:8066/test" ;
// jdbc:mysql://localhost:8066
private String connErrInfo;
private Connection conn;
public DataAccess(){
if (conn == null ){
conn = GetConnObj();
}
}
public Connection GetConnObj() {
try {
Class.forName("com.mysql.jdbc.Driver" );
conn = DriverManager.getConnection(CONNECTION_STRING,"root" , "admin" );
return conn;
} catch (ClassNotFoundException ex) {
this .connErrInfo += ";dbConn ex:" + ex.toString();
ex.printStackTrace();
} catch (SQLException es) {
this .connErrInfo += ";dbConn es:" + es.getMessage();
es.printStackTrace();
} catch (Exception e) {
this .connErrInfo += ";dbConn e:" + e.getMessage();
e.printStackTrace();
}
return null ;
}
public String commonUpdate(String rSqlString) {
if (conn != null ) {
try {
Statement stmt = conn.createStatement();
stmt.execute(rSqlString);
//conn.close();
} catch (SQLException e) {
return e.getMessage();
}
}
return "" ;
}
public ResultSet commonSelect(String rSqlString) {
if (conn != null ) {
try {
Statement stmt = conn.createStatement();
stmt.execute(rSqlString);
ResultSet result = stmt.executeQuery(rSqlString);
//conn.close();
return result;
} catch (SQLException es) {
this .connErrInfo = "dbProcess es:" + es.getMessage();
} catch (Exception e) {
this .connErrInfo = "dbProcess e:" + e.getMessage();
}
}
return null ;
}
public void close(){
if (conn != null ){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public String getConnErrInfo() {
return connErrInfo;
}
public void setConnErrInfo(String connErrInfo) {
this .connErrInfo = connErrInfo;
}
public static void main(String[] args) throws SQLException{
DataAccess dataAccess = new DataAccess();
java.util.Date startDate = new java.util.Date();
ResultSet rs = dataAccess.commonSelect("select * from test_table2 where ID in(14,15,16,50)" );
while (rs.next()){
String siteName = (String)rs.getString("name" );
System.out.println("siteName:" + siteName );
}
java.util.Date endDate = new java.util.Date();
long period = endDate.getTime() - startDate.getTime();
System.out.println("耗费时间：" + period);
dataAccess.close();
}
public Connection getConn() {
return conn;
}
public void setConn(Connection conn) {
this .conn = conn;
}
}

五、查询结果

Java代码

siteName: 10.2 . 224 .241_test_table1_14
siteName:10.2 . 224 .241_test_table1_15
siteName:10.2 . 224 .241_test_table1_16
siteName:test_table2_14
siteName:test_table2_15
siteName:test_table2_16
耗费时间：156

我现在只是模拟简单的规则查询，后面我们将逐步深入了解。

基于amoeba的mysql分布式数据库学习（1）

相关推荐