MySQL

MySQL读写分离最佳实践

2018-01-06  本文已影响1607人  BruceLiu1
图片来自网络

文/Bruce.Liu1

文章大纲

  1. 读写分离简介
    1.1. 常见高并发场景
    1.2. 读写分离原理
    1.3. 读写分离类型
  2. Maxsacle
    2.1. Maxsacle简介
    2.2. Maxsacle安装
    2.3. Maxsacle配置
  3. Keepalived lvs
    3.1. Keepalived lvs简介
    3.2. Keepalived lvs安装
    3.3. Keepalived lvs高可用验收
    3.4. Keepalived lvs运维注意事项
    3.5. 附录

1.读写分离简介

1.1.常见高并发场景

随着一个网站的业务不断扩展，数据不断增加，数据库的压力也会越来越大，对数据库或者SQL的基本优化可能达不到最终的效果，此时可以考虑通过添加数据库节点来使其达到提升性能的目的通常有以下常见几种方案。

1.2.读写分离原理

MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先部署主从复制，只有主从复制完了，才能在此基础上进行数据的读写分离。简单来说，读写分离就是只在主服务器上写，只在从服务器上读，基本的原理是让主数据库处理事务性查询，而从数据库处理select查询。当业务量非常大时，一台服务器的性能无法满足需求，就可以通过配置主从复制实现写分离来分摊负载，避免因负载太高而造成无法及时响应请求。

1.3.读写分离类型

2.Maxsacle

2.1.Maxsacle简介

图片来自网络

maxscale是mariadb公司开发的一套数据库中间件，可以很方便的实现读写分离方案；并且提供了读写分离的负载均衡和高可用性保障。另外maxscale对于前端应用而言是透明的，我们可以很方便的将应用迁移到maxscale中实现读写分离方案，来分担主库的压力。maxscale也提供了sql语句的解析过滤功能。这里我们主要讲解maxscale的安装、配置以及注意事项。

1.带权重的读写分离(负载均衡)
2.SQL防火墙
3.多种路由策略(Connection based， Statement based，Schema based)
4.自动检测MySQL master Failover (配合MHA或者MRM)
5.检测主从延时
6.多租户sharding架构

https://mariadb.com/kb/zh-cn/3028/
https://mariadb.com/downloads/mariadb-tx/maxscale

2.2.Maxsacle安装

2.2.1.软件参考文档

参考文档：
官方文档：https://mariadb.com/kb/en/mariadb-enterprise/maxscale-20-installation-guide/

软件下载：
maxSacle软件：https://downloads.mariadb.com/MaxScale/

2.1.2.系统环境介绍
2.1.3.安装软件
# rpm -ivh maxscale-2.0.5-1.rhel.6.x86_64.rpm
2.1.4.DB创建用户
mysql> grant replication slave, replication client on *.* to scalemon@'192.168.2.%' identified by 'monitor18';

mysql> grant all privileges on mycat.* to user_maxscale@'192.168.2.%' identified by 'H2kXvhaJxRSl';
mysql> grant select on *.* to user_maxscale@'192.168.2.%';
2.1.5.maxscale创建目录
mkdir -p /data1/maxscale4007/log/
mkdir -p /data1/maxscale4007/data/
mkdir -p /data1/maxscale4007/cache/
mkdir -p /data1/maxscale4007/pid/
chown -R maxscale:maxscale /data1/maxscale4007
2.1.6.maxsacle创建秘钥
# maxkeys /data1/maxscale4007/data/
# maxpasswd /data1/maxscale4007/data/.secrets monitor18
80068EB8F16774E59BECBF8EE23AD60D
# maxpasswd /data1/maxscale4007/data/.secrets H2kXvhaJxRSl
41A9A7EFD164EF7C2D590F43F405E465
2.1.6.maxscale创建配置文件
vim /etc/maxscale4007.cnf 

[maxscale]
threads=auto    #开启线程个数，默认为1.设置为auto会同cpu核数相同 
maxlog=1        #将日志写入到maxscale的日志文件中
log_to_shm=0    #不将日志写入到共享缓存中，开启debug模式时可打开加快速度
log_warning=1   #记录告警信息 
log_error=1     #记录错误信息
log_notice=1    #记录notice
log_info=0      #不记录info
log_debug=0     #不记录debug
#log_augmentation=1
LimitNOFILE=65536
logdir=/data1/maxscale4007/log/  
datadir=/data1/maxscale4007/data/  
cachedir=/data1/maxscale4007/cache/  
piddir=/data1/maxscale4007/pid/  
libdir=/usr/lib64/maxscale/  
execdir=/usr/bin/  

[server1]
type=server
address=192.168.2.120
port=3389
protocol=MySQLBackend

[server2]
type=server
address=192.168.2.121
port=3389
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2
user=scalemon
passwd=80068EB8F16774E59BECBF8EE23AD60D
monitor_interval=5000      #监控心跳为5秒
#detect_replication_lag=true  #监控主从复制延迟
#max_slave_replication_lag  #控制maxscale运行的最大延迟
detect_stale_master=true   #当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点

#[Read-Only Service]
#type=service
#router=readconnroute
#servers=server2
#user=user_maxscale
#passwd=A622DB34CBF413527DE9048EBC3FE42E66EF97D8DD167887F781C5108213FD21
#router_options=slave

#读写分离，用户需要有SELECT ON mysql.db；SELECT ON mysql.tables_priv；SHOW DATABASES ON *.*的权限  
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2
user=user_maxscale
passwd=41A9A7EFD164EF7C2D590F43F405E465
max_slave_connections=100%
use_sql_variables_in=master  #sql语句中的存在变量只指向master中执行
enable_root_user=1   #允许root用户登录执行
#master_accept_reads=true  #master节点也可以转发读请求 

[MaxAdmin Service]
type=service
router=cli

#[Read-Only Listener]
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=3300

#读写分离服务端口
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4007

#管理服务端口
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=/data1/maxscale4007/maxadmin.sock
port=6604

2.3.Maxsacle配置

2.3.1.maxscale启动
maxsacle -f /etc/maxscale4007.cnf
2.3.2.maxscale关闭
# maxadmin -S /data1/maxscale4007/maxadmin.sock "shutdown maxscale"
2.3.3.maxscale服务状态
MaxScale> list listeners
Listeners.
---------------------+--------------------+-----------------+-------+--------
Service Name         | Protocol Module    | Address         | Port  | State
---------------------+--------------------+-----------------+-------+--------
Read-Write Service   | MySQLClient        | *               |  4007 | Running
MaxAdmin Service     | maxscaled          | *               |  6604 | Running
MaxAdmin Service     | maxscaled          | /data1/maxscale4007/maxadmin.sock |     0 | Running
---------------------+--------------------+-----------------+-------+--------

MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.2.120   |  3389 |           0 | Master, Running
server2            | 192.168.2.121   |  3389 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

MaxScale> list services
Services.
--------------------------+----------------------+--------+---------------
Service Name              | Router Module        | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Read-Write Service        | readwritesplit       |      1 |     2
MaxAdmin Service          | cli                  |      3 |     3
--------------------------+----------------------+--------+---------------

show services
Service 0x18d6330
    Service:                             Read-Write Service
    Router:                              readwritesplit (0x7f17ce109d40)
    State:                               Started
    Number of router sessions:              1
    Current no. of router sessions:         1
    Number of queries forwarded:            1
    Number of queries forwarded to master:  0 (0.00%)
    Number of queries forwarded to slave:   1 (100.00%)
    Number of queries forwarded to all:     0 (0.00%)
    Started:                             Sat Jan  6 09:43:33 2018
    Root user access:                    Enabled
    Backend databases:
        192.168.2.120:3389  Protocol: MySQLBackend
        192.168.2.121:3389  Protocol: MySQLBackend
    Users data:                          0x18eb920
    Total connections:                   2
    Currently connected:                 2
