Mysql 基于 Amoeba 的 水平和垂直 分片（上）

 环境：

Servers

Amoeba Server (Linux): 192.168.14.129

Mysql 1 Server  (Linux): 192.168.14.131

Mysql 2 Server  (Linux): 192.168.14.133

Clients

Mysql GUI Tools (Windows): 192.168.14.28

Java Programs  (Eclipse): 192.168.14.28

假设以上程序都已经安装好了。

1. Mysql数据库远程访问授权

mysql 1 server 和 mysql 2 server 的 test 数据库，允许 amoeba server 访问。用户名：test_user 密码：1234

Sql代码  

1. grant all on test.* to test_user@192.168.14.129 identified by '1234';  

2.  创建测试表

在 mysql 1 server 中：

t_user:

Sql代码  

1. mysql> create table test.t_user (  
2.     -> user_id integer unsigned not null,  
3.     -> user_name varchar(45),  
4.     -> user_address varchar(100),  
5.     -> primary key (user_id)  
6.     -> )engine=innodb;  
7. Query OK, 0 rows affected (0.01 sec)  

t_blog:

Sql代码  

1. mysql> create table test.t_blog (  
2.     -> blog_id integer unsigned not null,  
3.     -> blog_title varchar(45),  
4.     -> blog_content text,  
5.     -> user_id integer,  
6.     -> primary key (blog_id)  
7.     -> )engine=innodb;  
8. Query OK, 0 rows affected (0.00 sec)  

t_message:

Sql代码  

1. mysql> create table test.t_message (  
2.     -> message_id integer unsigned not null,  
3.     -> message_content varchar(255),  
4.     -> user_id integer  
5.     -> )engine=innodb;  
6. Query OK, 0 rows affected (0.01 sec)  

在 mysql 2 server  中

t_user  同上。

t_attention:

Sql代码  

1. mysql> create table test.t_attention (  
2.     -> attention_id integer unsigned not null,  
3.     -> user_id integer,  
4.     -> blog_id integer  
5.     -> )engine=innodb;  
6. Query OK, 0 rows affected (0.01 sec)  

t_blog_comment:

Sql代码  

1. mysql> create table test.t_blog_comment (  
2.     -> comment_id integer unsigned not null,  
3.     -> commnet_content text,  
4.     -> blog_id integer  
5.     -> )engine=innodb;  
6. Query OK, 0 rows affected (0.01 sec)  

3. 配置 Amoeba 的切分数据库的规则

Amoeba 的详细使用说明请参见：http://docs.hexnova.com/amoeba/

dbServers.xml

Xml代码  

1. <?xml version="1.0" encoding="gbk"?>  
2.   
3. <!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">  
4. <amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">  
5.   
6.                 <!--  
7.                         Each dbServer needs to be configured into a Pool,  
8.                         If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:  
9.                          add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig  
10.                          such as 'multiPool' dbServer  
11.                 -->  
12.   
13.         <dbServer name="abstractServer" abstractive="true">  
14.                 <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">  
15.                         <property name="manager">${defaultManager}</property>  
16.                         <property name="sendBufferSize">64</property>  
17.                         <property name="receiveBufferSize">128</property>  
18.   
19.                         <!-- mysql port -->  
20.                         <property name="port">3306</property>  
21.   
22.                         <!-- mysql schema -->  
23.                         <property name="schema">test</property>  
24.   
25.                         <!-- mysql user -->  
26.                         <property name="user">test_user</property>  
27.   
28.                         <!--  mysql password -->  
29.                         <property name="password">1234</property>  
30.   
31.                 </factoryConfig>  
32.   
33.                 <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">  
34.                         <property name="maxActive">500</property>  
35.                         <property name="maxIdle">500</property>  
36.                         <property name="minIdle">10</property>  
37.                         <property name="minEvictableIdleTimeMillis">600000</property>  
38.                         <property name="timeBetweenEvictionRunsMillis">600000</property>  
39.                         <property name="testOnBorrow">true</property>  
40.                         <property name="testWhileIdle">true</property>  
41.                 </poolConfig>  
42.         </dbServer>  
43.   
44.         <dbServer name="server1"  parent="abstractServer">  
45.                 <factoryConfig>  
46.                         <!-- mysql ip -->  
47.                         <property name="ipAddress">192.168.14.131</property>  
48.                 </factoryConfig>  
49.         </dbServer>  
50.   
51.         <dbServer name="server2"  parent="abstractServer">  
52.                 <factoryConfig>  
53.                         <!-- mysql ip -->  
54.                         <property name="ipAddress">192.168.14.133</property>  
55.                 </factoryConfig>  
56.         </dbServer>  
57.   
58. </amoeba:dbServers>  

amoeba.xml

Xml代码  

