MySQL高可用实践 PDF mysql8高可用

转载

blueice 2023-06-16 15:03:18

文章标签 MySQL高可用实践 PDF 1024程序员节 mysql docker ci 文章分类 MySQL 数据库

概述

本次的文章会格外的长，网上大多的配置流程已经不可使用，本篇文章可以称为保姆级教程

MySQL高可用实践 PDF mysql8高可用_1024程序员节

如上图，VIP地址为192.168.10.100，如果主机192.168.10.129挂了，会自动切换到备机192.168.10.130上，由于是双主，数据可以进行同步恢复

部署流程大致为

在192.168.10.129服务器上部署docker、mysql一主一丛
在192.168.10.130服务器上部署docker、mysql一主一丛
192.168.10.129的Master1数据库和192.168.10.130的数据库互为主从
在两台服务器上安装keeplived形成高可用

配置第一台Master

部署Master

准备Master配置文件my.cnf

mkdir /home/mysql_config
mkdir /home/mysql_config/master1
mkdir /home/mysql_config/master1/config
touch /home/mysql_config/master1/config/my.cnf
chmod +x /home/mysql_config/master1/config/my.cnf

cat > /home/mysql_config/master1/config/my.cnf <<EOF
[mysqld]
server_id=110
user=mysql
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
max_connections=800
max_connect_errors=1000

log_bin=mysql_binlog_master1
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow.log

log_error=/var/lib/mysql/mysql.err
log_replica_updates=ON
enforce_gtid_consistency=ON
gtid_mode=ON

plugin-load="rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so"
#rpl_semi_sync_source_enabled=ON
#rpl_semi_sync_replica_enabled=ON

binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=performance_schema

[client]
default-character-set=utf8mb4
EOF

运行Master

会自动下载最新版mysql

docker run -d --name mysql_master1 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=admin123456 -e TZ="Asia/Shanghai" -v /home/mysql_config/master1/config:/etc/mysql/conf.d mysql --authentication_policy=mysql_native_password

启动半同步插件

此步主要是为了将#rpl_semi_sync_source_enabled=ON和#rpl_semi_sync_replica_enabled=ON的井号去掉

mysql会检测semisync_source.so和semisync_replica.so插件的情况，如果没有安装就不能设置rpl_semi_sync_source_enabled和rpl_semi_sync_replica_enabled

cat > /home/mysql_config/master1/config/my.cnf <<EOF
[mysqld]
server_id=110
user=mysql
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
max_connections=800
max_connect_errors=1000

log_bin=mysql_binlog_master1
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow.log

log_error=/var/lib/mysql/mysql.err
log_replica_updates=ON
enforce_gtid_consistency=ON
gtid_mode=ON

plugin-load="rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so"
rpl_semi_sync_source_enabled=ON
rpl_semi_sync_replica_enabled=ON

binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=performance_schema

[client]
default-character-set=utf8mb4
EOF

重新开启

docker restart mysql_master1
docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "show variables like 'rpl_semi_sync_%';"
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_replica_enabled               | ON         |
| rpl_semi_sync_replica_trace_level           | 32         |
| rpl_semi_sync_source_enabled                | ON         |
| rpl_semi_sync_source_timeout                | 10000      |
| rpl_semi_sync_source_trace_level            | 32         |
| rpl_semi_sync_source_wait_for_replica_count | 1          |
| rpl_semi_sync_source_wait_no_replica        | ON         |
| rpl_semi_sync_source_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+

部署Slave

准备Slave配置文件my.cnf

mkdir /home/mysql_config/slave1
mkdir /home/mysql_config/slave1/config
touch /home/mysql_config/slave1/config/my.cnf
chmod +x /home/mysql_config/slave1/config/my.cnf

cat > /home/mysql_config/slave1/config/my.cnf <<EOF
[mysqld]
server_id=120
user=mysql
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
max_connections=800
max_connect_errors=1000

log_bin=mysql_binlog_master1_slave1
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow.log