Service 0x18d4870
    Service:                             MaxAdmin Service
    Router:                              cli (0x7f17ce313560)
    State:                               Started
    Started:                             Sat Jan  6 09:43:33 2018
    Root user access:                    Disabled
    Backend databases:
    Users data:                          0x18ed620
    Total connections:                   3
    Currently connected:                 3
2.3.4.测试读写分离
# vim /etc/maxscale4007.cnf
log_info=1

# maxadmin -S /data1/maxscale4007/maxadmin.sock 
MaxScale> reload config
Reloading configuration from file.

# mysql -h 192.168.2.122 -P4007 -uuser_maxscale -pH2kXvhaJxRSl

2018-01-06 09:50:48   [7]  info   : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select * from t1 
2018-01-06 09:50:48   [7]  info   : Route query to slave    192.168.2.121:3389 <
2018-01-06 09:51:03   [7]  info   : > Autocommit: [enabled], trx is [open], cmd: COM_QUERY, type: QUERY_TYPE_BEGIN_TRX, stmt: begin 
2018-01-06 09:51:03   [7]  info   : Route query to master   192.168.2.120:3389 <
2018-01-06 09:51:03   [7]  info   : > Autocommit: [enabled], trx is [open], cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select * from t1 
2018-01-06 09:51:03   [7]  info   : Route query to master   192.168.2.120:3389 <
2018-01-06 09:51:03   [7]  info   : > Autocommit: [enabled], trx is [not open], cmd: COM_QUERY, type: QUERY_TYPE_COMMIT, stmt: commit 
2018-01-06 09:51:03   [7]  info   : Route query to master   192.168.2.120:3389 <

maxScale注意：

3.Keepalived lvs

3.1.Keepalived lvs简介

在互联网应用技术中，负载均衡一直是热门话题，使用负载均衡技术主要的目的包括如下几点：

虚拟服务器是负载均衡体系的基本架构，分为：转发器（Director）和真实服务器。

图片来自keepalived官网

3.2.Keepalived lvs安装

3.2.1.软件参考文档

参考文档：
官方文档：http://www.keepalived.org/pdf/sery-lvs-cluster.pdf

软件下载：
keepalived软件：http://www.keepalived.org/download.html
Ipvs软件：http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.24.tar.gz

3.2.2.系统环境介绍
图片来自原创
3.2.3.lvs客户端
# vim /usr/local/bin/lvs_real

#!/bin/bash
#description : start realserver

VIP=192.168.2.210

/etc/rc.d/init.d/functions

case "$1" in
start)
        echo " start LVS of REALServer"
        /sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255 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
        ;;
stop)
        /sbin/ifconfig lo:0 down
        echo "close LVS Directorserver"
        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 "Usage: $0 {start|stop}"
        exit 1
esac

# chmod 755 /usr/local/bin/lvs_real
# chmod 755 /etc/rc.d/init.d/functions
# /usr/local/bin/lvs_real start
# echo "/usr/local/bin/lvs_real start" >> /etc/rc.d/rc.local

# ifconfig

… …  省略  … …
lo:0      Link encap:Local Loopback  
          inet addr:10.209.5.201  Mask:255.255.255.255
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
3.2.4.安装配置keepalived
# yum -y install ipvsadm-1.26-4.el6.x86_64

# yum -y install keepalived.x86_64

# echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
# sysctl -p

# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   router_id lvs_mysql_3389
}

vrrp_instance vi_3389 { 
    state MASTER
    interface eth0
    lvs_sync_daemon_inteface eth0
    virtual_router_id 11
    priority 90
    nopreempt
    advert_int 1 
    authentication { 
        auth_type PASS 
        auth_pass mysql3389
    } 
    virtual_ipaddress { 
        192.168.2.210
    } 
}
 
virtual_server 192.168.2.210 3389 { 
    delay_loop 2 
    lb_algo wrr
    lb_kind DR 
#    persistence_timeout 60 
    protocol TCP 
 
    real_server 192.168.2.120 3389 {
        weight 3 
    TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3389
        }
    }
  
    real_server 192.168.2.121 3389 {
        weight 3 
    TCP_CHECK {                 
            connect_timeout 10      
            nb_get_retry 3          
            delay_before_retry 3    
            connect_port 3389
        }
    } 

}

# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   router_id lvs_mysql_3389
}

vrrp_instance vi_3389 { 
    state MASTER
    interface eth0
    lvs_sync_daemon_inteface eth0
    virtual_router_id 11
    priority 10
    nopreempt
    advert_int 1 
    authentication { 
        auth_type PASS 
        auth_pass mysql3389
    } 
    virtual_ipaddress { 
        192.168.2.210
    } 
}
 
virtual_server 192.168.2.210 3389 { 
    delay_loop 2 
    lb_algo wrr
    lb_kind DR 
#    persistence_timeout 60 
    protocol TCP 
 
    real_server 192.168.2.120 3389 {
        weight 3 
    TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3389
        }
    }
  
    real_server 192.168.2.121 3389 {
        weight 3 
    TCP_CHECK {                 
            connect_timeout 10      
            nb_get_retry 3          
            delay_before_retry 3    
            connect_port 3389
        }
    } 

}

# /etc/init.d/keepalived start
# chkconfig keepalived on

3.3.Keepalived lvs高可用验收

Master模拟宕机，测试Backup是否能够第一时间接管Keepalived服务，并切换vip

# init 6

# ip addr
… …  省略  … …
8: bond0: <BROADCAST,MULTICAST,MASTER,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP 
    link/ether a0:36:9f:79:07:4c brd ff:ff:ff:ff:ff:ff
    inet 10.209.5.195/25 brd 10.209.5.255 scope global bond0
    inet 10.209.5.201/32 scope global bond0    # bakcup已经看出可以接管
    inet6 fe80::a236:9fff:fe79:74c/64 scope link 
       valid_lft forever preferred_lft forever

将slave1 slave2主机进行重启，用于模拟宕机，测试是否会将请求转发至正常的Master1

# init 6

# init 6

# ipvsadm -L
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  10.209.5.201:chevinservices wrr
  -> 10.209.5.191:chevinservices  Route   3      0          0

# init 6

# ipvsadm -L
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  10.209.5.201:chevinservices wrr
  -> 10.209.5.192:chevinservices  Route   3      0          0         
  -> 10.209.5.193:chevinservices  Route   3      0          0

如果MySQL 原来的Master 是重启的情况，负载均衡器还会将请求转发至原Master,不会去检测原Master的复制状态以及是否有延迟的，以下是原Master起来后，负载均衡器上的链路状态

# ipvsadm -L
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  10.209.5.201:chevinservices wrr
  -> 10.209.5.191:chevinservices  Route   3      0          0         
  -> 10.209.5.192:chevinservices  Route   3      0          0         
  -> 10.209.5.193:chevinservices  Route   3      0          0

3.4.Keepalived lvs运维注意事项

线上keepalived环境(基于端口探测方式)不会检查MySQL复制链路以及延迟情况，所以对于大数据量表的DDL变更，最好是放在夜间或者业务低峰期进行，复制的异常也要及时的处理,避免因数据延迟或者复制不同步导致业务读取过期的数据

附录

