Shulou(Shulou.com)06/01 Report--

I do not know if you have any understanding of similar articles about MySQL sub-library and sub-table through OneProxy, today I am here to give you a brief introduction. If you are interested, let's take a look at the text. I believe you will gain something after reading the MySQL sub-library table through OneProxy.

A brief introduction to the implementation of MySQL Sub-Library and Table by OneProxy

Part1: write at the front

As the site grows, the MySQL database architecture generally goes through a process:

When we have a small amount of data, a single instance database is sufficient. When our data volume increases, we will adopt an one-master and multi-slave database architecture to reduce our read and write io. When one of our business tables reaches tens of millions, tens of millions or even hundreds of millions, we should go to separate tables. This paper demonstrates that OneProxy implements sub-table processing on the database, which is transparent to the front-end application.

Part2: introduction to the environment

HE1:192.168.1.248 Master1

HE3:192.168.1.250 Master2

HE4:192.168.1.251 Oneproxy

Environment construction

Part1: installing Oneproxy

The installation of Oneproxy is not the focus of this article, it needs to be moved to

OneProxy implements MySQL read-write separation and load balancing

Http://suifu.blog.51cto.com/9167728/1884673

Part2:proxy.cnf

The proxy.cnf file is the main parameter configuration file of oneproxy. The new version of oneproxy redivides the whole directory, and the configuration files are placed in the conf directory.

[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 file is a partition policy configuration file. In this blog, hash partition is used for a simple demonstration.

[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"}]]

Actual combat

Part1: start OneProxy

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

Part2: monitoring pa

I have two Master here.

Part3: creating related tables

Log in to the oneproxy management library to create a table

[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 '0mm,-> c2 int (10) unsigned DEFAULT NULL,-> c5 int (10) unsigned NOT NULL DEFAULT' 0mm,-> 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: inserting data

[root@HE1] # mysql-usys_admin-pMANAGER-h292.168.1.251-P3307 test-e "insert into helei (id,c1,c2,c5,c4) values" 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') "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 "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 Mysql: [Warning] Using a password on the command line interface can be insecure. Check

Part1: verifying the contents of the oneproxy table

Here you can see that the virtual table helei already has the content just inserted

[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 | 2016-12-23 00:07:10 | 2 | 3 | 3 | 3 | 2016-12-23 16:07:16 | 3 | + | -+-+

Part2: verifying the contents of 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 | 0-12-23 00:07:10 | 2 | +-+ 1 row in set (0.00 sec)

Part3: verifying the contents of 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 sec Note

Warning: warning 1

Precompiled statement PreparedStatement is not supported, and Bind and Execute call interfaces are not supported.

Warning: warning 2

The use command is not supported to switch back-end databases. The use command can be executed, but it means to switch to a different MySQL master / slave cluster. After supporting the function of database and table partition, OneProxy regards an active / standby cluster as a database. If you specify the database name when linking Oneproxy, you need to replace it with the name of Server Group.

Warning: warning 3

Prohibit the use of the set command, any set command will directly return success without doing any processing.

Warning: warning 4

CALL, PREPARE, EXECUTE, DEALLOCATE commands are disabled by default, and stored procedures and functions are not supported.

Warning: warning 5

OneProxy supports master for failover, but it is recommended to use the popular high-availability scheme MHA. After failover, OneProxy can automatically identify which machine is master. In addition, the architecture must be a master belt N slave, not a double master band N slave.

Through OneProxy to achieve MySQL sub-library sub-table after reading this article, what do you think? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.