mysql 读写分离

1、操作环境

windows  ip : 192.168.1.175 (master 可以是Linux 系统)
CentOS   ip : 192.168.1.144 (slaver)
CentOS   ip : 192.168.1.143 (mysql-proxy 调度器)

2、调度服务器安装 mysql

安装操作和之前Linux 环境的安装一样

mysql-proxy 依赖包较多，先使用 yum 进行安装

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

编译安装 lua

这里采用源码包进行安装,需要自行下载lua的源码包,实验的时候，放在/usr/local/src 目录，，下载地址：http://www.lua.org/ftp/ 选择一个版本进行下载。

cd /usr/local/src
tar zvfx lua-5.1.4.tar.gz
cd lua-5.1.4
vim  src/Makefile

⚠️ 在 CFLAGS= -O2 -Wall $(MYCFLAGS) 这一行记录里加上-fPIC，更改为 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 来避免编译过程中出现错误。

⚠️ make linux 出现系列错误提示解决办法
在包含自 lua.h：16 的文件中，
从 lua.c：15:
luaconf.h:275:31: 错误：readline/readline.h：没有那个文件或目录
luaconf.h:276:30: 错误：readline/history.h：没有那个文件或目录
lua.c: In function ‘pushline’:
lua.c:182: 警告：隐式声明函数 ‘readline’
lua.c:182: 警告：赋值时将整数赋给指针，未作类型转换
lua.c: In function ‘loadline’:
lua.c:210: 警告：隐式声明函数 ‘add_history’
make[2]: *** [lua.o] 错误 1
make[2]: Leaving directory `/data0/software/lua-5.1.4/src’
make[1]: *** [linux] 错误 2
make[1]: Leaving directory `/data0/software/lua-5.1.4/src’
make: *** [linux] 错误 2

解决方法

yum install libtermcap-devel ncurses-devel libevent-devel readline-devel

重新
make linux 
make install

配置环境变量

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

下载安装mysql-proxy

下载：http://dev.mysql.com/downloads/mysql-proxy/ 一定要下载对应的版本，由于实验用的系统是centos6.6 32位）（根据自己的需求进行下载）：

tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz

mv mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit  /usr/local/mysql-proxy

配置mysql-proxy，创建主配置文件

cd /usr/local/mysql-proxymkdir lua #创建脚本存放目录mkdir logs #创建日志目录
cp share/doc/mysql-proxy/rw-splitting.lua  ./lua #复制读写分离配置文件
vim /etc/mysql-proxy.cnf   #创建配置文件

[mysql-proxy]
user=root #运行mysql-proxy用户
admin-username=proxyuser #主从mysql共有的用户
admin-password=123456 #用户的密码
proxy-address=192.168.1.144:4040 #mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.1.143 #指定后端从slave读取数据
proxy-backend-addresses=192.168.1.175 #指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua #指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定义log日志级别
daemon=true    #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启

配置admin.lua 文件

⚠️在/etc/mysql-proxy.cnf 配置文件中，还差/usr/local/mysql-proxy/lua/admin.lua  的管理文件，实际现在还没有创建的。所以，现在需要编辑创建admin.lua文件。

mysql-proxy-0.8.5的这个版本，使用下面的admin.lua脚本，对这个版本才是有效的：

vim  /usr/local/mysql-proxy/lua/admin.lua

function set_error(errmsg)
        proxy.response = {
                type = proxy.MYSQLD_PACKET_ERR,
                errmsg = errmsg or "error"
        }
end
function 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_RESULT
end

修改读写分离配置文件

vim /usr/local/mysql-proxy/lua/rw-splitting.luaif not proxy.global.config.rwsplit then
 proxy.global.config.rwsplit = {
  min_idle_connections = 1, #默认超过4个连接数时，才开始读写分离，改为1
  max_idle_connections = 1, #默认8，改为1
  is_debug = false
 }
end

启用mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

netstat -tupln | grep 4000 #已经启动killall -9 mysql-proxy #关闭mysql-proxy使用

测试读写分离参考以下资料

读写分离测试参考
http://hellocjq.blog.51cto.com/11336969/1892094