Linux实战之MySQL数据库——keepalived实现Mysql双主高可用

Mysql双主高可用介绍

架构介绍

Mysql的双主高可用架构，主要设计思想是两台Mysql服务器通过Mysql复制技术互相将对方作为自己的主服务器，自己又同时作为对方的从服务器来进行复制，实现高可用架构中的数据同步功能，同时采用keepalived来实现mysql的自动故障切换

在这个架构中，两台Mysq|服务器互为主从，同一时刻只有一个Mysql服务器可读写，另一个Mysql服务器只能进行读操作，保证数据的一致性

Keepalived的作用

（1）Keepalived维护一个VIP，此IP用来对外部提供链接服务
（2）keepalived负责监控DB1和DB2上Mysql数据库的运行状态，DB1故障时自动将VIP地址和Mysql服务切换到DB2上
总结： 通过Keepalived保持数据库服务的连续性，整个切换过程非常快，并且对前端Web服务器主机透明

主主互备模式不足

1. 从库可能会一直处于空闲状态 可以用它当从库，负责部分查询
2. 从库要先与主库同步数据才能提供服务，造成一定程度的同步延时

Mysql双主高可用实现

建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP（非抢占模式），尽可能减少主库宕机对业务造成的影响，减少主从同步给线上主库带来的压力

主主互备模式配置

初始化环境

同步时间
关闭防火墙

环境规划

主机	IP	Server_ID
server1：mysql	192.168.213.124	1
server2：mysql-1	192.168.213.127	2

配置server2

[root@mysql-1 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
#master必备
log-bin=mysql-bin
log-bin-index=master-bin.index
#slave必备
relay-log=relay-log
relay-log-index=relay-log.index

重启服务

[root@localhost ~]# systemctl restart mysqld

在server1和server2上分别创建一个用于让从数据库连接的用户

mysql> create user 'copy'@'%' identified with mysql_native_password by 'Cloudbu@123';
mysql> grant replication slave on *.* to 'copy'@'%';
mysql> flush privileges; 刷新授权表信息

在server1上获取当前主节点当前binary log文件名和位置

[root@mysql ~]# mysql -uroot -pZhao123@com
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

设置主节点参数

[root@mysql-1 ~]# mysql -uroot -pZhao123@com
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.213.124',
MASTER_USER='copy',
MASTER_PASSWORD='Cloudbu@123',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=155;

查看主从同步状态

mysql> show slave status\G
	Slave_IO_Running: Yes
	Slave_SQL_Running: Yes
#其中的IO线程和SQL线程均为YES，则配置成功

配置server1

修改配置文件，必须指定中继日志的名称

[root@mysql ~]# vim /etc/my.cnf
[mysqld]
server_id=1
#master必备
log-bin=mysql-bin
log-bin-index=master-bin.index
#slave必备
relay-log=relay-log
relay-log-index=relay-log.index

重启服务

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld

在server1上获取当前主节点当前binary log文件名和位置

[root@mysql-1 ~]# mysql -uroot -pZhao123@com
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      825 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

设置主节点参数

[root@mysql-1 ~]# mysql -uroot -pZhao123@com
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.213.127',
MASTER_USER='copy',
MASTER_PASSWORD='Cloudbu@123',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=825;

查看主从同步状态

mysql> show slave status\G
	Slave_IO_Running: Yes
	Slave_SQL_Running: Yes
#其中的IO线程和SQL线程均为YES，则配置成功

keepalived配置

安装keepalived

两台服务器上都需要安装
（1）安装软件

[root@mysql ~]# wget https://www.keepalived.org/software/keepalived-2.0.1.tar.gz
[root@mysql ~]# mkdir -p /data/keepalived
[root@mysql ~]# tar -xf keepalived-2.0.1.tar.gz -C /usr/local/src
[root@mysql ~]# yum install openssl-devel gcc gcc-c++ make -y
[root@mysql ~]# cd /usr/local/src/keepalived-2.0.1
[root@mysql keepalived-2.0.1]# ./configure --prefix=/data/keepalived/
[root@mysql keepalived-2.0.1]# echo $?
0
[root@mysql keepalived-2.0.1]# make
[root@mysql keepalived-2.0.1]# echo $?
0
[root@mysql keepalived-2.0.1]# make install
[root@mysql keepalived-2.0.1]# echo $?
0

（2）配置规范启动

[root@bogon etc]# pwd
/usr/local/src/keepalived-2.0.1/keepalived/etc
[root@mysql etc]# pwd
/usr/local/src/keepalived-2.0.1/keepalived/etc
[root@mysql etc]# cp -R init /data/keepalived/
[root@mysql etc]# cp -R init /data/keepalived/etc/
[root@mysql etc]# cp -R init.d /data/keepalived/etc/
[root@mysql etc]# ll /data/keepalived/etc/
total 0
drwxr-xr-x 2 root root  86 Mar  3 20:25 init
drwxr-xr-x 2 root root 135 Mar  3 20:25 init.d
drwxr-xr-x 3 root root  44 Mar  3 20:23 keepalived
drwxr-xr-x 2 root root  24 Mar  3 20:23 sysconfig
[root@mysql etc]# cp /data/keepalived/etc/init.d/keepalived /etc/init.d/
[root@mysql etc]# cp /data/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@mysql etc]# mkdir /etc/keepalived
[root@mysql etc]# cp /data/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@mysql etc]# cp /data/keepalived/sbin/keepalived /usr/sbin/

（3）启动

[root@mysql ~]# /etc/init.d/keepalived start
Starting keepalived (via systemctl):                       [  OK  ]
[root@mysql ~]# ps -ef |grep keepalived
root       7935      1  9 20:28 ?        00:00:01 /data/keepalived/sbin/keepalived -D
root       7936   7935  0 20:28 ?        00:00:00 /data/keepalived/sbin/keepalived -D
root      20466   3788  0 20:28 pts/1    00:00:00 grep --color=auto keepalived
root      20490   7935  0 20:28 ?        00:00:00 [keepalived] <defunct>

keepalived配置

keepalived中的两种模式
（1）master->backup模式
一旦主库宕机，虚拟ip会自动漂移到从库，当主库修复后，keepalived启动后，还会把虚拟ip抢占过来，即使设置了非抢占模式（nopreempt）抢占ip的动作也会发生。
（2）backup->backup模式
当主库宕机后虚拟ip会自动漂移到从库上，当原主库恢复和keepalived服务启动后，并不会抢占新主的虚拟ip，即使是优先级高于从库的优先级别，也不会发生抢占。为了减少ip漂移次数，通常是把修复好的主库当做新的备库。

在数据库的应用场景中，不建议数据库的频繁切换，因此，将其配置为keepalived的VIP不抢占模式，具体配置如下：

在mysql上

[root@mysql ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    router_id HA_MYSQL01
}
vrrp_script check_mysqld {
    script "/etc/keepalived/check_mysqld.sh"
    interval 2
}
vrrp_instance HA_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 150
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.213.233
    }
    track_script {
        check_mysqld
    }
}

