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

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

一、下载amoeba代码

?

首先先到网站（http://sourceforge.net/projects/amoeba）上下载amoeba for MysqlC:/amoeba?

二、准备mysql数据库

?

Server1：Server2：????????

???????????????????????????????????

?????????

三、修改配置文件

?

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

?

1. <server>??
2. ????<!--?proxy?server绑定的端口?-->??
3. ????<property?name="port">8066</property>??
4. ??????
5. ????<!--?proxy?server绑定的IP?-->??
6. ????<property?name="ipAddress">127.0.0.1</property>??
7. ??????
8. ????<!--?proxy?server?net?IO?Read?thread?size?-->??
9. ????<property?name="readThreadPoolSize">20</property>??
10. ??????
11. ????<!--?proxy?server?client?process?thread?size?-->??
12. ????<property?name="clientSideThreadPoolSize">30</property>??
13. ??????
14. ????<!--?mysql?server?data?packet?process?thread?size?-->??
15. ????<property?name="serverSideThreadPoolSize">30</property>??
16. ??????
17. ????<!--?socket?Send?and?receive?BufferSize(unit:K)??-->??
18. ????<property?name="netBufferSize">100</property>??
19. ??????
20. ????<!--?Enable/disable?TCP_NODELAY?(disable/enable?Nagle's?algorithm).?-->??
21. ????<property?name="tcpNoDelay">true</property>??
22. ??????
23. ????<!--?对外验证的用户名?-->??
24. ????<property?name="user">root</property>??
25. ??????
26. ????<!--?对外验证的密码?-->??
27. ????<property?name="password">admin</property>??
28. ??????
29. </server>??

?

?

?

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

?

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

??

??? 修改查询规则文件：

?

?

1. <tableRule?name="test_table2"?schema="test"?defaultPools="server2,server1">??
2. ??????
3. ??
4. ????<rule?name="rule1">??
5. ????????<parameters>ID</parameters>??
6. ????????<expression><![CDATA[??ID?<=?20?]]></expression>??
7. ????????<defaultPools>server1</defaultPools>??
8. ????????<readPools>server1</readPools>??
9. ????????<writePools>server1</writePools>??
10. ????</rule>??
11. ??
12. ????<rule?name="rule2">??
13. ????????<parameters>ID</parameters>??
14. ????????<expression><![CDATA[?ID?>?20?]]></expression>??
15. ????????<defaultPools>server2</defaultPools>??
16. ????????<writePools>server2</writePools>??
17. ????????<readPools>server2</readPools>??
18. ????</rule>??
19. ???????????????</tableRule>???

?

?

?

??? 规则：以ID=20为界。

?

?

?

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

?

四、编写查询代码

?

??

1. import?java.sql.Connection;??
2. import?java.sql.DriverManager;??
3. import?java.sql.PreparedStatement;??
4. import?java.sql.ResultSet;??
5. import?java.sql.SQLException;??
6. import?java.sql.Statement;??
7. ??
8. public?class?DataAccess?{??
9. ??
10. ????private?String?CONNECTION_STRING?=?"jdbc:mysql://localhost:8066/test";??
11. //??jdbc:mysql://localhost:8066??
12. ????private?String?connErrInfo;??
13. ??????
14. ????private?Connection?conn;??
15. ??????
16. ????public?DataAccess(){??
17. ????????if(conn?==?null){??
18. ????????????conn?=?GetConnObj();??
19. ????????}??
20. ????}??
21. ??
22. ????public?Connection?GetConnObj()?{??
23. ????????try?{??
24. ????????????Class.forName("com.mysql.jdbc.Driver");??
25. ????????????conn?=?DriverManager.getConnection(CONNECTION_STRING,"root","admin");??
26. ????????????return?conn;??
27. ????????}?catch?(ClassNotFoundException?ex)?{??
28. ????????????this.connErrInfo?+=?";dbConn?ex:"?+?ex.toString();??
29. ????????????ex.printStackTrace();??
30. ????????}?catch?(SQLException?es)?{??
31. ????????????this.connErrInfo?+=?";dbConn?es:"?+?es.getMessage();??
32. ????????????es.printStackTrace();??
33. ????????}?catch?(Exception?e)?{??
34. ????????????this.connErrInfo?+=?";dbConn?e:"?+?e.getMessage();??
35. ????????????e.printStackTrace();??
36. ????????}??
37. ????????return?null;??
38. ????}??
39. ??
40. ????public?String?commonUpdate(String?rSqlString)?{??
41. ????????if?(conn?!=?null)?{??
42. ????????????try?{??
43. ????????????????Statement?stmt?=?conn.createStatement();??
44. ????????????????stmt.execute(rSqlString);??
45. ????????????????//conn.close();??
46. ????????????}?catch?(SQLException?e)?{??
47. ????????????????return?e.getMessage();??
48. ????????????}??
49. ????????}??
50. ????????return?"";??
51. ????}??
52. ??
53. ??????
54. ??????
55. ????public?ResultSet?commonSelect(String?rSqlString)?{??
56. ????????if?(conn?!=?null)?{??
57. ????????????try?{??
58. ????????????????Statement?stmt?=?conn.createStatement();??
59. ????????????????stmt.execute(rSqlString);??
60. ????????????????ResultSet?result?=?stmt.executeQuery(rSqlString);??
61. ????????????????//conn.close();??
62. ????????????????return?result;??
63. ????????????}?catch?(SQLException?es)?{??
64. ????????????????this.connErrInfo?=?"dbProcess?es:"?+?es.getMessage();??
65. ????????????}?catch?(Exception?e)?{??
66. ????????????????this.connErrInfo?=?"dbProcess?e:"?+?e.getMessage();??
67. ????????????}??
68. ????????}??
69. ????????return?null;??
70. ????}??
71. ??
72. ??????
73. ????public?void?close(){??
74. ????????if(conn?!=?null){??
75. ????????????try?{??
76. ????????????????conn.close();??
77. ????????????}?catch?(SQLException?e)?{??
78. ????????????????e.printStackTrace();??
79. ????????????}??
80. ????????}??
81. ????}??
82. ??????
83. ????public?String?getConnErrInfo()?{??
84. ????????return?connErrInfo;??
85. ????}??
86. ??
87. ????public?void?setConnErrInfo(String?connErrInfo)?{??
88. ????????this.connErrInfo?=?connErrInfo;??
89. ????}??
90. ??
91. ????public?static?void?main(String[]?args)?throws?SQLException{??
92. ??????????
93. ????????DataAccess?dataAccess?=?new?DataAccess();??
94. ??????????
95. ????????java.util.Date?startDate?=?new?java.util.Date();??
96. ????????ResultSet?rs?=?dataAccess.commonSelect("select?*?from?test_table2?where?ID?in(14,15,16,50)");??
97. ????????while(rs.next()){??
98. ????????????String?siteName?=?(String)rs.getString("name");??
99. ????????????System.out.println("siteName:"?+?siteName?);??
100. ????????}??
101. ????????java.util.Date?endDate?=?new?java.util.Date();??
102. ????????long?period?=?endDate.getTime()?-?startDate.getTime();??
103. ????????System.out.println("耗费时间："?+?period);??
104. ??????????
105. ????????dataAccess.close();??
106. ????}??
107. ??
108. ????public?Connection?getConn()?{??
109. ????????return?conn;??
110. ????}??
111. ??
112. ????public?void?setConn(Connection?conn)?{??
113. ????????this.conn?=?conn;??
114. ????}??
115. }??

?

?

?

五、查询结果

?

1. siteName:10.2.224.241_test_table1_14??
2. siteName:10.2.224.241_test_table1_15??
3. siteName:10.2.224.241_test_table1_16??
4. siteName:test_table2_14??
5. siteName:test_table2_15??
6. siteName:test_table2_16??
7. 耗费时间：156??

?

?

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