【Mysql】One-proxy簡單搭建

小亮520cl發表於2016-03-29
原文網址 : http://blog.itpub.net/29096438/viewspace-2071595/
MySql
1 下載
官網：

2 安裝
機器有限，oneproxy就安裝在192.168.1.6上
192.168.1.6--主---oneproxy
192.168.1.56---從

3 簡單讀寫分離
  1. [chenliang@hostnfsd oneproxy]$ more demo.sh
  2. #/bin/bash
  3. #
  4. export ONEPROXY_HOME=/usr/local/oneproxy

  6. # valgrind --leak-check=full \

  8. ${ONEPROXY_HOME}/bin/oneproxy --keepalive \
  9. --proxy-address=192.168.1.6:3308 \                                                 ####mysql互動埠
  10. --admin-address=192.168.1.6:4041 \                                                 ####管理埠
  11. --proxy-master-addresses=192.168.1.6:3307@group1 \                                 ####real mysql 
  12. --proxy-slave-addresses=192.168.1.56:3307@group1 \                                 ####real mysql
  13. --proxy-user-list=testuser/950434F7872CB57A600E1B2B7237766FA7E95460@testbak \      #####使用者以及對應的預設資料庫，該使用者需提前在db中建好，密碼登入管理埠passwd '密碼'  生成，這一步可第二次新增
  14. --proxy-group-policy=group1:Read-balance \                                         #####組策略，很多種，具體可看文件
  15. --proxy-charset=utf8_chinese_ci \
  16. --log-file=${ONEPROXY_HOME}/oneproxy.log \
  17. --pid-file=${ONEPROXY_HOME}/oneproxy.pid

（6與56只是名義上的主從，實際沒搭建呢，只是為了測試讀寫分別從哪個ip）


讀寫分離

  1. 插入資料：[chenliang@hostnfsd oneproxy]$ mysql -utestuser -ptestuser -h192.168.1.6 --port=3308 -e "insert into  test2(id) values(4)" testbak; 
    Warning: Using a password on the command line interface can be insecure.
    [chenliang@hostnfsd oneproxy]$ mysql -utestuser -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" testbak;             
    Warning: Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    1 | NULL |
    |    2 | NULL |
    |    3 | 4    |
    +------+------+

  2. 成功插入主庫

讀負載均衡測試：

  1. [chenliang@hostnfsd oneproxy]$ mysql -utestuser -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1;
    Warning: Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    1 | NULL |
    |    2 | NULL |
    |    3 | 4    |
    +------+------+
    [chenliang@hostnfsd oneproxy]$ mysql -utestuser -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1;
    Warning: Using a password on the command line interface can be insecure.
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    +------+
    [chenliang@hostnfsd oneproxy]$ mysql -utestuser -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1;
    Warning: Using a password on the command line interface can be insecure.
    +------+------+
    | id   | name |
    +------+------+
    |    1 | NULL |
    |    2 | NULL |
    |    3 | 4    |
    +------+------+
    [chenliang@hostnfsd oneproxy]$ mysql -utestuser -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1;
    Warning: Using a password on the command line interface can be insecure.
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    +------+
  3. 讀負載均衡，均衡分發至每個庫




多使用者：
  1. [root@hostnfsd :/usr/local/oneproxy]$ vi demo.sh 
    #/bin/bash
    #
    export ONEPROXY_HOME=/usr/local/oneproxy


    # valgrind --leak-check=full \


    ${ONEPROXY_HOME}/bin/oneproxy --keepalive \
    --proxy-address=192.168.1.6:3308 \
    --admin-address=192.168.1.6:4041  \
    --proxy-master-addresses=192.168.1.6:3307@group1 \
    --proxy-slave-addresses=192.168.1.56:3307@group1  \
    --proxy-user-list=testuser/950434F7872CB57A600E1B2B7237766FA7E95460@testbak \
    --proxy-user-list=jonn/950434F7872CB57A600E1B2B7237766FA7E95460@testbak2 \   ---就這不同，使用者指定預設資料庫不一樣罷了
    --proxy-group-policy=group1:Read-balance \
    --proxy-charset=utf8_chinese_ci \
    --log-file=${ONEPROXY_HOME}/oneproxy.log \
    --pid-file=${ONEPROXY_HOME}/oneproxy.pid
測試：
[root@hostnfsd :/root]$ mysql -ujonn -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1                         ---讀寫分離
Warning: Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  106 |
+------+
[root@hostnfsd :/root]$ mysql -ujonn -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1
Warning: Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|   56 |
+------+
[root@hostnfsd :/root]$ mysql -ujonn -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1
Warning: Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  106 |
+------+


[root@hostnfsd :/root]$ mysql -ujonn -ptestuser -h192.168.1.6 --port=3308 -e "insert into  test2 values(111)" group1              ---插入主庫
Warning: Using a password on the command line interface can be insecure.
[root@hostnfsd :/root]$ mysql -ujonn -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1           
Warning: Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|   56 |
+------+
[root@hostnfsd :/root]$ mysql -ujonn -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1
Warning: Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  106 |
|  111 |
+------+
[root@hostnfsd :/root]$ mysql -ujonn -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1
Warning: Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|   56 |
+------+
[root@hostnfsd :/root]$ mysql -ujonn -ptestuser -h192.168.1.6 --port=3308 -e "select * from test2" group1
Warning: Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  106 |
|  111 |



---待續，先做個筆記

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

相關文章