OneProxy分库分表

最新推荐文章于 2021-02-02 19:17:51 发布

liuxiansheng1228

最新推荐文章于 2021-02-02 19:17:51 发布

阅读量608

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/liuxiansheng1228/article/details/79918370

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

OneProxy分库分表

环境：

192.168.24.128 master

192.168.24.129 oneproxy

关闭防火墙或开启端口

一、配置master主机

1. 分配连接用户权限并创建数据库

mysql> grant all on *.* to oneproxy@'%' identified by '123.com';
mysql> create database test;

一、配置oneproxy中间件

1. 安装过程略（上一篇文档有）

2. 配置proxy.conf

[root@oneproxy oneproxy]# vim conf/proxy.conf 
[oneproxy]
#proxy-license              = A2FF461456A67F28,D2F6A5AD70C9042D
keepalive = 1
event-threads = 4
proxy-group-policy.1 = test:master-only
proxy-group-security = test:0
log-file = log/oneproxy.log
pid-file = log/oneproxy.pid
lck-file = log/oneproxy.lck
proxy-forward-clientip = 1
proxy-trans-debug = 1
mysql-version = 5.7.18
proxy-master-addresses.1 = 192.168.24.128:3306@test
proxy-user-list = oneproxy/7FB703DA3682A0CCC20168D44E8A7E92FE676A51@test
proxy-part-template = conf/template.txt
proxy-part-tables.1      = conf/part.txt
proxy-part-tables.2      = conf/part2.txt
proxy-part-tables.3      = conf/cust1.txt
proxy-charset            = utf8_bin
proxy-httpserver = :8080
proxy-httptitle = OneProxy Monitor

3. 配置part2.txt

[
   {
        "table"   : "user",
        "pkey"    : "id",
        "type"    : "int",
        "method"  : "hash",
        "partitions":
           [
               { "suffix" : "_0", "group": "test" },
               { "suffix" : "_1", "group": "test" },
               { "suffix" : "_2", "group": "test" },
               { "suffix" : "_3", "group": "test" }
           ]
   }
]

4. 启动oneproxy

[root@oneproxy oneproxy]# chmod +x demo.sh 
[root@oneproxy oneproxy]# ./demo.sh 
[root@oneproxy oneproxy]# ./oneproxy.service start
Starting OneProxy ...                                      [  OK  ]
[root@oneproxy oneproxy]# ss -anpt | grep 3307
LISTEN     0      128          *:3307                     *:*                   users:(("oneproxy",pid=3966,fd=11))
[root@oneproxy oneproxy]# ss -anpt | grep 4041
LISTEN     0      128          *:4041                     *:*                   users:(("oneproxy",pid=3966,fd=13))

三、验证

1. 从客户端登录连接端口

[root@localhost ~]# mysql -h 192.168.24.129 -u oneproxy -p123.com -P3307
mysql> show databases;
+----------+
| Database |
+----------+
| test     |
+----------+
1 row in set (0.00 sec)

2. 插入数据

mysql> create table user(id int ,c1 int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into user(id,c1) values (1,1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into user(id,c1) values (2,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user(id,c1) values (3,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user(id,c1) values (4,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user(id,c1) values (5,5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+------+------+
| id   | c1   |
+------+------+
|    4 |    4 |
|    1 |    1 |
|    5 |    5 |
|    2 |    2 |
|    3 |    3 |
+------+------+
5 rows in set (0.01 sec)

3. 回到master主机查看存储位置

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user_0         |
| user_1         |
| user_2         |
| user_3         |
+----------------+
4 rows in set (0.00 sec)

mysql>  select * from user_0;
+------+------+
| id   | c1   |
+------+------+
|    4 |    4 |
+------+------+
1 row in set (0.00 sec)

mysql>  select * from user_1;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
|    5 |    5 |
+------+------+
2 rows in set (0.00 sec)

mysql>  select * from user_2;
+------+------+
| id   | c1   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql>  select * from user_3;
+------+------+
| id   | c1   |
+------+------+
|    3 |    3 |
+------+------+
1 row in set (0.00 sec)

通过oneproxy实现了分离数据，验证成功