MYSQL PROXY搭建手冊

1.安裝包

gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*
libevent* glib* lua*

（若lua沒有的話也可以下載原始碼包來下載：

cd /opt/install
wget /> tar zvfx lua-5.1.4.tar.gz
cd lua-5.1.4

vi src/Makefile
在 CFLAGS= -O2 -Wall $(MYCFLAGS) 這一行記錄里加上-fPIC，更改為 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 來避免編譯過程中出現錯誤。

make linux
make install

cp etc/lua.pc /usr/lib/pkgconfig/
export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig）

 

2.安裝proxy

下載mysql-proxy：

wget

tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
mv mysql-proxy-0.8.1-linux-rhel5-x86-32bit /opt/mysql-proxy

3. 建立mysql-proxy服務管理指令碼

mkdir /opt/mysql-proxy/init.d/

vim mysql-proxy

--------------------------------------

#!/bin/sh

#

# mysql-proxy This script starts and stops the mysql-proxy daemon

#

# chkconfig: - 78 30

# processname: mysql-proxy

# description: mysql-proxy is a proxy daemon to mysql

 

# Source function library. 

. /etc/rc.d/init.d/functions

 

#PROXY_PATH=/usr/local/bin

PROXY_PATH=/opt/mysql-proxy/bin

 

prog="mysql-proxy"

 

# Source networking configuration.

. /etc/sysconfig/network

 

# Check that networking is up.

[ ${NETWORKING} = "no" ] && exit 0

 

# Set default mysql-proxy configuration.

#PROXY_OPTIONS="--daemon"

PROXY_OPTIONS="--proxy-backend-addresses=192.168.56.102:3306 --proxy-read-only-backend-addresses=192.168.56.103:3306 --proxy-lua-script=/opt/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid

 

# Source mysql-proxy configuration.

if [ -f /etc/sysconfig/mysql-proxy ]; then

        . /etc/sysconfig/mysql-proxy

fi

 

PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH

 

# By default it's all good

RETVAL=0

 

# See how we were called.

case "$1" in

  start)

        # Start daemon.

        echo -n $"Starting $prog: "

        $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql --log-level=debug --log-file=/opt/mysql-proxy/log/mysql-proxy.log

        RETVAL=$?

        echo

        if [ $RETVAL = 0 ]; then

                touch /var/lock/subsys/mysql-proxy

        fi

       ;;

  stop)

        # Stop daemons.

        echo -n $"Stopping $prog: "

        killproc $prog

        RETVAL=$?

        echo

        if [ $RETVAL = 0 ]; then

                rm -f /var/lock/subsys/mysql-proxy

                rm -f $PROXY_PID

        fi

       ;;

  restart)

        $0 stop

        sleep 3

        $0 start

       ;;

  condrestart)

       [ -e /var/lock/subsys/mysql-proxy ] && $0 restart

      ;;

  status)

        status mysql-proxy

        RETVAL=$?

       ;;

  *)

        echo "Usage: $0 {start|stop|restart|status|condrestart}"

        RETVAL=1

       ;;

esac

 

exit $RETVAL

 

---------------------------------------------------

指令碼引數詳解：
==============================================
PROXY_PATH=/opt/mysql-proxy/bin //定義mysql-proxy服務二進位制檔案路徑

PROXY_OPTIONS=" 
--proxy-read-only-backend-addresses=192.168.10.131:3306 \ //定義後端只讀從伺服器地址
--proxy-backend-addresses=192.168.10.130:3306 \ //定義後端主伺服器地址
--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua" \ //定義lua讀寫分離指令碼路徑

PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid //定義mysql-proxy PID檔案路徑

$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS \
--daemon \ //定義以守護程式模式啟動
--keepalive \ //使程式在異常關閉後能夠自動恢復
--pid-file=$PROXY_PID \ //定義mysql-proxy PID檔案路徑
--user=mysql \ //以mysql使用者身份啟動服務
--log-level=warning \ //定義log日誌級別，由高到低分別有(error|warning|info|message|debug)
--log-file=/opt/mysql-proxy/log/mysql-proxy.log //定義log日誌檔案路徑
==============================================

cp mysql-proxy /opt/mysql-proxy/init.d/
chmod +x /opt/mysql-proxy/init.d/mysql-proxy

mkdir /opt/mysql-proxy/run
mkdir /opt/mysql-proxy/log

mkdir /opt/mysql-proxy/scripts

 

4.配置並使用rw-splitting.lua讀寫分離指令碼

Vi /opt/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

--------------------------------------

-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, //預設為4
max_idle_connections = 1, //預設為8
is_debug = false
}
end
=============================

修改完成後，啟動mysql-proxy
/opt/mysql-proxy/init.d/mysql-proxy start

進入設定的log目錄檢視log

Cd /opt/mysql-proxy/log

tail -f mysql-proxy.log

2015-06-13 13:00:15: (message) Initiating shutdown, requested from signal handler

2015-06-13 13:00:15: (message) shutting down normally, exit code is: 0

2015-06-13 13:00:19: (critical) plugin proxy 0.8.5 started

2015-06-13 13:00:19: (debug) max open file-descriptors = 1024

2015-06-13 13:00:19: (message) proxy listening on port :4040

2015-06-13 13:00:19: (message) added read/write backend: 192.168.56.102:3306

2015-06-13 13:00:19: (message) added read-only backend: 192.168.56.103:3306

2015-06-13 13:00:19: (debug) now running as user: mysql (27/27)

 

5.驗證結果

建立用於讀寫分離的資料庫連線使用者

登陸主資料庫伺服器192.168.56.102：

mysql> GRANT ALL ON *.* TO 'proxy1'@'proxy伺服器' IDENTIFIED BY ‘111111';

由於我們配置了主從複製功能，因此從資料庫伺服器192.168.56.103上已經同步了此操作

為了清晰的看到讀寫分離的效果，需要暫時關閉MySQL主從複製功能

登陸從資料庫伺服器192.168.56.103

關閉Slave同步程式
mysql> stop slave;

連線MySQL-Proxy

mysql -uproxy1 -p111111 -P4040 -h192.168.56.101

mysql> use xx

mysql> show tables;

+--------------+

| Tables_in_xx |

+--------------+

| t1           |

| t2           |

+--------------+

mysql> insert into t1 values ('8');

mysql> select * from t1;

+------+

| id   |

+------+

| 1    |

| 2    |

| 3    |

| 4    |

| 8    |

+------+

5 rows in set (0.00 sec)

 

在slave伺服器裡去看：

mysql> select * from t1;

+------+

| id   |

+------+

| 1    |

| 2    |

| 3    |

| 4    |

+------+

4 rows in set (0.00 sec)

並沒有插入資料。

 

此時在slave上開啟主從同步：

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

此時mysql> select * from t1;

+------+

| id   |

+------+

| 1    |

| 2    |

| 3    |

| 4    |

| 8    |

+------+

5 rows in set (0.00 sec)

資料同步過來了。