oneProxy是一款优秀的数据库分区分表的软件，学习一下简单的使用；
- oneProxy的安装与配置
1、局域网配置
节点1:192.168.3.100 节点2:192.168.3.101 oneProxy:192.168.3.103
2、oneProxy的下载与解压缩
下载地址;wget http://www.onexsoft.cn/sofeware/oneproxy-rhel6-linux64-v5.8.1-ga.tar.gz
解压缩:tar -zxf oneproxy-rhel6-linux64-v5.8.1-ga.tar.gz
3、修改demo.sh的目录
ONEPROXY-HOME=/usr/local/oneproxy
4、在两个节点中创建用户和表
create user test@'192.168.3.%' indentified by '123456';
create databases orders;
grant all privileges on orders.* to test@'192.168.3.%';
5、获取数据库经过oneProxy加密后的密码
./demo.sh
mysql -p4041 -uadmin -pOneProxy -h127.0.0.1
passwd '123456'
拷贝加密后的字符串，下面称为jiami
6、修改/conf/proxy.conf
mysql-version = 5.7.9-log
proxy-address = :3306
proxy-master-addresses.1 = 192.168.3.100:3306@order01
proxy-master-addresses.2 = 192.168.3.101:3306@order02
proxy-user-list = test/jiami@orders
proxy-part-template = conf/order_part.txt
proxy-group-policy = order01:master-only
proxy-group-policy = order02:master-only
- 分区的数据库准备
1、编辑conf/order_part.txt
[
{
"table":"order_detail",##表名
"pkey":"order_id",##分区键
"type":"int",##类型
"method":"hash",##哈希法
"partitions":
[
{"suffix":"_0","group":"order01"},##节点1处添加后缀_0
{"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"},
]
}
]
2、在节点1上建立相关的表
create table order_detail_0(
order_id int not null,
add_time datetime not null,
order_amount decimal(6,2),
primary key (order_id)
);
create table order_production_0(
order_id int not null,
order_product_id int not null,
primary key (order_id)
);
create table order_category(
id int not null,
category_name varchar(10),
primary key (id)
);
3、在节点2上建立相关的表
create table order_detail_1(
order_id int not null,
add_time datetime not null,
order_amount decimal(6,2),
primary key (order_id)
);
create table order_production_1(
order_id int not null,
order_product_id int not null,
primary key (order_id)
);
create table order_category(
id int not null,
category_name varchar(10),
primary key (id)
);
4、杀掉oneProxy进程，进行重启操作；
./demo.sh
- 分区开始
1、验证oneProxy是否正确连接到配置的数据库
mysql -p4041 -h127.0.0.1 -uadmin -pOneProxy
list backen;
list tables;
2、编写用于生成数据的脚本test.sh
#!/bin/bash
order_id=1
while :
do
order_id=`echo $order_id + 1 | bc`
sql="insert into order_detail(order_id,add_time,order_amount) values(${order_id},now(),100)";
echo $sql | 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,order_name) values(${order_id},"test123")";
echo $sql3 | mysql -utest -p123456 -h127.0.0.1
done
3、查看oneProxy中数据库的情况（此数据库为oneProxy的代理）
mysql -utest -p123456 -h127.0.0.1 -p3306
开始进行数据验证阶段；
