图文演示通过OneProxy实现MySQL分库分表

不知道大家之前对类似通过OneProxy实现MySQL分库分表的文章有无了解，今天我在这里给大家再简单的讲讲。感兴趣的话就一起来看看正文部分吧，相信看完通过OneProxy实现MySQL分库分表你一定会有所收获的。

OneProxy实现MySQL分库分表

简介

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目录里

[root@HE4 oneproxy]# cat conf/proxy.conf [oneproxy]keepalive = 1event-threads = 4log-file = log/oneproxy.logpid-file = log/oneproxy.pidlck-file = log/oneproxy.lckmysql-version = 5.7.16proxy-address = :3307proxy-master-addresses.1 = 192.168.1.248:3306@group1proxy-master-addresses.2 = 192.168.1.250:3306@group2proxy-user-list = sys_admin/1C6D087BA5D2607A27DECB2F2AFE247E911E877A@testproxy-part-tables.1 = /root/oneproxy/conf/part.txt#proxy-part-tables.2 = /root/oneproxy/conf/part2.txtproxy-charset = utf8_binproxy-group-policy.1 = group1:master-onlyproxy-group-policy.2 = group2:master-onlyproxy-secure-client = 192.168.1.248proxy-sequence.1 = defaultproxy-httpserver = :8080proxy-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 startStarting OneProxy ...                                      [  OK  ]

Part2:监控页面

我这里是两台Master

Part3:创建相关表

登录oneproxy管理库创建表

[root@HE1 ~]# mysql -usys_admin -pMANAGER -h292.168.1.251 -P3307 testmysql: [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 103Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 -h292.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 -h292.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 -h292.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 -h292.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 -h292.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 testmysql: [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 158Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 testWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2997Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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分库分表看完这篇文章，大家觉得怎么样？如果想要了解更多相关，可以继续关注我们的行业资讯板块。