OneProxy实现MySQL分库分表

共计 7210 个字符，预计需要花费 19 分钟才能阅读完成。

简介

Part1:写在最前

随着网站的壮大，MySQL 数据库架构一般会经历一个过程：

OneProxy 实现 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://www.linuxidc.com/Linux/2017-11/148305.htm

Part2:proxy.cnf

proxy.cnf 文件是 oneproxy 的主要参数配置文件，新版的 oneproxy 对整个目录进行了重新的划分，配置文件都放在了 conf 目录里

[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 分区来进行简单演示

[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

[root@HE4 oneproxy]# ./oneproxy.service start 
Starting OneProxy ...                                      [OK] 

Part2:监控页面

我这里是两台 Master

Part3:创建相关表

[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:插入数据

[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 中已经具有刚刚插入的内容；

[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 中的内容

[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 中的内容

[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)