一键部署MySQL8+keepalived双主热备高可用

keepalived,双主,可用,一键部署 · 浏览次数 : 517

小编点评

# Keepalived状态转换测试 **Master** * 在Master备机上做写操作，第一次写会发生半同步等待（10s左右） * 第二写会退化成异步复制恢复主机上的mysqldocker * 最后变成MASTER状态 **Slave** * 在Master备机上做写操作，第一次写会发生半同步等待（10s左右） * 第二写会退化成异步复制恢复主机上的mysqldocker * 最后变成MASTER状态 **测试结果** * Keepalived状态从FAULT转变为BACKUP，最后变成MASTER状态 * Keepalived状态从MASTER转变为BACKUP，最后变成MASTER状态 **注意** * Keepalived状态转换测试需要在Master备机上做写操作 * First write会发生半同步等待，因为Master备机上做写操作后，先写操作会阻塞 * Second write会退化成异步复制，因为Master备机上做写操作后，先写操作会阻塞

正文

概述

本次的文章会格外的长，网上大多的配置流程已经不可使用，本篇文章可以称为保姆级教程，而且通过shell脚本大大减少了部署mysql主从，双主的工作量。

如上图，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

主从配置

安装MySQL客户端

两台Master都需要安装，而且后续keepalived也需要安装

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

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

编写脚本文件

touch /home/mysql_config/mysql_script.sh
chmod +x /home/mysql_config/mysql_script.sh

cat > /home/mysql_config/mysql_script.sh <<'EOF'
#!/bin/bash
MASTER_RPL_USER=rpl_admin
MASTER_RPL_PASSWORD=rpl_admin123456
# master and slaves admin user
ADMIN_USER=root
ADMIN_PASSWORD=admin123456
ALLOW_HOST=%

MASTER_HOST=192.168.10.129
MASTER_PORT=3306
SLAVE_HOST=192.168.10.129
SLAVE_PORT=4306

# master
DROP_USER_SQL="DROP USER '$MASTER_RPL_USER'@'$ALLOW_HOST';"
CREATE_USER_SQL="CREATE USER '$MASTER_RPL_USER'@'$ALLOW_HOST' IDENTIFIED BY '$MASTER_RPL_PASSWORD';"
GRANT_PRIVILEGES_SQL="GRANT REPLICATION SLAVE ON *.* TO '$MASTER_RPL_USER'@'$ALLOW_HOST';"
FLUSH_PRIVILEGES_SQL="FLUSH PRIVILEGES;"

mysql -h$MASTER_HOST -P$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$CREATE_USER_SQL"
mysql -h$MASTER_HOST -P$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$GRANT_PRIVILEGES_SQL"
mysql -h$MASTER_HOST -P$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$FLUSH_PRIVILEGES_SQL"

# master's slave shell
RESULT=`mysql -h$MASTER_HOST -p$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "SHOW MASTER STATUS;" | grep -v grep |tail -n +2| awk '{print $1,$2}'`
LOG_FILE_NAME=`echo $RESULT | grep -v grep | awk '{print $1}'`
LOG_FILE_POS=`echo $RESULT | grep -v grep | awk '{print $2}'`

SYNC_SQL="change master to master_host='$MASTER_HOST', master_port=$MASTER_PORT, master_user='$MASTER_RPL_USER',master_password='$MASTER_RPL_PASSWORD',master_log_file='$LOG_FILE_NAME',master_log_pos=$LOG_FILE_POS;"
START_SYNC_SQL="start REPLICA;"
STATUS_SQL="show REPLICA status\G;"

mysql -h$SLAVE_HOST -P$SLAVE_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$SYNC_SQL"
mysql -h$SLAVE_HOST -P$SLAVE_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$START_SYNC_SQL"
mysql -h$SLAVE_HOST -P$SLAVE_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$STATUS_SQL"
EOF

执行脚本并检查

sh /home/mysql_config/mysql_script.sh
*************************** 1. row ***************************
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

配置第二台

部署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

主从配置

安装MySQL客户端

两台Master都需要安装，而且后续keepalived也需要安装

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

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

编写脚本文件

