OneProxy分库分表的搭建

实验环境：
前期准备的三个节点的环形复制
master1：192.168.8.57
master2：192.168.8.58
master3：192.168.8.59
oneproxy：192.168.8.63
1、下载oneproxy，地址：
上传至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 SettingsONEPROXY_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 -h192.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)