OneProxy分库分表最佳实践-亲试ok-20190903-20201020

环境介绍：

mysql主：192.168.124.157 ——->master

mysql从：192.168.124.158 ——->slave

中间件：192.168.124.159 ——->oneproxy

为方便实验，三台机都关闭防火墙和selinux

————————————————

下载安装包：

http://www.onexsoft.com/?page_id=3391 //下载对应版本的,下载完成后上传服务器

http://www.onexsoft.cn/software/step_into_oneproxy.pdf //说明文档

wget http://www.onexsoft.cn/software/oneproxy-rhel6-linux64-v5.8.1-ga.tar.gz

解包

[root@localhost softwares]# ls -ltr

总用量 634460

-rw-r--r--. 1 root root 644869837 5月 13 20:16 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

drwxr-xr-x. 9 root root 4096 5月 13 20:20 mysql-5.7.26-linux-glibc2.12-x86_64

-rw-r--r--. 1 root root 4808441 9月 2 17:28 oneproxy-rhel5-linux64-v6.0.0-ga.tar.gz

[root@localhost softwares]# tar -zxvf oneproxy-rhel6-linux64-v5.8.1-ga.tar.gz

[root@localhost softwares]# cd oneproxy/

[root@localhost oneproxy]# ls -ltr

总用量 32

-rw-r--r--. 1 root root 483 5月 16 2015 testadmin.sql

-rw-r--r--. 1 root root 327 5月 17 2016 trantest.sql

-rw-r--r--. 1 root root 197 6月 21 2016 testautocommit.sql

-rwxr-xr-x. 1 root root 877 7月 4 2016 oneproxy.service

-rw-r--r--. 1 root root 1198 7月 8 2016 README

-rw-r--r--. 1 root root 2345 7月 12 2016 testproxy.sql

drwxr-xr-x. 2 root root 38 3月 10 2017 bin

-rwxrwxrwx. 1 root root 167 9月 2 17:35 demo.sh

drwxr-xr-x. 2 root root 4096 9月 3 14:46 conf

drwxr-xr-x. 2 root root 66 9月 3 14:46 log

修改demo.sh文件，修改该变量ONEPROXY_HOME为 oneproxy的实际路径

[root@localhost oneproxy]# cat demo.sh

#/bin/bash

export ONEPROXY_HOME=/softwares/oneproxy

# valgrind --leak-check=full \

${ONEPROXY_HOME}/bin/oneproxy --defaults-file=${ONEPROXY_HOME}/conf/proxy.conf

修改proxy.con配置文件

[root@localhost conf]# cat proxy.conf

[oneproxy]

keepalive = 1

event-threads = 4

#指定日志文件路径

log-file = log/oneproxy.log

#指定PID文件路径

pid-file = log/oneproxy.pid

#指定LCK文件路径

lck-file = log/oneproxy.lck

#OneProxy服务的端口号，默认跟mysql服务端口一致

proxy-address = :3306

#MySQL服务端版本

mysql-version = 5.7.26-log

#指定主服务器的IP地址格式：IP地址:端口@oneproxy组

proxy-master-addresses.1 = 192.168.124.157:3306@order01

#指定从服务器的IP地址格式：IP地址:端口@oneproxy组，这里只是测试因此两个都是master

proxy-master-addresses.2 = 192.168.124.158:3306@order02

#proxy-master-addresses.2 = 192.168.1.120:3306@server2

#用户列表格式：用户名/密文密码@数据库名称

proxy-user-list = test/9D7E55EAF8912CCBF32069443FAC452794F8941B@orders

#一个模板文件

proxy-part-template = conf/template.txt

#分表分库的配置文件

proxy-part-tables.1 = conf/order_part.txt 下文会修改该文件

#proxy-part-tables.2 = conf/part2.txt

#proxy-part-tables.3 = conf/cust1.txt

#proxy-part-tables.4 = conf/cobar.txt

#指定数据库字符集

proxy-charset = gbk_chinese_ci

proxy-group-policy =order01:master-only

proxy-group-policy =order02:master-only

proxy-secure-client = 127.0.0.1

proxy-license = A2FF461456A67F28

#指定Web服务的监听端口

proxy-httpserver = :8080

proxy-httptitle = OneProxy Monitor

#proxy-log-sqlaudit = 1

#proxy-regexpr-audits.1 = .*(update)+.*(my_hash_0)+.*

修改分库分表文件

[root@localhost conf]# cat order_part.txt

