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
- @
- 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:创建相关表
登录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> \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)
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