oracle11g 读写分离代理,OneProxy5.8.1数据库读写分离特性试验

OneProxy预制多种查询策略，通过OneProxy来查询数据库可以按策略的不同将查询分担到主从数据库中。

准备步骤：

1、在测试之前准备了两台linux(redhat5.4)虚拟机(机器1：bogon，IP：10.122.3.73；机器2：bogon_2，IP：10.122.3.69)，分别安装了MySQL5.6.27。

2、按照“1.OneProxy5.8.1新的目录组织结构运行试验”的步骤在bogon安装OneProxy。

3、在bogon和bogon_2的test数据库中创建测试表t，并分别插入一条记录用于区分，数据如下：

在bogon上：

mysql> select

* from t;

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

| a | b|

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

| 1 | bogon |

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

1 row in set

(0.00 sec)

在bogon_2上：

mysql> select

* from t;

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

| a | b|

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

| 1 | bogon_2 |

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

1 row in set

(0.00 sec)

测试内容：

分别测试不同组策略下的OneProxy读写方式，本次测试的组策略有read-failover、read-balance、big-failover。

策略1：read-failover，测试结论是在slave可用时，读操作只会访问slave的节点，如果slave不可用才访问master。

测试步骤：

1、修改配置文件proxy.conf，将两台机器设置成一个group并设置相应访问策略，内容如下：

[oneproxy]

keepalive= 1

event-threads = 4

log-file= log/oneproxy.log

pid-file= log/oneproxy.pid

lck-file= log/oneproxy.lck

mysql-version = 5.6.27

proxy-address= :3307

proxy-master-addresses.1 = 10.122.3.73:3306@server1

proxy-slave-addresses.1 = 10.122.3.69:3306@server1

proxy-user-list=

test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@test

proxy-user-group=

server1:test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3

proxy-part-template= conf/template.txt

proxy-part-tables.1= conf/part.txt

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

proxy-charset= gbk_chinese_ci

proxy-group-policy= server1:read-failover

2、启动bogon上的OneProxy，然后在bogon_2上通过bogon的3307端口来连接数据库，并执行select查询，可以观察发现此时查询走了slave数据库：

[root@bogon_2 init.d]#

mysql -h 10.122.3.73 -P 3307 -utest -ptest

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.6.27

OneProxy-Agent-5.8.1 (OneXSoft)

Copyright (c)

2000, 2015, Oracle and/or its affiliates. All rights reserved.

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> select * from t;

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

| a | b|

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

| 1 | bogon_2 |

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

1 row in set

(0.00 sec)

3、关闭bogon_2上的MySQL服务，然后再执行上述查询，可以发现此时查询走了master数据库：

mysql> select * from t;

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

| a | b|

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

| 1 | bogon |

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

1 row in set (0.01 sec)

策略2：read-balance，测试结论是读操作将会轮询分配到slave和master节点上。

1、修改配置文件proxy.conf，将两台机器设置成一个group并设置相应访问策略，内容如下：

[oneproxy]

keepalive= 1

event-threads = 4

log-file= log/oneproxy.log

pid-file= log/oneproxy.pid

lck-file= log/oneproxy.lck

mysql-version = 5.6.27

proxy-address= :3307

proxy-master-addresses.1 = 10.122.3.73:3306@server1

proxy-slave-addresses.1 = 10.122.3.69:3306@server1

proxy-user-list=

test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@test

proxy-user-group= server1:test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3

proxy-part-template= conf/template.txt

proxy-part-tables.1= conf/part.txt

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

proxy-charset= gbk_chinese_ci

proxy-group-policy= server1:read_balance

2、启动bogon上的OneProxy，然后在bogon_2上通过bogon的3307端口来连接数据库，并执行select查询，可以观察发现此时查询走了master数据库：

mysql> select * from t;

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

| a | b|

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

| 1 | bogon |

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

1 row in set

(0.00 sec)

3、然后关闭改连接在重新连接，可以发现此时查询走了slave数据库：

mysql> select * from t;

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

| a | b|

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

| 1 | bogon_2 |

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

1 row in set (0.00 sec)

策略3：big-failover，对于复杂查询只走slave节点，疑问是对于简单查询也没有走master节点。

1、修改配置文件proxy.conf，将两台机器设置成一个group并设置相应访问策略，内容如下：

[oneproxy]

keepalive= 1

event-threads = 4

log-file= log/oneproxy.log

pid-file= log/oneproxy.pid

lck-file= log/oneproxy.lck

mysql-version = 5.6.27

proxy-address= :3307

proxy-master-addresses.1 = 10.122.3.73:3306@server1

proxy-slave-addresses.1 = 10.122.3.69:3306@server1

proxy-user-list=

test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@test

proxy-user-group=

server1:test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3

proxy-part-template= conf/template.txt

proxy-part-tables.1= conf/part.txt

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

proxy-charset= gbk_chinese_ci

proxy-group-policy= server1:big-failover

2、启动bogon上的OneProxy，然后在bogon_2上通过bogon的3307端口来连接数据库，并执行select查询，可以观察发现此时查询走了slave数据库：

mysql> select a, b from

t where a=1;

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

| a | b|

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

| 1 | bogon_2 |

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

1 row in set (0.01 sec)

mysql> select a, b from

t;

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

| a | b|

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

| 1 | bogon_2 |

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

1 row in set

(0.00 sec)

3、然后关闭改连接在重新连接，可以发现此时查询还是走了slave数据库，多次重复这两步操作查询结果相同：

mysql> select a, b from

t where a=1;

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

| a | b|

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

| 1 | bogon_2 |

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

1 row in set (0.01 sec)

mysql> select a, b from

t;

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

| a | b|

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

| 1 | bogon_2 |

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

1 row in set (0.00 sec)