澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:MySQL实现高可用,不断更新

2019-10-05 作者:数据库网络   |   浏览(88)

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] ~]# 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架构为master/slave,当master故障时,vip漂移到slave上。提供劳务。当然也足以设置为双master,然实际不是各样方案都以周详的。这里设置为双master有个难点亟需在意,举个例子,当客户公布作品时,由于此时主机的压力非常的大时,若是落后2000秒,那么那台主机宕机了,另一台主机接管(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。

澳门新萄京官方网站 1

澳门新萄京官方网站 2澳门新萄京官方网站 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

澳门新萄京官方网站 4澳门新萄京官方网站 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]# 

澳门新萄京官方网站 6澳门新萄京官方网站 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中学关于复制的配置如下:

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

notify_stop: VRubiconRP截止以往实践的脚本。

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

澳门新萄京官方网站 8澳门新萄京官方网站 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的输出来认可。

澳门新萄京官方网站 10澳门新萄京官方网站 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;

澳门新萄京官方网站 12澳门新萄京官方网站 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

澳门新萄京官方网站 14澳门新萄京官方网站 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

澳门新萄京官方网站 16澳门新萄京官方网站 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
    }
}

澳门新萄京官方网站 18澳门新萄京官方网站 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才切换。

澳门新萄京官方网站 20澳门新萄京官方网站 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

澳门新萄京官方网站:MySQL实现高可用,不断更新。能够见到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,日志间接提醒如下新闻:

澳门新萄京官方网站 22澳门新萄京官方网站 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也已经切换了。如下:
澳门新萄京官方网站 24

         优化方案:在进行service keepalived stop后,等待5s,再次检查实验keepalived的图景,假若keepalived未有安息,则一向kill掉。

澳门新萄京官方网站:MySQL实现高可用,不断更新。         优化方案:在实践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

   

   

  

  

     

     

      

      

    

    

 

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:MySQL实现高可用,不断更新

关键词: