搭建keepalived+mysql主从复制高可用

准备工作

  • 完成keepalived的安装
  • 完成docker的安装
  • docker镜像里面自行安装iproute2, vim, iputils-ping(可选)等工具,便于测试
apt-get install iproute2
apt-get install vim
apt-get install iputils-ping

主数据库master

1. 使用docker安装mysql

mkdir -p ~/compose/mysql-master
cd ~/compose/mysql-master

cat docker-compose.yml
version: '2'
services:
  mysql-master:
    image: mysql:5
    restart: always
    container_name: mysql-master
    ports:
      - 3306:3306
    volumes:
      - ./conf.d:/etc/mysql/conf.d
      - /data/docker/mysql-master/data:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=123456
networks:
  default:
    external:
      name: service

假如没有把3306端口映射到宿主机,在宿主机上可通过docker-ip:3306来访问。

2. mysql的配置

cat conf.d/lowercase.cnf
[mysqld]
lower_case_table_names = 1
default-time-zone = '+08:00'
character-set-server = utf8
event_scheduler = on
log-bin = mysql-bin
server-id = 1

参数说明:

  • lower_case_table_names 设置不区分大小写
  • default-time-zone 设置时区为东八区
  • character-set-server 修改字符集为utf8
  • log-bin 开启二进制日志
  • server-id 设置server-id

master开启二进制日志后默认记录所有库所有表的操作,可以通过配置来指定只记录指定的数据库甚至指定的表的操作,具体在mysql配置文件的[mysqld]可添加修改如下选项:

# 不同步哪些数据库
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
# 只同步哪些数据库,除此之外,其他不同步
binlog-do-db = mydatabase

3. 启动服务

docker-compose pull && docker-compose up -d

4. 在宿主机连接mysql

说明: 由于我是使用虚拟机安装的字符版Ubuntu系统,所以使用MyCli作为mysql命令行工具来连接mysql。

# 查看master主机的ip地址为192.168.11.188,使用MyCli连接mysql
mycli -h 192.168.11.188 -u root -p 123456

# 给root用户分配远程访问权限:
grant all on *.* to root@'%' identified by "123456";
flush privileges; 

# 查看master状态
mysql root@192.168.11.188:(none)> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 586      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set
Time: 0.004s

需要记录主数据库的二进制文件名(mysql-bin.000003)和位置586。

从数据库slave

1. docker-compose.yml

只需要从主数据库的配置名称由mysql-master改为mysql-slave即可。

2. mysql配置

cat conf.d/lowercase.cnf
[mysqld]
lower_case_table_names = 1
default-time-zone = '+08:00'
character-set-server = utf8
event_scheduler = on
server-id= 2

需添加server-id并且与主数据库中不一致

3. 启动服务

docker-compose pull && docker-compose up -d

4. 在宿主机连接mysql

# 查看slave主机的ip地址为192.168.11.186,使用MyCli连接mysql
mycli -h 192.168.11.186 -u root -p 123456
# 给root用户分配远程访问权限,略

# 执行同步SQL语句,参照MASTER配置:
CHANGE MASTER TO MASTER_HOST='192.168.11.188',MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=586;

# 启动slave同步进程
start slave;

# 查看slave状态:
show slave status\G;

其中下面两项为YES则表示成功:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试主从复制

在MASTER中新建数据库和表,发现数据在SLAVE中已经实时同步过来

Keepalived监控mysql服务

1. master主机上的配置

cat /etc/keepalived/keepalived.conf

vrrp_script chk_mysql_port {     #检测mysql服务是否在运行。有很多方式,比如进程,用脚本检测等等
    script "/opt/chk_mysql.sh"   #这里通过脚本监测
    interval 2                   #脚本执行间隔,每2s检测一次
    weight -5                    #脚本结果导致的优先级变更,检测失败(脚本返回非0)则优先级 -5
    fall 2                    #检测连续2次失败才算确定是真失败。会用weight减少优先级(1-255之间)
    rise 1                    #检测1次成功就算成功。但不修改优先级
}
vrrp_instance VI_1 {
    state MASTER
    interface ens33 #指定虚拟ip的网卡接口,不一定是eth0根据ifconfig确定
    virtual_router_id 51 #路由器标识,MASTER和BACKUP必须是一致的
    priority 100 #定义优先级,数字越大,优先级越高,在同一个vrrp_instance下,MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后,就可以将VIP资源再次抢回来
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        192.168.11.25
    }
    track_script {
       chk_mysql_port
    }
}