[

{"table" : "order_detail",

"pkey" : "order_id",

"type" : "int",

"method" : "hash",

"partitions":

[

{ "suffix" : "_0", "group": "order01" },

{ "suffix" : "_1", "group": "order02" }

]

{

"table" : "order_product",

"pkey" : "order_id",

"type" : "int",

"method" : "hash",

"partitions":

[

{ "suffix" : "_0", "group": "order01" },

{ "suffix" : "_1", "group": "order02" }

]

{

"table" : "category",

"pkey" : "id",

"type" : "int",

"method" : "global",

"partitions":

[

{ "group": "order01" },

{ "group": "order02" }

]

}

]

在157mysql节点创建表

[root@localhost /]# mysql -uroot -p123456

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3713

Server version: 5.7.26-log MySQL Community Server (GPL)

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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

+--------------------+

1 rows in set (0.04 sec)

mysql> create database orders;

Query OK, 1 row affected (0.22 sec)

mysql> create user test@'192.168.124.%' identified by '123456';

Query OK, 0 rows affected (0.24 sec)

mysql> grant all on orders.* to test;

Query OK, 0 rows affected, 1 warning (0.08 sec)

####切换test用户

[root@localhost ~]# mysql -utest -p123456 -h192.168.124.157

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 3968

Server version: 5.7.26-log MySQL Community Server (GPL)

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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| orders |

+--------------------+

2 rows in set (0.00 sec)

mysql> use orders;

Database changed

mysql> show tables;

+------------------+

| Tables_in_orders |

+------------------+

| category |

+------------------+

1 row in set (0.00 sec)

###新建表

mysql> CREATE TABLE `order_detail_0` (

-> `order_id` int(11) NOT NULL,

-> `add_time` timestamp NOT NULL,

-> `order_amount` decimal(6,2) DEFAULT NULL,

-> PRIMARY KEY (`order_id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE `order_product_0` (

-> `order_id` int(11) NOT NULL,

-> `order_product_id` int(11) NOT NULL

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE `category` (

-> `id` int(11) NOT NULL,

-> `category_name` varchar(10) DEFAULT NULL,

-> PRIMARY KEY (`id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Query OK, 0 rows affected (0.09 sec)

mysql> show tables;

+------------------+

| Tables_in_orders |

+------------------+

| category |

| order_detail_0 |

| order_product_0 |

+------------------+

3 rows in set (0.00 sec)

在158mysql节点建表

[root@localhost /]# mysql -uroot -p123456

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3713

Server version: 5.7.26-log MySQL Community Server (GPL)

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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

+--------------------+

1 rows in set (0.04 sec)

mysql> create database orders;

Query OK, 1 row affected (0.22 sec)

mysql> create user test@'192.168.124.%' identified by '123456';

Query OK, 0 rows affected (0.24 sec)

mysql> grant all on orders.* to test;

Query OK, 0 rows affected, 1 warning (0.08 sec)

####切换test用户

[root@localhost ~]# mysql -utest -p123456 -h192.168.124.158

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 3968

Server version: 5.7.26-log MySQL Community Server (GPL)

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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| orders |

+--------------------+

2 rows in set (0.00 sec)

mysql> use orders;

Database changed

###新建表

mysql> CREATE TABLE `order_detail_1` (

-> `order_id` int(11) NOT NULL,

-> `add_time` timestamp NOT NULL,

-> `order_amount` decimal(6,2) DEFAULT NULL,

-> PRIMARY KEY (`order_id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE `order_product_1` (

-> `order_id` int(11) NOT NULL,

-> `order_product_id` int(11) NOT NULL

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE `category` (

-> `id` int(11) NOT NULL,

-> `category_name` varchar(10) DEFAULT NULL,

-> PRIMARY KEY (`id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Query OK, 0 rows affected (0.09 sec)

mysql> show tables;

+------------------+

| Tables_in_orders |

+------------------+

| category |

| order_detail_1 |

| order_product_1 |

+------------------+

3 rows in set (0.00 sec)

#7.启动OneProxy

[root@localhost oneproxy]# cd /softwares/oneproxy

[root@localhost oneproxy]# sh demo.sh

#8.OneProxy服务端登录OneProxy，OneProxy的默认用户密码 admin/OneProxy

[root@localhost conf]# mysql -P4041 -uadmin -pOneProxy -h127.0.0.1

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 102

Server version: 5.7.26-log OneProxy-Community-Admin-6.0.0 (OneXSoft)

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> list backend;

+------+----------------------+------+--------+--------+----------+-------+---------+------+------+-------+-------+-------+------+------+---------+

+------+----------------------+------+--------+--------+----------+-------+---------+------+------+-------+-------+-------+------+------+---------+

| 1 | 127.0.0.1:3306 | RW | UP | 0 | 0 | 1 | | No | No | NULL | NULL | NULL | NULL | NULL | NULL |

| 2 | 192.168.124.157:3306 | RW | UP | 1 | 0 | 1 | order01 | No | No | NULL | NULL | NULL | NULL | NULL | NULL |

| 3 | 192.168.124.158:3306 | RW | UP | 1 | 0 | 1 | order02 | No | No | NULL | NULL | NULL | NULL | NULL | NULL |

+------+----------------------+------+--------+--------+----------+-------+---------+------+------+-------+-------+-------+------+------+---------+

3 rows in set (0.00 sec)

mysql> list tables;

+---------------+----------+------+--------+------------+----------+----------+

+---------------+----------+------+--------+------------+----------+----------+

| order_detail | order_id | int | hash | 2 | 0 | NULL |

| order_product | order_id | int | hash | 2 | 0 | NULL |

| category | id | int | global | 2 | 0 | NULL |

+---------------+----------+------+--------+------------+----------+----------+

3 rows in set (0.00 sec)

测试OneProxy

测试语句如下：

[root@localhost softwares]# cat test_oneproxy.sh

#!/bin/bash

order_id=1

while :

order_id=`echo $order_id+1|bc`

sql1="insert into order_detail(order_id,add_time,order_amount) values(${order_id},now(),100.00)"

echo $sql1 | mysql -utest -p123456 -h127.0.0.1

sql2="insert into order_product(order_id,order_product_id) values(${order_id},${order_id}*10)"

echo $sql2 | mysql -utest -p123456 -h127.0.0.1

sql3="insert into category(id,category_name) values(${order_id},'test')"

echo $sql3 | mysql -utest -p123456 -h127.0.0.1

Done

运行该脚本，分别合计 157、158、159三个库中的数据总量

124.159

[root@localhost conf]# mysql -utest -p123456 -h127.0.0.1

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 105

Server version: 5.7.26-log OneProxy-Community-6.0.0 (OneXSoft)

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> show tables;

+------------------+

| Tables_in_orders |

+------------------+

| category |

| order_detail_0 |

| order_product_0 |

+------------------+

3 rows in set (0.05 sec)

mysql> select count(1) from category;

+----------+

| count(1) |

+----------+

| 739 |

+----------+

1 row in set (0.00 sec)

mysql> select count(1) from order_detail;

+----------+

| count(1) |

+----------+

| 740 |

+----------+

1 row in set (0.01 sec)

mysql> select count(1) from order_product;

+----------+

| count(1) |

+----------+

| 1111 |

+----------+

1 row in set (0.00 sec)

124.158

mysql> select count(1) from category;

+----------+

| count(1) |

+----------+

| 739 |

+----------+

1 row in set (0.00 sec)

mysql> select count(1) from order_detail_1;

+----------+

| count(1) |

+----------+

| 370 |

+----------+

1 row in set (0.29 sec)

mysql> select count(1) from order_product_1;

+----------+

| count(1) |

+----------+

| 741 |

+----------+

1 row in set (0.00 sec)

124.157

mysql> select count(1) from category;

+----------+

| count(1) |

+----------+

| 739 |

+----------+

1 row in set (0.00 sec)

mysql> select count(1) from order_detail;

ERROR 1146 (42S02): Table 'orders.order_detail' doesn't exist

mysql> select count(1) from order_detail_0;

+----------+

| count(1) |

+----------+

| 370 |

+----------+

1 row in set (0.00 sec)

mysql> select count(1) from order_product_0;

+----------+

| count(1) |

+----------+

| 370 |

+----------+

1 row in set (0.00 sec)

	order_product	order_detail	category
157	370	370	739
158	741	370	739
159	1111	740	739

由此可见：

OneProxy对于没有分片操作的表数据总量取两张表的平均值

对于有分片操作的表数据总量取两张表的和

创建时间：2021-12-22 11:34

넶浏览量：0

ꄴ前一个：无

ꄲ后一个：无

OneProxy分库分表最佳实践-亲试ok-20190903-20201020

全部评论 (0)