oneproxy的水平切表

oneproxy的水平切表

准备：

主机	ip
oneproxy	192.168.10.3
mysql主	192.168.10.10
mysql从	192.168.10.20
客户机	192.168.10.2

将oneproxy软件包拖拽到oneproxy主机
oneproxy-rhel5-linux64-v6.0.0-ga.tar.gz
oneproxy点此下载 提取码：tquu

1、oneproxy主机的操作
1.1 查看软件包并解压缩

[root@localhost ~]# ls oneproxy-rhel5-linux64-v6.0.0-ga.tar.gz
[root@localhost ~]# tar -zxf oneproxy-rhel5-linux64-v6.0.0-ga.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/oneproxy/

1.2 添加环境变量

[root@localhost oneproxy]# echo 'export ONEPROXY_HOME=/usr/local/oneproxy' >> /etc/profile
[root@localhost oneproxy]# source /etc/profile

1.3 修改配置

[root@localhost oneproxy]# vim oneproxy.service
     8   ONEPROXY_HOME修改为
     ONEPROXY_HOME=/usr/local/oneproxy
[root@localhost oneproxy]# cp oneproxy.service /etc/init.d/oneproxy
[root@localhost oneproxy]# chmod +x /etc/init.d/oneproxy

1.4 启动服务

[root@localhost oneproxy]# /etc/init.d/oneproxy start

1.5 修改proxy.conf文件

[root@localhost oneproxy]# vim conf/proxy.conf
   8 proxy-address            = 192.168.10.3:3307  #改为本机的ip
   9 mysql-version            = 5.5.52   #上述几个mysql的版本尽量保持一致
   10 proxy-master-addresses.1 = 192.168.10.10:3306@server1  # mysql主的ip
   11 proxy-slave-addresses.1 = 192.168.10.20:3306@server1  #mysql从的ip
   12 proxy-group-policy = server1:0
   13 proxy-group-security = server1:0

参数释义：
proxy-address：proxy server自身监听地址
mysql-version：mysql版本号
proxy-master-addresses：master节点地址（可写入节点）
proxy-slave-addresses：slave节点地址（可读取节点）
proxy-group-policy：预定策略，0代表Lua Script来决定， 默认为Master Only；1代表Read Failover；2代表Read/Write Split(Master节点不参与读操作)；3代表双Master结构，或者是XtraDB Cluster结构，即多主对等的方式；4代表Read/Write Split （Master节点共同参与读操作）；5代表读写随机。
proxy-group-security：为特定Server Group设置安全级别。安全级别，0默认值，1禁止DDL，2禁止不带条件的查询语句，3只允许Select。
1.6 安装mysql ，然后登陆，设置密码

[root@localhost oneproxy]# yum -y install mariadb-devel mariadb-server
[root@localhost oneproxy]# systemctl start mariadb
[root@localhost oneproxy]# mysqladmin -uroot password 123.com
[root@localhost oneproxy]# mysql -uadmin -pOneProxy -h 192.168.10.3 -P4041

   MySQL [(none)]> passwd '123.com';
   +---------+------------------------------------------+
   | TEXT    | PASSWORD                                 |
   +---------+------------------------------------------+
   | 123.com | 7FB703DA3682A0CCC20168D44E8A7E92FE676A51 |
   +---------+------------------------------------------+
   1 row in set (0.00 sec)
   
把加密密码粘贴到proxy.conf文件里，在修改一下字符集
   proxy-user-list          = test/7FB703DA3682A0CCC20168D44E8A7E92FE676A51@course
   proxy-charset            = utf8_chinese_ci

1.7 登录mysql 并授权，另外两台mysql主和从也需要授权 操作一样

[root@localhost oneproxy]# mysql -uroot -p123.com -P3306
  MariaDB [(none)]> grant all on *.* to 'test'@'192.168.10.3' identified by '123.com';
    Query OK, 0 rows affected (0.00 sec)
    
  MariaDB [(none)]> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

1.8 关于part.txt文件