log_error=/var/lib/mysql/mysql.err
log_replica_updates=ON     
enforce_gtid_consistency=ON
gtid_mode=ON

binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=performance_schema

[client]
default-character-set=utf8mb4
EOF

运行Slave

会自动下载最新版mysql

docker run -d --name mysql_master1_slave1 -p 4306:3306 -e MYSQL_ROOT_PASSWORD=admin123456 -e TZ="Asia/Shanghai" -v /home/mysql_config/slave1/config:/etc/mysql/conf.d mysql --authentication_policy=mysql_native_password

主从配置

创建主库同步用户

docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "CREATE USER 'rpl_admin'@'%' IDENTIFIED BY 'rpl_admin123456';"
docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "GRANT REPLICATION SLAVE ON *.* TO 'rpl_admin'@'%';"
docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "FLUSH PRIVILEGES;"

获取主库binlog文件和Position

docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "SHOW MASTER STATUS\G"
*************************** 1. row ***************************
             File: mysql_binlog_master1.000004
         Position: 1469
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,mysql,sys,performance_schema
Executed_Gtid_Set: 6558fc57-510c-11ed-b668-0242ac110002:1-6

从库设置

master_host:主库IP地址

master_port:主库端口

master_user:用于复制的主库用户

master_password：用于复制的主库用户密码

master_log_file：主库binlog文件

master_log_pos：主库binlog的Position

docker exec -it mysql_master1_slave1 mysql -uroot -padmin123456 -e "change master to master_host='192.168.10.129', master_port=3306, master_user='rpl_admin',master_password='rpl_admin123456',master_log_file='mysql_binlog_master1.000004',master_log_pos=1469;"

docker exec -it mysql_master1_slave1 mysql -uroot -padmin123456 -e "start slave;"
docker exec -it mysql_master1_slave1 mysql -padmin123456 -e "show slave status\G"

配置第二台

部署Master

准备Master配置文件my.cnf

# 创建文件夹
mkdir /home/mysql_config
mkdir /home/mysql_config/master2
mkdir /home/mysql_config/master2/config
touch /home/mysql_config/master2/config/my.cnf
chmod +x /home/mysql_config/master2/config/my.cnf

# 写入配置文件
cat > /home/mysql_config/master2/config/my.cnf <<EOF
[mysqld]
server_id=111
user=mysql
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
max_connections=800
max_connect_errors=1000

log_bin=mysql_binlog_master2
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow.log

log_error=/var/lib/mysql/mysql.err
log_replica_updates=ON
enforce_gtid_consistency=ON
gtid_mode=ON

plugin-load="rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so"
#rpl_semi_sync_source_enabled=ON
#rpl_semi_sync_replica_enabled=ON

binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=performance_schema

[client]
default-character-set=utf8mb4
EOF

运行Master

会自动下载最新版mysql

docker run -d --name mysql_master2 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=admin123456 -e TZ="Asia/Shanghai" -v /home/mysql_config/master2/config:/etc/mysql/conf.d mysql --authentication_policy=mysql_native_password

启用半同步插件

此步主要是为了将#rpl_semi_sync_source_enabled=ON和#rpl_semi_sync_replica_enabled=ON的井号去掉

mysql会检测semisync_source.so和semisync_replica.so插件的情况，如果没有安装就不能设置rpl_semi_sync_source_enabled和rpl_semi_sync_replica_enabled

cat > /home/mysql_config/master2/config/my.cnf <<EOF
[mysqld]
server_id=111
user=mysql
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
max_connections=800
max_connect_errors=1000

log_bin=mysql_binlog_master2
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow.log

log_error=/var/lib/mysql/mysql.err
log_replica_updates=ON
enforce_gtid_consistency=ON
gtid_mode=ON

plugin-load="rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so"
rpl_semi_sync_source_enabled=ON
rpl_semi_sync_replica_enabled=ON

binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=performance_schema

[client]
default-character-set=utf8mb4
EOF

重新开启

docker restart mysql_master2
docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "show variables like 'rpl_semi_sync_%';"
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_replica_enabled               | ON         |
| rpl_semi_sync_replica_trace_level           | 32         |
| rpl_semi_sync_source_enabled                | ON         |
| rpl_semi_sync_source_timeout                | 10000      |
| rpl_semi_sync_source_trace_level            | 32         |
| rpl_semi_sync_source_wait_for_replica_count | 1          |
| rpl_semi_sync_source_wait_no_replica        | ON         |
| rpl_semi_sync_source_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+

部署Slave

准备Slave配置文件my.cnf

mkdir /home/mysql_config/slave2
mkdir /home/mysql_config/slave2/config
touch /home/mysql_config/slave2/config/my.cnf
chmod +x /home/mysql_config/slave2/config/my.cnf

cat > /home/mysql_config/slave2/config/my.cnf <<EOF
[mysqld]
server_id=121
user=mysql
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
max_connections=800
max_connect_errors=1000

log_bin=mysql_binlog_master2_slave2
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow.log

log_error=/var/lib/mysql/mysql.err
log_replica_updates=ON     
enforce_gtid_consistency=ON
gtid_mode=ON

binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=performance_schema

[client]
default-character-set=utf8mb4
EOF

运行Slave

会自动下载最新版mysql

docker run -d --name mysql_master2_slave2 -p 4306:3306 -e MYSQL_ROOT_PASSWORD=admin123456 -e TZ="Asia/Shanghai" -v /home/mysql_config/slave2/config:/etc/mysql/conf.d mysql --authentication_policy=mysql_native_password

主从配置

创建主库同步用户

docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "CREATE USER 'rpl_admin'@'%' IDENTIFIED BY 'rpl_admin123456';"
docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "GRANT REPLICATION SLAVE ON *.* TO 'rpl_admin'@'%';"
docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "FLUSH PRIVILEGES;"

获取主库binlog文件和Position

docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "SHOW MASTER STATUS\G"
*************************** 1. row ***************************
             File: mysql_binlog_master2.000001
         Position: 157
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,mysql,sys,performance_schema
Executed_Gtid_Set: 0b45d31c-510f-11ed-b505-0242ac110002:1

从库设置

master_host:主库IP地址

master_port:主库端口

master_user:用于复制的主库用户

master_password：用于复制的主库用户密码

master_log_file：主库binlog文件

master_log_pos：主库binlog的Position

docker exec -it mysql_master2_slave2 mysql -uroot -padmin123456 -e "change master to master_host='192.168.10.130', master_port=3306, master_user='rpl_admin',master_password='rpl_admin123456',master_log_file='mysql_binlog_master2.000001',master_log_pos=157;"

docker exec -it mysql_master2_slave2 mysql -uroot -padmin123456 -e "start slave;"
docker exec -it mysql_master2_slave2 mysql -padmin123456 -e "show slave status\G"

双主配置

获取Master1的信息

进入Master1宿主机（192.168.10.129），获取Master1的File及Position

docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "show master status\G"
*************************** 1. row ***************************
             File: mysql_binlog_master1.000005
         Position: 197
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,mysql,sys,performance_schema
Executed_Gtid_Set: 6558fc57-510c-11ed-b668-0242ac110002:1-610.

获取Master2的信息

进入Master2宿主机（192.168.10.130），获取Master2的File及Position

[root@bogon ray]# docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "show master status\G"
*************************** 1. row ***************************
             File: mysql_binlog_master2.000002
         Position: 197
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,mysql,sys,performance_schema
Executed_Gtid_Set: 0b45d31c-510f-11ed-b505-0242ac110002:1-4

接下来，互相指定，指定Master1（192.168.10.129:3306）为Master2（192.168.10.130:3306）的主库，且指定Master2（192.168.10.130:3306）为Master1（192.168.10.129:3306）的主库

指定Master1为Master2的主库

指定Master1（192.168.10.129:3306）为Master2（192.168.10.130:3306）的主库

首先登录192.168.10.130宿主机，然后执行以下命令

docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "CHANGE MASTER TO MASTER_HOST='192.168.10.129', MASTER_USER='rpl_admin', MASTER_PASSWORD='rpl_admin123456', MASTER_LOG_FILE='mysql_binlog_master1.000005', MASTER_LOG_POS=197;"

docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "START SLAVE;"

docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "SHOW SLAVE STATUS\G;"
*************************** 1. row ***************************
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

指定Master2为Master1的主库

指定Master2（192.168.10.130:3306）为Master1（192.168.10.129:3306）的主库

首先登录192.168.10.129宿主机，然后执行以下命令

docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "CHANGE MASTER TO MASTER_HOST='192.168.10.130', MASTER_USER='rpl_admin', MASTER_PASSWORD='rpl_admin123456', MASTER_LOG_FILE='mysql_binlog_master2.000002', MASTER_LOG_POS=197;"

docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "START SLAVE;"

docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "SHOW SLAVE STATUS\G;"
*************************** 1. row ***************************
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

配置Keepalived

Master1主机配置

安装keepalived

yum install -y keepalived

编写keepalived配置文件

cat > /etc/keepalived/keepalived.conf <<'EOF'
! Configuration File for keepalived

global_defs {
    router_id HA-M1
    script_user root
}

vrrp_script chk_mysql {
    script /etc/keepalived/chk_mysql.sh
    interval 3
    fall 3
    rise 2
}

vrrp_instance VI_MYSQL {
    state MASTER
    interface ens160
    virtual_router_id 100
    priority 100
    advert_int 1
    unicast_src_ip  192.168.10.129
    unicast_peer {
            192.168.10.130
    }
    
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    
    virtual_ipaddress {
        192.168.10.100/24 dev ens160 label ens160:vip
    }
    
    track_script {
        chk_mysql
    }
}
EOF

router_id：标识，两台需不一样
state MASTER：表明这台是Master角色
priority：优先级，MASTER角色比BACKUP高
virtual_router_id：虚拟路由编号，两台需要一致，两个节点不能一样
192.168.10.100 dev ens32 label ens32:vip：VIP地址—192.168.10.100
script /etc/keepalived/chk_mysql.sh：MySQL的检测脚本，定时去检测MySQL进程是否挂掉，会重启，interval是为间隔时间（2s检测一次）

当Keepalived从BACKUP提升为MASTER时，那么VIP就会漂移到自己身上，这个时候我们只需要将Slave挂载在自己身上即可

MySQL状态监测脚本

安装MySQL客户端

在网站查询https://dev.mysql.com/downloads/repo/yum/要安装的mysql版本，以mysql80-community-release-el8-4.noarch.rpm举例

wget https://dev.mysql.com/get/mysql80-community-release-el8-4.noarch.rpm
rpm -ivh mysql80-community-release-el8-4.noarch.rpm
yum module disable mysql
yum install mysql-community-client
yum module enable mysql

配置Keepalived的MySQL状态监测脚本

touch /etc/keepalived/chk_mysql.sh
chmod +x /etc/keepalived/chk_mysql.sh

cat > /etc/keepalived/chk_mysql.sh <<'EOF'
#!/bin/bash
MYSQL=/usr/bin/mysql
MYSQL_HOST=192.168.10.129
MYSQL_USER=root
MYSQL_PASSWORD=admin123456
CHECK_TIME=3
  
#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
  
MYSQL_OK=1
  
function check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -u$MYSQL_USER -p${MYSQL_PASSWORD} -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
    let "CHECK_TIME -= 1"
    check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
    CHECK_TIME=0
    exit 0
fi
if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
then
    systemctl stop keepalived
    exit 1
fi
sleep 1
done
EOF

Master2备机配置

安装keepalived

yum install -y keepalived

编写keepalived配置文件

cat > /etc/keepalived/keepalived.conf <<'EOF'
! Configuration File for keepalived

global_defs {
    router_id HA-M2
    script_user root
}

vrrp_script chk_mysql {
    script /etc/keepalived/chk_mysql.sh
    interval 3
    fall 3
    rise 2
}

vrrp_instance VI_MYSQL {
    state BACKUP
    interface ens160
    virtual_router_id 100
    nopreempt
    priority 50
    advert_int 1
    unicast_src_ip  192.168.10.130
    unicast_peer {
            192.168.10.129
    }
    
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    
    virtual_ipaddress {
        192.168.10.100/24 dev ens160 label ens160:vip
    }
    
    track_script {
        chk_mysql
    }
}
EOF

注意router_id需要不一样，state BACKUP表明是备机，virtual_router_id需要一致，priority要比MASTER低！

chk_mysql.sh内容如下，可以看到可以根据当前Keepalived的状态分别处理，但是此处的处理都是启动MySQL（如果MySQL进程已退出）

MySQL状态监测脚本

安装MySQL客户端

在网站查询https://dev.mysql.com/downloads/repo/yum/要安装的mysql版本，以mysql80-community-release-el8-4.noarch.rpm举例

wget https://dev.mysql.com/get/mysql80-community-release-el8-4.noarch.rpm
rpm -ivh mysql80-community-release-el8-4.noarch.rpm
yum module disable mysql
yum install mysql-community-client
yum module enable mysql

配置Keepalived的MySQL状态监测脚本

touch /etc/keepalived/chk_mysql.sh
chmod +x /etc/keepalived/chk_mysql.sh

cat > /etc/keepalived/chk_mysql.sh <<'EOF'
#!/bin/bash
MYSQL=/usr/bin/mysql
MYSQL_HOST=192.168.10.130
MYSQL_USER=root
MYSQL_PASSWORD=admin123456
CHECK_TIME=3
  
#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

MYSQL_OK=1
  
function check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -u$MYSQL_USER -p${MYSQL_PASSWORD} -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
    let "CHECK_TIME -= 1"
    check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
    CHECK_TIME=0
    exit 0
fi
if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
then
    systemctl stop keepalived
    exit 1
fi
sleep 1
done
EOF

启动Keepalived

最后在Master1和Master2分别启动Keepalived

systemctl start keepalived

查看Master1网络配置

