基于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代理服务器配置信息：

	<server>
		<!-- proxy server绑定的端口 -->
		<property name="port">8066</property>
		
		<!-- proxy server绑定的IP -->
		<property name="ipAddress">127.0.0.1</property>
		
		<!-- proxy server net IO Read thread size -->
		<property name="readThreadPoolSize">20</property>
		
		<!-- proxy server client process thread size -->
		<property name="clientSideThreadPoolSize">30</property>
		
		<!-- mysql server data packet process thread size -->
		<property name="serverSideThreadPoolSize">30</property>
		
		<!-- socket Send and receive BufferSize(unit:K)  -->
		<property name="netBufferSize">100</property>
		
		<!-- Enable/disable TCP_NODELAY (disable/enable Nagle's algorithm). -->
		<property name="tcpNoDelay">true</property>
		
		<!-- 对外验证的用户名 -->
		<property name="user">root</property>
		
		<!-- 对外验证的密码 -->
		<property name="password">admin</property>
		
	</server>

 

 

 

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

 

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

  

    修改查询规则文件：

 

 

	<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 ，启动代理服务器。

 

四、编写查询代码

 

  

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;
	}
}

 

 

 

五、查询结果

 

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

 

 

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