OneProxy分库分表的搭建
实验环境：
前期准备的三个节点的环形复制
master1：192.168.8.57
master2：192.168.8.58
master3：192.168.8.59
oneproxy：192.168.8.63
1、下载oneproxy，地址：http://www.onexsoft.com/
上传至192.168.8.63，解压到/usr/local
2、修改文件描述符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、创建加密密码
[root@oneproxy bin]# ./MySQLpwd mysql
F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B
4、修改demo.sh
目录中的demo.sh是初次启动脚本，修改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、修改oneproxy.service
oneproxy.service是启动脚本，修改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、配置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.119:4041
proxy-httptitle = OneProxy Monitor
proxy-httpserver = :8080
proxy-part-tables.1 = conf/part.txt
7、配置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、启动oneproxy
[root@oneproxy conf]#./oneproxy.service start
Starting OneProxy ... [ OK ]
9、分库分表的功能测试
客户端连接OneProxy的3307端口，在test下创建steven表。
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、向steven表插入测试数据
insert into steven(id,a1,a2,a5,a4) values(1,1,1,1,'1');
insert into steven(id,a1,a2,a5,a4) values(2,2,2,2,'2');
insert into steven(id,a1,a2,a5,a4) values(3,3,3,3,'3');
insert into steven(id,a1,a2,a5,a4) values(4,4,4,4,'4');
insert into steven(id,a1,a2,a5,a4) values(5,5,5,5,'5');
insert into steven(id,a1,a2,a5,a4) values(6,6,6,6,'6');
insert into steven(id,a1,a2,a5,a4) values(7,7,7,7,'7');
insert into steven(id,a1,a2,a5,a4) values(8,8,8,8,'8');
insert into steven(id,a1,a2,a5,a4) values(9,9,9,9,'9');
11、查看各个节点数据：
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.01 sec)
分享题目：OneProxy分库分表的搭建
文章链接：http://pwwzsj.com/article/jopgds.html
前期准备的三个节点的环形复制
master1：192.168.8.57
master2：192.168.8.58
master3：192.168.8.59
oneproxy：192.168.8.63
1、下载oneproxy，地址：http://www.onexsoft.com/
上传至192.168.8.63，解压到/usr/local
2、修改文件描述符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、创建加密密码
[root@oneproxy bin]# ./MySQLpwd mysql
F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B
4、修改demo.sh
目录中的demo.sh是初次启动脚本，修改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、修改oneproxy.service
oneproxy.service是启动脚本，修改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、配置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.119:4041
proxy-httptitle = OneProxy Monitor
proxy-httpserver = :8080
proxy-part-tables.1 = conf/part.txt
7、配置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、启动oneproxy
[root@oneproxy conf]#./oneproxy.service start
Starting OneProxy ... [ OK ]
9、分库分表的功能测试
客户端连接OneProxy的3307端口，在test下创建steven表。
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、向steven表插入测试数据
insert into steven(id,a1,a2,a5,a4) values(1,1,1,1,'1');
insert into steven(id,a1,a2,a5,a4) values(2,2,2,2,'2');
insert into steven(id,a1,a2,a5,a4) values(3,3,3,3,'3');
insert into steven(id,a1,a2,a5,a4) values(4,4,4,4,'4');
insert into steven(id,a1,a2,a5,a4) values(5,5,5,5,'5');
insert into steven(id,a1,a2,a5,a4) values(6,6,6,6,'6');
insert into steven(id,a1,a2,a5,a4) values(7,7,7,7,'7');
insert into steven(id,a1,a2,a5,a4) values(8,8,8,8,'8');
insert into steven(id,a1,a2,a5,a4) values(9,9,9,9,'9');
11、查看各个节点数据：
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.01 sec)
分享题目：OneProxy分库分表的搭建
文章链接：http://pwwzsj.com/article/jopgds.html
