(10) 如何MySQL读压力大的问题
如何进行读写分离
- 由开发人员根据所执行的sql类型连接不同的服务器
- 由数据库中间层实现读写分离
读写分离时,需要注意,对于实时性要求比较高的数据,不适合在从库上查询(因为主从复制存在一定延迟(毫秒级)),比如库存就应该在主库上查询,如果放在从库上查询,可能会存在超卖的情况
由开发人员根据所执行的sql类型进行读写分离的方式
优点:
1. 完全由开发人员控制,实现更加灵活
2. 由程序直接连接数据库,所以性能损耗比较少
缺点:
1. 增加了开发的工作量,使程序代码更加复杂
2. 人为控制,容易出现错误
- 可采用dns轮询的方式
dns轮询:在同一个域名服务器上为同一个域名配置多个不同ip地址的a记录
应用端使用域名来连接数据库服务器,这样在进行域名解析时,域名服务器会循环的将不同的ip返回给应用端,应用端就可以按地址连接不同的只读服务器来进行读取操作
这种操作比较简单,只需要修改域名服务器的配置即可,但是如果某一后端服务器出现故障,则必须通过修改dns的方式把故障服务器剔除到只读服务器列表之外,性能较差,负载也不均衡,大多数情况下不推荐此方式
- 使用lvs/haproxy 等代理层软件
由于是通用的代理层软件,所以不能自动对sql语句进行分析,实现读写分离,但是可以完成只读服务器的负载均衡操作
lvs 四层代理,haproxy 七层代理,所以从性能来看lvs高于haproxy
- f5硬件:成本较高
keepalived+lvs的架构方式
此处使用keepalived+lvs的架构方式,演示如下
优点:
抗负载能力较强,属于四层代理,只进行流量分发,不会对数据内容进行解析,对内存和cpu的消耗也比较低,处理效率更高
工作稳定,自身有完整的双机热备方案,可进行高可用配置
无流量,只分发请求,流量不从它本身出去,不会对主机的网络io造成影响
服务器信息
# 主db ip:192.168.3.100 # 主备db ip:192.168.3.101 # slavedb ip:192.168.3.102 # keepalived vip:192.168.3.99 # lvs manage : 192.168.3.100/101 # lvs vip :192.168.3.98
1. 安装lvs管理工具
在192.168.3.100 和192.168.3.101上安装lvs管理工具
[root@node1 keepalived]# yum install -y ipvsadmin.x86_64
2. 加载ipvs模块
在 192.168.3.100 和192.168.3.101以及192.168.3.102 执行以下命令,加载ipvs模块
[root@node1 keepalived]# modprobe ip_vs
3. 在slave服务器上编写并运行要使用lvs脚本
在 192.168.3.101和 192.168.3.102上编写脚本
/etc/init.d/lvsrs 脚本文件内容如下
#!/bin/bash vip=192.168.3.98 . /etc/rc.d/init.d/functions case "$1" in start) /sbin/ifconfig lo down /sbin/ifconfig lo up echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce /sbin/sysctl -p >/dev/null 2>&1 /sbin/ifconfig lo:0 $vip netmask 255.255.255.255 up /sbin/route add -host $vip dev lo:0 echo "lvs-dr real server starts successfully.\n" ;; stop) /sbin/ifconfig lo:0 down /sbin/route del $vip >/dev/null 2>&1 echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce echo "lvs-dr real server stopped." ;; status) isloon=`/sbin/ifconfig lo:0 | grep "$vip"` isroon=`/bin/netstat -rn | grep "$vip"` if [ "$isloon" == "" -a "$isroon" == "" ]; then echo "lvs-dr real server has to run yet." else echo "lvs-dr real server is running." fi exit 3 ;; *) echo "usage: $0 {start|stop|status}" exit 1 esac exit 0
/etc/init.d/lvsrs 需要具有可执行权限
运行脚本
[root@node1 keepalived]# /etc/init.d/lvsrs start
运行成功后 通过ip addr 命令 可以看到lo中除了127.0.0.1外还有192.168.3.98
4. 修改主服务器上的keepalived.conf文件,通过keepalived,保证lvs的高可用
! configuration file for keepalived global_defs { router_id mysql_ha } vrrp_script check_run { script "/etc/keepalived/check_mysql.sh" interval 2 } vrrp_instance vi_1 { state backup interface eth0 virtual_router_id 200 priority 99 advert_int 1 nopreempt authentication { auth_type pass auth_pass 1200 } track_script { check_run } virtual_ipaddress { 192.168.3.99/24 } } vrrp_instance vi_2 { state backup interface eth0 virtual_router_id 201 priority 99 advert_int 1 nopreempt authentication { auth_type pass auth_pass 1200 } virtual_ipaddress { 192.168.3.98/24 } } virtual_server 192.168.3.99/24 3306 { delay_loop 5 lb_algo rr lb_kind dr persistence_timeout 120 protocol tcp sorry_server 192.168.3.99 3306 real_server 192.168.3.101 3306 { weight 1 misc_check { misc_path "/etc/keepalived/check_slave.sh -udba_monitor -p123456 -h10.103.9.204 -p3306" misc_dynamic } } real_server 192.168.3.102 3306 { weight 1 misc_check { misc_path "/etc/keepalived/check_slave.sh -udba_monitor -p123456 -h10.103.9.205 -p3306" misc_dynamic } }
delay_loop :健康检查时间,单位秒
lb_algo :lvs负载均衡调度算法,rr:轮询算法
lb_kind :lvs实现负载均衡的机制,有nat,tun,dr三种模式
persistence_timeout:会话保存时间,单位秒,如果要做session保持,可以将值设大点,可以保证同一个连接在指定时间内都会读取到同一台客户端服务器
sorry_server :后端所有服务器失效后,就会访问此服务器
check_slave.sh 用来监测slave服务器是否可用,当slave服务器宕机或者slave服务器延迟比较大时,脚本会把此slave服务器从lvs的读列表中去掉
脚本内容如下
#/bin/bash # check_slave.sh mysql=`which mysql` vip=192.168.3.98 vport=3306 function usage() { echo "usage:" echo "example:# mysql -umonitor -pmonitor -p3306 -h192.168.3.100" echo "-p, --password[=name]" echo "-p, --port" echo "-h, --host=name" echo "-u, --user=name" } while getopts "u:p:h:p:" option do case "$option" in u) dbuser="$optarg";; p) dbpwd="$optarg";; h) dbhost="$optarg";; p) dbport="$optarg";; \?) usage exit 1;; esac done if [ "-$dbuser" = "-" ]; then usage exit 1 fi if [ "-$dbpwd" = "-" ]; then usage exit 1 fi if [ "-$dbhost" = "-" ]; then usage exit 1 fi if [ "-$dbport" = "-" ]; then usage exit 1 fi $mysql -u$dbuser -p$dbpwd -p$dbport -h$dbhost -e "select @@version;" >/dev/null 2>&1 if [ $? = 0 ] ;then mysql_ok=1 else /sbin/ipvsadm -d -t $vip:$vport -r $dbhost:$vport exit 1 fi slave_status=$(${mysql} -u$dbuser -p$dbpwd -p$dbport -h$dbhost -e 'show slave status \g' | awk ' \ /slave_io_running/{io=$2} \ /slave_sql_running/{sql=$2} \ /seconds_behind_master/{printf "%s %s %d\n",io,sql,$2}') >/dev/null 2>&1 arr=($slave_status) io=${arr[0]} sql=${arr[1]} behind=${arr[2]} if [ "$io" == "no" ]||[ "$sql" == "no" ]; then /sbin/ipvsadm -d -t $vip:$vport -r $dbhost:$vport exit 1 elif [ $behind -gt 60 ]; then /sbin/ipvsadm -d -t $vip:$vport -r $dbhost:$vport exit 1 else /sbin/ipvsadm -a -t $vip:$vport -r $dbhost:$vport -g exit 0 fi
5. 创建lvs用于监控后端数据库所使用的数据库账号
[root@node1 keepalived]# mysql -uroot -p mysql> grant all privileges on *.* to dba_monitor@'192.168.3.%' identified by '123456';
6. 在使用lvs的manage服务器上编写并运行lvs所需要的脚本
在 192.168.3.100 上编写脚本lvsdr
/etc/init.d/lvsdr 脚本文件内容如下
#!/bin/bash vip=192.168.3.98 dev=eth0 . /etc/rc.d/init.d/functions case "$1" in start) echo "1">/proc/sys/net/ipv4/ip_forward /sbin/ipvsadm -a -t $vip:3306 -s rr -p 60 /sbin/ipvsadm -a -t $vip:3306 -r 10.103.9.204:3306 -g /sbin/ipvsadm -a -t $vip:3306 -r 10.103.9.205:3306 -g /sbin/ipvsadm --start-daemon echo "lvs-dr server starts successfully.\n" ;; stop) /sbin/route del $vip >/dev/null 2>&1 echo "0" >/proc/sys/net/ipv4/ip_forward /sbin/ipvsadm -c echo "lvs-dr real server stopped." ;; status) isloon=`/sbin/ifconfig lo:0 | grep "$vip"` isroon=`/bin/netstat -rn | grep "$vip"` if [ "$isloon" == "" -a "$isroon" == "" ]; then echo "lvs-dr real server has to run yet." else echo "lvs-dr real server is running." fi exit 3 ;; *) echo "usage: $0 {start|stop|status}" exit 1 esac exit 0
/etc/init.d/lvsdr 需要具有可执行权限
运行脚本
[root@node1 keepalived]# /etc/init.d/lvsdr start
7. 在从服务器*问虚拟ip,进行测试
[root@node3 ~]# mysql -udba_monitor -p123456 -h192.168.3.98 -e"show variables like ''server_id";
可以通过以上命令查看虚拟ip当前所在服务器的server_id
由于我们persistence_timeout设置的是120秒,所以接下来的120秒如果一直运行以上命令可以发现,一直访问的是同一个server_id
下面我们在192.168.3.102上查看一下ipvs的状态,命令如下
[root@node2 init.d]# ipvsadm -l -n
可以看到 192.168.3.98:3306 对应了两个服务器ip 192.168.3.101 和192.168.3.102
接下来我们模拟其中一个服务器宕机的情况
[root@node3 ~]# /etc/init.d/mysqld stop
然后我们再来查询ipvs状态
[root@node1 keepalived]# ipvsadm -l -n
发现 192.168.3.98:3306 现在只对应了1个服务器ip 192.168.3.101,而192.168.3.102已被剔除
二. 由数据库中间层完成读写分离
常用中间层软件有:mysqlproxy、maxscale、oneproxy 、 proxysql等
优点:
1. 由中间件根据查询语法分析,自动完成读写分离
通过判断sql语句如果是select语句则使用slave,如果是update、insert、delete、create语句则使用master服务器,无法判断的则使用master
2. 对程序透明,对于已有程序不用做任何调整
3. 前面所说到的一些中间层软件除了能做到读写分离外,还具有能对多个只读数据库进行负载均衡的功能
缺点:
1. 由于增加了中间层,所以对查询效率有损耗
2. 对于延迟敏感的业务无法自动在主库执行
使用maxscale解决读压力大的问题
maxscale介绍
支持高可用,负载均衡,良好扩展的插件式数据库中间层软件
maxscale允许用户开发和定制适合自己的插件,目前maxscale提供的插件功能主要分为5个种类
1. 认证插件
提供数据库登录认证的功能
2. 协议插件
负责 maxscale和外部系统间接口的协议,包括客户端到maxscale的接口,以及maxscale 到后端数据库的接口
3. 路由插件
readconnroute 用来解决多台读服务器的负载均衡
readwritesplit 用来实现读写分离
4. 监控插件
用于对后端数据库进行实时监控,以便将前端请求发送到正确的(即正常的可以对外提供服务的)数据库中
5. 过滤和日志插件
提供了简单的数据库防火墙功能,可以对某些sql进行过滤和改写,可以进行一些简单的sql容错和语句的自动转换
使用maxscale
安装方法自行百度
服务器信息
maxscale 节点 192.168.3.102 master db:192.168.3.100 slave db:192.168.3.101 slave db:192.168.3.102
1. 为监控模块创建mysql账号
mysql> create user scalemon@'192.168.3.%' identified by '123456'; mysql> grant replication slave,replication client on *.* to scalemon@'192.168.3.%';
2. 为路由模块创建mysql账号
用来读取mysql系统库下的表,获取后端数据库的权限
mysql> create user scaleroute@'192.168.3.%' identified by '123456'; mysql> grant select on mysql.* to scaleroute@'192.168.3.%';
3. 对数据库密码进行加密
因为maxscale的配置文件是一个文本格式的明文文件,在文件中直接书写mysql密码是不安全的
maxscale提供了加密mysql密码的命令,这个命令是在maxscale节点中运行
[root@node3 tools]# maxpassword /var/lib/maxscale/ 123456 e3aee4b7125b9c76bf742ae6246ecc5c
生成了密码123456对应的加密字符串
4. 对maxscale进行配置
[root@node3 tools]# vim /etc/maxscale.cnf
参数说明
[maxscale] thread=1 # 不要超过cpu的数量 [server1] type=server address=192.168.3.100 port=3306 protocol=mysqlbackend [server2] type=server address=192.168.3.101 port=3306 protocol=mysqlbackend [server3] type=server address=192.168.3.102 port=3306 protocol=mysqlbackend [mysql monitor] type=monitor module =mysqlmon servers=server1,server2,server3 user=scalemon passwd=e3aee4b7125b9c76bf742ae6246ecc5c # 使用刚刚的加密字符串 monitor_interval=1000 # 毫秒 [read-write service] type=service router=readwritesplit servers=server1,server2,server3 user=scalerouter passwd=e3aee4b7125b9c76bf742ae6246ecc5c # 使用刚刚的加密字符串 max_slave_connections=100% max_slave_replication_lag=60 [maxadmin service] type=service router=cli [read-write listener] type=listener service=read-write service protocol=mysqlclient port=4006 [maxadmin listener] type=listener service=maxadmin service protocol=maxscaled port=6603
5. 启动maxscale服务
[root@node3 tools]# maxscale -f /etc/maxscale.cnf
6. 查看maxscale服务状态
maxscale是使用maxadmin进行管理的,默认账号是admin,密码是mariadb
[root@node3 tools]# maxadmin --user=admin --password=mariadb
# 查看后端服务器列表 maxscale> list servers # 查看是否读取到了后端数据库服务器的账号 maxscale> show dbusers "read-write service"
引入maxscale后的架构
将双主架构改为了单主架构,因为maxscale会自动识别后端服务器的角色,如果使用双主架构,则无法分清当前的主是哪一个
上一篇: 详解webpack的proxyTable无效的解决方案
下一篇: 玩游戏 学Flex布局
推荐阅读
-
详解MySQL是如何解决幻读的
-
如何升级到Windows10系统?升级win10系统遇到的问题解决汇总(含视频体验)
-
如何解决win10与Ubuntu16.04时间不同步的问题的方法
-
小米10 Pro是如何解决自动调度调节问题的?请看科普
-
win10下MySQL 8.0登录Access denied for user‘root’@‘localhost’ (using password: YES)问题的解决方法
-
Win10环境下安装Mysql5.7.23问题及遇到的坑
-
使用wordpress的$wpdb类读mysql数据库做ajax时出现的问题该如何解决
-
MySQL 5.7中如何定位DDL被阻塞的问题
-
解决Win10系统安装MySQL8.0遇到的问题
-
Win10安装mysql8.0.15 winx64及连接服务器过程中遇到的问题