MySQL學習筆記之十：使用mysql-proxy實現MySQL讀寫分離

標籤：mysql-proxy   讀寫分離   

   MySQL讀寫分離是指讓master處理寫操作，讓slave處理讀操作，非常適用於讀操作量比較大的情境，可減輕master的壓力。

   本文使用mysql-proxy實現mysql的讀寫分離，mysql-proxy實際上是作為後端mysql主從伺服器的代理，它直接接受用戶端的請求，對SQL語句進行分析，判斷出是讀操作還是寫操作，然後分發至對應的mysql伺服器上。對於多節點slave叢集，還可以起到負載平衡的效果。

一、準備實驗環境

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M02/82/58/wKioL1dRjO_gsNNHAABFkEPtk2o985.png" title="2016-06-03_215319.png" width="600" height="293" border="0" hspace="0" vspace="0" style="width:600px;height:293px;" alt="wKioL1dRjO_gsNNHAABFkEPtk2o985.png" />

     MySQL的主從複製架構搭建詳見http://9124573.blog.51cto.com/9114573/1785454

二、安裝配置mysql-proxy

  1、這裡下載的mysql-proxy版本為mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz     

      # tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local

      # cd /usr/local

      # ln -sv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy

      # useradd -r mysql-proxy

[[email protected] ~]# tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/[[email protected] ~]# ln -s /usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy[[email protected] ~]# cd /usr/local/mysql-proxy[[email protected] mysql-proxy]# lsbin  include  lib  libexec  licenses  share[[email protected] mysql-proxy]# ls binmysql-binlog-dump  mysql-myisam-dump  mysql-proxy[[email protected] mysql-proxy]# ls share/doc/mysql-proxy/active-queries.lua       commit-obfuscator.lua  README              tutorial-constants.lua  tutorial-prep-stmts.lua  tutorial-scramble.lua  xtab.luaactive-transactions.lua  commit-obfuscator.msc  ro-balance.lua      tutorial-inject.lua     tutorial-query-time.lua  tutorial-states.luaadmin-sql.lua            COPYING                ro-pooling.lua      tutorial-keepalive.lua  tutorial-resultset.lua   tutorial-tokenize.luaanalyze-query.lua        histogram.lua          rw-splitting.lua    tutorial-monitor.lua    tutorial-rewrite.lua     tutorial-union.luaauditing.lua             load-multi.lua         tutorial-basic.lua  tutorial-packets.lua    tutorial-routing.lua     tutorial-warnings.lua[[email protected] mysql-proxy]# useradd -r mysql-proxy

  2、為mysql-proxy提供SysV服務指令碼

      # vim /etc/rc.d/init.d/mysql-proxy

         ...(內容略）

      # chmod +x /etc/rc.d/init.d/mysql-proxy

      # chkconfig --add mysql-proxy

[[email protected] ~]# vim /etc/rc.d/init.d/mysql-proxy...[[email protected] ~]# chmod +x !$chmod +x /etc/rc.d/init.d/mysql-proxy[[email protected] ~]# chkconfig --add mysql-proxy

  3、為服務指令碼提供設定檔/etc/sysconfig/mysql-proxy，內容如下所示：

      # Options for mysql-proxy 

      ADMIN_USER="admin"   #mysql-proxy的管理帳號

      ADMIN_PASSWORD="admin"

      ADMIN_ADDRESS=""

      ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"   #完成mysql-proxy管理功能的指令碼

      PROXY_ADDRESS=""

      PROXY_USER="mysql-proxy"   #以什麼身份運行mysql-proxy

      PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog"

      其中最後一行，需要按實際情境進行修改，例如：

         PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.30.10:3306 --proxy-read-only-backend-addresses=192.168.30.20:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

         其中的proxy-backend-addresses選項和proxy-read-only-backend-addresses選項均可重複使用多次，以實現指定多個讀寫伺服器或唯讀伺服器;

          rw-splitting.lua是實現讀寫分離功能的指令檔

[[email protected] ~]# vim /etc/sysconfig/mysql-proxy# Options for mysql-proxyADMIN_USER="admin"ADMIN_PASSWORD="admin"ADMIN_ADDRESS=""ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"PROXY_ADDRESS=""PROXY_USER="mysql-proxy"PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.30.10:3306 --proxy-read-only-backend-addresses=192.168.30.20:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

  4、mysql-proxy命令的配置選項

      我們也可使用mysql-proxy命令啟動服務進程，其配置選項大致可分為協助選項、管理選項、代理選項及應用程式選項幾類：

      --help

      --help-admin

      --help-proxy

      --help-all   #以上四個選項均用於擷取協助資訊；

      --proxy-address=host:port   #代理服務監聽的地址和連接埠

      --admin-address=host:port   #管理模組監聽的地址和連接埠，預設為0.0.0.0:4041

      --proxy-backend-addresses=host:port   #後端讀寫mysql伺服器的地址和連接埠；

      --proxy-read-only-backend-addresses=host:port   #後端唯讀mysql伺服器的地址和連接埠；

      --proxy-lua-script=file_name   #完成mysql代理功能的Lua指令碼；

      --daemon   #以守護進程模式啟動mysql-proxy；

      --keepalive   #在mysql-proxy崩潰時嘗試重啟之；

      --log-file=/path/to/log_file_name   #記錄檔名稱；

      --log-level=level   #記錄層級；

      --log-use-syslog   #基於syslog記錄日誌；

      --plugins=plugin,..   #在mysql-proxy啟動時載入的外掛程式；

      --user=user_name   #運行mysql-proxy進程的使用者；

      --defaults-file=/path/to/conf_file_name   #預設使用的設定檔路徑；其配置段使用[mysql-proxy]標識；

      --proxy-skip-profiling   #禁用profile；

      --pid-file=/path/to/pid_file_name   #進程檔案名稱；

  5、建立admin.lua檔案，將其儲存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目錄中（略）

[[email protected] ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua...

  6、啟動mysql-proxy

      service mysql-proxy start

      網上都說mysql-proxy預設的代理服務連接埠是4040，本例中為3306，沒有查到原因，有可能是新版本所做的更改；如果想使用其它連接埠，使用選項--proxy-address修改即可；

[[email protected] ~]# service mysql-proxy startStarting /usr/local/mysql-proxy/bin/mysql-proxy:           [  OK  ][[email protected] ~]# netstat -tanp...tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      52446/mysql-proxy   tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      52446/mysql-proxy

  7、管理mysql-proxy

      mysql -u admin -h 192.168.30.14 -P 4041 -p
      mysql> select * from backends;   #查看後端mysql伺服器狀態 

[[email protected] ~]# mysql -u admin -h 192.168.30.14 -P 4041 -pEnter password: ERROR 1045 (28000): password doesn‘t match[[email protected] ~]# mysql -u admin -h 192.168.30.14 -P 4041 -pEnter password: ...mysql> select * from backends;+-------------+--------------------+-------+------+------+-------------------+| backend_ndx | address            | state | type | uuid | connected_clients |+-------------+--------------------+-------+------+------+-------------------+|           1 | 192.168.30.10:3306 | up    | rw   | NULL |                 0 ||           2 | 192.168.30.20:3306 | up    | ro   | NULL |                 0 |+-------------+--------------------+-------+------+------+-------------------+2 rows in set (0.00 sec)

三、測試

  1、在主庫上建立一個使用者

     grant select,insert,create,delete on *.* to [email protected]‘192.168.30.%‘ identified by ‘proxypass‘;

     flush privileges;

     該操作會同步到從節點，故無需再在從庫上建立

  2、準備抓包

     # tcpdump -i eth0 -nn -XX ip dst host 192.168.30.10 and tcp dst port 3306

     # tcpdump -i eth0 -nn -XX ip dst host 192.168.30.20 and tcp dst port 3306

  3、串連mysql-proxy，進行讀、寫等操作

      mysql -u proxytest -h 192.168.30.14 -P 3306 -p

    經過測試發現：

      所有寫請求都被分發到主庫，所有讀請求被分發到從庫；

      從庫不線上時，讀請求被發往主庫

MySQL學習筆記之十：使用mysql-proxy實現MySQL讀寫分離