mysql數據分離（mysql-proxy配置） 1、安裝MySQL 2、安裝Lua # wget http://www.lua.org/ftp/lua-5.1.4.tar.gz # tar xzf lua-5.1.4.tar.gz # cd lua-5.1.4 # make # make linux INSTALL_TOP= /usr/local/lua # make install 3、安裝glib # wget http://ftp.gnome.org/pub/gnome/sources/glib/2.28/glib-2.28.4.tar.bz2 # tar jxf glib-2.28.4.tar.bz2 # cd glib-2.28.4 # ./configure --prefix=/opt/module/glib2 # make && make install # echo "/opt/module/glib2/lib" >> /etc/ld.so.conf # ldconfig 4、安裝pkg-config # wget http://pkg-config.freedesktop.org/releases/pkgconfig-0.18.tar.gz # tar xzf pkgconfig-0.18.tar.gz # cd pkgconfig-0.18 # ./configure && make && make install 5、安裝libevent # wget http://monkey.org/%7Eprovos/libevent-2.0.10-stable.tar.gz # tar zxf libevent-2.0.10-stable.tar.gz # cd libevent-2.0.10-stable # ./configure --prefix=/opt/module/libevent # make; make install 6、安裝mysql-proxy # wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz/from/http://mysql.he.net/ # tar xzf mysql-proxy-0.8.1.tar.gz # cd mysql-proxy-0.8.1 # export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm" export PKG_CONFIG_PATH="/usr/lib/pkgconfig/" export GLIB_CFLAGS="-I/opt/module/glib2/include/glib-2.0 -I/opt/module/glib2/lib/glib-2.0/include" GLIB_LIBS="-L/opt/module/glib2/lib -lglib-2.0" export GMODULE_CFLAGS="-I/opt/module/glib2/include" GMODULE_LIBS="-L/opt/module/glib2/lib" export GTHREAD_CFLAGS="-I/opt/module/glib2/include" export GTHREAD_LIBS="-L/opt/module/glib2/lib" export CPPFLAGS="$CPPFLAGS -I/opt/module/libevent/include" export CFLAGS="$CFLAGS -I/opt/module/libevent/include" export LDFLAGS="$LDFLAGS -L/opt/module/libevent/lib -lm" export LIBS="/opt/module/glib2/lib/libgthread-2.0.so /opt/module/glib2/lib/libgmodule-2.0.so" # ./configure --with-mysql="/opt/module/mysql" --prefix="/opt/module/mysql-proxy" 查看是否安裝成功： # /opt/module/mysql-proxy/bin/mysql-proxy --help-all | less 配置： # vi /etc/mysql-proxy.cnf =================== [mysql-proxy] admin-username = mysqlproxyadm 主從數據庫都需要的數據庫用戶 admin-password = 123456 daemon = true keepalive = true log-file = /opt/module/mysql-proxy/log log-level = debug proxy-backend-addresses = 192.168.1.223:3306 主 proxy-read-only-backend-address = 192.168.1.222:3306 從 proxy-read-only-backend-address = 192.168.1.224:3306 從 proxy-lua-script = /opt/module/mysql-proxy/rw-splitting.lua admin-lua-script = /opt/module/mysql-proxy/lib/mysql-proxy/lua/admin.lua =================== # chmod 0660 /etc/mysql-proxy.cnf 制作啟動腳本： # vi /etc/init.d/mysql-proxy =================== #!/bin/sh export LUA_PATH=/opt/module/mysql-proxy/?.lua mode=$1; if [ -z $mode ] ; then mode="start" fi case $mode in start) /opt/module/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf>/opt/module/mysql-proxy/log & ;; stop) killall -9 mysql-proxy ;; *) echo "Usage: $0 (start|stop)" exit 1 ;; esac exit 0; ===================== # chmod +x /etc/init.d/mysql-proxy 編寫mysql-proxy實現讀寫分離的腳本 # vi /opt/module/mysql-proxy/rw-splitting.lua ============ 發送所有的非事務性select到一個從數據庫 if is_in_transaction==0 and packet:byte() == proxy.COM_QUERY and packet:sub(2, 7)=="SELECT" then local max_conns=-1 local max_conns_ndx=0 for i=1, #proxy.servers do local s=proxy.servers[i] 需要選擇一個擁有空閒連接的從數據庫 if s.type==proxy.BACKEND_TYPE_RO and s.idling_connections>0 then if max_conns==-1 or s.connected_clients<MAX_CONNS max_conns_ndx="i" if 至此，找到了一個擁有空閒連接的從數據庫 end max_conns="s.connected_clients" then>0 then proxy.connection.backend_ndx=max_conns_ndx end else 發送到主數據庫 end return proxy.PROXY_SEND_QUERY ============ 啟動mysql-proxy # /etc/init.d/mysql-proxy start 注意：copy虛擬機的虛擬硬盤例如219.vdi，重命名為218.vdi，此時用virtualbox來創建一個虛擬主機，直接連接這個硬盤使用，會提示uuid重復的提示，這是用virtualbox的內部命令可以解決： C:\Documents and Settings\Administrator>"c:\Program Files\Oracle\VirtualBox"\VBoxManage internalcommands setvdiuuid E:\HardDisks\217.vdi