需要配置的地方有:script,state,interface,virtual_router_id,priority,virtual_ipaddress等

2. slave主机上的配置

cat /etc/keepalived/keepalived.conf

vrrp_script chk_mysql_port {     
    script "/opt/chk_mysql.sh"
    interval 2
    weight -5
    fall 2
    rise 1
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        192.168.11.25
    }
    track_script {
       chk_mysql_port
    }
}

只需要设置state为BACKUP, priority比MASTER低即可。

3. 监测监本的配置

cat /opt/chk_mysql.sh

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]
then
    /etc/init.d/keepalived stop
else
   echo "running..." >> /opt/keepalived-running-info.log
   sleep 5000
fi

Keepalived监测Mysql测试

先要保证两台服务器的mysql服务正常启动哦~

1. 启动Keepalived

# 在master和slave上执行
sudo /etc/init.d/keepalived start

# 查看脚本是否正常执行
tail -f /opt/keepalived-running-info.log

# 查看master的ip,发现虚拟ip绑定成功
ip addr

2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:64:35:17 brd ff:ff:ff:ff:ff:ff
    inet 192.168.11.188/24 brd 192.168.11.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.11.25/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe64:3517/64 scope link
       valid_lft forever preferred_lft forever

2. 高可用测试

在任意一台主机执行以下命令测试:

mycli -h 192.168.11.25 -u root -p 123456 #ok
mycli -h 192.168.11.188 -u root -p 123456 #ok
mycli -h 192.168.11.186 -u root -p 123456 #ok

再次查看master主机的ip,发现虚拟ip不见了:

ip addr

2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:64:35:17 brd ff:ff:ff:ff:ff:ff
    inet 192.168.11.188/24 brd 192.168.11.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe64:3517/64 scope link
       valid_lft forever preferred_lft forever

此时查看slave主机的ip定, 发现ip漂移情况,虚拟ip自动绑定到到了slave主机上:

ip addr

2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:f0:00:ad brd ff:ff:ff:ff:ff:ff
    inet 192.168.11.186/24 brd 192.168.11.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.11.25/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fef0:ad/64 scope link
       valid_lft forever preferred_lft forever

此时查看slave主机情况,Slave_IO_Running变成了Connecting:

mycli -h 192.168.11.186 -u root -p 123456
show slave status\G;

Slave_IO_Running | Connecting
Slave_SQL_Running | Yes

继续测试

现在把master重新启动

mycli -h 192.168.11.188 -u root -p 123456 #ok
show master status;
+------------------+----------+--------------+-------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB              | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------+-------------------+
| mysql-bin.000009 | 154      |              | mysql,test,information_schema |                   |
+------------------+----------+--------------+-------------------------------+-------------------+
1 row in set
Time: 0.004s

会发现信息发生了改变,再次查看slave的状态恢复正常:

Slave_IO_Running | Yes
Slave_SQL_Running | Yes

继续查看master主机发现未绑定vip,vip依然存在于slave所属主机上面。

那么现在把slave停掉试试看:

docker stop mysql-slave

mycli -h 192.168.11.186 -u root -p 123456 #error
mycli -h 192.168.11.25 -u root -p 123456 # error
mycli -h 192.168.11.188 -u root -p 123456 # ok

出现只有master主机的mysql服务能访问的情况,是因为上面测试把两个mysql服务停止,脚本监测不到3306端口执行了/etc/init.d/keepalived stop,所以需要重新启动keepalived :

# master主机
sudo /etc/init.d/keepalived start

# slave主机
docker start mysql-slave
sudo /etc/init.d/keepalived start

这个时候发现vip又重新绑定到master上面,OK,一切正常。

总结

写的可能有些啰嗦,但是每一步的操作和测试又是必要的,只为记录自己的一次学习心得。

原创文章,作者:kinpo, k,如若转载,请注明出处:https://www.yidc.net/archives/5288