共计 7210 个字符，预计需要花费 19 分钟才能阅读完成。
简介
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://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@testproxy-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 MonitorPart3: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:创建相关表
登录 oneproxy 管理库创建表
[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> \qPart4:插入数据
[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)注意
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://www.linuxidc.com/Linux/2017-11/148306.htm
正文完
星哥玩云-微信公众号