touch /home/mysql_config/mysql_script.sh
chmod +x /home/mysql_config/mysql_script.sh

cat > /home/mysql_config/mysql_script.sh <<'EOF'
#!/bin/bash
MASTER_RPL_USER=rpl_admin
MASTER_RPL_PASSWORD=rpl_admin123456
# master and slaves admin user
ADMIN_USER=root
ADMIN_PASSWORD=admin123456
ALLOW_HOST=%

MASTER_HOST=192.168.10.130
MASTER_PORT=3306
SLAVE_HOST=192.168.10.130
SLAVE_PORT=4306

# master
DROP_USER_SQL="DROP USER '$MASTER_RPL_USER'@'$ALLOW_HOST';"
CREATE_USER_SQL="CREATE USER '$MASTER_RPL_USER'@'$ALLOW_HOST' IDENTIFIED BY '$MASTER_RPL_PASSWORD';"
GRANT_PRIVILEGES_SQL="GRANT REPLICATION SLAVE ON *.* TO '$MASTER_RPL_USER'@'$ALLOW_HOST';"
FLUSH_PRIVILEGES_SQL="FLUSH PRIVILEGES;"

mysql -h$MASTER_HOST -P$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$CREATE_USER_SQL"
mysql -h$MASTER_HOST -P$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$GRANT_PRIVILEGES_SQL"
mysql -h$MASTER_HOST -P$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "$FLUSH_PRIVILEGES_SQL"

# master's slave shell
RESULT=`mysql -h$MASTER_HOST -p$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "SHOW MASTER STATUS;" | grep -v grep |tail -n +2| awk '{print $1,$2}'`
LOG_FILE_NAME=`echo $RESULT | grep -v grep | awk '{print $1}'`
LOG_FILE_POS=`echo $RESULT | grep -v grep | awk '{print $2}'`

SYNC_SQL="change master to master_host='$MASTER_HOST', master_port=$MASTER_PORT, master_user='$MASTER_RPL_USER',master_password='$MASTER_RPL_PASSWORD',master_log_file='$LOG_FILE_NAME',master_log_pos=$LOG_FILE_POS;"
START_SYNC_SQL="start REPLICA;"
STATUS_SQL="show REPLICA status\G;"

mysql -h$SLAVE_HOST -P$SLAVE_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$SYNC_SQL"
mysql -h$SLAVE_HOST -P$SLAVE_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$START_SYNC_SQL"
mysql -h$SLAVE_HOST -P$SLAVE_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$STATUS_SQL"
EOF

执行脚本并检查

sh /home/mysql_config/mysql_script.sh
*************************** 1. row ***************************
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

双主配置

Master1作为主，Master2作为备份

Master1的IP地址为：192.168.10.129
Master2的IP地址为：192.168.10.130

安装MySQL客户端

两台Master都需要安装，而且后续keepalived也需要安装

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

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

编写配置文件

touch /home/mysql_config/rplShell.sh
chmod +x /home/mysql_config/rplShell.sh

cat > /home/mysql_config/rplShell.sh <<'EOF'
#!/bin/bash
MASTER_RPL_USER=rpl_admin
MASTER_RPL_PASSWORD=rpl_admin123456
# master and slaves admin user
ADMIN_USER=root
ADMIN_PASSWORD=admin123456

MASTER_HOST=192.168.10.130
MASTER_PORT=3306
BACKUP_HOST=192.168.10.129
BACKUP_PORT=3306

RESULT=`mysql -h$MASTER_HOST -P$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "SHOW MASTER STATUS;" | grep -v grep |tail -n +2| awk '{print $1,$2}'`
LOG_FILE_NAME=`echo $RESULT | grep -v grep | awk '{print $1}'`
LOG_FILE_POS=`echo $RESULT | grep -v grep | awk '{print $2}'`

SYNC_SQL="change master to master_host='$MASTER_HOST', master_port=$MASTER_PORT, master_user='$MASTER_RPL_USER',master_password='$MASTER_RPL_PASSWORD',master_log_file='$LOG_FILE_NAME',master_log_pos=$LOG_FILE_POS;"
START_SYNC_SQL="start REPLICA;"
STATUS_SQL="show REPLICA status\G;"

