dtcms WebStorm JavaWeb SpringApplication JDK动态代理 Angular Transformer rest neo4j download mui ddos压力测试 jquery拼接字符串 collection框架的结构 bootstrap模态框传参 移动端上传图片插件 如何升级python python数据类型 mysql事务 python如何注释 python语言 python例子 python函数返回 java基础课程 java基础编程 java定义变量 java输出当前时间 java数据类型转换 房产证生成器 黑白照片一键变彩色 ps选择反向快捷键 3389扫描器 mssql python缩进规则 爱奇艺无法投屏 一键换肤大师 ftp客户端软件 office2010免费版 冬青黑体简体中文 极速pdf转word
当前位置: 首页 > 学习教程  > 编程学习

Mysql主从复制—5.7 gtid 双主 + Keepalived 配置及简单测试

2021/1/24 9:42:43 文章标签: keepalived

参考:https://cloud.tencent.com/developer/article/1134077 keepalive下载:https://www.keepalived.org/download.html 这里主要讲keepalived配置和测试, mysql的gtid配置主从 详见 https://blog.csdn.net/fly43108622/article/details/8608…

 

参考:https://cloud.tencent.com/developer/article/1134077

 

keepalive下载:https://www.keepalived.org/download.html

 

这里主要讲keepalived配置和测试, mysql的gtid配置主从 详见 https://blog.csdn.net/fly43108622/article/details/86089178

 

说明:

master:192.168.56.80    mysqld的server_id 为222

slave   :192.168.56.61    mysqld的server_id 为111

vip      :192.168.56.168

 

注:maste 和slave  是gtid配置好的双主状态。

 

 

一、配置文件文件 /etc/keepalived/keepalived.conf   注释说明如下

global_defs {
   router_id MYSQL-1 //表示运行keepalived服务器的一个标识}
// 定义自动核对mysql的脚本
vrrp_script check_mysql {
    script "/etc/keepalived/bin/check_mysql.sh"
    interval 22
    weight 2   
}
vrrp_instance VI_1 {
    state BACKUP //指定keepalived的角色, 两台配置此处均是BACKUP,设为BACKUP将根据优先级决定主或从
    interface eth1 //指定HA监测网络的接口
    virtual_router_id 51 /虚拟路由标识,这个标识是一个数字(取值在0-255之间,用来区分多个instance的VRRP组播),同一个vrrp实例使用唯一的标识,确保和master2相同,同网内不同集群此项必须不同,否则发生冲突。
    priority 100   //用来选举master的,要成为master,该项取值范围是1-255(在此范围之外会被识别成默认值100),此处master2上设置为50
    advert_int 1 //发VRRP包的时间间隔,即多久进行一次master选举(可以认为是健康查检时间间隔)
    nopreempt //不抢占,即允许一个priority比较低的节点作为master,即使有priority更高的节点启动
    authentication {
        //认证区域,认证类型有PASS和HA(IPSEC),推荐使用PASS(密码只识别前8位)
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        // 指定vip地址
        192.168.1.66
    }
    track_script {
        //指定核对的脚本,check_mysql是上述自定义的
        check_mysql
    }
}
virtual_server 192.168.1.66 3306 {
    delay_loop 2  //设置运行情况检查时间,单位是秒
    lb_algo rr   //设置后端调度算法,这里设置为rr,即轮询算法
    lb_kind DR //设置LVS实现负载均衡的机制,有NAT、TUN、DR三个模式可选
    persistence_timeout 50 //会话保持时间,单位是秒。这个选项对动态网页是非常有用的,为集群系统中的session共享提供了一个很好的解决方案。有了这个会话保持功能,用户的请求会被一直分发到某个服务节点,直到超过这个会话的保持时间。
    protocol TCP //指定转发协议类型,有TCP和UDP两种
    real_server 192.168.1.60 3306 {
        weight 3 //配置服务节点的权值,权值大小用数字表示,数字越大,权值越高,设置权值大小为了区分不同性能的服务器
        notify_stop /etc/keepalived/bin/shutdown.sh //检测到mysql服务down后执行的脚本,在一些版本中叫notify_down
        TCP_CHECK {
            connect_timeout 3 //连接超时时间
            nb_get_retry 3 //重连次数
            delay_before_retry 3 //重连间隔时间
            connect_port 3306 //健康检查端口
        }
    }}

 

 

二、主库上配置keepalived  master:192.168.56.80(slave备库配置同理, 只是keepalived.conf 配置文件需要略为修改,详见如下)

--解压缩
[root@hostmysql80 mysql_setup]# tar -xzvf keepalived-2.0.19.tar.gz

--修改权限
[root@hostmysql80 mysql_setup]# chown -R root:root  keepalived-2.0.19/


--新建keepalive目录
[root@hostmysql80 mysql_setup]# mkdir keepalived
[root@hostmysql80 mysql_setup]# cd keepalived-2.0.19/


--初次配置 报错 需要安装包
[root@hostmysql80 keepalived-2.0.19]# ./configure  --prefix=/mysql_setup/keepalived
*** WARNING - this build will not support IPVS with IPv6. Please install libnl/libnl-3 dev libraries to support IPv6 with IPVS.

--安装libnl/libnl-3
[root@hostmysql80 mysql_setup]# rpm -ivh libnl-devel-1.1.4-3.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:libnl-devel-1.1.4-3.el7          ################################# [100%]
[root@hostmysql80 mysql_setup]# rpm -ivh libnl3-devel-3.2.28-4.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:libnl3-devel-3.2.28-4.el7        ################################# [100%]
[root@hostmysql80 mysql_setup]#


--再次配置  成功
[root@hostmysql80 keepalived-2.0.19]# ./configure  --prefix=/mysql_setup/keepalived-2.0.19

--编译&安装
[root@hostmysql80 keepalived-2.0.19]# make && make install


--注册系统服务
[root@hostmysql80 keepalived]# cp /mysql_setup/keepalived/sbin/keepalived /usr/sbin/
[root@hostmysql80 keepalived]# cp /mysql_setup/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/
[root@hostmysql80 keepalived]# cp /mysql_setup/keepalived-2.0.19/keepalived/etc/init.d/keepalived  /etc/init.d
[root@hostmysql80 keepalived]# mkdir -p /etc/keepalived
[root@hostmysql80 keepalived]# cp /mysql_setup/keepalived/etc/keepalived/keepalived.conf  /etc/keepalived/


--修改keepalived的systemctl 服务
[root@hostmysql80 ~]# vi /lib/systemd/system/keepalived.service

[Unit]
Description=LVS and VRRP High Availability Monitor
After=network-online.target syslog.target
Wants=network-online.target

[Service]
Type=forking
PIDFile=/run/keepalived.pid
KillMode=process
EnvironmentFile=/etc/sysconfig/keepalived
ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target


--生效新配置keepalived的systemctl 服务
[root@hostmysql80 mysql_setup]# systemctl daemon-reload



--编辑keepalived.conf配置文件
[root@hostmysql80 keepalived]# pwd
/etc/keepalived

[root@hostmysql80 keepalived]# vi keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id MYSQL-M               //备库改为MYSQL-S
}

vrrp_script check_mysql {
   script "/etc/keepalived/bin/check_mysql.sh"
   interval 2
   weight 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface enp0s3
    virtual_router_id 51
    priority 100                    //备库改为50  权重
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 192.168.56.80    //备库改ip(为本机ip)
    unicast_peer {
        192.168.56.61               //备库改ip(为对端ip)
    }
    virtual_ipaddress {
        192.168.56.168
    }
    track_script {
        check_mysql
    }
}

virtual_server 192.168.56.168 3306 {
    delay_loop 2
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 192.168.56.80 3306 {     //备库改ip(为本机ip)
        weight 3
        notify_stop "/etc/keepalived/bin/shutdown.sh"
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}



--建立check_mysql.sh check文件  ,用来检测在mysqld服务没有时 关闭本地keepalived服务 让vip指向其他real ip 
[root@localmysql-source keepalived]# pwd
/etc/keepalived
[root@hostmysql80 keepalived]# mkdir bin
[root@hostmysql80 keepalived]# cd bin
[root@hostmysql80 bin]# vi check_mysql.sh


#!/bin/bash
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=Root123$


mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&1
if [ $? == 0 ]
then
    echo " $host mysql login successfully "
    exit 0
else
    service keepalived stop
    exit 2
fi



--建立shutdown.sh 文件
[root@localmysql-source bin]# pwd
/etc/keepalived/bin
[root@hostmysql80 bin]# vi shutdown.sh


#!/bin/bash
service keepalived stop



--执行权限
[root@localmysql-source bin]# chmod +x /etc/keepalived/bin/check_mysql.sh
[root@localmysql-source bin]# chmod +x /etc/keepalived/bin/shutdown.sh



--启动keepalived
[root@hostmysql80 keepalived]# service keepalived start
Redirecting to /bin/systemctl start keepalived.service


--查看keepalived状态
[root@hostmysql80 keepalived]# service keepalived status
Redirecting to /bin/systemctl status keepalived.service
鈼?[0m keepalived.service - LVS and VRRP High Availability Monitor
   Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-10-25 16:36:09 CST; 4s ago
  Process: 3178 ExecStart=/mysql_setup/keepalived/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 3179 (keepalived)
    Tasks: 3
   CGroup: /system.slice/keepalived.service
           鈹溾攢3179 /mysql_setup/keepalived/sbin/keepalived -D
           鈹溾攢3180 /mysql_setup/keepalived/sbin/keepalived -D
           鈹斺攢3181 /mysql_setup/keepalived/sbin/keepalived -D


Oct 25 16:36:11 hostmysql80 Keepalived_healthcheckers[3180]: TCP connection to [192.168.56.80]:tcp:3306 success.
Oct 25 16:36:12 hostmysql80 Keepalived_vrrp[3181]: (VI_1) Receive advertisement timeout
Oct 25 16:36:13 hostmysql80 Keepalived_vrrp[3181]: (VI_1) Entering MASTER STATE
Oct 25 16:36:13 hostmysql80 Keepalived_vrrp[3181]: (VI_1) setting VIPs.
Oct 25 16:36:13 hostmysql80 Keepalived_vrrp[3181]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 25 16:36:13 hostmysql80 Keepalived_vrrp[3181]: (VI_1) Sending/queueing gratuitous ARPs on enp0s3 for 192.168.56.168
Oct 25 16:36:13 hostmysql80 Keepalived_vrrp[3181]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 25 16:36:13 hostmysql80 Keepalived_vrrp[3181]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 25 16:36:13 hostmysql80 Keepalived_vrrp[3181]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 25 16:36:13 hostmysql80 Keepalived_vrrp[3181]: Sending gratuitous ARP on enp0s3 for 192.168.56.168



--查看ip,有vip出现192.168.56.168
[root@hostmysql80 keepalived]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 08:00:27:78:13:79 brd ff:ff:ff:ff:ff:ff
    inet 192.168.56.80/24 brd 192.168.56.255 scope global noprefixroute enp0s3
       valid_lft forever preferred_lft forever
    inet 192.168.56.168/32 scope global enp0s3
       valid_lft forever preferred_lft forever
    inet6 fe80::fb26:9506:8c30:d55d/64 scope link noprefixroute
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:71:1f:75 brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:71:1f:75 brd ff:ff:ff:ff:ff:ff
[root@hostmysql80 keepalived]#

 

 

 

三、测试流程&#xff1a;

--1、说明
1&#xff1a;master(192.168.56.80)和slave (192.168.56.61)&#xff0c;都开启keepalived&#xff0c;此时连接vip的mysqld为master(192.168.56.80)    因为master上keepalived配置的priority大
2&#xff1a;在master(192.168.56.80)上停止mysqld服务(systemctl stop mysqld.service) &#xff0c;当master(192.168.56.80)的check_mysql.sh 检测到mysqld服务停止时&#xff0c;会停止掉master(192.168.56.80)的keepalived。
3&#xff1a;此时keepalived的实例会漂移到另一个节点上&#xff0c;也就是slave (192.168.56.61)的keepalived会开启&#xff0c;
4&#xff1a;此时连接vip的mysqld为slave (192.168.56.61)&#xff0c; 建立测试表插入数据 (create table  t_20191030_slave_61(a int) ;  insert into t_20191030_slave_61 values(1);)  这时slave库有数据但master库没有
5&#xff1a;master(192.168.56.80)修复好后&#xff0c;启动master的mysqld和keepalived  (systemctl start mysqld.service    systemctl start keepalived.service)
6&#xff1a;由于keepalived不是抢占模式(nopreempt)&#xff0c;需要让slave (192.168.56.61)的keepalived停掉(systemctl stop keepalived.service)&#xff0c;这样vip回切到master(192.168.56.80)
7&#xff1a;查看master(192.168.56.80) 是有t_20191030_slave_61表 且有数据(select * from t_20191030_slave_61;)&#xff0c;回切成功 且 停掉时间段的数据无丢失。


--2、流程

--master(192.168.56.80) 的mysqld和keepalived 状态 都是开启&#xff0c;并且现在keepalived在master上
[root@hostmysql80 ~]# systemctl status mysqld.service

[root@hostmysql80 ~]# systemctl status keepalived.service



--slave(192.168.56.61) 的mysqld和keepalived 状态 都是开启&#xff0c;但现在keepalived不在slave上
[root@hostmysql61 ~]# systemctl status mysqld.service

[root@hostmysql61 ~]# systemctl status keepalived.service

--现在连接vip 192.168.56.168 是server_id 222的&#xff0c;也就是master上的id

 

--停掉master(192.168.56.80)的mysqld服务后&#xff0c;因为check_mysql.sh自动检测脚本&#xff0c;也会关闭keepalived服务&#xff0c;使得real ip 漂移到可用的节点上(这里为slave(192.168.56.61) 节点)
[root@hostmysql80 keepalived]# systemctl stop mysqld.service
[root@hostmysql80 ~]# systemctl status keepalived.service


--此时查看slave(192.168.56.61)&#xff0c;发现keepalived已漂移过来&#xff0c;
[root@hostmysql61 ~]# systemctl status keepalived.service

 

--现在连接vip 192.168.56.168 是server_id 111的&#xff0c;也就是slave上的id&#xff0c;漂移成功

 

--vip现在 连接的是slave库 &#xff0c;建立测试表并插入数据

 

--修复master操作&#xff0c;手动启动master库的mysqld和keepalived
[root@hostmysql80 ~]# systemctl start mysqld.service
[root@hostmysql80 ~]#  systemctl status mysqld.service

[root@hostmysql80 ~]# systemctl start keepalived.service
[root@hostmysql80 ~]#  systemctl status keepalived.service


--回切操作&#xff0c;停掉slave库的keepalived
[root@hostmysql61 ~]# systemctl stop keepalived.service
[root@hostmysql61 ~]# systemctl status keepalived.service

 

--此时vip成功回切到master (master库的server_id为222 )

 

--vip现在 连接的是master库 &#xff0c;刚才在slave库创建的测试表可以查询出数据&#xff0c;master停掉期间内的数据同步成功

 

 

四、配置keepalived单独的日志文件

--1、修改/etc/sysconfig/keepalived&#xff0c;从KEEPALIVED_OPTIONS="-D" 改为 KEEPALIVED_OPTIONS="-D -d -S 0"
[root@hostmysql80 ~]# vi /etc/sysconfig/keepalived
# Options for keepalived. See `keepalived --help' output and keepalived(8) and
# keepalived.conf(5) man pages for a list of all options. Here are the most
# common ones :
#
# --vrrp               -P    Only run with VRRP subsystem.
# --check              -C    Only run with Health-checker subsystem.
# --dont-release-vrrp  -V    Dont remove VRRP VIPs & VROUTEs on daemon stop.
# --dont-release-ipvs  -I    Dont remove IPVS topology on daemon stop.
# --dump-conf          -d    Dump the configuration data.
# --log-detail         -D    Detailed log messages.
# --log-facility       -S    0-7 Set local syslog facility (default=LOG_DAEMON)
#

KEEPALIVED_OPTIONS="-D -d -S 0"



--2、修改/etc/rsyslog.conf文件 &#xff0c;最后增加 local0.*    /var/log/keepalived.log
[root@hostmysql80 ~]# vi /etc/rsyslog.conf
#keepalived#
local0.*                                                /var/log/keepalived.log

--重启rsyslog和keepalived
[root@hostmysql80 ~]# systemctl restart rsyslog.service
[root@hostmysql80 ~]# systemctl restart keepalived.service

--查看/var/log/keepalived.log 文件有日志生成
[root@hostmysql80 ~]# tail -10 /var/log/keepalived.log
Oct 29 17:25:36 hostmysql80 Keepalived_vrrp[2701]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 29 17:25:36 hostmysql80 Keepalived_vrrp[2701]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 29 17:25:36 hostmysql80 Keepalived_vrrp[2701]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 29 17:25:36 hostmysql80 Keepalived_vrrp[2701]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 29 17:25:41 hostmysql80 Keepalived_vrrp[2701]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 29 17:25:41 hostmysql80 Keepalived_vrrp[2701]: (VI_1) Sending/queueing gratuitous ARPs on enp0s3 for 192.168.56.168
Oct 29 17:25:41 hostmysql80 Keepalived_vrrp[2701]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 29 17:25:41 hostmysql80 Keepalived_vrrp[2701]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 29 17:25:41 hostmysql80 Keepalived_vrrp[2701]: Sending gratuitous ARP on enp0s3 for 192.168.56.168
Oct 29 17:25:41 hostmysql80 Keepalived_vrrp[2701]: Sending gratuitous ARP on enp0s3 for 192.168.56.168

 

 

 

 


本文链接: http://www.dtmao.cc/news_show_1257729.shtml

附件下载

相关教程

    暂无相关的数据...

共有条评论 网友评论

验证码: 看不清楚?