择·简

  博客园 :: 首页 :: 新随笔 :: 联系 :: 订阅 :: 管理 ::
基于Docker的mysql keepalived 的集群环境构建

基于Docker的mysql keepalived 的集群环境构建

mysql docker keepalived 高可用

概述

  1. 目的
    为解决Mysql数据库单点问题，实现数据库的高可用。当Master数据库出现问题时，Slave切换为Master继续工作。当Master主机宕机时，由于无法通过scp拷贝位置文件，会导致无法恢复同步复制，需手动恢复同步复制。

  2. 环境说明

 序号 服务器IP  用途  备注 
 1  172.19.0.2  主机A  Master
 2  172.19.0.3  主机B  Slave
 3  172.19.0.110  VIP  

切换原理和过程

Keepalived可实现将虚拟IP地址在实体物理机上来回漂移。Keepalived在转换状态时会依照状态来呼叫配置文件中内置的定义。
当进入Master状态时会呼叫notify_master定义的脚本
当进入Backup状态时会呼叫notify_backup定义的脚本
当keepalived程序终止时呼叫notify_stop定义的脚本
当发现异常情况时进入Fault状态呼叫notify_fault定义的脚本
切换的过程如下：
1）在Master主机上keepalived运行时执行mycheck.sh脚本不停的检查mysql的运行状态，当发现mysql停止后将keepalived进程杀掉。
2）此时Slave主机上会接管虚拟IP地址，并调用notify_master定义的脚本
3）当原Master主机上的mysql和keepalived进程恢复正常后，会调用notify_backup定义的脚本，此时数据库的主端还在Savle主机上。
4）回切，关闭Slave端的keepavlied进程，会调用notify_stop脚本，同时Master主机上会调用notify_master定义的脚本。此时数据库的主端在Master主机上
5）启动Slave端的keepavlied进程，会调用notify_backup脚本，此时完成数据同步。

docker-compose配置Mysql主从高可用

  1. 文件列表
├── docker-compose.yml
└── mysql
    ├── master
    │   ├── config
    │   │   ├── keepalived.conf
    │   │   ├── my.cnf
    │   │   └── mysqlenv
    │   ├── data
    │   └── init
    ├── slave
    │   ├── config
    │   │   ├── keepalived.conf
    │   │   ├── my.cnf
    │   │   └── mysqlenv
    │   ├── data
    │   └── init
    ├── scripts
    │   ├── master
    │   │   └── logs
    │   ├── slave
    │   │   └── logs
    │   ├── mybackup.sh
    │   ├── mycheck.sh
    │   ├── mymaster.sh
    │   ├── mystop.sh
  1. docker-compose.yml文件说明(文件内容)
    创建mysql-master和mysql-slave容器的配置文件
# 创建并启动容器
# docker-compose up -d
# 登陆Master
# docker exec -it mysql-master /bin/bash

注意：docker宿主机需安装keepalived和ipvsadm，否则容器中的keepalived服务无法正常启动

# 在宿主机中执行以下指令安装keepalived和ipvsadm
# yum install -y keepalived ipvsadm
# ipvsadm --save > /etc/sysconfig/ipvsadm
# echo 1 > /proc/sys/net/ipv4/ip_forward
# systemctl enable ipvsadm
# systemctl start ipvsadm
# 开机启动需配置net.ipv4.ip_forward=1到/etc/sysctl.conf

切换脚本说明

  1. 检查脚本mycheck(文件内容)
    检查mysql运行状态，如果运行正常，退出。如果运行不正常调用pkill keepalived

  2. 切换脚本mymaster(文件内容)
    先判断同步复制是否执行完成，如果未执行完成等待1分钟后，停止同步（stop slave），并且记录切换后的日志和pos

  3. 回切脚本mybackup(文件内容)
    清空slave配置，重新获取远程日志文件及Pos,并开启同步

  4. 停止脚本mystop(文件内容)
    设置参数保证数据不丢失，最后检查看是否还有写操作，最后1分钟退出

