基于MySQL-proxy中间件实现mysql读写分离

目录

• 部署MySQL-proxy

部署MySQL-proxy

1. 先安装lua

# 下载依赖
yum -y install gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*  libevent* glib* readline-devel
# 建立安装目录
mkdir -p /opt/lua && cd /opt/lua
# 下载lua
wget http://www.lua.org/ftp/lua-5.4.3.tar.gz
# 解压lua压缩包
tar -zxvf lua-5.4.3.tar.gz -C /usr/local/ && cd /usr/local
# 编译安装
mv lua-5.4.3/ lua && cd lua && make linux && make install
# 设置环境变量
export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm"

2. 下载mysql-proxy的安装包

# 解压安装包到/usr/local
tar -zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local && mv /usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/ /usr/local/mysql-proxy && cd /usr/local/mysql-proxy  && mkdir ./conf ./log
# 设置环境变量
export PATH=$PATH:/usr/local/mysql-proxy/bin/
# 设置lua脚本
cp /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua /usr/local/mysql-proxy/ && vi /usr/local/mysql-proxy/rw-splitting.lua
#
#min_idle_connections = 4,  
#max_idle_connections = 8,  
#这里的4、8是指定链接数,修改对应即可
#
# 配置mysql-proxy配置
vi /usr/local/mysql-proxy/conf/mysql-proxy.conf
# 修改配置文件权限
chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

• mysql-proxy配置文件

# --Start
#mysql-proxy.conf
[mysql-proxy]
# 监听本机中所有IP的端口。
proxy-address=0.0.0.0:3306
# 进行写的数据库
proxy-backend-addresses=10.1.1.101:3306
# 只进行读的MySQLServer
proxy-read-only-backend-addresses=10.1.1.102:3306
# 运行读写分离脚本
# proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
proxy-lua-script=/usr/local/mysql-proxy/rw-splitting.lua
# pid文件
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
# 生成日志文件
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
# proxy插件
plugins=proxy
# 日志等级为debug
log-level=debug
# 心跳检测
keepalive=true
# 守护进程
daemon=true
# --end

3. 启动代理

mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

4. 查看日志

tailf /usr/local/mysql-proxy/log/mysql-proxy.log

--输出
2021-11-24 09:44:52: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=4998 alive
2021-11-24 09:44:52: (debug) chassis-unix-daemon.c:157: waiting for 4998
2021-11-24 09:44:52: (debug) chassis-unix-daemon.c:121: we are the child: 4998
2021-11-24 09:44:52: (critical) plugin proxy 0.8.5 started
2021-11-24 09:44:52: (debug) max open file-descriptors = 1024
2021-11-24 09:44:52: (message) proxy listening on port 0.0.0.0:3306
2021-11-24 09:44:52: (message) added read/write backend: 10.1.1.101:3306
2021-11-24 09:44:52: (message) added read-only backend: 10.1.1.102:3306