Part1:写在最前
随着网站的壮大，MySQL数据库架构一般会经历一个过程：
当我们数据量比较小的时候，一台单实例数据库足矣。等我们数据量增大的时候，我们会采用一主多从的数据库架构来降低我们的读写io。当我们某张业务表达到几百万上千万甚至上亿时，就应该去进行分表处理。本文演示OneProxy对数据库实现分表处理，对前端应用是透明的。
Part2:环境简介
HE1:192.168.1.248 Master1
HE3:192.168.1.250 Master2
HE4:192.168.1.251 Oneproxy
环境构建
Part1:安装Oneproxy
Oneproxy的安装不是本文讲述的重点，需要的可移步至
OneProxy实现MySQL读写分离与负载均衡
http://suifu.blog.51cto.com/9167728/1884673
Part2:proxy.cnf
proxy.cnf文件是oneproxy的主要参数配置文件，新版的oneproxy对整个目录进行了重新的划分，配置文件都放在了conf目录里
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
[root@HE4 oneproxy]
# cat conf/proxy.conf
[oneproxy]
keepalive = 1
event-threads = 4
log-
file
= log
/oneproxy
.log
pid-
file
= log
/oneproxy
.pid
lck-
file
= log
/oneproxy
.lck
mysql-version = 5.7.16
proxy-address = :3307
proxy-master-addresses.1 = 192.168.1.248:3306@group1
proxy-master-addresses.2 = 192.168.1.250:3306@group2
proxy-user-list = sys_admin
/1C6D087BA5D2607A27DECB2F2AFE247E911E877A
@
test
proxy-part-tables.1 =
/root/oneproxy/conf/part
.txt
#proxy-part-tables.2 = /root/oneproxy/conf/part2.txt
proxy-charset = utf8_bin
proxy-group-policy.1 = group1:master-only
proxy-group-policy.2 = group2:master-only
proxy-secure-client = 192.168.1.248
proxy-sequence.1 = default
proxy-httpserver = :8080
proxy-httptitle = OneProxy Monitor
|
Part3:part.txt
part.txt文件是分区策略配置文件，在本博文中，采取hash分区来进行简单演示
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[root@HE4 oneproxy]
# cat conf/part.txt
[
{
"table"
:
"helei"
,
"pkey"
:
"id"
,
"type"
:
"int"
,
"method"
:
"hash"
,
"partitions"
:
[
{
"suffix"
:
"_0"
,
"group"
:
"group1"
},
{
"suffix"
:
"_1"
,
"group"
:
"group2"
},
{
"suffix"
:
"_2"
,
"group"
:
"group1"
},
{
"suffix"
:
"_3"
,
"group"
:
"group2"
}
]
}
]
|
实战
Part1:启动OneProxy
|
1
2
|
[root@HE4 oneproxy]
# ./oneproxy.service start
Starting OneProxy ... [ OK ]
|
Part2:监控页面
我这里是两台Master
Part3:创建相关表
登录oneproxy管理库创建表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
[root@HE1 ~]
# mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 test
mysql: [Warning] Using a password on the
command
line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 103
Server version: 5.7.16 OneProxy-Community-5.8.5 (OneXSoft)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> create table helei(
->
id
int(10) unsigned NOT NULL AUTO_INCREMENT,
-> c1 int(10) NOT NULL DEFAULT
'0'
,
-> c2 int(10) unsigned DEFAULT NULL,
-> c5 int(10) unsigned NOT NULL DEFAULT
'0'
,
-> c3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> c4 varchar(200) NOT NULL DEFAULT
''
,
-> PRIMARY KEY(
id
),
-> KEY idx_c1(c1),
-> KEY idx_c2(c2)
-> )ENGINE=InnoDB ;
Query OK, 0 rows affected (0.27 sec)
mysql> \q
|
Part4:插入数据
|
1
2
3
4
5
6
7
8
|
[root@HE1 ~]
# mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 test -e"insert into helei(id,c1,c2,c5,c4) values(1,1,1,1,'1')"
mysql: [Warning] Using a password on the
command
line interface can be insecure.
[root@HE1 ~]
# mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 test -e"insert into helei(id,c1,c2,c5,c4) values(2,2,2,2,'2')"
mysql: [Warning] Using a password on the
command
line interface can be insecure.
[root@HE1 ~]
# mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 test -e"insert into helei(id,c1,c2,c5,c4) values(3,3,3,3,'3')"
mysql: [Warning] Using a password on the
command
line interface can be insecure.
[root@HE1 ~]
# mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 test -e"insert into helei(id,c1,c2,c5,c4) values(4,4,4,4,'4')"
mysql: [Warning] Using a password on the
command
line interface can be insecure.
|
校验
Part1:校验oneproxy表内容
这里可以看到虚拟表helei中已经具有刚刚插入的内容；
|
1
2
3
4
5
6
7
8
9
10
|
[root@HE1 ~]
# mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 test -e"select * from helei";
mysql: [Warning] Using a password on the
command
line interface can be insecure.
+----+----+------+----+---------------------+----+
|
id
| c1 | c2 | c5 | c3 | c4 |
+----+----+------+----+---------------------+----+
| 4 | 4 | 4 | 4 | 2016-12-23 00:07:21 | 4 |
| 1 | 1 | 1 | 1 | 2016-12-23 16:07:04 | 1 |
| 2 | 2 | 2 | 2 | 2016-12-23 00:07:10 | 2 |
| 3 | 3 | 3 | 3 | 2016-12-23 16:07:16 | 3 |
+----+----+------+----+---------------------+----+
|
Part2:校验Master1中的内容
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
[root@HE1 ~]
# mysql -uroot -pMANAGER test
mysql: [Warning] Using a password on the
command
line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 158
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| checksums |
| helei_0 |
| helei_2 |
| sbtest |
+----------------+
4 rows
in
set
(0.00 sec)
mysql>
select
* from helei_0;
+----+----+------+----+---------------------+----+
|
id
| c1 | c2 | c5 | c3 | c4 |
+----+----+------+----+---------------------+----+
| 4 | 4 | 4 | 4 | 2016-12-23 00:07:21 | 4 |
+----+----+------+----+---------------------+----+
1 row
in
set
(0.00 sec)
mysql>
select
* from helei_2;
+----+----+------+----+---------------------+----+
|
id
| c1 | c2 | c5 | c3 | c4 |
+----+----+------+----+---------------------+----+
| 2 | 2 | 2 | 2 | 2016-12-23 00:07:10 | 2 |
+----+----+------+----+---------------------+----+
1 row
in
set
(0.00 sec)
|
Part3:校验Master2中的内容
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
[root@HE3 ~]
# mysql -uroot -pMANAGER test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 2997
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| checksums |
| helei_1 |
| helei_3 |
+----------------+
3 rows
in
set
(0.00 sec)
mysql>
select
* from helei_1;
+----+----+------+----+---------------------+----+
|
id
| c1 | c2 | c5 | c3 | c4 |
+----+----+------+----+---------------------+----+
| 1 | 1 | 1 | 1 | 2016-12-23 16:07:04 | 1 |
+----+----+------+----+---------------------+----+
1 row
in
set
(0.00 sec)
mysql>
select
* from helei_3;
+----+----+------+----+---------------------+----+
|
id
| c1 | c2 | c5 | c3 | c4 |
+----+----+------+----+---------------------+----+
| 3 | 3 | 3 | 3 | 2016-12-23 16:07:16 | 3 |
+----+----+------+----+---------------------+----+
1 row
in
set
(0.00 sec)
|
注意
Warning:警告1
不支持预编译语句 PreparedStatement,不支持Bind、Execute调用接口。
Warning:警告2
不支持使用use命令来切换后端数据库，use命令可执行，但其含义是切换到不同的MySQL主备集群，OneProxy在支持分库分表功能后，就将一个主备集群视为一个数据库了，链接Oneproxy时如果指定了数据库名，则需替换成Server Group的名字
Warning:警告3
禁止使用set命令，任何set命令都会直接返回成功，而不做任何处理。
Warning:警告4
默认禁止CALL、PREPARE、EXECUTE、DEALLOCATE命令，不支持存储过程和函数。
Warning:警告5
OneProxy支持master进行故障转移切换，但建议采用流行的高可用方案MHA实现。故障切换后，OneProxy可以自动识别哪台机器是master。另外，架构必须是一主带N从，不能是双主带N从。
——总结——
至此，OneProxy对MySQL的分库分表测试完成，对于前端应用而言，表名是透明的。无需变更代码。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
参考文献：
http://hcymysql.blog.51cto.com/5223301/1685146
http://www.onexsoft.com
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1885581，如需转载请自行联系原作者
