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

技术分享 3年前 (2022-12-10) 0 999+

关注

概述

本次的文章会格外的长，网上大多的配置流程已经不可使用，本篇文章可以称为保姆级教程，而且通过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 statusG;"  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 statusG;"  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 statusG;"  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 statusG;"  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.