演练过程

  1. master端数据库宕机（中止mysqld数据库服务），发生：（1）master端keepalived被中止；（2）vip飘到slave端，并调用mymaster.sh脚本停止slave进程复制，相当于服务切换至slave端。
  2. 恢复master端数据库（启动mysqld数据库服务）和keepalived服务，发生：master端调用mybackup.sh脚本启动slave进程复制，相当于同步复制切换至master端。
  3. slave端数据库宕机。
  4. 恢复slave端数据库。
    注意：12步骤切换，34步骤恢复
[root@localhost mysql]# docker-compose up -d
<-----启动容器

[root@localhost mysql]# docker exec -it mysql-master systemctl stop mysqld
[root@localhost mysql]# tail -10 mysql/scripts/master/logs/mysql_switch.log 
2019-08-09 07:09:16 The mycheck.sh, mysql is down, after switch...
2019-08-09 07:09:19 The mystop.sh, master sync ok...
[root@localhost mysql]# tail -10 mysql/scripts/slave/logs/mysql_switch.log 
2019-08-09 07:09:18 The mymaster.sh, slave sync ok... 
File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000010	586	mydatabase	mysql,test,information_schema	
2019-08-09 07:09:18 The mymaster.sh, Sync pos file sucess.
<-----1.master端数据库宕机（中止mysqld数据库服务），发生：（1）master端keepalived被中止；（2）vip飘到slave端，并调用mymaster.sh脚本停止slave进程复制，相当于服务切换至slave端。

[root@localhost mysql]# docker exec -it mysql-master systemctl start mysqld
[root@localhost mysql]# docker exec -it mysql-master systemctl start keepalived
[root@localhost mysql]# tail -20 mysql/scripts/master/logs/mysql_switch.log 
2019-08-09 07:15:15 This mybackup.sh, New_ReM_File:mysql-bin.000010,New_ReM_Position:586
*************************** 1. row ***************************
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
2019-08-09 07:15:15 The mybackup.sh, Sync pos file sucess...
<-----2.恢复master端数据库（启动mysqld数据库服务）和keepalived服务，发生：master端调用mybackup.sh脚本启动slave进程复制，相当于同步复制切换至master端。

[root@localhost mysql]# docker exec -it mysql-slave systemctl stop mysqld
[root@localhost mysql]# tail -20 mysql/scripts/slave/logs/mysql_switch.log 
......
2019-08-09 07:19:19 The mycheck.sh, mysql is down, after switch...
2019-08-09 07:19:22 The mystop.sh, master sync ok...
[root@localhost mysql]# tail -20 mysql/scripts/master/logs/mysql_switch.log 
2019-08-09 07:19:21 The mymaster.sh, slave sync ok... 
File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000010	586	mydatabase	mysql,test,information_schema	
2019-08-09 07:19:21 The mymaster.sh, Sync pos file sucess.
<-----3.slave端数据库宕机

[root@localhost mysql]# docker exec -it mysql-slave systemctl start mysqld
[root@localhost mysql]# docker exec -it mysql-slave systemctl start keepalived
[root@localhost mysql]# tail -20 mysql/scripts/slave/logs/mysql_switch.log 
2019-08-09 07:23:24 This mybackup.sh, New_ReM_File:mysql-bin.000010,New_ReM_Position:586
*************************** 1. row ***************************
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
2019-08-09 07:23:24 The mybackup.sh, Sync pos file sucess...
2019-08-09 07:23:34 The mycheck.sh, mysql is running...
<-----4.恢复slave端数据库

文件详情

docker-compose

docker-compose.yml

version: '3'
services:
  mysql-master:
    image: 'oracle/mysql:5.7'
    hostname: master
    restart: always
    container_name: mysql-master
    privileged: true
    volumes:
      - ./mysql/master/data:/var/lib/mysql
      - ./mysql/scripts:/etc/keepalived/mysql
      - ./mysql/master/config/my.cnf:/etc/my.cnf
      - ./mysql/master/config/mysqlenv:/root/.mysqlenv
      - ./mysql/master/config/keepalived.conf:/etc/keepalived/keepalived.conf
      - ./mysql/master/init:/docker-entrypoint-initdb.d/
    networks:
       extnetwork:
          ipv4_address: 172.19.0.2
    ports:
      - '3307:3306'
    environment:      
      - MYSQL_ROOT_PASSWORD=123456
  mysql-slave:
    image: 'oracle/mysql:5.7'
    hostname: slave
    restart: always
    container_name: mysql-slave
    privileged: true
    volumes:
      - ./mysql/slave/data:/var/lib/mysql
      - ./mysql/scripts:/etc/keepalived/mysql
      - ./mysql/slave/config/my.cnf:/etc/my.cnf
      - ./mysql/slave/config/mysqlenv:/root/.mysqlenv
      - ./mysql/slave/config/keepalived.conf:/etc/keepalived/keepalived.conf
      - ./mysql/slave/init:/docker-entrypoint-initdb.d/
    networks:
       extnetwork:
          ipv4_address: 172.19.0.3
    ports:
      - '3308:3306'
    environment:
      - MYSQL_ROOT_PASSWORD=123456