state BACKUP #在ser01和ser02上均配置为backup
nopreempt #不抢占模式，只在优先级高的机器上设置即可，优先级低的机器上不设置

在mysql-1上

[root@mysql-1 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    router_id HA_MYSQL01
}
vrrp_script check_mysqld {
    script "/etc/keepalived/check_mysqld.sh"
    interval 2
}
vrrp_instance HA_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.213.233
    }
    track_script {
        check_mysqld
    }
}

监控脚本

[root@mysql ~]# cat /etc/keepalived/check_mysqld.sh
#!/bin/bash
user=root
pass=Zhao123@com
/usr/bin/mysql -u$user -p$pass -e "show status;" > /dev/null 2>&1
if [ $? -eq 0 ];
then
    MYSQL_STATUS=0
else
    MYSQL_STATUS=1
fi
exit $MYSQL_STATUS
[root@mysql ~]# chmod +x /etc/keepalived/check_mysqld.sh

Mysql配置账号测试

（1）授权账号

mysql> create user 'keepalived'@'%' identified by 'Cloudbu@123';
mysql> ALTER USER 'keepalived'@'%' IDENTIFIED WITH mysql_native_password BY
'Cloudbu@123';
mysql> flush privileges;

（2）结果测试
在测试节点上用192.168.213.233，即浮动ip测试

[root@configure ~]# mysql -h192.168.213.233 -ukeepalived -pCloudbu@123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

关闭优先级别较高的mysql，VIP会漂移到mysql-1上，客户端连接会显示如下信息，但可重新登录上

[root@configure ~]# mysql -h192.168.213.233 -ukeepalived -pCloudbu@123
mysql> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query

[root@configure ~]# mysql -h192.168.213.233 -ukeepalived -pCloudbu@123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

当mysql恢复后，能够实现数据同步，vip并没有漂移回mysql上，而是保持在mysql-1上；当mysql-1故障后，VIP自然漂移到了mysql上