mysql数据分离（mysql-proxy配置）

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