mysql读写分离结构
环境： centos7.2 mysql5.7
场景说明：
数据库主节点： 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 3359 mirrors.ustc.edu.cn/epel//7/x86 _ 64/packages/e/epel-release-7-11.noarch.rpm
rpm-ivhepel-release-7-11.noarch.rpm
yum clean all
yum更新
yum install-ygcc * gcc-c * autoconf * automake * zlib * libxml * ncurses-devel * libmcrypt * libtool * flex * pkgconfig *
2 .编译并安装lua
MySQL-Proxy的读写分离主要通过rw-splitting.lua脚本实现，因此需要安装lua
可以通过以下方法获得lua
从3http://www.Lua.org/download.html下载软件包
从rpm.pbone.net中搜索相关的rpm软件包
download.fedora.red hat.com/pub/fedora/epel/5/i386/Lua-5.1.4-4.el5.i386.rpm
download.fedora.red hat.com/pub/fedora/epel/5/x86 _ 64/Lua-5.1.4-4.el5.x86 _ 65374; 644.rpm
此处建议使用源包进行安装
cd /opt/install
wget 3358 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
制造安装
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-yreadline-devel ncurses-devel
MySQL-Proxy的安装配置
下载mysql-proxy
wget 3359 downloads.MySQL.com/archives/get/file/MySQL-proxy-0.8.5-Linux-glibc 2.3-x86-64 bit.tar.1
tarzxvfmysql-proxy-0.8.5-Linux-glibc 2.3-x86-64 bit.tar.gz
mvzxvfmysql-0.8.5-Linux-glibc 2.3-x86-64位/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用户
管理员用户名称=代理用户#主从mysql通用用户
管理员-密码=123456 #用户密码
proxy-address=192.168.206.21033604040 # MySQL-proxy在没有端口的情况下运行ip和端口，默认值为4040
代理只读后端地址=192.168.206.200 #指定后端从slave读取数据
proxy-back end-addresses=192.168.206.100 #指定后端主主机写入数据
代理- l
ua-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