MySQL高可用集群方案

HiperMatrix平台目前对MySQL没有太大的并发量的读写，目前使用一个双主的集群既可以满足高可用的需求

Docker部署MySQL双主高可用集群

准备两个服务器节点，节点信息如下：

节点1: 192.168.1.101（MySQL master节点）
节点2: 192.168.1.102（MySQL slave节点）

节点1上部署MySQL服务1

在节点1上面创建MySQL master配置文件, 命名为master.cnf, 内容如下:

[mysqld]
# MySQL实例id值，数值，保证集群里面的MySQL id不要重复
server-id       = 1
# 要生成二进制日志文件 master服务器一定要开启
log-bin         = mysql-bin
# auto_imcrement步进值，一般情况下集群里面有几个MySQL实例就填上该数值
auto_increment_increment = 2 
# 表示自增长字段起始值, 一般按照master master服务器的顺序进行设置
auto_increment_offset=1  
# 允许的最大连接数量
max_connections = 2000

节点1上运行：

docker run -d --name mysql-1 --net iot \
    -p 3306:3306 \
    -v ./master.cnf:/etc/mysql/conf.d/master.cnf \
    -v /opt/db/master/mysql:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=123456 \
    mysql:8.0.26

mysql-1启动完成后，在节点1依次执行以下启动mysql console

docker exec -t mysql-1 bash
mysql -uroot -p123456

然后在mysql console中执行一下的几条msql指令

create user 'slave'@'%' identified with mysql_native_password by 'replPassWd';
grant replication slave,replication client on *.* to 'slave'@'%';
flush privileges;

show master status;

也可以执行一下命令查看用户权限授予情况：

show grants for 'slave'@'%';

完整的执行过程如下：

