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
echo "export MYSQL_PROXY=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit">>/etc/profile
echo "export PATH=$MYSQL_PROXY/bin:$PATH">>/etc/profile
source /etc/profile

mkdir -p /data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/logs/
firewall-cmd --add-port=4040/tcp --permanent
firewall-cmd --reload

vi /data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/mysql-proxy-readwrite.cnf
[mysql-proxy]
user=root
admin-username=root
admin-password=xmrbi3967968@
proxy-address=10.0.39.90:4040
proxy-read-only-backend-addresses=realmysqlip
proxy-backend-addresses=realmysqlip
proxy-lua-script=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua
admin-lua-script=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/admin-sql.lua
log-file=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/logs/mysql-proxy.log
log-level=info
daemon=true
keepalive=true

保存退出

chmod 600 /data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/mysql-proxy-readwrite.cnf

修改/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua

if 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 --defaults-file=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/mysql-proxy-readwrite.cnf

编写服务:

vi /usr/lib/systemd/system/mysql-proxy-readwrite.service
[Unit]
Description=Mysql Proxy read write
After=rc-local.service

[Service]
Type=forking
ExecStart=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/bin/mysql-proxy --defaults-file=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/mysql-proxy-readwrite.cnf
ExecReload=/usr/bin/pkill mysql-proxy && /data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/bin/mysql-proxy --defaults-file=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/mysql-proxy-readwrite.cnf
ExecStop=/usr/bin/pkill mysql-proxy
Restart=on-failure
RestartSec=42s
PrivateTmp=true

[Install]
WantedBy=multi-user.target

保存退出

chmod 754 /usr/lib/systemd/system/mysql-proxy-readwrite.service

获取进程号：ps -ef|grep mysql-proxy|grep -v 'grep'|awk '{print $2}'

关掉进程：kill -9 ps -ef|grep mysql-proxy|grep -v 'grep'|awk '{print $2}'

编写负载均衡脚本

!/bin/bash

/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/bin/mysql-proxy
--proxy-address=ipaddress:4040 \ #这个是安装mysql-proxy的主机上的ip，这个4040端口是mysql-proxy的默认端口
--proxy-backend-addresses=192.168.2.45:3125 \ #这个是mysql服务器安装主机的ip和mysql的端口
--proxy-backend-addresses=192.168.2.45:3126
--log-level=info
--log-file=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/logs/mysql-proxy.log \ #存放日志文件的位置
--daemon

mkdir /data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/logs/

touch /data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/logs/mysql-proxy.log

连接

mysql -uroot -p123456 -h1.0.0.3 -P4040

-L 1 一次提取一行

xargs -L 1 kill -HUP

vi /data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/mysql-proxy-readwrite.sh

!bash/bin

/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/bin/mysql-proxy
--proxy-address=10.0.39.90:4040
--proxy-backend-addresses=realmysqlip:3306
--proxy-read-only-backend-addresses=realmysqlip:3306
--proxy-lua-script=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua
--log-level=info
--log-file=/data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/logs/mysql-proxy-readwrite.log
--daemon

保存

chmod +x /data/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/mysql-proxy-readwrite.sh