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)

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)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7913676/viewspace-1985195/，如需轉載，請註明出處，否則將追究法律責任。

OneProxy5.8.1資料庫讀寫分離特性試驗

相關文章