volumes:
  data:
    driver: local

networks:
   extnetwork:
      ipam:
         config:
         - subnet: 172.19.0.0/16
keepalived.conf

Master

! Configuration File for keepalived
global_defs {
  router_id KeepAlive_Mysql   
  # 标识，主从一致
}
vrrp_script check_run {
  script "/etc/keepalived/mysql/mycheck.sh"  
  # Mysql状态检查脚本
  interval 10
}

vrrp_sync_group VG1 {
  group {
    VI_1
  }
}

vrrp_instance VI_1 {
  state BACKUP
  # 注意，主从两端都配置成了backup，因为使用了nopreempt，即非抢占模式

  interface eth0
  virtual_router_id 51 
  # 分组，主从相同
  priority 100
  # 优先级，这个高一点则先把它作为master
  advert_int 1
  nopreempt 
  # 不主动抢占资源，设置非抢占模式
  authentication { 
  # 主从一致
    auth_type PASS
    auth_pass 123456
  }
  track_script {
    check_run
  }
  notify_master /etc/keepalived/mysql/mymaster.sh
  notify_backup /etc/keepalived/mysql/mybackup.sh
  notify_stop /etc/keepalived/mysql/mystop.sh
  virtual_ipaddress {
    172.19.0.110/24 brd 172.19.0.255 dev eth0 label eth0:0  
    # 虚拟IP地址
  }
}

Slave

! Configuration File for keepalived
global_defs {
  router_id KeepAlive_Mysql
}
vrrp_script check_run {
  script "/etc/keepalived/mysql/mycheck.sh"
  interval 10
}
vrrp_sync_group VG1 {
  group {
    VI_1
  }
}
vrrp_instance VI_1 {
  state BACKUP
  interface eth0
  virtual_router_id 51
  priority 90
  advert_int 1
  nopreempt
  authentication {
    auth_type PASS
    auth_pass 123456
  }
  track_script {
    check_run
  }
  notify_master /etc/keepalived/mysql/mymaster.sh
  notify_backup /etc/keepalived/mysql/mybackup.sh
  notify_stop /etc/keepalived/mysql/mystop.sh
  virtual_ipaddress {
    172.19.0.110/24 brd 172.19.0.255 dev eth0 label eth0:0
  }
}
mycheck
#!/bin/sh
##################################################
#File Name  : mycheck.sh
#Date       : 2019-08-07
#Description: mysql is working MYSQL_OK is 1
#             mysql is down MYSQL_OK is 0
#Writer     ：by L.H.W
##################################################
BASEPATH=/etc/keepalived/mysql/$HOSTNAME
LOGSPATH=$BASEPATH/logs
[[ -d $LOGSPATH ]] || mkdir -p $LOGSPATH
CHECK_TIME=3
MYSQL_OK=1
source /root/.mysqlenv

function check_mysql_helth(){
$mysql -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ;then
  MYSQL_OK=1
else
  MYSQL_OK=0
fi
return $MYSQL_OK
}

while [ $CHECK_TIME -ne 0 ]
do
CHECK_TIME=$((CHECK_TIME-1))
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
  CHECK_TIME=0
  echo "$(date "+%Y-%m-%d %H:%M:%S") The mycheck.sh, mysql is running..." >> $LOGSPATH/mysql_switch.log
  exit 0
fi

if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ];then
  echo "$(date "+%Y-%m-%d %H:%M:%S") The mycheck.sh, mysql is down, after switch..." >> $LOGSPATH/mysql_switch.log
  systemctl stop keepalived
  exit 1