1. <?xml version="1.0" encoding="gbk"?>  
2.   
3. <!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">  
4. <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">  
5.   
6.         <proxy>  
7.   
8.                 <!-- service class must implements com.meidusa.amoeba.service.Service -->  
9.                 <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">  
10.                         <!-- port -->  
11.                         <property name="port">8066</property>  
12.   
13.                         <!-- bind ipAddress -->  
14.                         <!-- 
15.                         <property name="ipAddress">127.0.0.1</property> 
16.                          -->  
17.   
18.                         <property name="manager">${clientConnectioneManager}</property>  
19.   
20.                         <property name="connectionFactory">  
21.                                 <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">  
22.                                         <property name="sendBufferSize">128</property>  
23.                                         <property name="receiveBufferSize">64</property>  
24.                                 </bean>  
25.                         </property>  
26.   
27.                         <property name="authenticator">  
28.                                 <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">  
29.   
30.                                         <property name="user">root</property>  
31.   
32.                                         <property name="password">root</property>  
33.   
34.                                         <property name="filter">  
35.                                                 <bean class="com.meidusa.amoeba.server.IPAccessController">  
36.                                                         <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>  
37.                                                 </bean>  
38.                                         </property>  
39.                                 </bean>  
40.                         </property>  
41.   
42.                 </service>  
43.   
44.                 <!-- server class must implements com.meidusa.amoeba.service.Service -->  
45.                 <service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">  
46.                         <!-- port -->  
47.                         <!--  default value: random number  
48.                         <property name="port">9066</property>  
49.                         -->  
50.                         <!-- bind ipAddress -->  
51.                         <property name="ipAddress">127.0.0.1</property>  
52.                         <property name="daemon">true</property>  
53.                         <property name="manager">${clientConnectioneManager}</property>  
54.                         <property name="connectionFactory">  
55.                                 <bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>  
56.                         </property>  
57.   
58.                 </service>  
59.   
60.                 <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">  
61.                         <!-- proxy server net IO Read thread size -->  
62.                         <property name="readThreadPoolSize">20</property>  
63.   
64.                         <!-- proxy server client process thread size -->  
65.                         <property name="clientSideThreadPoolSize">30</property>  
66.   
67.                         <!-- mysql server data packet process thread size -->  
68.                         <property name="serverSideThreadPoolSize">30</property>  
69.   
70.                         <!-- per connection cache prepared statement size  -->  
71.                         <property name="statementCacheSize">500</property>  
72.   
73.                         <!-- query timeout( default: 60 second , TimeUnit:second) -->  
74.                         <property name="queryTimeout">60</property>  
75.                 </runtime>  
76.   
77.         </proxy>  
78.         <!--  
79.                 Each ConnectionManager will start as thread  
80.                 manager responsible for the Connection IO read , Death Detection  
81.         -->  
82.         <connectionManagerList>  
83.                 <connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">  
84.                         <property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>  
85.                         <!--  
86.                           default value is avaliable Processors  
87.                         <property name="processors">5</property>  
88.                          -->  
89.                 </connectionManager>  
90.                 <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">  
91.                         <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>  
92.   
93.                         <!--  
94.                           default value is avaliable Processors  
95.                         <property name="processors">5</property>  
96.                          -->  
97.                 </connectionManager>  
98.         </connectionManagerList>  
99.   
100.                 <!-- default using file loader -->  
101.         <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">  
102.                 <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>  
103.         </dbServerLoader>  
104.   
105.         <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">  
106.                 <property name="ruleLoader">  
107.                         <bean class="com.meidusa.amoeba.route.TableRuleFileLoader">  
108.                                 <property name="ruleFile">${amoeba.home}/conf/rule.xml</property>  
109.                                 <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>  
110.                         </bean>  
111.                 </property>  
112.                 <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>  
113.                 <property name="LRUMapSize">1500</property>  
114.                 <property name="defaultPool">server1</property><!-- 默认数据库，即主数据库 -->  
115.   
116.                 <!--  
117.                 <property name="writePool">server1</property>  
118.                 <property name="readPool">server1</property>  
119.                 -->  
120.                 <property name="needParse">true</property>  
121.         </queryRouter>  
122. </amoeba:configuration>  

rule.xml

t_user 表 根据 user_id 字段的奇偶性 水平切分 ， 偶数分到 server1 ， 奇数分到 server2。其中 server1，server2 是在 dbServers.xml 中定义的。

t_attention 表垂直切分到 server2

主数据库是 server1 ， 在 amoeba.xml 中定义

Xml代码  

1. <property name="defaultPool">server1</property>  

主数据库说明：连接到 Amoeba 代理的时候，主数据库的所有表均可访问。 但是其他的数据库的表，需要在分片规则中有涉及的表，才能访问，其他表不能访问。

