- LAMP+Redis+Discuz缓解了MySQL部分压力,但是如果访问量非常大,Redis缓存中第一次没有缓存数据,会导致MySQL数据库压力增大,此时可以基于分库、分表、分布式集群或者读写分离来分担MySQL数据库的压力,以读写分离为案例,来实现分担MySQL数据库的压力
- MySQL读写分离的原理,让master数据库处理事务增加、删除、修改、更新操作(create、update、insert、delete),而让slave数据库处理select操作,MySQL读写分离前提是基于MySQL主从复制,这样可以保证在master上修改数据,slave同步之后,Web应用可以读取到slave端的数据
- 实现MySQl读写分离可以基于第三方插件,也可以通过开发修改代码实现,具体有4种
- MySQL proxy读写分离
- Amoeba读写分离
- Mycat读写分离
- 基于程序读写分离(效率高,实施难度大,需开发代码)
- MySQL proxy是MySQL官方提供的MySQL中间件服务,支持无数客户端连接,同时后端可连接若干台MySQL server服务器,MySQL proxy自身基于MySQL协议,连接MySQL proxy的客户端无须修改任何设置,跟正常连接MySQL server没有区别,无需修改程序代码
- MySQL proxy是App应用(客户端)与MySQL server之间的一个连接代理,MySQL proxy负责将App应用的SQL请求根据转发规则,转发至相应的后端数据库,基于LUA脚本,可以实现复杂的连接控制和过滤,从而实现读写分离和负载均衡需求
- MySQL proxy允许用户指定LUA脚本对SQL请求进行拦截,对请求进行分析与修改,还允许用户指定LUA脚本对服务器的返回结果进行修改,加入一些结果集或者取出一些结果集,对SQL的请求通常为读请求、写请求,基于LUA脚本,可以实现SQL读请求转发至后端slave服务器,将SQL写请求转发至后端master服务器
image.png
- 两台Web通过MySQL-proxy连接后端10.0.0.30和10.0.0.203服务器
- 服务器配置为
- 10.0.0.205 MySQL proxy
- 10.0.0.30 master
- 10.0.0.203 slave
image.png
-
构建MySQL读写分离架构首先需要将两台MySQL服务器配置为主从复制,配置完毕后,在10.0.0.205服务器上安装MySQL proxy服务,配置步骤为
- 下载MySQL proxy软件,解压并重命名至/usr/local/mysql-proxy,命令如下
https://downloads.mysql.com/archives/proxy/ 官网下载MySQL proxy rz 接收MySQL proxy压缩包 useradd mysql-proxy tar xvf mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/ # -C指定新的目录 cd /usr/local mv mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit mysql-proxy- 在环境变量配置文件/etc/profile中加入如下代码
export PATH=$PATH:/usr/local/mysql-proxy/bin- 1启动mysql-proxy中间件
/mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="10.0.0.30:3306" --proxy-read-only-backend-addresses="10.0.0.203:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"3.2 或者使用自定义的配置文件启动
mkdir -p /usr/local/log mkdir -p /usr/local/conf vim /usr/local/conf/mysql-proxy.conf # 加入以下配置内容 [mysql-proxy] user=mysql-proxy plugins=proxy admin-username=admin # 连接主从mysql共有的用户 admin-password=admin # 连接用户的密码 proxy-address=10.0.0.205:4040 # mysql-proxy监听本地工作ip和端口，不加端口默认是4040， proxy-backend-addresses=10.0.0.30:3306 # 指定后端主库 proxy-read-only-backend-addresses=10.0.0.203:3306 # 指定后端只读从库 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua # 指定实现读写分离的lua脚本 admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua # 指定管理脚本 pid-file=/var/run/mysql-proxy.pid log-file=/usr/local/mysql-proxy/log/mysql-proxy.log # 日志位置 log-level=debug # 定义log日志级别，由高到低分别有(error|warning|info|message|debug) daemon=true # 以守护进程方式运行 keepalive=true # work进程崩溃时，尝试重启 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf #启动mysql-proxy # 测试mysql-proxy服务是否启动 [root@localhost ~]# netstat -tunlp|grep mysql tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 7862/mysql-proxy tcp 0 0 10.0.0.205:3306 0.0.0.0:* LISTEN 7862/mysql-proxy
image.png
-
MySQL-proxy相关参数
- --help-all ———— 用于获取全部帮助信息
- --proxy-address=host:port ———— 代理服务监听的地址和端口 ,默认为4040
- --admin-address=host:port ———— 管理模块监听的地址和端口 ,默认为4041
- --proxy-backend-addresses=host:port ———— 后端mysql服务器的地址和端口（主服务器）
- --proxy-read-only-backend-addresses=host:port ———— 后端只读mysql服务器的地址和端口（从服务器）
- --proxy-lua-script=file_name ———— 完成mysql代理功能的Lua脚本
- --daemon ———— 以守护进程模式启动mysql-proxy
- --keepalive ———— 在MySQL proxy崩溃时重启
- --log-file=/path/to/log_file_name ———— 日志文件名称
- --log-level=level ———— 日志级别
- --log-use-syslog ———— 基于syslog记录日志
- --plugins=plugin ———— 在MySQL proxy启动时加载的插件
- --user=user_name ———— 运行MySQL proxy进程的用户
- --defaults-file=/path/to/conf_file_name ———— 默认使用的配置文件路径
- --proxy-skip-profiling ———— 禁用profile
- --pid-file=/path/to/pid_file_name ———— 进程文件名
MySQL proxy启动后,在服务端查看端口,其中4040位proxy代理端口用于Web应用连接,4041位管理端口用于SA或者DBA管理
image.png
- 基于4041端口MySQL proxy查看读写分离状态,登录4041管理端口命令如下
mysql -uadmin -padmin -P4041 -h10.0.0.205
- 以4041管理口登录,然后执行select命令,当state状态都为up状态,type类型为rw、ro则证明读写分离成功.如果状态为unknown未知状态,则以4040端口登录执行'show databases'命令,直至state状态变为up
mysql -h10.0.0.205 -uproxy -p123 -P4040 -e "show databases;"
image.png
- 读写分离数据测试,以3306端口登录到从库,进行数据写入和测试,在主库和从库上创建test测试库,并写入内容
master:
grant insert,delete,update,select on *.* to proxy@'10.0.0.205' identified by '123'; # 授权proxy用户权限,之后用proxy用户登录mysql-proxy4040端口测试读写分离
create database test;
use test
create table teacher(id int,name varchar(20)); # 创建teacher表
insert into teacher values(1,'oldwang');
show tables; # 查看teacher表是否被创建
slave:
show databases; # 查看主库的test数据库是否被同步
use test
create table stu(id int,name varchar(20)); # 创建stu表
insert into stu values(1,'maomao'); # 插入一条数据
show tables; # 查看stu表是否被创建
image.png
image.png
- 读写分离数据测试,以4040代理端口登录,执行如下命令,可以查看到数据即证明读写分离成功
mysql -h10.0.0.205 -uproxy -p123 -P4040 -e "select * from test.stu;"
# 查询到了在从库上创建的stu表内容,在主库上只创建了teacher表并没有创建stu表,所以读写分离测试成功
image.png