fi
sleep 1
done
mymaster
#!/bin/sh
##################################################
#File Name  : mymaster.sh
#Date       : 2019-08-07
#Description: First determine whether synchronous
#             replication is performed, and if no
#             execution is completed, wait for 1
#             minutes. Log logs and POS after
#             switching, and record files synchronously.
#Writer     ：by L.H.W
##################################################
BASEPATH=/etc/keepalived/mysql/$HOSTNAME
LOGSPATH=$BASEPATH/logs
[[ -d $LOGSPATH ]] || mkdir -p $LOGSPATH
source /root/.mysqlenv

$mysql -e "show slave status\G" > $LOGSPATH/mysqlslave.states
Master_Log_File=`cat $LOGSPATH/mysqlslave.states | grep -w Master_Log_File | awk -F": " '{print $2}'`
Relay_Master_Log_File=`cat $LOGSPATH/mysqlslave.states | grep -w Relay_Master_Log_File | awk -F": " '{print $2}'`
Read_Master_Log_Pos=`cat $LOGSPATH/mysqlslave.states | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}'`
Exec_Master_Log_Pos=`cat $LOGSPATH/mysqlslave.states | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}'`

i=1
while true
do
  if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos -eq $Exec_Master_Log_Pos ];then
    echo "$(date "+%Y-%m-%d %H:%M:%S") The mymaster.sh, slave sync ok... " >> $LOGSPATH/mysql_switch.log
    break
  else
    sleep 1
    if [ $i -gt 60 ];then
      break
    fi
    continue
    let i++
  fi
done

$mysql -e "stop slave;"
$mysql -e "set global innodb_support_xa=0;"
$mysql -e "set global sync_binlog=0;"
$mysql -e "set global innodb_flush_log_at_trx_commit=0;"
$mysql -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';flush privileges;"
$mysql -e "show master status;" > $LOGSPATH/master_status.txt
cat $LOGSPATH/master_status.txt >> $LOGSPATH/mysql_switch.log
# sync pos file
/usr/bin/ssh -o StrictHostKeyChecking=no root@$REMOTE_IP date 
/usr/bin/scp $LOGSPATH/master_status.txt root@$REMOTE_IP:/tmp/backup_master.status
echo "$(date "+%Y-%m-%d %H:%M:%S") The mymaster.sh, Sync pos file sucess." >> $LOGSPATH/mysql_switch.log
mybackup
#!/bin/sh
##################################################
#File Name  : mybackup.sh
#Date       : 2019-08-07
#Description: Empty the slave configuration, retrieve
#             the remote log file and Pos, and open
#             the synchronization
#Writer     ：by L.H.W
##################################################
BASEPATH=/etc/keepalived/mysql/$HOSTNAME
LOGSPATH=$BASEPATH/logs
[[ -d $LOGSPATH ]] || mkdir -p $LOGSPATH
source /root/.mysqlenv
CHECK_TIME=6
SLAVE_OK=1

$mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';flush privileges;"
$mysql -e "set global innodb_support_xa=0;"
$mysql -e "set global sync_binlog=0;"
$mysql -e "set global innodb_flush_log_at_trx_commit=0;"
$mysql -e "flush logs;"
$mysql -e "reset slave all;"


function check_slave(){
LOGFILE=$1
IO_STATUS=`grep Slave_IO_Running: $LOGFILE| awk -F": " '{print $2}'`
SQL_STATUS=`grep Slave_SQL_Running: $LOGFILE| awk -F": " '{print $2}'`

if [[ "$IO_STATUS" = "Yes" ]] && [[ "$SQL_STATUS" = "Yes" ]] ;then
  SLAVE_OK=1
else
  SLAVE_OK=0
fi
return $SLAVE_OK
}

while [ $CHECK_TIME -ne 0 ]
do
CHECK_TIME=$((CHECK_TIME-1))

# 存在同步位置信息文件则尝试进行从库同步
if [ -f /tmp/backup_master.status ]; then
  New_ReM_File=`cat /tmp/backup_master.status | grep -v File |awk '{print $1}'` 
  New_ReM_Position=`cat /tmp/backup_master.status | grep -v File |awk '{print $2}'`
  echo "$(date "+%Y-%m-%d %H:%M:%S") This mybackup.sh, New_ReM_File:$New_ReM_File,New_ReM_Position:$New_ReM_Position" >> $LOGSPATH/mysql_switch.log
  $mysql -e "change master to master_host='$REMOTE_IP',master_port=3306,master_user='repl',master_password='123456',master_log_file='$New_ReM_File',master_log_pos=$New_ReM_Position;"
  $mysql -e "start slave;"
fi

SLAVE_LOGFILE=$LOGSPATH/slave_status.txt
$mysql -e "show slave status\G;" > $SLAVE_LOGFILE
check_slave $SLAVE_LOGFILE
cat $SLAVE_LOGFILE >> $LOGSPATH/mysql_switch.log

# 同步成功则正常退出
if [ $SLAVE_OK = 1 ] ; then
  CHECK_TIME=0
  echo "$(date "+%Y-%m-%d %H:%M:%S") The mybackup.sh, Sync pos file sucess..." >> $LOGSPATH/mysql_switch.log
  rm -f /tmp/backup_master.status
  exit 0
fi

# 同步失败 且 尝试次数超过CHECK_TIME次数
if [ $SLAVE_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ];then
  echo "$(date "+%Y-%m-%d %H:%M:%S") The scripts mybackup.sh running error..." >> $LOGSPATH/mysql_switch.log
  exit 1
fi

sleep 15
done
mystop
#!/bin/sh
##################################################
#File Name  : mystop.sh
#Date       : 2019-08-07
#Description: Set parameters to ensure that the data
#             is not lost, and finally check to see
#             if there are still write operations,
#             the last 1 minutes to exit
#Writer     ：by L.H.W
##################################################
BASEPATH=/etc/keepalived/mysql/$HOSTNAME
LOGSPATH=$BASEPATH/logs
[[ -d $LOGSPATH ]] || mkdir -p $LOGSPATH
source /root/.mysqlenv

$mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';flush privileges;"
$mysql -e "set global innodb_support_xa=1;"
$mysql -e "set global sync_binlog=1;"
$mysql -e "set global innodb_flush_log_at_trx_commit=1;"

$mysql -e "show master status\G" > $LOGSPATH/mysqlmaster0.states
M_File1=`cat $LOGSPATH/mysqlmaster0.states | awk -F': ' '/File/{print $2}'`
M_Position1=`cat $LOGSPATH/mysqlmaster0.states | awk -F': ' '/Position/{print $2}'`
sleep 2
$mysql -e "show master status\G" > $LOGSPATH/mysqlmaster1.states
M_File2=`cat $LOGSPATH/mysqlmaster1.states | awk -F': ' '/File/{print $2}'`
M_Position2=`cat $LOGSPATH/mysqlmaster1.states | awk -F': ' '/Position/{print $2}'`

i=1
while true
do
  if [ $M_File1 = $M_File2 ] && [ $M_Position1 -eq $M_Position2 ];then
    echo "$(date "+%Y-%m-%d %H:%M:%S") The mystop.sh, master sync ok..." >> $LOGSPATH/mysql_switch.log
    exit 0
  else
    sleep 1
    if [ $i -gt 60 ];then
      break
    fi
    continue
    let i++
  fi
done
echo "$(date "+%Y-%m-%d %H:%M:%S") The mystop.sh, master sync exceed one minutes..." >> $LOGSPATH/mysql_switch.log
my.cnf

Master

[mysqld]
log-bin=mysql-bin
lower_case_table_names = 1
default-time-zone = '+08:00'
character-set-server = utf8
event_scheduler = on
server-id= 1
expire_logs_days = 10

binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema

binlog-do-db = mydatabase

skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid

Slave

[mysqld]
log-bin=mysql-bin
lower_case_table_names = 1
default-time-zone = '+08:00'
character-set-server = utf8
event_scheduler = on
server-id= 2
expire_logs_days = 10

binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema

binlog-do-db = mydatabase

skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid
mysqlenv

Master

export REMOTE_IP=172.19.0.3
export mysql='/usr/bin/mysql -uroot -p123456'

Slave

export REMOTE_IP=172.19.0.2
export mysql='/usr/bin/mysql -uroot -p123456'
posted on 2019-08-09 13:29  L.H.W  阅读(1682)  评论(0)    收藏  举报