ITPub博客

首页 > 数据库 > 国内数据库 > OneProxy5.8.1数据库读写分离特性试验

OneProxy5.8.1数据库读写分离特性试验

原创 国内数据库 作者:yb708 时间:2016-02-01 15:05:11 0 删除 编辑

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

准备步骤:

1、 在测试之前准备了两台linuxredhat5.4)虚拟机(机器1bogonIP10.122.3.73;机器2bogon_2IP10.122.3.69),分别安装了MySQL5.6.27

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

3、 bogonbogon_2test数据库中创建测试表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-failoverread-balancebig-failover

 

策略1read-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上通过bogon3307端口来连接数据库,并执行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)

策略2read-balance,测试结论是读操作将会轮询分配到slavemaster节点上。

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上通过bogon3307端口来连接数据库,并执行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)

 

策略3big-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上通过bogon3307端口来连接数据库,并执行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)

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7913676/viewspace-1985195/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-07

  • 博文量
    11
  • 访问量
    186382