MySQL读写分离配置
环境：CentOS7.2 MySQL5.7
场景描述： 数据库Master主服务器：192.168.206.100 数据库Slave从服务器：192.168.206.200 MySQL-Proxy调度服务器：192.168.206.210
以下操作，均是在192.168.206.210即MySQL-Proxy调度服务器 上进行的。
1.检查系统所需软件包
安装之前需要配置EPEL YUM源 wget https://mirrors.ustc.edu.cn/epel//7/x86_64/Packages/e/epel-release-7-11.noarch.rpm rpm -ivh epel-release-7-11.noarch.rpm yum clean all yum update
yum install -y gcc* gcc-c * autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib*
2.编译安装lua
MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua
lua可通过以下方式获得 从http://www.lua.org/download.html下载源码包
从rpm.pbone.net搜索相关的rpm包 download.Fedora.RedHat.com/pub/fedora/epel/5/i386/lua-5.1.4-4.el5.i386.rpm download.fedora.redhat.com/pub/fedora/epel/5/x86_64/lua-5.1.4-4.el5.x86_64.rpm
这里我们建议采用源码包进行安装 cd /opt/install wget http://www.lua.org/ftp/lua-5.1.4.tar.gz tar zvfx lua-5.1.4.tar.gz cd lua-5.1.4 make linux make install mkdir /usr/lib/pkgconfig/ cp /opt/install/lua-5.1.4/etc/lua.pc /usr/lib/pkgconfig/ export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig
注意的问题
编译的时候，遇到的问题是，缺少依赖包** readline**, 然后readline又依赖ncurses,所以要先安装着两个软件 yum install -y readline-devel ncurses-devel
3.安装配置MySQL-Proxy
下载mysql-proxy
下载：http://dev.mysql.com/downloads/mysql-proxy/ wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz mv zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy
** 配置mysql-proxy，创建主配置文件** cd /usr/local/mysql-proxy mkdir lua #创建脚本存放目录 mkdir logs #创建日志目录 cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件 vi /etc/mysql-proxy.cnf #创建配置文件 [mysql-proxy] user=root #运行mysql-proxy用户 admin-username=proxyuser #主从mysql共有的用户 admin-password=123456 #用户的密码 proxy-address=192.168.206.210:4040 #mysql-proxy运行ip和端口，不加端口，默认4040 proxy-read-only-backend-addresses=192.168.206.200 #指定后端从slave读取数据 proxy-backend-addresses=192.168.206.100 #指定后端主master写入数据 proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置 admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua #指定管理脚本 log-file=/var/log/mysql-proxy.log #日志位置 log-level=info #定义log日志级别 daemon=true#以守护进程方式运行 keepalive=true #mysql-proxy崩溃时，尝试重启
这里就有一个坑了
配置文件里面的注释要全部删掉，不然可能会引起一些字符不能识别的错误。 这还不是最坑的，最坑的是：即使删掉注释，去除多余的空白字符，仍然可能会报如下错误： 2018-09-21 06:39:40: (critical) Key file contains key “daemon” which has a value that cannot be interpreted.
或者： 2018-09-21 06:52:22: (critical) Key file contains key “keepalive” which has a value that cannot be interpreted.
出现以上问题的原因是daemon=true，keepalive=true现在不这样写了，要改为： daemon=1 keepalive=1
给配置文件执行权限 chmod 660 /etc/mysql-porxy.cnf
配置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 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
