金沙官网线上基于keepalived搭建MySQL的高可用集群

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

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

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和PXC。

金沙官网线上 1

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

master     192.168.0.100
slave      192.168.0.101
VIP        192.168.0.88

下面来部署一下

主从复制环境的搭建我这里就不演示了。有需要的同学自己看看官方手册金沙官网线上,。下面直接介绍keepalived的安装及配置使用。

 

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

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

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

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

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

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

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

 

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

一、 配置MySQL双主复制环境

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

     1. 修改配置文件

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

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

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

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

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

     master2

nopreempt: 不进行抢占操作

[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. 创建复制用户

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

    master1中创建:

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

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

金沙官网线上 2金沙官网线上 3

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

View Code

  3. 执行CHANGE MASTER TO语句

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

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

金沙官网线上 4金沙官网线上 5

     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;

View Code

    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;

金沙官网线上 6金沙官网线上 7

    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

脚本中检查复制是否延时的思想如下:
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

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

     1. 安装Keepalived

金沙官网线上 8金沙官网线上 9

      # 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的配置文件

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

     master1

金沙官网线上 10金沙官网线上 11

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

本文由金沙官网线上发布于数据库,转载请注明出处:金沙官网线上基于keepalived搭建MySQL的高可用集群

您可能还会对下面的文章感兴趣: