您的位置:新葡亰496net > 网络数据库 > 新葡亰496netMySQL实现高可用,基于keepalived搭建M

新葡亰496netMySQL实现高可用,基于keepalived搭建M

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

    MySQL的高可用方案有那些,譬如Cluster,MMM,MHA,DRBD等,那几个都相比较复杂,作者近日的篇章也许有介绍。这几天Oracle官方也生产了Fabric。有的时候大家不要求这么复杂的条件,这几个方案工力悉敌。有时轻松的且我们能够hold住的方案才是相符大家的。比如MySQL Replication,然后加上种种高可用软件,举例Keepalived等,就能够落到实处大家供给的高可用情况。

    1)主从复制延时剖断 (转 )

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

    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. 改造配置文件

          master第11中学关于复制的安排如下:

    [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] 新葡亰496netMySQL实现高可用,基于keepalived搭建MySQL的高可用集群。~]# 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切回到master第11中学呢?

    #!/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必得加可实行权限。

        假使前边三个没有加可试行权限,则master第11中学将不会绑定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架构为master/slave,当master故障时,vip漂移到slave上。提供劳务。当然也能够设置为双master,然而不是各样方案都以应有尽有的。这里安装为双master有个难点亟待潜心,比方,当客商发布作品时,由于此时主机的下压力十分的大时,要是落后3000秒,那么那台主机宕机了,另一台主机接管(vip漂移到从机上)时,因为一块延时大,客商方才发布的作品还没复制过来,于是客商又公布了叁回小说,当原本的master修复好后,由于I/O和SQL线程还地处展开状态,因而还大概会一连联合刚才未有一块复制完的多少,那时有极大可能率把客商新发布的稿子退换掉。这里所以利用master/slave架构。在这种框架结构中,故障切换现在,选择手动操作的不二诀要与新的master举办复制。

       说明:

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

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

    粗略境况如下:

    无须通过Seconds_Behind_Master去看清,该值表示slave上SQL线程和IO线程之间的延期
    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 的差异,那么非常细心的做法是同有时候在主库试行show master status和在从库下面推行show slave status 的出口实行相比。MHA正是那样保证数据一致性的。MMM都不曾产生。那也算MHA比MMM越发出彩的地点。

    正如常用的是keepalived 双主,MHA和PXC。

    正如常用的是keepalived 双主,MHA和PXC。

    新葡亰496net 1

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

    对于小商城,一般推荐应用keepalived 双主,轻松。

    对此小商店,一般推荐使用keepalived 双主,简单。

    master     192.168.0.100
    slave      192.168.0.101
    VIP        192.168.0.88
    
    #!/bin/bash
    # 判断主从复制是否延迟
    # write by yayun 2014-07-23
    # http://www.cnblogs.com/gomysql/
    
    # slave
    s_psswd=123456
    s_user=root
    s_port=3306
    s_host=localhost
    
    # master
    m_psswd=123456
    m_user=root
    m_port=3306
    m_host=192.168.0.102
    
    
    slave_wan_ip=`ifconfig | sed -n '/inet /{s/.*addr://;s/ .*//;p}' | head -n1`
    
    while true
    do
        sleep 1
        echo -e "e[1;33m###################################e[0m"
        Master_Log_File=$(mysql -u$s_user -p$s_psswd -h$s_host -P$s_port -e "show slave statusG" | grep -w Master_Log_File | awk -F": " '{print $2}')
        Relay_Master_Log_File=$(mysql -u$s_user -p$s_psswd -h$s_host -P$s_port -e "show slave statusG" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
        Read_Master_Log_Pos=$(mysql -u$s_user -p$s_psswd -h$s_host -P$s_port -e "show slave statusG" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
        Exec_Master_Log_Pos=$(mysql -u$s_user -p$s_psswd -h$s_host -P$s_port -e "show slave statusG" | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}'|sed 's/[ t]*$//g')
        Master_Log_File_Num=`echo $Master_Log_File | awk -F '.' '{print $2}' | sed 's/^0 //'`
        Master_File=$(mysql -u$m_user -p$m_psswd -h$m_host -P$m_port -Nse "show master status" | awk '{print $1}')
        Master_Pos=$(mysql -u$m_user -p$m_psswd -h$m_host -P$m_port -Nse "show master status" | awk '{print $2}'|sed 's/[ t]*$//g')
        Master_File_Num=`echo $Master_File | awk -F '.' '{print $2}' | sed 's/^0 //'`
    
        if [ -z $Master_Log_File ] && [ -z $Relay_Master_Log_File ] && [ -z $Read_Master_Log_Pos ] && [ -z $Exec_Master_Log_Pos ]
        then
            echo -e "e[1;31mSLAVE 没有取到值,请检查参数设置!e[0m"
            exit 1
        fi
    
        if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]
        then
            if [ $Master_Log_File = $Master_File ] && [ $Exec_Master_Log_Pos = $Master_Pos ]
            then
                echo -e "e[1;32mMaster-slave 复制无延迟 ^_^e[0m"
            else
                if [ $Master_Log_File_Num -gt $Master_File_Num ] || [ $Master_Pos -gt $Exec_Master_Log_Pos ]
                then
                    log_count=$(expr $Master_Log_File_Num - $Master_File_Num)
                    pos_count=$(expr $Master_Pos - $Exec_Master_Log_Pos)
                    echo -e "e[1;31mMaster-slave 复制延迟 !!!e[0m"
                    echo -e "e[1;31mMaster:$m_host Slave:$slave_wan_ipe[0m"
                    echo -e "e[1;31mMaster当前binlog: $Master_File"
                    echo -e "e[1;31mSlave当前binlog:  $Master_Log_File"
                    echo -e "e[1;31mbinlog相差文件数: $log_counte[0m"
                    echo -e "e[1;31mPos点相差:        $pos_counte[0m"
                fi
            fi
        fi
    done
    

    下边来安排一下

    下边来配置一下

    主从复制遇到的搭建本身那边就不演示了。有亟待的同室团结看看合法手册。上边直接介绍keepalived的设置及配置利用。

    View Code

     

     

    1.keepalived软件设置(主从操作一样)

     主从复删除主键争辩的笔录

    配置情形:

    配备情形:

    [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
    

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

    剧中人物                                    主机IP                    主机名               操作系统版本     软件版本

    剧中人物                                    主机IP                    主机名               操作系统版本     软件版本

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

    #!/bin/bash
    #Delete duplicate records primary key conflict
    #Write by yayun 2014-05-17
    
    mysql=/usr/local/mysql-5.1.66/bin/mysql
    sock=/data/mysql-slave-3311/mysql.sock
    passwd=123456
    
    while true
    do
        SQL_THREAD=`$mysql -uroot -p$passwd -S $sock -e 'show slave statusG' | egrep 'Slave_SQL_Running' | awk '{print $2}'`
        LAST_ERROR=`$mysql -uroot -p$passwd -S $sock -e 'show slave statusG' | egrep Last_Errno | awk '{print $2}'`
        duplicate=`$mysql -uroot -p$passwd -S $sock -e 'show slave statusG' | grep Last_Error | awk '/Duplicate entry/{print $5}' | awk -F "'" '{print $2}'`
        DATABASE=`$mysql -uroot -p$passwd -S $sock -e 'show slave statusG' | grep Last_Error | awk '{print $13}' | awk -F "'" '{print $2}'`
        TABLE=`$mysql -uroot -p$passwd -S $sock -e 'show slave statusG' | grep Last_Error | awk -F ":" '{print $4}' | awk -F "(" '{print $1}' | awk '{print $NF}'`
    
        $mysql -uroot -p$passwd -S $sock -e 'show slave statusG' | grep HA_ERR_FOUND_DUPP_KEY
        if [ $? -eq 1 ]
        then
            if [ "$SQL_THREAD" == No ] && [ "$LAST_ERROR" == 1062 ]
            then
                FILED=`$mysql -uroot -p$passwd -S $sock -Nse "desc $DATABASE.$TABLE" | grep PRI | awk '{print $1}'`
                $mysql -uroot -p$passwd -S $sock -e "delete from $DATABASE.$TABLE where $FILED=$duplicate"
                $mysql -uroot -p$passwd -S $sock -e "start slave sql_thread"
            else
                echo "====================== ok ========================"
                $mysql -uroot -p$passwd -S $sock -e 'show slave statusG' | egrep 'Slave_.*_Running'
                echo "====================== ok ========================"
                break
            fi
        fi
    done
    

    VIP                                    192.168.244.10

    VIP                                    192.168.244.10

    [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]# 
    

    View Code

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

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

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

     写了三个本子运转 mysqladmin.sh

    master2                             192.168.244.146       master2            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

    [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]# 
    

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

     

     

    中间有多少个首要参数的地方:
    notify_master:状态改造为master以后实行的脚本。

        #!/bin/sh  
    
        mysql_port=3306  
        mysql_username="root"  
        mysql_password=""  
    
        function_start_mysql()  
        {  
            printf "Starting MySQL...n"  
            /bin/sh /usr/local/webserver/mysql/bin/mysqld_safe --defaults-file=/data1/mysql/${mysql_port}/my.cnf 2>&1 > /dev/null &  
        }  
    
        function_stop_mysql()  
        {  
            printf "Stoping MySQL...n"  
            /usr/local/webserver/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -h 127.0.0.1 -S /tmp/mysql.sock shutdown  
        }  
    
        function_restart_mysql()  
        {  
            printf "Restarting MySQL...n"  
            function_stop_mysql  
            sleep 5  
            function_start_mysql  
        }  
    
        function_kill_mysql()  
        {  
            kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')  
            kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')  
        }  
    
        if [ "$1" = "start" ]; then  
            function_start_mysql  
        elif [ "$1" = "stop" ]; then  
            function_stop_mysql  
        elif [ "$1" = "restart" ]; then  
        function_restart_mysql  
        elif [ "$1" = "kill" ]; then  
        function_kill_mysql  
        else  
            printf "Usage: /data1/mysql/${mysql_port}/mysql {star|stop|restart|kill}n"  
        fi  
    

    一、 配置MySQL双主复制境遇

    一、 配置MySQL双主复制境遇

    notify_backup: 状态更动为backup以往实施的脚本。

    View Code

         1. 修改配置文件

         1. 修改配置文件

    notify_fault: 状态改造为fault后奉行的剧本。

     

          master第11中学有关复制的布局如下:

    新葡亰496net,      master第11中学有关复制的配备如下:

    notify_stop: VCR-VRP结束现在实施的剧本。

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

    state backup:大家都设置为了backup,正是为着爆发故障未来不会活动切换。

         master2

         master2

    nopreempt: 不开展抢占操作

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

    在这之中使用了那4个本子:backup.sh  master.sh  mysql_check.sh  stop.sh

       2. 创办理并答复制客商

       2. 开立复制客户

    mysql_check.sh是为着检查mysqld进度是还是不是存活的台本,当发掘接二连三不上mysql,自动把keepalived进度干掉,让VIP进行漂移。

        master1中创建:

        master1中创建:

    下边包车型客车脚本主从服务器下边都有,只是从服务器上面包车型地铁master.sh有些不一样。增多了当slave提高为主库时,发送邮件布告。

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

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

        master2中创建:

        master2中创建:

    [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]# 
    
    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.145' IDENTIFIED BY 'mysql';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.145';
    

    View Code

      3. 执行CHANGE MASTER TO语句

      3. 执行CHANGE MASTER TO语句

    master.sh的效果是情状改为master以往实践的台本。首先推断复制是或不是有延迟,假设有延期,等1分钟后,不论是还是不是有延期。都跳过,并甘休复制。并且授权账号,记录binlog和pos点。

         因是初阶搭建MySQL主从复制集群,所以无需得到全局读锁来获得二进制日志文件的岗位,直接根据show master status的输出来承认。

         因是开头搭建MySQL主从复制集群,所以没有需求获得全局读锁来取得二进制日志文件的地方,间接依照show master status的输出来承认。

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

         master1上执行:

         master1上执行:

    [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]# 
    
    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;
    
    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;
    

    View Code

        master2上执行:

        master2上执行:

    slave上的master.sh

    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.145',
      MASTER_USER='repl',
      MASTER_PASSWORD='mysql',
      MASTER_LOG_FILE='mysql-bin.000003',
      MASTER_LOG_POS=68479;
    

    新葡亰496net 12新葡亰496net 13

        4. 分头在八个节点上实践start slave语句并经过show slave statusG查看复制是不是搭建成功。

        4. 分头在四个节点上实践start slave语句并由此show slave statusG查看复制是还是不是搭建成功。

    [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
    

            成功规范:

            成功标准:

    View Code

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    

    本子中反省复制是或不是延时的妄想如下:
    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 的差异

     

     

    并不是通过Seconds_Behind_Master去看清,该值表示slave上SQL线程和IO线程之间的推迟,实际上还要思考到 Master_Log_File 和 Relay_Master_Log_File 是还是不是有差别,更严酷的则是要同临时候在master上实行show master status实行对照。那也是MHA在切换进程中得以做到的。MMM的切换也只是在从库上举办了show slave status。所以数据一致性要求照旧MHA给力。扯远了。^_^

    二、 配置Keepalived

    二、 配置Keepalived

    backup.sh脚本的职能是情状改造为backup未来实行的台本。

         1. 安装Keepalived

         1. 安装Keepalived

    新葡亰496net 14新葡亰496net 15

          # yum install -y keepalived

          # yum install -y 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;"
    

          当然,也可平昔编写翻译官方的源码包。

          当然,也可平素编写翻译官方的源码包。

    View Code

         2. 改造Keepalived的布署文件

         2. 修改Keepalived的配备文件

    stop.sh 代表keepalived截至以后须要施行的剧本。改换密码,设置参数,检查是还是不是还应该有写入操作,最终无论是或不是试行落成,都退出。

         master1

         master1

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

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

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

    [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]# 
    
    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
        }
    }
    
    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
        }
    }
    

    View Code

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

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

    到这里基本就介绍完了。最终大家先看主从复制是不是健康,即使经常,然后分别运行keepalived,然后开展故障切换测量检验。

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

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

    slave状态:

    #!/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
    
    #!/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
    
    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)) > 
    

    通超过实际际的查询语句来判别数据库服务的可用性,假使查询战败,则剖断mysqld进度本人的状态,借使不正规,则直接甘休当前节点的keepalived,将VIP转移到别的多个节点,若无问题,则等待30s,再一次奉行查询语句,如故败诉,则将近些日子的master节点设置为read_only,并kill掉当前的客商端连接,然后截止当前的keepalived。

    因而实际的查询语句来判断数据库服务的可用性,假如查询战败,则决断mysqld进程自己的情况,如果不正规,则直接结束当前节点的keepalived,将VIP转移到别的一个节点,尽管不荒谬,则等待30s,再一次施行查询语句,如故败诉,则将前段时间的master节点设置为read_only,并kill掉当前的客商端连接,然后结束当前的keepalived。

    master 状态:

           

           

    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)) > 
    

           master2 

           master2 

    依靠本身前边给的论断规范,能够见见笔者的复制未有别的延时。
    上边分别在master上和slave上运营keepalived进度。以及查看日志(下面的查看只是给大家表明什么推断复制是或不是推迟)

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

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

    master

    ! 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
        }
    }
    
    ! 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
        }
    }
    

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

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

    其中,/etc/keepalived/notify_master_mysql.sh的原委如下:

    [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
    
    #!/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
    
    #!/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
    

    View Code

     

     

    slave

    一切脚本的逻辑是让从的Exec_Master_Log_Pos尽大概的追上Read_Master_Log_Pos,它给了10s的限量,假若照旧不曾追上,则一向将master2设置为主(通过免去read_only属性),其实那之中或许有待商谈的,例如10s的限定是不是合理,依然自然供给Exec_Master_Log_Pos=Read_Master_Log_Pos才切换。

    全套脚本的逻辑是让从的Exec_Master_Log_Pos尽恐怕的追上Read_Master_Log_Pos,它给了10s的限制,倘若照旧未有追上,则一向将master2设置为主(通过解除read_only属性),其实那中间或许有待商榷的,例如10s的限量是还是不是站得住,依然自然需求Exec_Master_Log_Pos=Read_Master_Log_Pos才切换。

    新葡亰496net 20新葡亰496net 21

     

     

    [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
    

    当原主恢复不荒谬后,怎么着将VIP从master2切回到master第11中学吗?

    当原主复苏符合规律后,怎么着将VIP从master2切回到master第11中学呢?

    View Code

    #!/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
    
    #!/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
    

    能够看见VIP已经绑定在了master上,施行ip addr看看是或不是有其一VIP

     

     

    [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 ~]# 
    

    总结:

    总结:

    能够望见vip也已经绑定成功。

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

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

    今昔大家从远程机器登录看看,使用vip,创立测量试验库,插入数据,最后模拟mysqld crash

        如果前面三个未有加可实施权限,则master1准将不会绑定VIP,日志间接提醒如下消息:

        假若前面多少个未有加可实施权限,则master1少校不会绑定VIP,日志直接提示如下音讯:

    新葡亰496net 22新葡亰496net 23

    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
    
    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
    
    [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> 
    

    2. 在Keepalived中有三种方式,分别是master->backup情势和backup->backup情势,那三种格局有哪些差距吗?

    2. 在Keepalived中有二种格局,分别是master->backup形式和backup->backup形式,那三种方式有哪些界别吧?

    View Code

        在master->backup方式下,一旦主库宕掉,设想IP会自动漂移到从库,当主库修复后,keepalived运维后,还有大概会把设想IP抢过来,尽管你设置nopreempt(不抢占)的不二等秘书技强占IP的动作也会爆发。在backup->backup形式下,当主库宕掉后虚构IP会自动漂移到从库上,当原主苏醒未来重启keepalived服务,并不会抢占新主的杜撰IP,尽管是事先级高于从库的优先等第,也不会抢占IP。为了减少IP的飘浮次数,生产中大家无独有偶是把修复好的主库当做新主库的备库。

        在master->backup情势下,一旦主库宕掉,虚构IP会自动漂移到从库,当主库修复后,keepalived运转后,还恐怕会把虚构IP抢过来,纵然你设置nopreempt(不抢占)的措施私吞IP的动作也会爆发。在backup->backup形式下,当主库宕掉后设想IP会自动漂移到从库上,当原主苏醒现在重启keepalived服务,并不会抢占新主的虚拟IP,就算是事先级高于从库的事先等级,也不会抢占IP。为了减小IP的漂流次数,生产中大家日常是把修复好的主库当做新主库的备库。

    发觉选择vip登入没反常,创立库以及插入数据都木十分。今后杀掉mysqld进程,看vip是还是不是实行了悬浮,以及查看数据是还是不是留存。

    1. 正文是在MySQL主库高可用 -- 双主单活故障自动切换方案 基础上,结合本人对于MySQL的驾驭整理的。原来的小说的本子直接实施多少难点,思路有有一些劣势,于是结成本身的实在条件,重新修改了一把。

    2. 在测量检验的进度中,有以下几点需求留神:

    1. 正文是在MySQL主库高可用 -- 双主单活故障自动切换方案 基础上,结合本身对此MySQL的了然整理的。原来的小说的脚本直接实行多少难题,思路有有一点瑕玷,于是结成自个儿的骨子里条件,重新修改了一把。

    2. 在测验的进度中,有以下几点须求注意:

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

        1> master1检查实验脚本的逻辑是一旦MySQL的劳务不可用,则透过service keepalived stop命令来关闭keepalived,但在实质上测量检验的历程中,却出现了就是实行了service keepalived stop命令,keepalived进度依然未有安息,导致MySQL的劳务尽管不可用了,但VIP并不未有漂移到master2上。

        1> master1检查测量检验脚本的逻辑是就算MySQL的服务不可用,则透过service keepalived stop命令来关闭keepalived,但在实质上测量检验的进程中,却出现了尽管实施了service keepalived stop命令,keepalived进程依然未有平息,导致MySQL的劳务尽管不可用了,但VIP并不未有漂移到master2上。

    过了一阵子,报警邮件就到了,以及vip也早已切换了。如下:
    新葡亰496net 24

             优化方案:在试行service keepalived stop后,等待5s,再次检验keepalived的景况,假使keepalived未有关闭,则直接kill掉。

             优化方案:在奉行service keepalived stop后,等待5s,再度检查实验keepalived的情况,如若keepalived未有关闭,则直接kill掉。

    查看slave上面的message音讯,如下输出:

        2>  keepalived的日记暗许是出口到/var/log/messages中,这样不便民查看。怎么样自定义keepalived的日志输出文件呢?

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

    [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 ~]# 
    

              借使是用service运转的,修改/etc/sysconfig/keepalived文件

              假如是用service运维的,修改/etc/sysconfig/keepalived文件

    末了大家重新行使vip登入;发现数目尚未那多少个。复制也结束了,因为已经切换为主库。

    KEEPALIVED_OPTIONS="-D -d -S 0" 
    
    KEEPALIVED_OPTIONS="-D -d -S 0" 
    
    [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 
    
    /usr/local/keepalived/sbin/keepalived -d -D -S 0 
    

            修改/etc/syslog.conf

            修改/etc/syslog.conf

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

           重启syslog

           重启syslog

           RHEL 5&6:service syslog restart

           RHEL 5&6:service syslog restart

           RHEL 7:service rsyslog restart

           RHEL 7:service rsyslog restart

       

       

      

      

         

         

          

          

        

        

     

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496netMySQL实现高可用,基于keepalived搭建M

    关键词: