MySQL--整合Keepalived进行双机热备自动切换

太过爱你忘了你带给我的痛 2023-09-23 14:52 126阅读 0赞

原文网址：[MySQL--整合Keepalived进行双机热备自动切换\_IT利刃出鞘的博客-CSDN博客][MySQL--_Keepalived_IT_-CSDN]

## 简介 ##

**说明**

本文介绍MySQL如何整合Keepalived进行双机热备自动切换。

为解决Mysql数据库单点问题，用两台MySQL数据库互为主备，双向replication。当一Master出现问题，则将Slave切换为Master继续工作。

**环境说明**

系统版本：CentOS Linux release 7.6.1810 (Core)

MySQL版本：mysql Ver 14.14 Distrib 5.7.27

keepalived版本：Keepalived v1.2.13

序号 　　 服务器IP 　　　　用途

1 　　　　192.168.158.10 Master  
2 　　　　192.168.158.20 Slave  
3 　　　　192.168.158.30 VIP

## 一、MySQL互为主从配置 ##

1.> 两台安装相同版本的MySQL数据库.  
2.> 主备机NTP时钟同步  
3.> 双机互信配置ssh免密认证  
4.> 数据库配置(Master的配置和Slave的配置server-id不能一致,别的都可以一样)  
4.1> 修改Master主机上MySQL数据库的配置文件，然后新启动MySQL。

/ect/my.cnf

[mysqld]
    log-bin=mysql-bin
    server-id=100
    expire_logs_days = 10
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    symbolic-links=0
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    validate_password=off         #关闭密码安全策略
    default_password_lifetime=0     #设置密码不过期
    log_bin=/var/log/mysql/mysql-bin

4.2> 修改Slave主机上MySQL数据库的配置文件，然后新启动MySQL

/ect/my.cnf

[mysqld]
    log-bin=mysql-bin
    server-id=101
    expire_logs_days = 10
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    symbolic-links=0
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    validate_password=off           
    default_password_lifetime=0     
    log_bin=/var/log/mysql/mysql-bin

5.> 启动MySQL服务

systemctl start mysqld

6.> 查询相关状态，以Master主机为例，如下

7.> 创建复制账号并同步  
7.1> 在Master库和Slave库分别执行，创建数据同步复制账号.

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO replication@'%' IDENTIFIED BY 'replication';
    mysql> flush  privileges;

7.2> 在Master主机上,执行同步操作(注意master\_host参数主备机相互指向),如下：

mysql> change master to master_host='192.168.158.20',master_port=3306,master_user='replication',master_password='replication',master_log_file='mysql-bin.000001',master_log_pos=154;
    mysql> start slave;

7.3> 在Slave主机上,执行同步操作(注意master\_host参数主备机相互指向),如下：

mysql> change master to master_host='192.168.158.10',master_port=3306,master_user='replication',master_password='replication',master_log_file='mysql-bin.000001',master_log_pos=154;
    mysql> start slave;

7.4> 在Master、Slave主机上，查询同步状态“show slave status\\G”，检查结果中Slave\_IO\_Running: Yes和Slave\_SQL\_Running: Yes，否则有异常。

8.> 配置密文命令访问(两台主机都配置)  
Mysql数据库使用mysql或mysqldump等相关命令时，需要在命令行界面输入密码，当使用脚本时，在脚本里填写密码显然不太安全，因此可以设置Mysql的密文文件。

# mysql_config_editor set --login-path=local --user=root --port=3306 --password
    # mysql_config_editor print --all

9.> 创建切换脚本

切换脚本规划，如本次是mysql切换，因此在该目录下创建mysql目录,将所有切换脚本放在/home/mysql目录下,本次相关脚本说明如下：

进入/home/mysql目录，如下文件：

Logs 　　 //存储日志的文件目录

mybackup.sh //清空slave配置，重新获取远程日志文件及Pos,并开启同步

mycheck.sh //检查mysql运行状态，如果运行正常，退出。如果运行不正常调用pkill keepalived

