oneProxy的学习和使用

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
开始进行数据验证阶段；

oneProxy更多内容
数据库分区和分表的区别