mysql读写分离

新增一台服务器

1、安装jdk
https://www.oracle.com/cn/java/technologies/downloads/
rpm -ivh jdk-19_linux-x64_bin.rpm
2、安装mysql-proxy
https://downloads.mysql.com/archives/proxy/
mkdir /usr/local/mysql
tar -zxf mysql-proxy.xxx.tar.gz -O /usr/local/mysql
cat >/etc/mysql-proxy.cnf <
[mysql-proxy]
user=root #运行mysql-proxy用户
admin-username=proxy #主从mysql共有的用户
admin-password=123.com #用户的密码
proxy-address=192.168.0.204:4000 #mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.0.203 #指定后端从slave读取数据
proxy-backend-addresses=192.168.0.202 #指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true    #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启
EOF
	chmod 660 /etc/mysql-porxy.cnf
vi /usr/local/mysql-proxy/lua/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=/etc/mysql-proxy.cnf
3、创建用户
create user 'proxy'@'mysql-proxyIP' IDENTIFIED BY 'proxy';
4、授权用户
grant all on *.* to 'proxy'@'mysql-proxyIP';
alter USER 'proxy'@'mysql-proxyIP' IDENTIFIED WITH mysql_native_password BY 'proxy';
测试：mysql -h {mysql-proxyIP} -u proxy -P {mysql-proxyPORT} -p