Xml代码  

1. <?xml version="1.0" encoding="gbk"?>  
2. <!DOCTYPE amoeba:rule SYSTEM "rule.dtd">  
3.   
4. <amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/">  
5.         <tableRule name="t_user" schema="test" defaultPools="server1,server2">  
6.                 <rule name="rule1">  
7.                         <parameters>user_id</parameters>  
8.                         <expression><![CDATA[ user_id % 2 == 0 ]]></expression>  
9.                         <defaultPools>server1</defaultPools>  
10.                 </rule>  
11.                 <rule name="rule2">  
12.                         <parameters>user_id</parameters>  
13.                         <expression><![CDATA[ user_id % 2 == 1 ]]></expression>  
14.                         <defaultPools>server2</defaultPools>  
15.                 </rule>  
16.         </tableRule>  
17.         <tableRule name="t_attention" schema="test" defaultPools="server2" />  
18.         <tableRule name="t_blog_comment" schema="test" defaultPools="server2" />  
19.  </amoeba:rule>  

启动 msyql1, mysql2, amoeba 切分完成。

4. 测试

访问的时候，需要通过 Amoeba 代理来访问。不能直接访问实体数据库，不然切分无效。

使用 Mysql GUI Tools 连接 Amoeba

用户名：root   密码： root  端口号：8066

以上信息在 amoeba.xml 中定义

Xml代码  

1. <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">  
2.                         <!-- port 连接端口号-->  
3.                         <property name="port">8066</property>  
4.   
5.                         <!-- bind ipAddress -->  
6.                         <!-- 
7.                         <property name="ipAddress">127.0.0.1</property> 
8.                          -->  
9.   
10.                         <property name="manager">${clientConnectioneManager}</property>  
11.   
12.                         <property name="connectionFactory">  
13.                                 <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">  
14.                                         <property name="sendBufferSize">128</property>  
15.                                         <property name="receiveBufferSize">64</property>  
16.                                 </bean>  
17.                         </property>  
18.   
19.                         <property name="authenticator">  
20.                                 <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">  
21.                                         <!--用户名，密码 -->  
22.                                         <property name="user">root</property>  
23.   
24.                                         <property name="password">root</property>  
25.   
26.                                         <property name="filter">  
27.                                                 <bean class="com.meidusa.amoeba.server.IPAccessController">  
28.                                                         <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>  
29.                                                 </bean>  
30.                                         </property>  
31.                                 </bean>  
32.                         </property>  
33.   
34.                 </service>  

可以发现，在GUI 工具中，能看到的表 只有在 server1 中，创建的表。 上面已经说明了，因为 server1 中主数据库。

验证垂直切分的 t_attention 表

为了验证，垂直切分出去的 t_attention 表。  直接执行 select * from test.t_attention;

可以看到，查询成功。 说明的确可以访问，切分出去的 t_attention 表。

验证水平切分的 t_user 表

t_user 的水平切分规则是 根据 user_id 的奇偶性进行切分。

上图的左下角可以看到：2 rows affected by the last command ， 说明刚才那个插入语句，作用到了两条记录

查询看一下插入结果：

发现有两条一样的数据。到网上找了一下原因。

后来知道 amoeba 是根据 sql 解析来进行 水平切分的， 需要把切分的关键字段（这里是user_id），加入到 sql 中。否则 切分规则无效。无效后，会在 server1, server2 均都插入数据。

即变为：insert into t_user(user_id, user_name, user_address) values(1, 'n1', 'a1')

分别检查一下，两个数据库


 

两个数据库均插入了一条相同的数据。  同时也了解到，amoeba 的查询会将水平切分的表在两个数据库的结果合并。

修改 sql 语句后再执行一次。

可以看到左下角：只作用到一条记录

看下查询结果

因为 user_id 为 偶数， 所有分派到 server 1 了。

水平切分的排序与分页

排序结果：分别查询的 server1 和 server2 然后简单合并。 所以 排序不正确。

分页结果：同样是分别执行的 server1 和 server2 然后合并。 每个 server 取两条记录，记录也变为了 4 条。分页不正确

验证连接查询

在 t_message , t_blog, t_attention 表分别插入两个数据

分别执行以上查询语句。

发现只有 第二 和 第四 可以执行成功。

结果：

1. 如果 表1 被水平分片，连接查询时，需要连接的表，在 表1 分片的所有数据库中均存在（即相同的分片规则），才可以连接查询。

2. 如果 表1 被垂直分片，连接查询时，需要连接的表也同样分片到同一个数据库中（即相同的分片规则）。

3. 综上，只有在连接的表，在同一个数据库中均存在时，才能连接操作。即 不支持跨数据库的连接。