[root@localhost oneproxy]# vim conf/part.txt 
  #删除所有内容，把下边内容粘贴进去
  [
        {
        "table"  :"test",
        "pkey"   :"id",
        "type"   :"int",
        "method"  :"hash",
        "partitions":
          [
             {"suffix":"_0","group":"server1"},
             {"suffix":"_1","group":"server1"},
             {"suffix":"_2","group":"server2"},
             {"suffix":"_3","group":"server2"},
             {"suffix":"_4","group":"server3"},
             {"suffix":"_5","group":"server3"},
             {"suffix":"_6","group":"server4"},
             {"suffix":"_7","group":"server4"}
           ]
        },                                                                                                

        {
        "table"  :"benet",
        "pkey"   :"id",
        "type"   :"int",
        "method"  :"hash",
        "partitions":
          [
             {"suffix":"_0","group":"server1"},
             {"suffix":"_1","group":"server1"},
             {"suffix":"_2","group":"server2"},
             {"suffix":"_3","group":"server2"},
             {"suffix":"_4","group":"server3"},
             {"suffix":"_5","group":"server3"},
             {"suffix":"_6","group":"server4"},
             {"suffix":"_7","group":"server4"}
           ]
        }
]

1.9 配置完proxy.conf和part.txt之后，启动oneproxy（启动时可以先关闭再启动）

[root@localhost oneproxy]# /etc/init.d/oneproxy stop
Stopping oneproxy (via systemctl):                         [  确定  ]

[root@localhost oneproxy]# /etc/init.d/oneproxy start
Starting oneproxy (via systemctl):                         [  确定  ]

#如果启动不成功 oneproxy不能登录

2、客户机的操作
2.1 安装mysql 并启用

[root@localhost ~]#  yum -y install mariadb-devel mariadb-server
[root@localhost ~]# systemctl start mariadb.service

2.2 设置mysql登录密码 并登录数据库添加授权

[root@localhost ~]# mysqladmin -uroot password 123.com
[root@localhost ~]# mysql -u root -p123.com
    MariaDB [(none)]> grant all on *.* to 'test'@'192.168.10.3' identified by '123.com';
      Query OK, 0 rows affected (0.00 sec)
      
    MariaDB [(none)]> flush privileges;
      Query OK, 0 rows affected (0.00 sec)

2.3 登录oneproxy

[root@localhost ~]# mysql -utest -p123.com -h 192.168.10.3 -P 3307
MySQL [(none)]> use course;
Database changed

MySQL [course]> create table benet(id int);
 #这里会卡着不动，过一会儿按三次Ctrl +c直接退出，然后再次进来

MySQL [(none)]> show tables;
+------------------+
| Tables_in_course |
+------------------+
| benet_0          |
| benet_1          |
+------------------+
2 rows in set (0.00 sec)
#看到两个benet表，表示切表成功，然后插值验证

添加读写分离
1、在主库上创表 看从库是否可以同步

MariaDB [(none)]> use course;
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

MariaDB [course]> create table one(id int,name varchar(30));
Query OK, 0 rows affected (0.00 sec)

MariaDB [course]> show tables;
+------------------+
| Tables_in_course |
+------------------+
| benet_0          |
| benet_1          |
| one              |
+------------------+
3 rows in set (0.00 sec)

2、从库已同步one表

MariaDB [(none)]> use course;
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

MariaDB [course]> show tables;
+------------------+
| Tables_in_course |
+------------------+
| benet_0          |
| benet_1          |
| one              |
+------------------+
3 rows in set (0.00 sec)

3、在从库插入数据并查看

MariaDB [course]> insert into one values(1,'tiechui');
Query OK, 1 row affected (0.38 sec)

MariaDB [course]> select * from one;
+------+---------+
| id   | name    |
+------+---------+
|    1 | tiechui |
+------+---------+
1 row in set (0.03 sec)
      #注意：此数据不会同步给主库

4、主库查看表 发现内容为空 因为从库写的数据无效

MariaDB [course]> select * from one;
Empty set (0.00 sec)

5、客户端登录oneproxy进行one表的数据查询发现 可以看到数据 说明 读是在从库进行的

MySQL [(none)]> use course;
Database changed
MySQL [course]> select * from one;
+------+---------+
| id   | name    |
+------+---------+
|    1 | tiechui |
+------+---------+
1 row in set (0.00 sec)
 

使用oneproxy进行读写分离成功！