ifconfig
ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.10.129  netmask 255.255.255.0  broadcast 192.168.10.255
        inet6 fe80::20c:29ff:fe23:aab2  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:23:aa:b2  txqueuelen 1000  (Ethernet)
        RX packets 4797  bytes 524410 (512.1 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 2860  bytes 332307 (324.5 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160:vip: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.10.100  netmask 255.255.255.255  broadcast 0.0.0.0
        ether 00:0c:29:23:aa:b2  txqueuelen 1000  (Ethernet)

查看Master2网络配置

ens160: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.10.130  netmask 255.255.255.0  broadcast 192.168.10.255
        inet6 fe80::20c:29ff:feb4:3ede  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:b4:3e:de  txqueuelen 1000  (Ethernet)
        RX packets 4672  bytes 2048171 (1.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 2703  bytes 275576 (269.1 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

主机上有ens160:vip而备机上没有vip

Keepalived可能会遇到的问题

主机能启动，备机不能启动

关闭防火墙
两台机器的网卡uuid是否冲突（如果是vm ware复制的linux，因为网卡uuid一致导致的）

主机和备机keepalived都为master，有两种方法

关闭防火墙，或设置组播ip可用
两端keepalived设置为单播（使用unicast_src_ip ）

测试

数据同步测试

进入Master1，创建数据库test_db，表test_user ，并插入一条数据。

docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "CREATE DATABASE test_db;"
docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "CREATE TABLE test_db.test_user (id INT(11),name VARCHAR(25),age int(11) );"
docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "INSERT INTO test_db.test_user values (1,'zhangsan',18);"
docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"

查看Master1及从库master1_slave1、查看Master2及从库master2_slave2的数据同步情况

docker exec -it mysql_master1 mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   18 |
+------+----------+------+

docker exec -it mysql_master1_slave1 mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   18 |
+------+----------+------+

docker exec -it mysql_master2 mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   18 |
+------+----------+------+

docker exec -it mysql_master2_slave2 mysql -uroot -padmin123456 -e "SELECT * FROM test_db.test_user;"
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   18 |
+------+----------+------+

高可用测试

启用备机测试

停止主机上的mysql

docker stop mysql_master1

主机上的Keepalived状态

Keepalived[4213]: Stopping
Keepalived_vrrp[4214]: (VI_MYSQL) sent 0 priority
Keepalived_vrrp[4214]: (VI_MYSQL) removing VIPs.

备机的Keepalived状态

Keepalived_vrrp[8986]: (VI_MYSQL) Backup received priority 0 advertisement
Keepalived_vrrp[8986]: (VI_MYSQL) Receive advertisement timeout
Keepalived_vrrp[8986]: (VI_MYSQL) Entering MASTER STATE
Keepalived_vrrp[8986]: (VI_MYSQL) setting VIPs.
Keepalived_vrrp[8986]: (VI_MYSQL) Sending/queueing gratuitous ARPs on ens160 for 192.168.10.100

注意
Master备机上做写操作，第一次写会发生半同步等待（10s左右），第二次写会退化成异步复制

主机恢复测试

启动主机上的mysql和keepalived

docker start mysql_master1
systemctl start keepalived

主机上的Keepalived状态

Keepalived_vrrp[5413]: (VI_MYSQL) Entering BACKUP STATE
Keepalived_vrrp[5413]: VI_MYSQL: sending gratuitous ARP for 192.168.10.129
Keepalived_vrrp[5413]: Sending gratuitous ARP on ens160 for 192.168.10.129
Keepalived_vrrp[5413]: (VI_MYSQL) received lower priority (50) advert from 192.168.10.130 - discarding
Keepalived_vrrp[5413]: (VI_MYSQL) received lower priority (50) advert from 192.168.10.130 - discarding
Keepalived_vrrp[5413]: (VI_MYSQL) received lower priority (50) advert from 192.168.10.130 - discarding
Keepalived_vrrp[5413]: (VI_MYSQL) Receive advertisement timeout
Keepalived_vrrp[5413]: (VI_MYSQL) Entering MASTER STATE
Keepalived_vrrp[5413]: (VI_MYSQL) setting VIPs.
Keepalived_vrrp[5413]: (VI_MYSQL) Sending/queueing gratuitous ARPs on ens160 for 192.168.10.100
Keepalived_vrrp[5413]: Sending gratuitous ARP on ens160 for 192.168.10.100
Keepalived_vrrp[5413]: Sending gratuitous ARP on ens160 for 192.168.10.100
Keepalived_vrrp[5413]: Sending gratuitous ARP on ens160 for 192.168.10.100
Keepalived_vrrp[5413]: Sending gratuitous ARP on ens160 for 192.168.10.100
Keepalived_vrrp[5413]: Sending gratuitous ARP on ens160 for 192.168.10.100

备机的Keepalived状态

Keepalived_vrrp[8986]: (VI_MYSQL) Master received advert from 192.168.10.129 with higher priority 100, ours 50
Keepalived_vrrp[8986]: (VI_MYSQL) Entering BACKUP STATE
Keepalived_vrrp[8986]: (VI_MYSQL) removing VIPs.

结论

如果杀掉主机上的mysql

VIP会漂移到备机上，ifconfig能看到VIP信息
备机的Keepalived从BACKUP状态转换为了MASTER状态
Master备机上做写操作，第一次写会发生半同步等待（10s左右），第二次写会退化成异步复制

恢复主机mysql

ifconfig看下VIP会漂移回来，并且备机上的VIP会消失
刚才在Master备机上写入的数据同步过来了
Keepalived的状态是从FAULT转变为BACKUP，最后变成MASTER

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：MySQl中数据库事务具有哪四个特性 mysql 事务的实现原理

下一篇：MySQL隐藏列查询隐藏mysql版本号

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