基於Mysql-Proxy 實現MariaDB 讀寫分離

標籤：

• 一、Mysql-Proxy 簡單介紹

  MySQL-Proxy是一個處於你的client端和MySQL server端之間的簡單程式，它可以監測、分析或改變它們的通訊。它使用靈活，沒有限制，常見的用途包括：Server Load Balancer，故障、查詢分析，查詢過濾和修改等等。

  MySQL-Proxy就是這麼一個中介層代理，簡單的說，MySQL-Proxy就是一個串連池，負責將前台應用的串連請求轉寄給背景資料庫，並且通過使用lua指令碼，可以實現複雜的串連控制和過濾，從而實現讀寫分離和Server Load Balancer。對於應用來說，MySQL-Proxy是完全透明的，應用則只需要串連到MySQL-Proxy的監聽連接埠即可。當然，這樣proxy機器可能成為單點失效，但完全可以使用多個proxy機器做為冗餘，在應用伺服器的串連池配置中配置到多個proxy的串連參數即可。

  MySQL-Proxy更強大的一項功能是實現“讀寫分離”，基本原理是讓主要資料庫處理事務性查詢，讓從庫處理SELECT查詢。資料庫複寫被用來把事務性查詢導致的變更同步到叢集中的從庫。

   

  二、實驗環境

   

  伺服器	IP地址	系統版本
  master主伺服器	172.16.8.8	Centos6.5
  slave從伺服器	172.16.8.9	Centos6.5
  mysql-proxy伺服器	172.16.8.5	Centos6.5

  3台伺服器時間要一致

  # ntpdate 172.16.0.1 //同步時間

  注，此實驗是基於上一篇部落格，http://584014981.blog.51cto.com/8605371/1396724 ，先實現主從複製，再實現讀寫分離。

   

  三、Mysql-Proxy 命令簡介

   

  mysql-proxy 命令

  --help-all ———— 用於擷取全部協助資訊

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

  --admin-address=host:port ———— 管理模組監聽的地址和連接埠

  --proxy-backend-addresses=host:port ———— 後端mysql伺服器的地址和連接埠（主伺服器）

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

  --proxy-lua-script=file ———— 完成mysql代理功能的Lua指令碼

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

  --defaults-file=/path/to/conf_file_name ———— 預設使用的設定檔路徑

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

  --log-level=level ———— 記錄層級

  --log-use-syslog ———— 基於syslog記錄日誌

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

  四、Mysql-Proxy主從分離

  1.增加代理使用者

  在Mysql-Proxy伺服器上操作：

  [[email protected] ~]# groupadd -g 3306 mysql-proxy[[email protected] ~]# useradd -u 3306 -g mysql-proxy -s /sbin/nologin -M mysql-proxy

   

  2.安裝mysql-proxy

  ：

  http://pan.baidu.com/s/1sjQ4czV

  [[email protected] ~]# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz  -C /usr/local[[email protected] ~]# cd /usr/local[[email protected] local]# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit mysql-proxy[[email protected] local]# cd mysql-proxy

   

  3.修改檔案的擁有者與所屬組

  [[email protected] mysql-proxy]# chown -R root:mysql-proxy /usr/local/mysql-proxy/*

   

  4.修改PATH環境變數

  [[email protected] mysql-proxy]# vim /etc/profile.d/mysql-proxy.sh//添加如下內容：export PATH=$PATH:/usr/local/mysql-proxy/bin[[email protected] mysql-proxy]# source /etc/profile //重讀設定檔

   

  5.啟動mysql-proxy

  [[email protected] mysql-proxy]#  mysql-proxy  --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.16.8.8:3306" --proxy-read-only-backend-addresses="172.16.8.9:3306" #啟動mysql-proxy

  從日誌與進程來看，已經啟動成功!

   

  6.在主伺服器上建立測試的帳號與密碼

  master:

  MariaDB [(none)]> grant all on *.* to [email protected]‘172.16.8.%‘ identified by ‘123‘;MariaDB [(none)]> flush privileges;

   

  7.在從服務上進行測試

   

  slave:

  驗證是否能通過代理連接埠4040 連接埠串連到 mysql-proxy

   

  [[email protected] ~]# mysql -uroot -p -h172.16.8.5 --port=4040

  注，已經成功從4040連接埠，串連進mysql-proxy。下面我們來實現讀寫分離。

   

  8.讀寫分離

  1.Mysql-Proxy本身不會實現讀寫分離，主要是依靠 Lua 指令碼實現的

  在mysql-proxy 的安裝目錄中有一個rw-splitting.lua 指令碼，專門用來實現 讀寫分離，路徑是/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

   

  2.為mysql-proxy 提供一個管理介面，方便以後隨時查看後端mysql 伺服器的狀態和訪問類型，實現管理功能

  注，下面為大家提供一個管理介面的指令碼，同樣也是一個LUA 指令碼，建議跟讀寫分離指令碼放在同一目錄。

  [[email protected] mysql-proxy]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.luafunction set_error(errmsg)    proxy.response = {        type = proxy.MYSQLD_PACKET_ERR,        errmsg = errmsg or "error"    }endfunction read_query(packet)    if packet:byte() ~= proxy.COM_QUERY then        set_error("[admin] we only handle text-based queries (COM_QUERY)")        return proxy.PROXY_SEND_RESULT    end    local query = packet:sub(2)    local rows = { }    local fields = { }    if query:lower() == "select * from backends" then        fields = {            { name = "backend_ndx",              type = proxy.MYSQL_TYPE_LONG },            { name = "address",              type = proxy.MYSQL_TYPE_STRING },            { name = "state",              type = proxy.MYSQL_TYPE_STRING },            { name = "type",              type = proxy.MYSQL_TYPE_STRING },            { name = "uuid",              type = proxy.MYSQL_TYPE_STRING },            { name = "connected_clients",              type = proxy.MYSQL_TYPE_LONG },        }        for i = 1, #proxy.global.backends do            local states = {                "unknown",                "up",                "down"            }            local types = {                "unknown",                "rw",                "ro"            }            local b = proxy.global.backends[i]            rows[#rows + 1] = {                i,                b.dst.name,          -- configured backend address                states[b.state + 1], -- the C-id is pushed down starting at 0                types[b.type + 1],   -- the C-id is pushed down starting at 0                b.uuid,              -- the MySQL Server‘s UUID if it is managed                b.connected_clients  -- currently connected clients            }        end    elseif query:lower() == "select * from help" then        fields = {            { name = "command",              type = proxy.MYSQL_TYPE_STRING },            { name = "description",              type = proxy.MYSQL_TYPE_STRING },        }        rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }        rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }    else        set_error("use ‘SELECT * FROM help‘ to see the supported commands")        return proxy.PROXY_SEND_RESULT    end    proxy.response = {        type = proxy.MYSQLD_PACKET_OK,        resultset = {            fields = fields,            rows = rows        }    }    return proxy.PROXY_SEND_RESULTend

   

  3.重新啟動mysql-proxy

  注，這次啟動要添加以下啟動選項 ，因為我們添加了額外的外掛程式，把新加功能添加進來,選項如下。

  --plugins=admin 在mysql-proxy啟動時載入的外掛程式；

  --admin-username="admin" 運行mysql-proxy進程管理的使用者；

  --admin-password="admin" 密碼

  --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" 外掛程式使用的設定檔路徑；

   

  先把原來的串連kill掉，再程式啟動

  [[email protected] mysql-proxy]# killall mysql-proxy[[email protected] mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.16.8.8:3306" --proxy-read-only-backend-addresses="172.16.8.9:3306" --proxy-lua-script=" /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

   

  4.在slave測試一下，記得使用管理連接埠4041 登陸，密碼是admin。

  [[email protected] ~]# mysql -uadmin -p -h172.16.8.5 --port=4041  //密碼為adminMySQL [(none)]> select * from backends; //查看讀寫分離狀態

  注，大家可以看到狀態資訊是unknown，因為我們還沒有執行讀寫操作，所有狀態還有UP起來，下面我們來測試一下讀寫資訊，再來查看狀態。

   

  5.執行一下讀寫操作

   

  [[email protected] mysql]# mysql -uroot -p123 -h172.16.8.5 --port=4040 -e "create database wowo"[[email protected] mysql]# mysql -uroot -p123 -h172.16.8.5 --port=4040 -e "select user from mysql.user"[[email protected] mysql]# mysql -uroot -p123 -h172.16.8.5 --port=4040 -e "select user from mysql.user"

   

  6.再次查看一下讀寫分離狀態

  [[email protected] ~]# mysql -uadmin -padmin -h172.16.8.5 --port=4041MySQL [(none)]> select * from backends;

  注，大家可以看到，這時狀態全部是up。說明讀寫分離配置成功。 在測試讀寫分離的時候，我們可以分布測試，比如先測試讀的語句，查看讀寫狀態是否UP ，然後在測試寫的語句。反反覆複多測試幾次才能看出效果 。

   

  五、最佳化啟動指令碼，並修改串連連接埠

  每次啟動的時候，啟動選項非常多，一不小心都會寫錯，所以我們建立一個指令碼或者設定檔把選項都寫進去，每次啟動的時候都調用此指令碼。

  (1).增加設定檔

   

  [[email protected] mysql-proxy]# vim /etc/sysconfig/mysql-proxy//添加如下內容ADMIN_USER="admin"ADMIN_PASSWORD="admin"ADMIN_ADDRESS=""ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"PROXY_ADDRESS="0.0.0.0:3306"PROXY_USER="mysql-proxy"PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log" --plugins=proxy --plugins=admin --proxy-backend-addresses=172.16.8.8:3306 --proxy-read-only-backend-addresses=172.16.8.9:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

   

  (2).增加sysV指令碼

  [[email protected] mysql-proxy]# vim /etc/init.d/mysql-proxy//添加如下內容：#!/bin/bash## mysql-proxy This script starts and stops the mysql-proxy daemon## chkconfig: - 78 30# processname: mysql-proxy# description: mysql-proxy is a proxy daemon for mysql# Source function library.. /etc/rc.d/init.d/functionsprog="/usr/local/mysql-proxy/bin/mysql-proxy"# Source networking configuration.if [ -f /etc/sysconfig/network ]; then    . /etc/sysconfig/networkfi# Check that networking is up.[ ${NETWORKING} = "no" ] && exit 0# Set default mysql-proxy configuration.ADMIN_USER="admin"ADMIN_PASSWD="admin"ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"PROXY_OPTIONS="--daemon"PROXY_PID=/var/run/mysql-proxy.pidPROXY_USER="mysql-proxy"# Source mysql-proxy configuration.if [ -f /etc/sysconfig/mysql-proxy ]; then    . /etc/sysconfig/mysql-proxyfiRETVAL=0start() {    echo -n $"Starting $prog: "    daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD"    RETVAL=$?    echo    if [ $RETVAL -eq 0 ]; then        touch /var/lock/subsys/mysql-proxy    fi}stop() {    echo -n $"Stopping $prog: "    killproc -p $PROXY_PID -d 3 $prog    RETVAL=$?    echo    if [ $RETVAL -eq 0 ]; then        rm -f /var/lock/subsys/mysql-proxy        rm -f $PROXY_PID    fi}# See how we were called.case "$1" in    start)        start        ;;    stop)        stop        ;;    restart)        stop        start        ;;    condrestart|try-restart)        if status -p $PROXY_PIDFILE $prog >&/dev/null; then            stop            start        fi        ;;    status)        status -p $PROXY_PID $prog        ;;    *)        echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"        RETVAL=1        ;;esacexit $RETVAL

   

  給mysql-proxy執行許可權並且添加進服務

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

   

  (3).啟動測試指令碼

  [[email protected] mysql-proxy]# killall mysql-proxy //先關閉mysql-proxy[[email protected] mysql-proxy]# service mysql-proxy start //啟動mysql-proxy

   

  (5).測試連接一下

   

  大家可以看到，現在串連不用指定連接埠，預設使用3306連接埠。好了，到這裡mysql-proxy實現讀寫分離配置全部完成!

 

http://www.it165.net/database/html/201404/6141.html

基於Mysql-Proxy 實現MariaDB 讀寫分離