The construction of OneProxy sub-database and sub-table
Experimental environment:
Circular replication of three nodes prepared in advance
Master1:192.168.8.57
Master2:192.168.8.58
Master3:192.168.8.59
Oneproxy:192.168.8.63
1. Download oneproxy at http://www.onexsoft.com/
Upload to 192.168.8.63 and decompress to / usr/local
2. Modify the file descriptor 65535
Vi / etc/security/limits.conf
Soft nofile 65535
Hard nofile 65535
Vi / etc/sysctl.conf
Fs.file-max=655350
Net.ipv4.ip_local_port_range=1025 65000
Net.ipv4.tcp_tw_reuse=1
3. Create an encrypted password
[root@oneproxy bin] #. / mysqlpwd mysql
F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B
4. Modify demo.sh
Demo.sh in the directory is the first startup script to modify ONEPROXY_HOME
[root@oneproxy oneproxy] # cat demo.sh
# / bin/bash
#
Export ONEPROXY_HOME=/usr/local/oneproxy
Ulimit-c unlimited
# valgrind-- leak-check=full\
${ONEPROXY_HOME} / bin/oneproxy-- defaults-file=$ {ONEPROXY_HOME} / conf/proxy.conf
5. Modify oneproxy.service
Oneproxy.service is a startup script that modifies ONEPROXY_HOME
[root@oneproxy oneproxy] # cat oneproxy.service
#! / bin/bash
# chkconfig:-30 21
# description: OneProxy service.
# Source Function Library
. / etc/init.d/functions
# OneProxy Settings
ONEPROXY_HOME=/usr/local/oneproxy
ONEPROXY_SBIN= "${ONEPROXY_HOME} / bin/oneproxy"
ONEPROXY_CONF= "${ONEPROXY_HOME} / conf/proxy.conf"
ONEPROXY_PID= "${ONEPROXY_HOME} / log/oneproxy.pid"
RETVAL=0
Prog= "OneProxy"
Start () {
Echo-n $"Starting $prog..."
Daemon $ONEPROXY_SBIN-- defaults-file=$ONEPROXY_CONF
RETVAL=$?
Echo
}
Stop () {
Echo-n $"Stopping $prog..."
If [- e ${ONEPROXY_PID}]; then
Daemon kill-INT $(cat ${ONEPROXY_PID})
RETVAL=$?
Fi
Echo
}
Restart () {
Stop
Sleep 1
Start
}
Case "$1" in
Start)
Start
Stop)
Stop
Restart)
Restart
*)
Echo $"Usage: $0 {start | stop | restart}"
RETVAL=1
Esac
Exit $RETVAL
6. Configure OneProxy
Root@oneproxy conf] # cat proxy.conf
[oneproxy]
Keepalive = 1
Event-threads = 1
Log-file = log/oneproxy.log
Pid-file = log/oneproxy.pid
Lck-file = log/oneproxy.lck
# proxy-license = A2FF461456A67F28 D2F6A5AD70C9042D
# proxy-auto-readonly = 1
# proxy-forward-clientip = 1
# proxy-trans-debug = 1
Proxy-address =: 3307
Admin-address =: 4040
Mysql-version = 5.7.18
Proxy-master-addresses.1 = 192.168.8.57:3306@group1
Proxy-master-addresses.2 = 192.168.8.58:3306@group2
Proxy-master-addresses.3 = 192.168.8.59:3306@group3
Proxy-user-list = test/F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B@test
# proxy-user-list2 = root/F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B@test
# proxy-user-group = test:root/F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B@test
Proxy-charset = utf8_bin
Proxy-group-policy.1 = group1:master-only
Proxy-group-policy.2 = group2:master-only
Proxy-group-policy.3 = group3:master-only
Proxy-secure-client.1 = 192.168.8.57
Proxy-secure-client.2 = 192.168.8.58
Proxy-secure-client.3 = 192.168.8.59
# remote-address.1 = 192.168.1.119purl 4041
Proxy-httptitle = OneProxy Monitor
Proxy-httpserver =: 8080
Proxy-part-tables.1 = conf/part.txt
7. Configure part.txt
[root@oneproxy conf] # cat part.txt
[
{
"table": "steven"
"pkey": "id"
"type": "int"
"method": "hash"
"partitions":
[
{"suffix": "_ 0", "group": "group1"}
{"suffix": "_ 1", "group": "group2"}
{"suffix": "_ 2", "group": "group3"}
]
}
]
8. Start oneproxy
[root@oneproxy conf] #. / oneproxy.service start
Starting OneProxy... [OK]
9. Functional test of sub-database and sub-table.
The client connects to port 3307 of OneProxy and creates the steven table under test.
Mysql-utest-pmysql-h292.168.8.63-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.18 OneProxy-6.2.0 (OneXSoft)
Copyright (c) 2000, 2017, 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 steven (
Id int (10) unsigned NOT NULL AUTO_INCREMENT
A1 int (10) NOT NULL DEFAULT'0'
A2 int (10) unsigned DEFAULT NULL
A5 int (10) unsigned NOT NULL DEFAULT'0'
A3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
A4 varchar (200) NOT NULL DEFAULT''
PRIMARY KEY (id)
KEY idx_a1 (A1)
KEY idx_a2 (a2)) ENGINE=InnoDB
10. Insert test data into the steven table
Insert into steven (id,a1,a2,a5,a4) values (1)
Insert into steven (id,a1,a2,a5,a4) values (2pm 2pm 2pm 2m 2')
Insert into steven (id,a1,a2,a5,a4) values (3 recollection, 3 recitation, 3 recitation, 3')
Insert into steven (id,a1,a2,a5,a4) values (4, 4, 4, 4 and 4)
Insert into steven (id,a1,a2,a5,a4) values (5pm, 5pm, 5pm, 5pm)
Insert into steven (id,a1,a2,a5,a4) values (6, 6, 6, 6)
Insert into steven (id,a1,a2,a5,a4) values (7, 7, 7, 7)
Insert into steven (id,a1,a2,a5,a4) values (8, 8, 8, 8 and 8)
Insert into steven (id,a1,a2,a5,a4) values (9, 9, 9, 9, and 9)
11. View the data of each node:
Mysql > show databases
+-+
| | Database |
+-+
| | group3 |
| | group1 |
| | group2 |
+-+
3 rows in set (0.00 sec)
Mysql > use group1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-A
Database changed
Mysql > show tables
+-+
| | Tables_in_test |
+-+
| | oneproxy_replication_timestamp |
| | steven_0 |
| | steven_1 |
| | steven_2 |
+-+
4 rows in set (0.00 sec)
Mysql > select * from steven_0
->
+-+-
| | id | A1 | a2 | A5 | A3 | A4 |
+-+-
| | 3 | 3 | 3 | 3 | 2018-03-07 10:08:43 | 3 |
| | 6 | 6 | 6 | 6 | 2018-03-07 10:08:43 | 6 |
| | 9 | 9 | 9 | 9 | 2018-03-07 10:08:44 | 9 |
+-+-
3 rows in set (0.00 sec)
Mysql > select * from steven_1
->
+-+-
| | id | A1 | a2 | A5 | A3 | A4 |
+-+-
| | 1 | 1 | 1 | 1 | 2018-03-07 10:08:44 | 1 |
| | 4 | 4 | 4 | 4 | 2018-03-07 10:08:44 | 4 |
| | 7 | 7 | 7 | 7 | 2018-03-07 10:08:44 | 7 |
+-+-
3 rows in set (0.00 sec)
Mysql > select * from steven_2
+-+-
| | id | A1 | a2 | A5 | A3 | A4 |
+-+-
| | 2 | 2 | 2 | 2 | 2018-03-07 10:08:43 | 2 |
| | 5 | 5 | 5 | 5 | 2018-03-07 10:08:43 | 5 |
| | 8 | 8 | 8 | 8 | 2018-03-07 10:08:43 | 8 |
+-+-
3 rows in set (0.01sec)