global_defs {
   notification_email {  #指定keepalived在发生切换时需要发送email到的对象，一行一个
    sysadmin@fire.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc #指定发件人
   smtp_server localhost #指定smtp服务器地址
   smtp_connect_timeout 30 #指定smtp连接超时时间
   router_id LVS_DEVEL #运行keepalived机器的一个标识
}
vrrp_sync_group VG_1{ #监控多个网段的实例
group {
inside_network #实例名
outside_network
}
notify_master /path/xx.sh #指定当切换到master时，执行的脚本
netify_backup /path/xx.sh #指定当切换到backup时，执行的脚本
notify_fault "path/xx.sh VG_1" #故障时执行的脚本
notify /path/xx.sh 
smtp_alert #使用global_defs中提供的邮件地址和smtp服务器发送邮件通知
}
vrrp_instance inside_network {
    state BACKUP #指定那个为master，那个为backup，如果设置了nopreempt这个值不起作用，主备考priority决

定
    interface eth0 #设置实例绑定的网卡
    dont_track_primary #忽略vrrp的interface错误（默认不设置）
    track_interface{ #设置额外的监控，里面那个网卡出现问题都会切换
    eth0
    eth1
    }
    mcast_src_ip #发送多播包的地址，如果不设置默认使用绑定网卡的primary ip
    garp_master_delay #在切换到master状态后，延迟进行gratuitous ARP请求
    virtual_router_id 50 #VPID标记
    priority 99 #优先级，高优先级竞选为master
    advert_int 1 #检查间隔，默认1秒
    nopreempt #设置为不抢占 注：这个配置只能设置在backup主机上，而且这个主机优先级要比另外一台高
    preempt_delay #抢占延时，默认5分钟
    debug #debug级别
    authentication { #设置认证
        auth_type PASS #认证方式
        auth_pass 111111 #认证密码
    }
    virtual_ipaddress { #设置vip
        192.168.202.200
    }
}
virtual_server 192.168.202.200 23 {
    delay_loop 6 #健康检查时间间隔
    lb_algo rr  #lvs调度算法rr|wrr|lc|wlc|lblc|sh|dh
    lb_kind DR  #负载均衡转发规则NAT|DR|RUN
    persistence_timeout 5 #会话保持时间
    protocol TCP #使用的协议
    persistence_granularity <NETMASK> #lvs会话保持粒度
    virtualhost <string> #检查的web服务器的虚拟主机（host：头）    
    sorry_server<IPADDR> <port> #备用机，所有realserver失效后启用
real_server 192.168.200.5 23 {
            weight 1 #默认为1,0为失效
            inhibit_on_failure #在服务器健康检查失效时，将其设为0，而不是直接从ipvs中删除 
            notify_up <string> | <quoted-string> #在检测到server up后执行脚本
            notify_down <string> | <quoted-string> #在检测到server down后执行脚本
            
TCP_CHECK {
            connect_timeout 3 #连接超时时间
            nb_get_retry 3 #重连次数
            delay_before_retry 3 #重连间隔时间
            connect_port 23  健康检查的端口的端口
            bindto <ip>   
          }
HTTP_GET | SSL_GET{
    url{ #检查url，可以指定多个
         path /
         digest <string> #检查后的摘要信息
         status_code 200 #检查的返回状态码
        }
    connect_port <port> 
    bindto <IPADD>
    connect_timeout 5
    nb_get_retry 3
    delay_before_retry 2
}

SMTP_CHECK{
    host{
    connect_ip <IP ADDRESS>
    connect_port <port> #默认检查25端口
    bindto <IP ADDRESS>
         }
    connect_timeout 5
    retry 3
    delay_before_retry 2
    helo_name <string> | <quoted-string> #smtp helo请求命令参数，可选
}
MISC_CHECK{
    misc_path <string> | <quoted-string> #外部脚本路径
    misc_timeout #脚本执行超时时间
    misc_dynamic #如设置该项，则退出状态码会用来动态调整服务器的权重，返回0 正常，不修改；返回1，

检查失败，权重改为0；返回2-255，正常，权重设置为：返回状态码-2
}
    }



上一篇 下一篇

猜你喜欢

热点阅读