mysql -h$BACKUP_HOST -P$BACKUP_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$SYNC_SQL"
mysql -h$BACKUP_HOST -P$BACKUP_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$START_SYNC_SQL"
mysql -h$BACKUP_HOST -P$BACKUP_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$STATUS_SQL"
EOF

执行脚本并检查

sh /home/mysql_config/rplShell.sh
*************************** 1. row ***************************
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

Master2作为主，Master1作为备份

Master1的IP地址为：192.168.10.129
Master2的IP地址为：192.168.10.130

安装MySQL客户端

两台Master都需要安装，而且后续keepalived也需要安装

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

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

编写配置文件

touch /home/mysql_config/rplShell.sh
chmod +x /home/mysql_config/rplShell.sh

cat > /home/mysql_config/rplShell.sh <<'EOF'
#!/bin/bash
MASTER_RPL_USER=rpl_admin
MASTER_RPL_PASSWORD=rpl_admin123456
# master and slaves admin user
ADMIN_USER=root
ADMIN_PASSWORD=admin123456

MASTER_HOST=192.168.10.129
MASTER_PORT=3306
BACKUP_HOST=192.168.10.130
BACKUP_PORT=3306

RESULT=`mysql -h$MASTER_HOST -P$MASTER_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e "SHOW MASTER STATUS;" | grep -v grep |tail -n +2| awk '{print $1,$2}'`
LOG_FILE_NAME=`echo $RESULT | grep -v grep | awk '{print $1}'`
LOG_FILE_POS=`echo $RESULT | grep -v grep | awk '{print $2}'`

SYNC_SQL="change master to master_host='$MASTER_HOST', master_port=$MASTER_PORT, master_user='$MASTER_RPL_USER',master_password='$MASTER_RPL_PASSWORD',master_log_file='$LOG_FILE_NAME',master_log_pos=$LOG_FILE_POS;"
START_SYNC_SQL="start REPLICA;"
STATUS_SQL="show REPLICA status\G;"

mysql -h$BACKUP_HOST -P$BACKUP_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$SYNC_SQL"
mysql -h$BACKUP_HOST -P$BACKUP_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$START_SYNC_SQL"
mysql -h$BACKUP_HOST -P$BACKUP_PORT -u"$ADMIN_USER" -p"$ADMIN_PASSWORD" -e"$STATUS_SQL"
EOF

执行脚本并检查

sh /home/mysql_config/rplShell.sh
*************************** 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可能会遇到的问题

主机能启动，备机不能启动
1. 关闭防火墙
2. 两台机器的网卡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

一键部署MySQL8+keepalived双主热备高可用

小编点评

正文

概述

部署流程

配置第一台Master

部署Master

准备Master配置文件my.cnf

运行Master

启动半同步插件

重新开启

部署Slave

准备Slave配置文件my.cnf

运行Slave

主从配置

安装MySQL客户端

编写脚本文件

执行脚本并检查

配置第二台

部署Master

准备Master配置文件my.cnf

运行Master

启用半同步插件

重新开启

部署Slave

准备Slave配置文件my.cnf

运行Slave

主从配置

安装MySQL客户端

编写脚本文件

执行脚本并检查

双主配置

Master1作为主，Master2作为备份

安装MySQL客户端

编写配置文件

执行脚本并检查

Master2作为主，Master1作为备份

安装MySQL客户端

编写配置文件

执行脚本并检查

配置Keepalived

Master1主机配置

安装keepalived

编写keepalived配置文件

MySQL状态监测脚本

安装MySQL客户端

配置Keepalived的MySQL状态监测脚本

Master2备机配置

安装keepalived

编写keepalived配置文件

MySQL状态监测脚本

安装MySQL客户端

配置Keepalived的MySQL状态监测脚本

启动Keepalived

Keepalived可能会遇到的问题

测试

数据同步测试

高可用测试

启用备机测试

主机恢复测试

结论

与一键部署MySQL8+keepalived双主热备高可用相似的内容：

# 热门排行