$ docker exec -it mysql-1 bash
root@cabf6d592173:/# mysql -uroot -p123456
mysql: [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 16
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 user 'slave'@'%' identified with mysql_native_password by 'replPassWd';
Query OK, 0 rows affected (0.02 sec)

mysql> grant replication slave,replication client on *.* to 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      848 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

节点2上部署MySQL服务2

在节点2上面创建MySQL master配置文件, 命名为master.cnf, 内容如下:

[mysqld]
# MySQL实例id值，数值，保证集群里面的MySQL id不要重复
server-id       = 2
# 要生成二进制日志文件 master服务器一定要开启
log-bin         = mysql-bin
# auto_imcrement步进值，一般情况下集群里面有几个MySQL实例就填上该数值
auto_increment_increment = 2 
# 表示自增长字段起始值, 一般按照master master服务器的顺序进行设置
auto_increment_offset=2
# 允许的最大连接数量
max_connections = 2000

节点2上运行：

docker run -d --name mysql-2 --net iot \
    -p 3306:3306 \
    -v ./master.cnf:/etc/mysql/conf.d/master.cnf \
    -v /opt/db/master/mysql:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=123456 \
    mysql:8.0.26

mysql-2启动完成后，在节点2依次执行以下命令启动mysql console

docker exec -t mysql-1 bash
mysql -uroot -p123456

然后在mysql console中执行一下的几条msql指令

change master to master_host='192.168.1.101',
master_port=3306,
master_user='slave',
master_password='replPassWd',
master_log_file='mysql-bin.000003',
master_log_pos=848;

start slave;

其中 master_log_file 配置的文件为节点1上show master status命令看到的文件名，master_log_pos为Position的值

完整的执行过程如下：

$ docker exec -it mysql-1 bash
root@cabf6d592173:/# mysql -uroot -p123456
mysql: [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 16
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> change master to master_host='192.168.1.101',
    -> master_port=3306,
    -> master_user='slave',
    -> master_password='replPassWd',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=848;
Query OK, 0 rows affected, 9 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

以上命令是将节点2配置为节点1 MySQL的从节点，节点2的实例会不停的从mysql-1实例上面同步数据过来

可以用一下命令看看slave同步数据的状态：

show slave status\G

节点1配置MySQL为节点2的从节点

在节点2上创建slave用户用于同步数据，方式和节点1上的步骤是一样的

create user 'slave'@'%' identified with mysql_native_password by 'replPassWd';
grant replication slave,replication client on *.* to 'slave'@'%';
flush privileges;

执行完成后，使用show master status查看信息如下：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |  3118397 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

然后再在节点1上执行：

change master to master_host='192.168.1.102',
master_port=3306,
master_user='slave',
master_password='replPassWd',
master_log_file='mysql-bin.000001',
master_log_pos=3118397;

start slave;

其中 master_log_file 配置的文件为前面节点2上执行show master status命令看到的文件名，master_log_pos为Position的值

start slave命令执行之后节点1上MySQL实例也即为mysql-2的从节点，这样mysql-1和mysql-2就组成了一个双主的集群

配置KeepAlived实现VIP访问

安装KeepAlived

以ubuntu为例安装KeepAlived, 执行一下命令就可以完成keepalived的安装，其他Linux系统采用其对应的包管理命令安装KeepAlived

sudo apt install keepalived

节点1配置master KeepAlived

在节点1上创建/etc/keepalived/keepalived_check_mysql.sh，内容如下：

#!/bin/bash

INSTANCE=mysql-1
USER=root
PWD=123456
CMD="mysql -u$USER -p$PWD  -e 'show status;'"
LOG_FILE=/etc/keepalived/action.log


function log() {
    msg=$1

    echo $(date +'%Y-%m-%d %H:%M:%S') $msg >> $LOG_FILE
}

container=`docker ps | grep $INSTANCE`
if [ -z "$container" ]; then
    echo $(date +'%Y-%m-%d %H:%M:%S') "$INSTANCE is not running" >> $LOG_FILE
    exit 1
fi

docker exec -t $INSTANCE bash -c "$CMD" >/dev/null 2>&1

if [ $? == 0 ]; then
    log "mysql login successfully"
    exit 0
else
    let "n=0"
    while [ $n -lt 5 ]
    do
        docker exec -t $INSTANCE bash -c "$CMD" >/dev/null 2>&1
        if [ $? == 0 ]; then
            log "mysql re-login successfully"
            exit 0
        else
            let "n=n+1"
        fi
        sleep 3
    done
    
    log  "mysql connection faild!"

    exit 1
fi

然后将keepalived_check_mysql文件权限更改为可执行：

chmod +x /etc/keepalived/keepalived_check_mysql.sh

在节点1上创建/etc/keepalived/keepalived.conf文件，内容如下：

! Configuration File for keepalived

global_defs {
   notification_email {
     support@h-visions.com
   }
   notification_email_from kp-master@h-visions.com
   smtp_server localhost
   smtp_connect_timeout 30
}

vrrp_script check_mysql_status {
	script "/etc/keepalived/keepalived_check_mysql.sh"
	interval 10
}

vrrp_instance VI_1 {
    state BACKUP
    interface enp1s0
    virtual_router_id 101
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.103
    }
    track_script {
	    check_mysql_status
    }
}

notification_email: 根据实际情况进行更改
notification_email_from: 根据情况进行调整
VI_1: 实例名称，相同的备节点的命令保证和该名称相同
interface: 网卡名称，使用ifconfig查看需要配置VIP地址的网卡
virtual_router_id: 路由id，主节点和备节点必须一致
priority: 优先级，一般备节点要低于主节点
advert_int: 通信检查间隔，这里配置的为1秒
virtual_ipaddress: 虚拟ip地址，根据实际网络环境进行调整，后面访问mysql的集群通过该vip进行访问

配置文件完成后，重启keepalived服务：

sudo service keepalived restart

然后可以查看vip是否已经生效

$ ip addr show enp1s0
2: enp1s0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether e4:3a:6e:3b:fb:04 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.101/24 brd 192.168.10.255 scope global enp1s0
       valid_lft forever preferred_lft forever
    inet 192.168.1.103/32 scope global enp1s0
       valid_lft forever preferred_lft forever
    inet6 fe80::e63a:6eff:fe3b:fb04/64 scope link 
       valid_lft forever preferred_lft forever

看到这一行（inet 192.168.1.103/32 scope global enp1s0）出现，表示vip（192.168.1.103）已经生效

节点2配置backup KeepAlived

在节点2上创建/etc/keepalived/keepalived_check_mysql.sh，内容如下：

#!/bin/bash

INSTANCE=mysql-2
USER=root
PWD=123456
CMD="mysql -u$USER -p$PWD  -e 'show status;'"
LOG_FILE=/etc/keepalived/action.log


function log() {
    msg=$1

    echo $(date +'%Y-%m-%d %H:%M:%S') $msg >> $LOG_FILE
}

container=`docker ps | grep $INSTANCE`
if [ -z "$container" ]; then
    echo $(date +'%Y-%m-%d %H:%M:%S') "$INSTANCE is not running" >> $LOG_FILE
    exit 1
fi

docker exec -t $INSTANCE bash -c "$CMD" >/dev/null 2>&1

if [ $? == 0 ]; then
    log "mysql login successfully"
    exit 0
else
    let "n=0"
    while [ $n -lt 5 ]
    do
        docker exec -t $INSTANCE bash -c "$CMD" >/dev/null 2>&1
        if [ $? == 0 ]; then
            log "mysql re-login successfully"
            exit 0
        else
            let "n=n+1"
        fi
        sleep 3
    done
    
    log  "mysql connection faild!"

    exit 1
fi

然后将keepalived_check_mysql文件权限更改为可执行：

sudo chmod +x /etc/keepalived/keepalived_check_mysql.sh

在节点2上创建/etc/keepalived/keepalived.conf文件，内容如下：

! Configuration File for keepalived

global_defs {
   notification_email {
     support@h-visions.com
   }
   notification_email_from kp-master@h-visions.com
   smtp_server localhost
   smtp_connect_timeout 30

   script_user root
   enable_script_security
}

vrrp_script check_mysql_status {
    script "/etc/keepalived/keepalived_check_mysql.sh"
    interval 10
}

vrrp_instance VI_1 {
    state BACKUP
    interface enp1s0
    virtual_router_id 101
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.103
    }
    track_script {
        check_mysql_status
    }
}

同样当配置完成后，重启keepalived服务：

sudo service keepalived restart

待两个节点的keepalived服务都跑起来之后，之后mysql通过vip（192.168.1.103）的地址进行访问即可，如jdbc:mysql://192.168.1.103:3306/iot_data，当其中的一个数据库实例停止之后，keepalived自动切换到另外一个正常的mysql实例上