您的位置:新葡亰496net > 网络数据库 > MySQL达成高可用,基于keepalived搭建MySQL的高可用集

MySQL达成高可用,基于keepalived搭建MySQL的高可用集

发布时间:2019-09-22 11:12编辑:网络数据库浏览(172)

    MySQL的高可用方案一般有如下几种:

    基于keepalived搭建MySQL的高可用集群

    基于keepalived搭建MySQL的高可用集群,keepalived搭建mysql

    MySQL的高可用方案一般有如下几种:

    keepalived 双主,MHA,MMM,Heartbeat DRBD,PXC,Galera Cluster

    比较常用的是keepalived 双主,MHA和PXC。

    对于小公司,一般推荐使用keepalived 双主,简单。

    下面来部署一下

     

    配置环境:

    角色                                    主机IP                    主机名               操作系统版本     软件版本

    VIP                                    192.168.244.10

    master1                             192.168.244.145       master1            CentOS7.1       MySQL 5.6.26,Keepalived v1.2.13

    master2                             192.168.244.146       master2            CentOS7.1       MySQL 5.6.26,Keepalived v1.2.13

     

    一、 配置MySQL双主复制环境

         1. 修改配置文件

          master1中有关复制的配置如下:

    [mysqld]
    log-bin=mysql-bin
    server-id=1
    log_slave_updates=1
    

         master2

    [mysqld]
    log-bin=mysql-bin
    server-id=2
    log_slave_updates=1
    read_only=1
    

       2. 创建复制用户

        master1中创建:

    CREATE USER 'repl'@'192.168.244.146' IDENTIFIED BY 'mysql';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.146';
    

        master2中创建:

    CREATE USER 'repl'@'192.168.244.145' IDENTIFIED BY 'mysql';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.145';
    

      3. 执行CHANGE MASTER TO语句

         因是从头搭建MySQL主从复制集群,所以不需要获取全局读锁来得到二进制日志文件的位置,直接根据show master status的输出来确认。

         master1上执行:

    CHANGE MASTER TO
      MASTER_HOST='192.168.244.146',
      MASTER_USER='repl',
      MASTER_PASSWORD='mysql',
      MASTER_LOG_FILE='mysql-bin.000004',
      MASTER_LOG_POS=64729;
    

        master2上执行:

    CHANGE MASTER TO
      MASTER_HOST='192.168.244.145',
      MASTER_USER='repl',
      MASTER_PASSWORD='mysql',
      MASTER_LOG_FILE='mysql-bin.000003',
      MASTER_LOG_POS=68479;
    

        4. 分别在两个节点上执行start slave语句并通过show slave statusG查看复制是否搭建成功。

            成功标准:

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    

     

    二、 配置Keepalived

         1. 安装Keepalived

          # yum install -y keepalived

          当然,也可直接编译官方的源码包。

         2. 修改Keepalived的配置文件

         master1

         [[email protected] ~]# vim /etc/keepalived/keepalived.conf

    vrrp_script chk_mysql {
        script "/etc/keepalived/check_mysql.sh"
        interval 30         #设置检查间隔时长,可根据自己的需求自行设定
    }
    vrrp_instance VI_1 {
        state BACKUP        #通过下面的priority来区分MASTER和BACKUP,也只有如此,底下的nopreempt才有效
        interface eno16777736
        virtual_router_id 51
        priority 100
        advert_int 1
        nopreempt           #防止切换到从库后,主keepalived恢复后自动切换回主库
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        track_script {
            chk_mysql
        }
    
        virtual_ipaddress {
            192.168.244.10/24
        }
    }
    

    关于keepalived的参数的详细介绍,可参考:LVS Keepalived搭建MyCAT高可用负载均衡集群

    其中,/etc/keepalived/check_mysql.sh内容如下:

    #!/bin/bash
    
    ###判断如果上次检查的脚本还没执行完,则退出此次执行
    if [ `ps -ef|grep -w "$0"|grep -v "grep"|wc -l` -gt 2 ];then
        exit 0
    fi 
    mysql_con='mysql -uroot -p123456'
    error_log="/etc/keepalived/logs/check_mysql.err"
    
    ###定义一个简单判断mysql是否可用的函数
    function excute_query {
        ${mysql_con} -e "select 1;" 2>> ${error_log}
    }
    
    ###定义无法执行查询,且mysql服务异常时的处理函数
    function service_error {
        echo -e "`date " %F  %H:%M:%S"`    -----mysql service error,now stop keepalived-----" >> ${error_log}
        service keepalived stop &>> ${error_log}
        echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,请及时处理!" [email protected]126.com 2>> ${error_log}
        echo -e "n---------------------------------------------------------n" >> ${error_log}
    }
    
    ###定义无法执行查询,但mysql服务正常的处理函数
    function query_error {
        echo -e "`date " %F  %H:%M:%S"`    -----query error, but mysql service ok, retry after 30s-----" >> ${error_log}
        sleep 30
        excute_query
        if [ $? -ne 0 ];then
            echo -e "`date " %F  %H:%M:%S"`    -----still can't execute query-----" >> ${error_log}
    
            ###对DB1设置read_only属性
            echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 1 on DB1-----" >> ${error_log}
            mysql_con -e "set global read_only = 1;" 2>> ${error_log}
    
            ###kill掉当前客户端连接
            echo -e "`date " %F  %H:%M:%S"`    -----kill current client thread-----" >> ${error_log}
            rm -f /tmp/kill.sql &>/dev/null
            ###这里其实是一个批量kill线程的小技巧
            mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
            mysql_con -e "source /tmp/kill.sql"
            sleep 2    ###给kill一个执行和缓冲时间
            ###关闭本机keepalived       
            echo -e "`date " %F  %H:%M:%S"`    -----stop keepalived-----" >> ${error_log}
            service keepalived stop &>> ${error_log}
            echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,请及时处理!" [email protected]126.com 2>> ${error_log}
            echo -e "n---------------------------------------------------------n" >> ${error_log}
        else
            echo -e "`date " %F  %H:%M:%S"`    -----query ok after 30s-----" >> ${error_log}
            echo -e "n---------------------------------------------------------n" >> ${error_log}
        fi
    }
    
    ###检查开始: 执行查询
    excute_query
    if [ $? -ne 0 ];then
        service mysqld status &>/dev/null
        if [ $? -ne 0 ];then
            service_error
        else
            query_error
        fi
    fi
    

    通过具体的查询语句来判断数据库服务的可用性,如果查询失败,则判断mysqld进程本身的状态,如果不正常,则直接停止当前节点的keepalived,将VIP转移到另外一个节点,如果正常,则等待30s,再次执行查询语句,还是失败,则将当前的master节点设置为read_only,并kill掉当前的客户端连接,然后停止当前的keepalived。

           

           master2 

           [[email protected] ~]# vim /etc/keepalived/keepalived.conf

    ! Configuration File for keepalived
    
    vrrp_instance VI_1 {
        state BACKUP
        interface eno16777736
        virtual_router_id 51
        priority 90
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        notify_master /etc/keepalived/notify_master_mysql.sh    #此条指令告诉keepalived发现自己转为MASTER后执行的脚本
        virtual_ipaddress {
            192.168.244.10/24
        }
    }
    

    其中,/etc/keepalived/notify_master_mysql.sh的内容如下:

    #!/bin/bash
    ###当keepalived监测到本机转为MASTER状态时,执行该脚本
    
    change_log=/etc/keepalived/logs/state_change.log
    mysql_con='mysql -uroot -p123456'
    echo -e "`date " %F  %H:%M:%S"`   -----keepalived change to MASTER-----" >> $change_log
    
    slave_info() {
        ###统一定义一个函数取得slave的position、running、和log_file等信息
        ###根据函数后面所跟参数来决定取得哪些数据
        if [ $1 = slave_status ];then
            slave_stat=`${mysql_con} -e "show slave statusG;"|egrep -w "Slave_IO_Running|Slave_SQL_Running"`
            Slave_IO_Running=`echo $slave_stat|awk '{print $2}'`
            Slave_SQL_Running=`echo $slave_stat|awk '{print $4}'`
        elif [ $1 = log_file -a $2 = pos ];then
            log_file_pos=`${mysql_con} -e "show slave statusG;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
            Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
            Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
            Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
            Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
        fi
    }
    
    action() {
        ###经判断'应该&可以'切换时执行的动作
        echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 0 on DB2-----" >> $change_log
    
        ###解除read_only属性
        ${mysql_con} -e "set global read_only = 0;" 2>> $change_log
    
        echo "DB2 keepalived转为MASTER状态,线上数据库切换至DB2"|mail -s "DB2 keepalived change to MASTER"
        [email protected]126.com 2>> $change_log
    
        echo -e "---------------------------------------------------------n" >> $change_log
    }
    
    slave_info slave_status
    if [ $Slave_SQL_Running = Yes ];then
        i=0    #一个计数器
        slave_info log_file pos
            ###判断从master接收到的binlog是否全部在本地执行(这样仍无法完全确定从库已追上主库,因为无法完全保证io_thread没有延时(由网络传输问题导致的从库落后的概率很小)
        until [ $Master_Log_File = $Relay_Master_Log_File -a $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]
         do
            if [ $i -lt 10 ];then    #将等待exec_pos追上read_pos的时间限制为10s
                echo -e "`date " %F  %H:%M:%S"`    -----Relay_Master_Log_File=$Relay_Master_Log_File,Exec_Master_Log_Pos=$Exec_Master_Log_Pos is behind Master_Log_File=$Master_Log_File,Read_Master_Log_Pos=$Read_Master_Log_Pos, wait......" >> $change_log    #输出消息到日志,等待exec_pos=read_pos
                i=$(($i 1))
                sleep 1
                slave_info log_file pos
            else
                echo -e "The waits time is more than 10s,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
                action
                exit 0
            fi
        done
        action 
    
    else
        slave_info log_file pos
        echo -e "DB2's slave status is wrong,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
        action
    fi
    

     

    整个脚本的逻辑是让从的Exec_Master_Log_Pos尽可能的追上Read_Master_Log_Pos,它给了10s的限制,如果还是没有追上,则直接将master2设置为主(通过解除read_only属性),其实这里面还是有待商榷的,譬如10s的限制是否合理,还是一定需要Exec_Master_Log_Pos=Read_Master_Log_Pos才切换。

     

    当原主恢复正常后,如何将VIP从master2切回到master1中呢?

    #!/bin/bash
    ###手动执行将主库切换回DB1的操作
    
    mysql_con='mysql -uroot -p123456'
    
    echo -e "`date " %F  %H:%M:%S"`    -----change to BACKUP manually-----" >> /etc/keepalived/logs/state_change.log
    echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 1 on DB2-----" >> /etc/keepalived/logs/state_change.log
    $mysql_con -e "set global read_only = 1;" 2>> /etc/keepalived/logs/state_change.log
    
    ###kill掉当前客户端连接
    echo -e "`date " %F  %H:%M:%S"`    -----kill current client thread-----" >> /etc/keepalived/logs/state_change.log
    rm -f /tmp/kill.sql &>/dev/null
    ###这里其实是一个批量kill线程的小技巧
    $mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
    $mysql_con -e "source /tmp/kill.sql" 2>> /etc/keepalived/logs/state_change.log
    sleep 2    ###给kill一个执行和缓冲时间
    
    ###确保DB1已经追上了,下面的repl为复制所用的账户,-h后跟DB1的内网IP
    log_file_pos=`mysql -urepl -pmysql -h192.168.244.145 -e "show slave statusG;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
    Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
    Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
    Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
    Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
    until [ $Read_Master_Log_Pos = $Exec_Master_Log_Pos -a $Master_Log_File = $Relay_Master_Log_File ]
    do
        echo -e "`date " %F  %H:%M:%S"`    -----DB1 Exec_Master_Log_Pos($exec_pos) is behind Read_Master_Log_Pos($read_pos), wait......" >> /etc/keepalived/logs/state_change.log
        sleep 1
    done
    
    ###然后解除DB1的read_only属性
    echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 0 on DB1-----" >> /etc/keepalived/logs/state_change.log
    ssh 192.168.244.145 'mysql -uroot -p123456 -e "set global read_only = 0;" && /etc/init.d/keepalived start' 2>> /etc/keepalived/logs/state_change.log
    
    ###重启DB2的keepalived使VIP漂移到DB1
    echo -e "`date " %F  %H:%M:%S"`    -----make VIP move to DB1-----" >> /etc/keepalived/logs/state_change.log
    /sbin/service keepalived restart &>> /etc/keepalived/logs/state_change.log
    
    echo "DB2 keepalived转为BACKUP状态,线上数据库切换至DB1"|mail -s "DB2 keepalived change to BACKUP" [email protected]126.com 2>> /etc/keepalived/logs/state_change.log
    
    echo -e "--------------------------------------------------n" >> /etc/keepalived/logs/state_change.log
    

     

    总结:

    1. /etc/keepalived/check_mysql.sh和/etc/keepalived/notify_master_mysql.sh必须加可执行权限。

        如果前者没有加可执行权限,则master1上将不会绑定VIP,日志直接提示如下信息:

    May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Entering BACKUP STATE
    May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
    May 25 14:37:50 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Now in FAULT state
    

    2. 在Keepalived中有两种模式,分别是master->backup模式和backup->backup模式,这两种模式有什么区别呢?

        在master->backup模式下,一旦主库宕掉,虚拟IP会自动漂移到从库,当主库修复后,keepalived启动后,还会把虚拟IP抢过来,即使你设置nopreempt(不抢占)的方式抢占IP的动作也会发生。在backup->backup模式下,当主库宕掉后虚拟IP会自动漂移到从库上,当原主恢复之后重启keepalived服务,并不会抢占新主的虚拟IP,即使是优先级高于从库的优先级别,也不会抢占IP。为了减少IP的漂移次数,生产中我们通常是把修复好的主库当做新主库的备库。

    1. 本文是在MySQL主库高可用 -- 双主单活故障自动切换方案 基础上,结合自己对于MySQL的理解整理的。原文的脚本直接执行有点问题,思路有有点瑕疵,于是结合自己的实际环境,重新修改了一把。

    2. 在测试的过程中,有以下几点需要注意:

        1> master1检测脚本的逻辑是如果MySQL的服务不可用,则通过service keepalived stop命令来关闭keepalived,但在实际测试的过程中,却出现了即便执行了service keepalived stop命令,keepalived进程依然没有停止,导致MySQL的服务虽然不可用了,但VIP并不没有漂移到master2上。

             优化方案:在执行service keepalived stop后,等待5s,再次检测keepalived的状态,如果keepalived没有关闭,则直接kill掉。

        2>  keepalived的日志默认是输出到/var/log/messages中,这样不便于查看。如何自定义keepalived的日志输出文件呢?

              如果是用service启动的,修改/etc/sysconfig/keepalived文件

    KEEPALIVED_OPTIONS="-D -d -S 0" 
    

             如果不是,则启动的时候指定以上参数,如:

    /usr/local/keepalived/sbin/keepalived -d -D -S 0 
    

            修改/etc/syslog.conf

    # keepalived -S 0 
    local0.*                                                /var/log/keepalived.log
    

           重启syslog

           RHEL 5&6:service syslog restart

           RHEL 7:service rsyslog restart 

            

     

      

     

     

     

            

           

     

     

     

       

      

         

          

        

     

    MySQL的高可用方案一般有如下几种: keepalived 双主,MHA,MMM,Heartbeat DRBD,PXC,Galera...

    MySQL的高可用方案有很多,比如Cluster,MMM,MHA,DRBD等,这些都比较复杂,我前面的文章也有介绍。最近Oracle官方也推出了Fabric。有时我们不需要这么复杂的环境,这些方案各有优劣。有时简单的且我们能够hold住的方案才是适合我们的。比如MySQL Replication,然后加上各种高可用软件,比如Keepalived等,就能实现我们需要的高可用环境。

    keepalived 双主,MHA,MMM,Heartbeat DRBD,PXC,Galera Cluster

     原文:

    MySQL架构为master/slave,当master故障时,vip漂移到slave上。提供服务。当然也可以设置为双master,但是不是每个方案都是完美的。这里设置为双master有个问题需要注意,比如,当用户发表文章时,由于此时主机的压力很大时,假设落后2000秒,那么这台主机宕机了,另一台主机接管(vip漂移到从机上)时,因为同步延时大,用户刚才发表的文章还没复制过来,于是用户又发表了一遍文章,当原来的master修复好后,由于I/O和SQL线程还处于开启状态,因此还会继续同步刚才没有同步复制完的数据,这时有可能把用户新发表的文章更改掉。这里所以采用master/slave架构。在这种架构中,故障切换以后,采取手动操作的方式与新的master进行复制。

    比较常用的是keepalived 双主,MHA和PXC。

    MySQL的高可用方案一般有如下几种:

    简单环境如下:

    对于小公司,一般推荐使用keepalived 双主,简单。

    keepalived 双主,MHA,MMM,Heartbeat DRBD,PXC,Galera Cluster

    新葡亰496net 1

    下面来部署一下

    比较常用的是keepalived 双主,MHA和PXC。

    master     192.168.0.100
    slave      192.168.0.101
    VIP        192.168.0.88
    

     

    对于小公司,一般推荐使用keepalived 双主,简单。

    主从复制环境的搭建我这里就不演示了。有需要的同学自己看看官方手册MySQL达成高可用,基于keepalived搭建MySQL的高可用集群。。下面直接介绍keepalived的安装及配置使用。

    配置环境:

    下面来部署一下

    1.keepalived软件安装(主从操作一样)

    角色                                    主机IP                    主机名               操作系统版本     软件版本

     

    [root@mysql-server-01 ~]# wget -q http://www.keepalived.org/software/keepalived-1.2.13.tar.gz
    [root@mysql-server-01 ~]# tar xf keepalived-1.2.13.tar.gz
    [root@mysql-server-01 ~]# cd keepalived-1.2.13
    [root@mysql-server-01 keepalived-1.2.13]# ./configure && make && make install
    
    [root@mysql-server-01 keepalived]# cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
    [root@mysql-server-01 keepalived]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
    [root@mysql-server-01 keepalived]# mkdir /etc/keepalived
    [root@mysql-server-01 keepalived]# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
    [root@mysql-server-01 keepalived]# cp /usr/local/sbin/keepalived /usr/sbin/
    [root@mysql-server-01 keepalived]# chkconfig --add keepalived
    [root@mysql-server-01 keepalived]# chkconfig --level 345 keepalived on
    

    VIP                                    192.168.244.10

    配置环境:

    2.主从的配置文件修改(主的keepalived配置文件修改后如下,其实不相同的就优先级而已)
    master的keepalived配置文件如下

    master1                             192.168.244.145       master1            CentOS7.1       MySQL 5.6.26,Keepalived v1.2.13

    角色                                    主机IP                    主机名               操作系统版本     软件版本

    [root@mysql-server-01 keepalived]# cat keepalived.conf
    global_defs {
       router_id MySQL-HA
    } 
    
    vrrp_script check_run {
    script "/data/sh/mysql_check.sh"
    interval 300
    }
    
    vrrp_sync_group VG1 {
    group {
    VI_1
    }
    }
    
    vrrp_instance VI_1 {
        state BACKUP
        interface eth1  
        virtual_router_id 51
        priority 100  
        advert_int 1
        nopreempt
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        track_script {
        check_run
        }
    
        notify_master /data/sh/master.sh
        notify_backup /data/sh/backup.sh
        notify_stop /data/sh/stop.sh
    
        virtual_ipaddress {
            192.168.0.88
        }
    }
    
    [root@mysql-server-01 keepalived]# 
    

    master2                             192.168.244.146       master2            CentOS7.1       MySQL 5.6.26,Keepalived v1.2.13

    VIP                                    192.168.244.10

    slave的keepalived配置文件修改以后如下:

     

    master1                             192.168.244.145       master1            CentOS7.1       MySQL 5.6.26,Keepalived v1.2.13

    [root@mysql-server-02 keepalived]# cat keepalived.conf
    global_defs {
       router_id MySQL-HA
    } 
    
    vrrp_script check_run {
    script "/data/sh/mysql_check.sh"
    interval 300
    }
    
    vrrp_sync_group VG1 {
    group {
    VI_1
    }
    }
    
    vrrp_instance VI_1 {
        state BACKUP
        interface eth1
        virtual_router_id 51
        priority 90 
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        track_script {
        check_run
        }
    
        notify_master /data/sh/master.sh
        notify_backup /data/sh/backup.sh
        notify_stop /data/sh/stop.sh
    
        virtual_ipaddress {
            192.168.0.88
        }
    }
    [root@mysql-server-02 keepalived]# 
    

    一、 配置MySQL双主复制环境

    master2                             192.168.244.146       master2            CentOS7.1       MySQL 5.6.26,Keepalived v1.2.13

    其中有几个关键参数的地方:
    notify_master:状态改变为master以后执行的脚本。

         1. 修改配置文件

     

    notify_backup: 状态改变为backup以后执行的脚本。

          master1中有关复制的配置如下:

    一、 配置MySQL双主复制环境

    notify_fault: 状态改变为fault后执行的脚本。

    [mysqld]
    log-bin=mysql-bin
    server-id=1
    log_slave_updates=1
    

         1. 修改配置文件

    notify_stop: VRRP停止以后执行的脚本。

         master2

          master1中有关复制的配置如下:

    state backup:我们都设置为了backup,就是为了发生故障以后不会自动切换。

    [mysqld]
    log-bin=mysql-bin
    server-id=2
    log_slave_updates=1
    read_only=1
    
    [mysqld]
    log-bin=mysql-bin
    server-id=1
    log_slave_updates=1
    

    nopreempt: 不进行抢占操作

       2. 创建复制用户

    新葡亰496net,     master2

    其中用到了这4个脚本:backup.sh  master.sh  mysql_check.sh  stop.sh

        master1中创建:

    [mysqld]
    log-bin=mysql-bin
    server-id=2
    log_slave_updates=1
    read_only=1
    

    mysql_check.sh是为了检查mysqld进程是否存活的脚本,当发现连接不上mysql,自动把keepalived进程干掉,让VIP进行漂移。

    CREATE USER 'repl'@'192.168.244.146' IDENTIFIED BY 'mysql';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.146';
    

       2. 创建复制用户

    下面的脚本主从服务器上面都有,只是从服务器上面的master.sh有些不一样。添加了当slave提升为主库时,发送邮件通知。

        master2中创建:

        master1中创建:

    新葡亰496net 2新葡亰496net 3

    CREATE USER 'repl'@'192.168.244.145' IDENTIFIED BY 'mysql';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.145';
    
    CREATE USER 'repl'@'192.168.244.146' IDENTIFIED BY 'mysql';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.146';
    
    [root@mysql-server-01 sh]# cat mysql_check.sh 
    #!/bin/bash
    
    . /root/.bash_profile
    
    count=1
    
    while true
    do
    
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14520.sock -e "show status;" > /dev/null 2>&1
    i=$?
    ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
    j=$?
    if [ $i = 0 ] && [ $j = 0 ]
    then
       exit 0
    else
       if [ $i = 1 ] && [ $j = 0 ]
       then
           exit 0
       else
            if [ $count -gt 5 ]
            then
                  break
            fi
       let count  
       continue
       fi
    fi
    
    done
    
    /etc/init.d/keepalived stop
    [root@mysql-server-01 sh]# 
    

      3. 执行CHANGE MASTER TO语句

        master2中创建:

    View Code

         因是从头搭建MySQL主从复制集群,所以不需要获取全局读锁来得到二进制日志文件的位置,直接根据show master status的输出来确认。

    CREATE USER 'repl'@'192.168.244.145' IDENTIFIED BY 'mysql';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.145';
    

    master.sh的作用是状态改为master以后执行的脚本。首先判断复制是否有延迟,如果有延迟,等1分钟后,不论是否有延迟。都跳过,并停止复制。并且授权账号,记录binlog和pos点。

         master1上执行:

      3. 执行CHANGE MASTER TO语句

    新葡亰496net 4新葡亰496net 5

    CHANGE MASTER TO
      MASTER_HOST='192.168.244.146',
      MASTER_USER='repl',
      MASTER_PASSWORD='mysql',
      MASTER_LOG_FILE='mysql-bin.000004',
      MASTER_LOG_POS=64729;
    

         因是从头搭建MySQL主从复制集群,所以不需要获取全局读锁来得到二进制日志文件的位置,直接根据show master status的输出来确认。

    [root@mysql-server-02 sh]# cat master.sh 
    #!/bin/bash
    
    . /root/.bash_profile
    
    Master_Log_File=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave statusG" | grep -w Master_Log_File | awk -F": " '{print $2}')
    Relay_Master_Log_File=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave statusG" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
    Read_Master_Log_Pos=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave statusG" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
    Exec_Master_Log_Pos=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave statusG" | 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 "ok"
       break
    else
       sleep 1
    
       if [ $i -gt 60 ]
       then
          break
       fi
       continue
       let i  
    fi
    done
    
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "stop slave;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_support_xa=0;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global sync_binlog=0;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_flush_log_at_trx_commit=0;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master status;" > /tmp/master_status_$(date " %y%m%d-%H%M").txt
    
    
    [root@mysql-server-02 sh]# 
    

        master2上执行:

         master1上执行:

    View Code

    CHANGE MASTER TO
      MASTER_HOST='192.168.244.145',
      MASTER_USER='repl',
      MASTER_PASSWORD='mysql',
      MASTER_LOG_FILE='mysql-bin.000003',
      MASTER_LOG_POS=68479;
    
    CHANGE MASTER TO
      MASTER_HOST='192.168.244.146',
      MASTER_USER='repl',
      MASTER_PASSWORD='mysql',
      MASTER_LOG_FILE='mysql-bin.000004',
      MASTER_LOG_POS=64729;
    

    slave上的master.sh

        4. 分别在两个节点上执行start slave语句并通过show slave statusG查看复制是否搭建成功。

        master2上执行:

    新葡亰496net 6新葡亰496net 7

            成功标准:

    CHANGE MASTER TO
      MASTER_HOST='192.168.244.145',
      MASTER_USER='repl',
      MASTER_PASSWORD='mysql',
      MASTER_LOG_FILE='mysql-bin.000003',
      MASTER_LOG_POS=68479;
    
    [root@mysql-server-02 sh]# cat master.sh 
    #!/bin/bash
    
    . /root/.bash_profile
    
    Master_Log_File=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave statusG" | grep -w Master_Log_File | awk -F": " '{print $2}')
    Relay_Master_Log_File=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave statusG" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
    Read_Master_Log_Pos=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave statusG" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
    Exec_Master_Log_Pos=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show slave statusG" | 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 "ok"
       break
    else
       sleep 1
    
       if [ $i -gt 60 ]
       then
          break
       fi
       continue
       let i  
    fi
    done
    
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "stop slave;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_support_xa=0;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global sync_binlog=0;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_flush_log_at_trx_commit=0;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master status;" > /tmp/master_status_$(date " %y%m%d-%H%M").txt
    
    
    #当slave提升为主以后,发送邮件
    echo "#####################################" > /tmp/status
    echo "salve已经提升为主库,请进行检查!" >> /tmp/status
    ifconfig | sed -n '/inet /{s/.*addr://;s/ .*//;p}' | grep -v 127.0.0.1 >> /tmp/status
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -Nse "show variables like 'port'" >> /tmp/status
    echo "#####################################" >> /tmp/status
    master=`cat /tmp/status`
    echo "$master" | mutt -s "slave to primary!!!" 13143753516@139.com
    
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    

        4. 分别在两个节点上执行start slave语句并通过show slave statusG查看复制是否搭建成功。

    View Code

     

            成功标准:

    脚本中检查复制是否延时的思想如下:
    1、首先看 Relay_Master_Log_File 和 Master_Log_File 是否有差异
    2、如果Relay_Master_Log_File 和 Master_Log_File 有差异的话,那说明延迟很大了
    3、如果Relay_Master_Log_File 和 Master_Log_File 没有差异,再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异

    二、 配置Keepalived

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    

    而不是通过Seconds_Behind_Master去判断,该值表示slave上SQL线程和IO线程之间的延迟,实际上还要考虑到 Master_Log_File 和 Relay_Master_Log_MySQL达成高可用,基于keepalived搭建MySQL的高可用集群。File 是否有差距,更严谨的则是要同时在master上执行show master status进行对比。这也是MHA在切换过程中可以做到的。MMM的切换也只是在从库上执行了show slave status。所以数据一致性要求还是MHA给力。扯远了。^_^

         1. 安装Keepalived

     

    backup.sh脚本的作用是状态改变为backup以后执行的脚本。

          # yum install -y keepalived

    二、 配置Keepalived

    新葡亰496net 8新葡亰496net 9

          当然,也可直接编译官方的源码包。

         1. 安装Keepalived

    [root@mysql-server-02 sh]# cat backup.sh 
    #!/bin/bash
    
    . /root/.bash_profile
    
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global event_scheduler=0;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_support_xa=0;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global sync_binlog=0;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_flush_log_at_trx_commit=0;"
    

         2. 修改Keepalived的配置文件

          # yum install -y keepalived

    View Code

         master1

          当然,也可直接编译官方的源码包。

    stop.sh 表示keepalived停止以后需要执行的脚本。更改密码,设置参数,检查是否还有写入操作,最后无论是否执行完毕,都退出。

         [root@master1 ~]# vim /etc/keepalived/keepalived.conf

         2. 修改Keepalived的配置文件

    新葡亰496net 10新葡亰496net 11

    vrrp_script chk_mysql {
        script "/etc/keepalived/check_mysql.sh"
        interval 30         #设置检查间隔时长,可根据自己的需求自行设定
    }
    vrrp_instance VI_1 {
        state BACKUP        #通过下面的priority来区分MASTER和BACKUP,也只有如此,底下的nopreempt才有效
        interface eno16777736
        virtual_router_id 51
        priority 100
        advert_int 1
        nopreempt           #防止切换到从库后,主keepalived恢复后自动切换回主库
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        track_script {
            chk_mysql
        }
    
        virtual_ipaddress {
            192.168.244.10/24
        }
    }
    

         master1

    [root@mysql-server-02 sh]# cat stop.sh 
    #!/bin/bash
    
    . /root/.bash_profile
    
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '1q2w3e4r';flush privileges;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_support_xa=1;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global sync_binlog=1;"
    mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "set global innodb_flush_log_at_trx_commit=1;"
    
    M_File1=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master statusG" | awk -F': ' '/File/{print $2}')
    M_Position1=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master statusG" | awk -F': ' '/Position/{print $2}')
    sleep 1
    M_File2=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master statusG" | awk -F': ' '/File/{print $2}')
    M_Position2=$(mysql -uroot -pmsandbox -S /tmp/mysql_sandbox14521.sock -e "show master statusG" | awk -F': ' '/Position/{print $2}')
    
    i=1
    
    while true
    do
    
    if [ $M_File1 = $M_File1 ] && [ $M_Position1 -eq $M_Position2 ]
    then
       echo "ok"
       break
    else
       sleep 1
    
       if [ $i -gt 60 ]
       then
          break
       fi
       continue
       let i  
    fi
    done
    
    
    [root@mysql-server-02 sh]# 
    

    关于keepalived的参数的详细介绍,可参考:LVS Keepalived搭建MyCAT高可用负载均衡集群

         [root@master1 ~]# vim /etc/keepalived/keepalived.conf

    View Code

    其中,/etc/keepalived/check_mysql.sh内容如下:

    新葡亰496net 12

    到这里基本就介绍完了。最后我们先看主从复制是否正常,如果正常,然后分别启动keepalived,然后进行故障切换测试。

    #!/bin/bash
    
    ###判断如果上次检查的脚本还没执行完,则退出此次执行
    if [ `ps -ef|grep -w "$0"|grep -v "grep"|wc -l` -gt 2 ];then
        exit 0
    fi 
    mysql_con='mysql -uroot -p123456'
    error_log="/etc/keepalived/logs/check_mysql.err"
    
    ###定义一个简单判断mysql是否可用的函数
    function excute_query {
        ${mysql_con} -e "select 1;" 2>> ${error_log}
    }
    
    ###定义无法执行查询,且mysql服务异常时的处理函数
    function service_error {
        echo -e "`date " %F  %H:%M:%S"`    -----mysql service error,now stop keepalived-----" >> ${error_log}
        service keepalived stop &>> ${error_log}
        echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,请及时处理!" slowtech@126.com 2>> ${error_log}
        echo -e "n---------------------------------------------------------n" >> ${error_log}
    }
    
    ###定义无法执行查询,但mysql服务正常的处理函数
    function query_error {
        echo -e "`date " %F  %H:%M:%S"`    -----query error, but mysql service ok, retry after 30s-----" >> ${error_log}
        sleep 30
        excute_query
        if [ $? -ne 0 ];then
            echo -e "`date " %F  %H:%M:%S"`    -----still can't execute query-----" >> ${error_log}
    
            ###对DB1设置read_only属性
            echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 1 on DB1-----" >> ${error_log}
            mysql_con -e "set global read_only = 1;" 2>> ${error_log}
    
            ###kill掉当前客户端连接
            echo -e "`date " %F  %H:%M:%S"`    -----kill current client thread-----" >> ${error_log}
            rm -f /tmp/kill.sql &>/dev/null
            ###这里其实是一个批量kill线程的小技巧
            mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
            mysql_con -e "source /tmp/kill.sql"
            sleep 2    ###给kill一个执行和缓冲时间
            ###关闭本机keepalived       
            echo -e "`date " %F  %H:%M:%S"`    -----stop keepalived-----" >> ${error_log}
            service keepalived stop &>> ${error_log}
            echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,请及时处理!" slowtech@126.com 2>> ${error_log}
            echo -e "n---------------------------------------------------------n" >> ${error_log}
        else
            echo -e "`date " %F  %H:%M:%S"`    -----query ok after 30s-----" >> ${error_log}
            echo -e "n---------------------------------------------------------n" >> ${error_log}
        fi
    }
    
    ###检查开始: 执行查询
    excute_query
    if [ $? -ne 0 ];then
        service mysqld status &>/dev/null
        if [ $? -ne 0 ];then
            service_error
        else
            query_error
        fi
    fi
    
    vrrp_script chk_mysql {
        script "/etc/keepalived/check_mysql.sh"
        interval 30         #设置检查间隔时长,可根据自己的需求自行设定
    }
    vrrp_instance VI_1 {
        state BACKUP        #通过下面的priority来区分MASTER和BACKUP,也只有如此,底下的nopreempt才有效
        interface eno16777736
        virtual_router_id 51
        priority 100
        advert_int 1
        nopreempt           #防止切换到从库后,主keepalived恢复后自动切换回主库
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        track_script {
            chk_mysql
        }
    
        virtual_ipaddress {
            192.168.244.10/24
        }
    }
    

    slave状态:

    通过具体的查询语句来判断数据库服务的可用性,如果查询失败,则判断mysqld进程本身的状态,如果不正常,则直接停止当前节点的keepalived,将VIP转移到另外一个节点,如果正常,则等待30s,再次执行查询语句,还是失败,则将当前的master节点设置为read_only,并kill掉当前的客户端连接,然后停止当前的keepalived。

    新葡亰496net 13

    node2 [localhost] {msandbox} ((none)) > pager cat | egrep 'Master_Log_File|Relay_Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Running'
    PAGER set to 'cat | egrep 'Master_Log_File|Relay_Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Running''
    node2 [localhost] {msandbox} ((none)) > show slave statusG
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 409
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
              Exec_Master_Log_Pos: 409
          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
    1 row in set (0.00 sec)
    
    node2 [localhost] {msandbox} ((none)) > 
    

           

    关于keepalived的参数的详细介绍,可参考:LVS Keepalived搭建MyCAT高可用负载均衡集群

    master 状态:

           master2 

    其中,/etc/keepalived/check_mysql.sh内容如下:

    node1 [localhost] {msandbox} ((none)) > show master status;
     ------------------ ---------- -------------- ------------------ ------------------- 
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
     ------------------ ---------- -------------- ------------------ ------------------- 
    | mysql-bin.000001 |      409 |              |                  |                   |
     ------------------ ---------- -------------- ------------------ ------------------- 
    1 row in set (0.00 sec)
    
    node1 [localhost] {msandbox} ((none)) > 
    

           [root@master2 ~]# vim /etc/keepalived/keepalived.conf

    新葡亰496net 14

    根据我前面给的判断条件,可以看出我的复制没有任何延时。
    下面分别在master上和slave上启动keepalived进程。以及查看日志(上面的查看只是给大家说明如何判断复制是否延迟)

    ! Configuration File for keepalived
    
    vrrp_instance VI_1 {
        state BACKUP
        interface eno16777736
        virtual_router_id 51
        priority 90
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        notify_master /etc/keepalived/notify_master_mysql.sh    #此条指令告诉keepalived发现自己转为MASTER后执行的脚本
        virtual_ipaddress {
            192.168.244.10/24
        }
    }
    
    #!/bin/bash
    
    ###判断如果上次检查的脚本还没执行完,则退出此次执行
    if [ `ps -ef|grep -w "$0"|grep -v "grep"|wc -l` -gt 2 ];then
        exit 0
    fi 
    mysql_con='mysql -uroot -p123456'
    error_log="/etc/keepalived/logs/check_mysql.err"
    
    ###定义一个简单判断mysql是否可用的函数
    function excute_query {
        ${mysql_con} -e "select 1;" 2>> ${error_log}
    }
    
    ###定义无法执行查询,且mysql服务异常时的处理函数
    function service_error {
        echo -e "`date " %F  %H:%M:%S"`    -----mysql service error,now stop keepalived-----" >> ${error_log}
        service keepalived stop &>> ${error_log}
        echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,请及时处理!" slowtech@126.com 2>> ${error_log}
        echo -e "n---------------------------------------------------------n" >> ${error_log}
    }
    
    ###定义无法执行查询,但mysql服务正常的处理函数
    function query_error {
        echo -e "`date " %F  %H:%M:%S"`    -----query error, but mysql service ok, retry after 30s-----" >> ${error_log}
        sleep 30
        excute_query
        if [ $? -ne 0 ];then
            echo -e "`date " %F  %H:%M:%S"`    -----still can't execute query-----" >> ${error_log}
    
            ###对DB1设置read_only属性
            echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 1 on DB1-----" >> ${error_log}
            mysql_con -e "set global read_only = 1;" 2>> ${error_log}
    
            ###kill掉当前客户端连接
            echo -e "`date " %F  %H:%M:%S"`    -----kill current client thread-----" >> ${error_log}
            rm -f /tmp/kill.sql &>/dev/null
            ###这里其实是一个批量kill线程的小技巧
            mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
            mysql_con -e "source /tmp/kill.sql"
            sleep 2    ###给kill一个执行和缓冲时间
            ###关闭本机keepalived       
            echo -e "`date " %F  %H:%M:%S"`    -----stop keepalived-----" >> ${error_log}
            service keepalived stop &>> ${error_log}
            echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止,请及时处理!" slowtech@126.com 2>> ${error_log}
            echo -e "n---------------------------------------------------------n" >> ${error_log}
        else
            echo -e "`date " %F  %H:%M:%S"`    -----query ok after 30s-----" >> ${error_log}
            echo -e "n---------------------------------------------------------n" >> ${error_log}
        fi
    }
    
    ###检查开始: 执行查询
    excute_query
    if [ $? -ne 0 ];then
        service mysqld status &>/dev/null
        if [ $? -ne 0 ];then
            service_error
        else
            query_error
        fi
    fi
    

    master

    其中,/etc/keepalived/notify_master_mysql.sh的内容如下:

    新葡亰496net 15

    新葡亰496net 16新葡亰496net 17

    #!/bin/bash
    ###当keepalived监测到本机转为MASTER状态时,执行该脚本
    
    change_log=/etc/keepalived/logs/state_change.log
    mysql_con='mysql -uroot -p123456'
    echo -e "`date " %F  %H:%M:%S"`   -----keepalived change to MASTER-----" >> $change_log
    
    slave_info() {
        ###统一定义一个函数取得slave的position、running、和log_file等信息
        ###根据函数后面所跟参数来决定取得哪些数据
        if [ $1 = slave_status ];then
            slave_stat=`${mysql_con} -e "show slave statusG;"|egrep -w "Slave_IO_Running|Slave_SQL_Running"`
            Slave_IO_Running=`echo $slave_stat|awk '{print $2}'`
            Slave_SQL_Running=`echo $slave_stat|awk '{print $4}'`
        elif [ $1 = log_file -a $2 = pos ];then
            log_file_pos=`${mysql_con} -e "show slave statusG;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
            Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
            Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
            Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
            Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
        fi
    }
    
    action() {
        ###经判断'应该&可以'切换时执行的动作
        echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 0 on DB2-----" >> $change_log
    
        ###解除read_only属性
        ${mysql_con} -e "set global read_only = 0;" 2>> $change_log
    
        echo "DB2 keepalived转为MASTER状态,线上数据库切换至DB2"|mail -s "DB2 keepalived change to MASTER"
        slowtech@126.com 2>> $change_log
    
        echo -e "---------------------------------------------------------n" >> $change_log
    }
    
    slave_info slave_status
    if [ $Slave_SQL_Running = Yes ];then
        i=0    #一个计数器
        slave_info log_file pos
            ###判断从master接收到的binlog是否全部在本地执行(这样仍无法完全确定从库已追上主库,因为无法完全保证io_thread没有延时(由网络传输问题导致的从库落后的概率很小)
        until [ $Master_Log_File = $Relay_Master_Log_File -a $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]
         do
            if [ $i -lt 10 ];then    #将等待exec_pos追上read_pos的时间限制为10s
                echo -e "`date " %F  %H:%M:%S"`    -----Relay_Master_Log_File=$Relay_Master_Log_File,Exec_Master_Log_Pos=$Exec_Master_Log_Pos is behind Master_Log_File=$Master_Log_File,Read_Master_Log_Pos=$Read_Master_Log_Pos, wait......" >> $change_log    #输出消息到日志,等待exec_pos=read_pos
                i=$(($i 1))
                sleep 1
                slave_info log_file pos
            else
                echo -e "The waits time is more than 10s,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
                action
                exit 0
            fi
        done
        action 
    
    else
        slave_info log_file pos
        echo -e "DB2's slave status is wrong,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
        action
    fi
    

    通过具体的查询语句来判断数据库服务的可用性,如果查询失败,则判断mysqld进程本身的状态,如果不正常,则直接停止当前节点的keepalived,将VIP转移到另外一个节点,如果正常,则等待30s,再次执行查询语句,还是失败,则将当前的master节点设置为read_only,并kill掉当前的客户端连接,然后停止当前的keepalived。

    [root@mysql-server-01 sh]# /etc/init.d/keepalived start
    Starting keepalived:                                       [  OK  ]
    [root@mysql-server-01 sh]# tail -f /var/log/messages
    Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Netlink reflector reports IP 192.168.87.134 added
    Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Netlink reflector reports IP 192.168.0.100 added
    Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Registering Kernel netlink reflector
    Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Registering Kernel netlink command channel
    Jul 20 20:48:03 mysql-server-01 Keepalived_vrrp[13040]: Registering gratuitous ARP shared channel
    Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.0.100 added
    Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.87.134 added
    Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.0.100 added
    Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Registering Kernel netlink reflector
    Jul 20 20:48:03 mysql-server-01 Keepalived_healthcheckers[13039]: Registering Kernel netlink command channel
    Jul 20 20:48:23 mysql-server-01 Keepalived_healthcheckers[13039]: Opening file '/etc/keepalived/keepalived.conf'.
    Jul 20 20:48:23 mysql-server-01 Keepalived_healthcheckers[13039]: Configuration is using : 6489 Bytes
    Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: Opening file '/etc/keepalived/keepalived.conf'.
    Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: Configuration is using : 66476 Bytes
    Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: Using LinkWatch kernel netlink reflector...
    Jul 20 20:48:23 mysql-server-01 Keepalived_healthcheckers[13039]: Using LinkWatch kernel netlink reflector...
    Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Entering BACKUP STATE
    Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]
    Jul 20 20:48:23 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Script(check_run) succeeded
    Jul 20 20:48:27 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Transition to MASTER STATE
    Jul 20 20:48:27 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Group(VG1) Syncing instances to MASTER state
    Jul 20 20:48:28 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Entering MASTER STATE
    Jul 20 20:48:28 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) setting protocol VIPs.
    Jul 20 20:48:28 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88
    Jul 20 20:48:28 mysql-server-01 Keepalived_healthcheckers[13039]: Netlink reflector reports IP 192.168.0.88 added
    Jul 20 20:48:33 mysql-server-01 Keepalived_vrrp[13040]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88
    

     

           

    View Code

    整个脚本的逻辑是让从的Exec_Master_Log_Pos尽可能的追上Read_Master_Log_Pos,它给了10s的限制,如果还是没有追上,则直接将master2设置为主(通过解除read_only属性),其实这里面还是有待商榷的,譬如10s的限制是否合理,还是一定需要Exec_Master_Log_Pos=Read_Master_Log_Pos才切换。

           master2 

    slave

     

           [root@master2 ~]# vim /etc/keepalived/keepalived.conf

    新葡亰496net 18新葡亰496net 19

    当原主恢复正常后,如何将VIP从master2切回到master1中呢?

    新葡亰496net 20

    [root@mysql-server-02 tmp]# /etc/init.d/keepalived start
    Starting keepalived:                                       [  OK  ]
    [root@mysql-server-02 tmp]# tail -f /var/log/messages
    Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc91 added
    Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Netlink reflector reports IP 192.168.0.101 added
    Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc91 added
    Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc9b added
    Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Registering Kernel netlink reflector
    Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Netlink reflector reports IP fe80::20c:29ff:fefe:dc9b added
    Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Registering Kernel netlink reflector
    Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Registering Kernel netlink command channel
    Jul 20 20:48:14 mysql-server-02 Keepalived_healthcheckers[10679]: Registering Kernel netlink command channel
    Jul 20 20:48:14 mysql-server-02 Keepalived_vrrp[10680]: Registering gratuitous ARP shared channel
    Jul 20 20:48:34 mysql-server-02 Keepalived_healthcheckers[10679]: Opening file '/etc/keepalived/keepalived.conf'.
    Jul 20 20:48:34 mysql-server-02 Keepalived_healthcheckers[10679]: Configuration is using : 6467 Bytes
    Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: Opening file '/etc/keepalived/keepalived.conf'.
    Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: Configuration is using : 66454 Bytes
    Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: Using LinkWatch kernel netlink reflector...
    Jul 20 20:48:34 mysql-server-02 Keepalived_healthcheckers[10679]: Using LinkWatch kernel netlink reflector...
    Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: VRRP_Instance(VI_1) Entering BACKUP STATE
    Jul 20 20:48:34 mysql-server-02 Keepalived_vrrp[10680]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]
    Jul 20 20:48:35 mysql-server-02 Keepalived_vrrp[10680]: VRRP_Script(check_run) succeeded
    
    #!/bin/bash
    ###手动执行将主库切换回DB1的操作
    
    mysql_con='mysql -uroot -p123456'
    
    echo -e "`date " %F  %H:%M:%S"`    -----change to BACKUP manually-----" >> /etc/keepalived/logs/state_change.log
    echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 1 on DB2-----" >> /etc/keepalived/logs/state_change.log
    $mysql_con -e "set global read_only = 1;" 2>> /etc/keepalived/logs/state_change.log
    
    ###kill掉当前客户端连接
    echo -e "`date " %F  %H:%M:%S"`    -----kill current client thread-----" >> /etc/keepalived/logs/state_change.log
    rm -f /tmp/kill.sql &>/dev/null
    ###这里其实是一个批量kill线程的小技巧
    $mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
    $mysql_con -e "source /tmp/kill.sql" 2>> /etc/keepalived/logs/state_change.log
    sleep 2    ###给kill一个执行和缓冲时间
    
    ###确保DB1已经追上了,下面的repl为复制所用的账户,-h后跟DB1的内网IP
    log_file_pos=`mysql -urepl -pmysql -h192.168.244.145 -e "show slave statusG;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
    Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
    Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
    Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
    Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
    until [ $Read_Master_Log_Pos = $Exec_Master_Log_Pos -a $Master_Log_File = $Relay_Master_Log_File ]
    do
        echo -e "`date " %F  %H:%M:%S"`    -----DB1 Exec_Master_Log_Pos($exec_pos) is behind Read_Master_Log_Pos($read_pos), wait......" >> /etc/keepalived/logs/state_change.log
        sleep 1
    done
    
    ###然后解除DB1的read_only属性
    echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 0 on DB1-----" >> /etc/keepalived/logs/state_change.log
    ssh 192.168.244.145 'mysql -uroot -p123456 -e "set global read_only = 0;" && /etc/init.d/keepalived start' 2>> /etc/keepalived/logs/state_change.log
    
    ###重启DB2的keepalived使VIP漂移到DB1
    echo -e "`date " %F  %H:%M:%S"`    -----make VIP move to DB1-----" >> /etc/keepalived/logs/state_change.log
    /sbin/service keepalived restart &>> /etc/keepalived/logs/state_change.log
    
    echo "DB2 keepalived转为BACKUP状态,线上数据库切换至DB1"|mail -s "DB2 keepalived change to BACKUP" slowtech@126.com 2>> /etc/keepalived/logs/state_change.log
    
    echo -e "--------------------------------------------------n" >> /etc/keepalived/logs/state_change.log
    
    ! Configuration File for keepalived
    
    vrrp_instance VI_1 {
        state BACKUP
        interface eno16777736
        virtual_router_id 51
        priority 90
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        notify_master /etc/keepalived/notify_master_mysql.sh    #此条指令告诉keepalived发现自己转为MASTER后执行的脚本
        virtual_ipaddress {
            192.168.244.10/24
        }
    }
    

    View Code

     

    新葡亰496net 21

    可以看见VIP已经绑定在了master上,执行ip addr看看是否有这个VIP

    总结:

    其中,/etc/keepalived/notify_master_mysql.sh的内容如下:

    [root@mysql-server-01 ~]# ip addr | grep eth1
    3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
        inet 192.168.0.100/24 brd 192.168.0.255 scope global eth1
        inet 192.168.0.88/32 scope global eth1
    [root@mysql-server-01 ~]# 
    

    1. /etc/keepalived/check_mysql.sh和/etc/keepalived/notify_master_mysql.sh必须加可执行权限。

    新葡亰496net 22

    可以看见vip也已经绑定成功。

        如果前者没有加可执行权限,则master1上将不会绑定VIP,日志直接提示如下信息:

    #!/bin/bash
    ###当keepalived监测到本机转为MASTER状态时,执行该脚本
    
    change_log=/etc/keepalived/logs/state_change.log
    mysql_con='mysql -uroot -p123456'
    echo -e "`date " %F  %H:%M:%S"`   -----keepalived change to MASTER-----" >> $change_log
    
    slave_info() {
        ###统一定义一个函数取得slave的position、running、和log_file等信息
        ###根据函数后面所跟参数来决定取得哪些数据
        if [ $1 = slave_status ];then
            slave_stat=`${mysql_con} -e "show slave statusG;"|egrep -w "Slave_IO_Running|Slave_SQL_Running"`
            Slave_IO_Running=`echo $slave_stat|awk '{print $2}'`
            Slave_SQL_Running=`echo $slave_stat|awk '{print $4}'`
        elif [ $1 = log_file -a $2 = pos ];then
            log_file_pos=`${mysql_con} -e "show slave statusG;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
            Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
            Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
            Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
            Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
        fi
    }
    
    action() {
        ###经判断'应该&可以'切换时执行的动作
        echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 0 on DB2-----" >> $change_log
    
        ###解除read_only属性
        ${mysql_con} -e "set global read_only = 0;" 2>> $change_log
    
        echo "DB2 keepalived转为MASTER状态,线上数据库切换至DB2"|mail -s "DB2 keepalived change to MASTER"
        slowtech@126.com 2>> $change_log
    
        echo -e "---------------------------------------------------------n" >> $change_log
    }
    
    slave_info slave_status
    if [ $Slave_SQL_Running = Yes ];then
        i=0    #一个计数器
        slave_info log_file pos
            ###判断从master接收到的binlog是否全部在本地执行(这样仍无法完全确定从库已追上主库,因为无法完全保证io_thread没有延时(由网络传输问题导致的从库落后的概率很小)
        until [ $Master_Log_File = $Relay_Master_Log_File -a $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]
         do
            if [ $i -lt 10 ];then    #将等待exec_pos追上read_pos的时间限制为10s
                echo -e "`date " %F  %H:%M:%S"`    -----Relay_Master_Log_File=$Relay_Master_Log_File,Exec_Master_Log_Pos=$Exec_Master_Log_Pos is behind Master_Log_File=$Master_Log_File,Read_Master_Log_Pos=$Read_Master_Log_Pos, wait......" >> $change_log    #输出消息到日志,等待exec_pos=read_pos
                i=$(($i 1))
                sleep 1
                slave_info log_file pos
            else
                echo -e "The waits time is more than 10s,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
                action
                exit 0
            fi
        done
        action 
    
    else
        slave_info log_file pos
        echo -e "DB2's slave status is wrong,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
        action
    fi
    

    现在我们从远程机器登陆看看,使用vip,创建测试库,插入数据,最后模拟mysqld crash

    May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Entering BACKUP STATE
    May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
    May 25 14:37:50 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Now in FAULT state
    

    新葡亰496net 23

    新葡亰496net 24新葡亰496net 25

    2. 在Keepalived中有两种模式,分别是master->backup模式和backup->backup模式,这两种模式有什么区别呢?

     

    [root@mysql-server-03 ~]# mysql -uadmin -p123456 -h 192.168.0.88 -P 14520   
    Warning: Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 47
    Server version: 5.6.19-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> create database dengyayun;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> use dengyayun
    Database changed
    mysql> create table t1 ( id int);
    Query OK, 0 rows affected (0.38 sec)
    
    mysql> insert into t1 select 999;
    Query OK, 1 row affected (0.03 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    mysql> 
    

        在master->backup模式下,一旦主库宕掉,虚拟IP会自动漂移到从库,当主库修复后,keepalived启动后,还会把虚拟IP抢过来,即使你设置nopreempt(不抢占)的方式抢占IP的动作也会发生。在backup->backup模式下,当主库宕掉后虚拟IP会自动漂移到从库上,当原主恢复之后重启keepalived服务,并不会抢占新主的虚拟IP,即使是优先级高于从库的优先级别,也不会抢占IP。为了减少IP的漂移次数,生产中我们通常是把修复好的主库当做新主库的备库。

    整个脚本的逻辑是让从的Exec_Master_Log_Pos尽可能的追上Read_Master_Log_Pos,它给了10s的限制,如果还是没有追上,则直接将master2设置为主(通过解除read_only属性),其实这里面还是有待商榷的,譬如10s的限制是否合理,还是一定需要Exec_Master_Log_Pos=Read_Master_Log_Pos才切换。

    View Code

    1. 本文是在MySQL主库高可用 -- 双主单活故障自动切换方案 基础上,结合自己对于MySQL的理解整理的。原文的脚本直接执行有点问题,思路有有点瑕疵,于是结合自己的实际环境,重新修改了一把。

    2. 在测试的过程中,有以下几点需要注意:

     

    发现使用vip登陆没有问题,创建库以及插入数据都木有问题。现在杀掉mysqld进程,看vip是否进行了漂移,以及查看数据是否存在。

        1> master1检测脚本的逻辑是如果MySQL的服务不可用,则通过service keepalived stop命令来关闭keepalived,但在实际测试的过程中,却出现了即便执行了service keepalived stop命令,keepalived进程依然没有停止,导致MySQL的服务虽然不可用了,但VIP并不没有漂移到master2上。

    当原主恢复正常后,如何将VIP从master2切回到master1中呢?

    [root@mysql-server-01 ~]# pkill -9 mysqld
    

             优化方案:在执行service keepalived stop后,等待5s,再次检测keepalived的状态,如果keepalived没有关闭,则直接kill掉。

    新葡亰496net 26

    过了一会儿,报警邮件就到了,以及vip也已经切换了。如下:
    新葡亰496net 27

        2>  keepalived的日志默认是输出到/var/log/messages中,这样不便于查看。如何自定义keepalived的日志输出文件呢?

    #!/bin/bash
    ###手动执行将主库切换回DB1的操作
    
    mysql_con='mysql -uroot -p123456'
    
    echo -e "`date " %F  %H:%M:%S"`    -----change to BACKUP manually-----" >> /etc/keepalived/logs/state_change.log
    echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 1 on DB2-----" >> /etc/keepalived/logs/state_change.log
    $mysql_con -e "set global read_only = 1;" 2>> /etc/keepalived/logs/state_change.log
    
    ###kill掉当前客户端连接
    echo -e "`date " %F  %H:%M:%S"`    -----kill current client thread-----" >> /etc/keepalived/logs/state_change.log
    rm -f /tmp/kill.sql &>/dev/null
    ###这里其实是一个批量kill线程的小技巧
    $mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query" or command="Execute" into outfile "/tmp/kill.sql";'
    $mysql_con -e "source /tmp/kill.sql" 2>> /etc/keepalived/logs/state_change.log
    sleep 2    ###给kill一个执行和缓冲时间
    
    ###确保DB1已经追上了,下面的repl为复制所用的账户,-h后跟DB1的内网IP
    log_file_pos=`mysql -urepl -pmysql -h192.168.244.145 -e "show slave statusG;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
    Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
    Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
    Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
    Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
    until [ $Read_Master_Log_Pos = $Exec_Master_Log_Pos -a $Master_Log_File = $Relay_Master_Log_File ]
    do
        echo -e "`date " %F  %H:%M:%S"`    -----DB1 Exec_Master_Log_Pos($exec_pos) is behind Read_Master_Log_Pos($read_pos), wait......" >> /etc/keepalived/logs/state_change.log
        sleep 1
    done
    
    ###然后解除DB1的read_only属性
    echo -e "`date " %F  %H:%M:%S"`    -----set read_only = 0 on DB1-----" >> /etc/keepalived/logs/state_change.log
    ssh 192.168.244.145 'mysql -uroot -p123456 -e "set global read_only = 0;" && /etc/init.d/keepalived start' 2>> /etc/keepalived/logs/state_change.log
    
    ###重启DB2的keepalived使VIP漂移到DB1
    echo -e "`date " %F  %H:%M:%S"`    -----make VIP move to DB1-----" >> /etc/keepalived/logs/state_change.log
    /sbin/service keepalived restart &>> /etc/keepalived/logs/state_change.log
    
    echo "DB2 keepalived转为BACKUP状态,线上数据库切换至DB1"|mail -s "DB2 keepalived change to BACKUP" slowtech@126.com 2>> /etc/keepalived/logs/state_change.log
    
    echo -e "--------------------------------------------------n" >> /etc/keepalived/logs/state_change.log
    

    查看slave上面的message信息,如下输出:

              如果是用service启动的,修改/etc/sysconfig/keepalived文件

    新葡亰496net 28

    [root@mysql-server-02 ~]# tail -n 20 /var/log/messages 
    Jul 20 22:00:20 mysql-server-02 Keepalived_healthcheckers[13327]: Registering Kernel netlink command channel
    Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: Opening file '/etc/keepalived/keepalived.conf'.
    Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: Configuration is using : 66454 Bytes
    Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: Using LinkWatch kernel netlink reflector...
    Jul 20 22:00:40 mysql-server-02 Keepalived_healthcheckers[13327]: Opening file '/etc/keepalived/keepalived.conf'.
    Jul 20 22:00:40 mysql-server-02 Keepalived_healthcheckers[13327]: Configuration is using : 6467 Bytes
    Jul 20 22:00:40 mysql-server-02 Keepalived_healthcheckers[13327]: Using LinkWatch kernel netlink reflector...
    Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Entering BACKUP STATE
    Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]
    Jul 20 22:00:40 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Script(check_run) succeeded
    Jul 20 22:07:47 mysql-server-02 dhclient[7343]: DHCPREQUEST on eth0 to 192.168.87.254 port 67 (xid=0x4ada08db)
    Jul 20 22:07:47 mysql-server-02 dhclient[7343]: DHCPACK from 192.168.87.254 (xid=0x4ada08db)
    Jul 20 22:07:49 mysql-server-02 dhclient[7343]: bound to 192.168.87.135 -- renewal in 885 seconds.
    Jul 20 22:10:38 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Transition to MASTER STATE
    Jul 20 22:10:38 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Group(VG1) Syncing instances to MASTER state
    Jul 20 22:10:39 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Entering MASTER STATE
    Jul 20 22:10:39 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) setting protocol VIPs.
    Jul 20 22:10:39 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88
    Jul 20 22:10:39 mysql-server-02 Keepalived_healthcheckers[13327]: Netlink reflector reports IP 192.168.0.88 added
    Jul 20 22:10:44 mysql-server-02 Keepalived_vrrp[13328]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88
    [root@mysql-server-02 ~]# 
    
    KEEPALIVED_OPTIONS="-D -d -S 0" 
    

     

    最后我们再次使用vip登陆;发现数据没有异常。复制也停止了,因为已经切换为主库。

             如果不是,则启动的时候指定以上参数,如:

    总结:

    [root@mysql-server-03 ~]# mysql -uadmin -p123456 -h 192.168.0.88 -P14521
    Warning: Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 301
    Server version: 5.6.19-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> select * from dengyayun.t1;
     ------ 
    | id   |
     ------ 
    |  999 |
     ------ 
    1 row in set (0.00 sec)
    
    mysql> pager cat | egrep 'IO_Running|SQL_Running'
    PAGER set to 'cat | egrep 'IO_Running|SQL_Running''
    mysql> show slave statusG
                 Slave_IO_Running: No
                Slave_SQL_Running: No
          Slave_SQL_Running_State: 
    1 row in set (0.00 sec)
    
    mysql> 
    
    /usr/local/keepalived/sbin/keepalived -d -D -S 0 
    

    1. /etc/keepalived/check_mysql.sh和/etc/keepalived/notify_master_mysql.sh必须加可执行权限。

            修改/etc/syslog.conf

        如果前者没有加可执行权限,则master1上将不会绑定VIP,日志直接提示如下信息:

    # keepalived -S 0 
    local0.*                                                /var/log/keepalived.log
    
    May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Entering BACKUP STATE
    May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
    May 25 14:37:50 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Now in FAULT state
    

           重启syslog

    2. 在Keepalived中有两种模式,分别是master->backup模式和backup->backup模式,这两种模式有什么区别呢?

           RHEL 5&6:service syslog restart

        在master->backup模式下,一旦主库宕掉,虚拟IP会自动漂移到从库,当主库修复后,keepalived启动后,还会把虚拟IP抢过来,即使你设置nopreempt(不抢占)的方式抢占IP的动作也会发生。在backup->backup模式下,当主库宕掉后虚拟IP会自动漂移到从库上,当原主恢复之后重启keepalived服务,并不会抢占新主的虚拟IP,即使是优先级高于从库的优先级别,也不会抢占IP。为了减少IP的漂移次数,生产中我们通常是把修复好的主库当做新主库的备库。

           RHEL 7:service rsyslog restart

    1. 本文是在MySQL主库高可用 -- 双主单活故障自动切换方案 基础上,结合自己对于MySQL的理解整理的。原文的脚本直接执行有点问题,思路有有点瑕疵,于是结合自己的实际环境,重新修改了一把。

    2. 在测试的过程中,有以下几点需要注意:

       

        1> master1检测脚本的逻辑是如果MySQL的服务不可用,则通过service keepalived stop命令来关闭keepalived,但在实际测试的过程中,却出现了即便执行了service keepalived stop命令,keepalived进程依然没有停止,导致MySQL的服务虽然不可用了,但VIP并不没有漂移到master2上。

      

             优化方案:在执行service keepalived stop后,等待5s,再次检测keepalived的状态,如果keepalived没有关闭,则直接kill掉。

         

        2>  keepalived的日志默认是输出到/var/log/messages中,这样不便于查看。如何自定义keepalived的日志输出文件呢?

          

              如果是用service启动的,修改/etc/sysconfig/keepalived文件

        

    KEEPALIVED_OPTIONS="-D -d -S 0" 
    

     

             如果不是,则启动的时候指定以上参数,如:

    /usr/local/keepalived/sbin/keepalived -d -D -S 0 
    

            修改/etc/syslog.conf

    # keepalived -S 0 
    local0.*                                                /var/log/keepalived.log
    

           重启syslog

           RHEL 5&6:service syslog restart

           RHEL 7:service rsyslog restart

    本文由新葡亰496net发布于网络数据库,转载请注明出处:MySQL达成高可用,基于keepalived搭建MySQL的高可用集

    关键词:

上一篇:jdbc学习笔记02,Java面试题之JavaWeb基础

下一篇:没有了