mymaster.sh //先判断同步复制是否执行完成，如果未执行完成等待1分钟后，停止同步（stop slave;），并且记录切换后的日志和pos

.mysqlenv //脚本运行环境文件

mystop.sh //设置参数保证数据不丢失，最后检查看是否还有写操作，最后1分钟退出

syncposfile //每次切换后，Master最后一次File值和Position值。

10.环境文件

10.1> Master主机端的环境文件（.mysqlenv）

MYSQL=/usr/bin/mysql
    MYSQL_CMD="--login-path=local"
    #远端主机的IP地址
    REMOTE_IP=192.168.158.20
    export mysql="$MYSQL $MYSQL_CMD "

10.2> Slave主机端的环境文件（.mysqlenv）

MYSQL=/usr/bin/mysql
    MYSQL_CMD="--login-path=local"
    #远端主机的IP地址
    REMOTE_IP=192.168.158.10
    export mysql="$MYSQL $MYSQL_CMD"

11.> 服务检查脚本

11.1> mycheck.sh

#!/bin/sh
    
    ##################################################
    #File Name  : mycheck.sh
    #Description: mysql is working MYSQL_OK is 1
    #             mysql is down MYSQL_OK is 0
    ##################################################
    
    BASEPATH=/home/mysql
    LOGSPATH=$BASEPATH/logs
    source $BASEPATH/.mysqlenv
    
    CHECK_TIME=3
    MYSQL_OK=1
    ##################################################################
    function check_mysql_helth (){
      $mysql -e "show status;" >/dev/null 2>&1
      if [ $? == 0 ] 
      then 
        MYSQL_OK=1
      else
        MYSQL_OK=0
        #systemctl status keepalived
     fi
     return $MYSQL_OK
    }
    
    #check_mysql_helth
    while [ $CHECK_TIME -ne 0 ] #不等于
    do
        let "CHECK_TIME -= 1"
        check_mysql_helth
        if [ $MYSQL_OK = 1 ] ; then
        CHECK_TIME=0
        echo "$(date "+%Y-%m-%d %H:%M:%S") The scripts mycheck.sh is running ..." >> $LOGSPATH/mysql_switch.log
        exit 0
        fi
        if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ] #等于
        then
        systemctl stop keepalived
        echo "$(date "+%Y-%m-%d %H:%M:%S") The mycheck.sh, mysql is down, after switch..." >> $LOGSPATH/mysql_switch.log
        exit 1
        fi
        sleep 1　　
    done

11.2> 切换脚本（mymaster.sh）

#!/bin/sh
    
    ##################################################
    #File Name  : mymaster.sh
    #Description: First determine whether synchronous
    #             replication is performed, and if no
    #             execution is completed, wait for 1
    #             minutes. Log logs and POS after
    #             switching, and record files synchronously.
    ##################################################
    
    BASEPATH=/home/mysql
    LOGSPATH=$BASEPATH/logs
    source $BASEPATH/.mysqlenv
    
    $mysql -e "show slave status\G" > $LOGSPATH/mysqlslave.states
    Master_Log_File=`cat $LOGSPATH/mysqlslave.states | grep -w Master_Log_File | awk -F": " '{print $2}'`
    Relay_Master_Log_File=`cat $LOGSPATH/mysqlslave.states | grep -w Relay_Master_Log_File | awk -F": " '{print $2}'`
    Read_Master_Log_Pos=`cat $LOGSPATH/mysqlslave.states | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}'`
    Exec_Master_Log_Pos=`cat $LOGSPATH/mysqlslave.states | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}'`
    i=1
    
    while true
    do
        if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ];then
            echo "$(date "+%Y-%m-%d %H:%M:%S") The mymaster.sh, slave sync ok... " >> $LOGSPATH/mysql_switch.log
            break
        else
            sleep 1
            if [ $i -gt 60 ];then
                break
            fi
            continue
            let i++
        fi
    done
    
    $mysql -e "stop slave;"
    $mysql -e "set global innodb_support_xa=0;"
    $mysql -e "set global sync_binlog=0;"
    $mysql -e "set global innodb_flush_log_at_trx_commit=0;"
    $mysql -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication';flush privileges;"
    $mysql -e "show master status;" > $LOGSPATH/master_status_$(date "+%y%m%d-%H%M").txt
    
    # sync pos file
    /usr/bin/scp $LOGSPATH/master_status_$(date "+%y%m%d-%H%M").txt root@$REMOTE_IP:$BASEPATH/syncposfile/backup_master.status
    echo "$(date "+%Y-%m-%d %H:%M:%S") The mymaster.sh, Sync pos file sucess." >> $LOGSPATH/mysql_switch.log

11.3> 回切脚本（mybackup.sh）

#!/bin/sh
    
    ##################################################
    #File Name  : mybackup.sh
    #Description: Empty the slave configuration, retrieve
    #             the remote log file and Pos, and open
    #             the synchronization
    ##################################################
    
    BASEPATH=/home/mysql
    LOGSPATH=$BASEPATH/logs
    source $BASEPATH/.mysqlenv
    
    $mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication';flush privileges;"
    $mysql -e "set global innodb_support_xa=0;"
    $mysql -e "set global sync_binlog=0;"
    $mysql -e "set global innodb_flush_log_at_trx_commit=0;"
    $mysql -e "flush logs;"
    $mysql -e "reset slave all;"
    
    if [ -f $BASEPATH/syncposfile/backup_master.status ];then
            New_ReM_File=`cat $BASEPATH/syncposfile/backup_master.status | grep -v File |awk '{print $1}'`
            New_ReM_Position=`cat $BASEPATH/syncposfile/backup_master.status | grep -v File |awk '{print $2}'`
            echo "$(date "+%Y-%m-%d %H:%M:%S") This mybackup.sh, New_ReM_File:$New_ReM_File,New_ReM_Position:$New_ReM_Position" >> $LOGSPATH/mysql_switch.log
            $mysql -e "change master to master_host='$REMOTE_IP',master_port=3306,master_user='replication',master_password='replication',master_log_file='$New_ReM_File',master_log_pos=$New_ReM_Position;"
            $mysql -e "start slave;"
            $mysql -e "show slave status\G;" > $LOGSPATH/slave_status_$(date "+%y%m%d-%H%M").txt
            cat $LOGSPATH/slave_status_$(date "+%y%m%d-%H%M").txt >> $LOGSPATH/mysql_switch.log
            rm -f $BASEPATH/syncposfile/backup_master.status
    else
        echo "$(date "+%Y-%m-%d %H:%M:%S") The scripts mybackup.sh running error..." >> $LOGSPATH/mysql_switch.log
    fi
    [root@localhost mysql]#

11.4> 停止脚本（mystop.sh）

#!/bin/sh
    
    ##################################################
    #File Name  : mystop.sh
    #Description: Set parameters to ensure that the data
    #             is not lost, and finally check to see
    #             if there are still write operations,
    #             the last 1 minutes to exit
    
    ##################################################
    
    BASEPATH=/home/mysql
    LOGSPATH=$BASEPATH/logs
    source $BASEPATH/.mysqlenv
    
    $mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication';flush privileges;"
    $mysql -e "set global innodb_support_xa=1;"
    $mysql -e "set global sync_binlog=1;"
    $mysql -e "set global innodb_flush_log_at_trx_commit=1;"
    $mysql -e "show master status\G" > $LOGSPATH/mysqlmaster0.states
    M_File1=`cat $LOGSPATH/mysqlmaster0.states | awk -F': ' '/File/{print $2}'`
    M_Position1=`cat $LOGSPATH/mysqlmaster0.states | awk -F': ' '/Position/{print $2}'`
    sleep 2
    $mysql -e "show master status\G" > $LOGSPATH/mysqlmaster1.states
    M_File2=`cat $LOGSPATH/mysqlmaster1.states | awk -F': ' '/File/{print $2}'`
    M_Position2=`cat $LOGSPATH/mysqlmaster1.states | awk -F': ' '/Position/{print $2}'`
    
    i=1
    
    while true
    do
        if [ $M_File1 = $M_File2 ] && [ $M_Position1 -eq $M_Position2 ];then
            echo "$(date "+%Y-%m-%d %H:%M:%S") The mystop.sh, master sync ok.." >> $LOGSPATH/mysql_switch.log
            exit 0
        else
            sleep 1
            if [$i -gt 60 ];then
                break
            fi
            continue
            let i++
        fi
    done
    echo "$(date "+%Y-%m-%d %H:%M:%S") The mystop.sh, master sync exceed one minutes..." >> $LOGSPATH/mysql_switch.log

## 二、Keepalived安装与配置 ##

**1.两台都安装Keepalived(略)**

**2.切换原理**

Keepalived可实现将虚拟IP地址在实体物理机上来回漂移。Keepalived在转换状态时会依照状态来呼叫配置文件中内置的定义。  
当进入Master状态时会呼叫notify\_master定义的脚本  
当进入Backup状态时会呼叫notify\_backup定义的脚本  
当keepalived程序终止时呼叫notify\_stop定义的脚本  
当发现异常情况时进入Fault状态呼叫notify\_fault定义的脚本

切换的过程如下：

1.>在Master主机上keepalived运行时执行mycheck.sh脚本不停的检查mysql的运行状态，当发现mysql停止后将keepalived进程杀掉。  
2.>此时Slave主机上会接管虚拟IP地址，并调用notify\_master定义的脚本  
3.>当原Master主机上的mysql和keepalived进程恢复正常后，会调用notify\_backup定义的脚本，此时数据库的主端还在Savle主机上。  
4.>回切，关闭Slave端的keepavlied进程，会调用notify\_stop脚本，同时Master主机上会调用notify\_master定义的脚本。此时数据库的主端在Master主机上  
5.>启动Slave端的keepavlied进程，会调用notify\_backup脚本，此时完成数据同步。

**3.Keepalived的配置**

在Master端和Savle端均安装好keepalived后，进行配置，修改/etc/keepalived/keepalived.conf文件.  
3.1> Master端配置（keepalived.conf）

global_defs {
       router_id MySQL-HA
    } 
    
    vrrp_script check_run {
    script "/home/mysql/mycheck.sh"
    interval 10
    }
    
    vrrp_sync_group VG1 {
    group {
    VI_1
    }
    }
    
    vrrp_instance VI_1 {
        state MASTER
        #state BACKUP
        interface enp0s3 
        virtual_router_id 51
        priority 100
        advert_int 1
        #nopreempt
        authentication {
            auth_type PASS
            auth_pass 1234
        }
        track_script {
        check_run
        }
        notify_master /home/mysql/mymaster.sh
        notify_backup /home/mysql/mybackup.sh
        notify_stop /home/mysql/mystop.sh
    
        virtual_ipaddress {
            192.168.158.30/24
        }
    
      }

3.2> Slave端配置（keepalived.conf）

global_defs {
       router_id MySQL-HA
    } 
    
    vrrp_script check_run {
    script "/home/mysql/mycheck.sh"
    interval 10
    }
    
    vrrp_sync_group VG1 {
    group {
    VI_1
    }
    }
    
    vrrp_instance VI_1 {
        state MASTER
        #state BACKUP
        interface enp0s3 
        virtual_router_id 51
        priority 90
        advert_int 1
        #nopreempt
        authentication {
            auth_type PASS
            auth_pass 1234
        }
        track_script {
        check_run
        }
        notify_master /home/mysql/mymaster.sh
        notify_backup /home/mysql/mybackup.sh
        notify_stop /home/mysql/mystop.sh
    
        virtual_ipaddress {
            192.168.158.30/24
        }
      }

3.3> 重新启动相关服务

systemctl restart keepalived

## 三、切换验证 ##

1. 保证两台主机上面keepalived、MySQL服务都是正常启动着的.  
2. 停止主端  
2.1> 将MySQL进程杀死

systemctl stop  mysqld

2.2> 检查状态  
主端查看脚本切换日志

[root@localhost ~]# tail -100f /home/mysql/logs/mysql_switch.log
    ......
    2019-08-27 23:34:34 The scripts mycheck.sh is running ...
    2019-08-27 23:34:44 The scripts mycheck.sh is running ...
    2019-08-27 23:34:54 The scripts mycheck.sh is running ...
    2019-08-27 23:35:04 The scripts mycheck.sh is running ...
    2019-08-27 23:35:14 The scripts mycheck.sh is running ...
    2019-08-27 23:35:25 The mycheck.sh, mysql is down, after switch...

2.3> 主端查看浮动IP地址的切换过程

#浮动IP地址原先在Master端，如下：
    # 切换后，在从Master端验查看，浮动IP已被切走到备机
    # 在Slave端查看验证，确认
    # 外部ping浮动IP地址效果，有一个丢包

2.4> 主端Keepalived日志/var/log/messages如下：

Aug 27 23:35:16 localhost systemd: Stopping MySQL Server...
    Aug 27 23:35:19 localhost systemd: Stopped MySQL Server.
    Aug 27 23:35:24 localhost systemd: Stopping SYSV: Start and stop Keepalived...
    Aug 27 23:35:24 localhost Keepalived[10554]: Stopping Keepalived v1.2.13 (08/17,2019)
    Aug 27 23:35:24 localhost Keepalived_vrrp[10557]: VRRP_Instance(VI_1) sending 0 priority
    Aug 27 23:35:24 localhost Keepalived_vrrp[10557]: VRRP_Instance(VI_1) removing protocol VIPs.
    Aug 27 23:35:24 localhost Keepalived_healthcheckers[10556]: Netlink reflector reports IP 192.168.158.30 removed
    Aug 27 23:35:24 localhost keepalived: Stopping keepalived: [  OK  ]
    Aug 27 23:35:24 localhost systemd: Stopped SYSV: Start and stop Keepalived.
    Aug 27 23:35:29 localhost systemd: Started Session 23 of user root.
    Aug 27 23:35:29 localhost systemd-logind: New session 23 of user root.
    Aug 27 23:35:29 localhost systemd-logind: Removed session 23.

2.5> 备端查看切换日志/home/mysql/logs/mysql\_switch.log

2019-08-27 23:35:29 The scripts mycheck.sh is running ...
    2019-08-27 23:35:30 The mymaster.sh, slave sync ok... 
    2019-08-27 23:35:32 The mymaster.sh, Sync pos file sucess.
    2019-08-27 23:35:39 The scripts mycheck.sh is running ...

2.6> 备端查看/var/log/messages.log日志

Aug 27 23:35:28 localhost Keepalived_vrrp[23052]: VRRP_Instance(VI_1) Transition to MASTER STATE
    Aug 27 23:35:28 localhost Keepalived_vrrp[23052]: VRRP_Group(VG1) Syncing instances to MASTER state
    Aug 27 23:35:29 localhost Keepalived_vrrp[23052]: VRRP_Instance(VI_1) Entering MASTER STATE
    Aug 27 23:35:29 localhost Keepalived_vrrp[23052]: VRRP_Instance(VI_1) setting protocol VIPs.
    Aug 27 23:35:29 localhost Keepalived_healthcheckers[23051]: Netlink reflector reports IP 192.168.158.30 added
    Aug 27 23:35:29 localhost Keepalived_vrrp[23052]: VRRP_Instance(VI_1) Sending gratuitous ARPs on enp0s3 for 192.168.158.30
    Aug 27 23:35:34 localhost Keepalived_vrrp[23052]: VRRP_Instance(VI_1) Sending gratuitous ARPs on enp0s3 for 192.168.158.30

\# mysql\_config\_editor set --login-path=local --user=root --port=3306 --password\# mysql\_config\_editor print --all

## 其他网址 ##

[Keepalived与MySQL互为主从自动切换配置 - 梦徒 - 博客园][Keepalived_MySQL_ - _ -]

[MySQL--_Keepalived_IT_-CSDN]: https://knife.blog.csdn.net/article/details/127803406
[Keepalived_MySQL_ - _ -]: https://www.cnblogs.com/saneri/p/11423059.html