欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql读写分离配置(整理)

程序员文章站 2023-12-21 17:47:16
mysql读写分离配置 环境:centos7.2 mysql5.7 场景描述: 数据库Master主服务器:192.168.206.100 数据库Slave从服务器:192.168.206.200 MySQL Proxy调度服务器:192.168.206.210 以下操作,均是在192.168.20 ......

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

下载:

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

上一篇:

下一篇: