草庐IT

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

大妖史莱姆 2023-03-28 原文

概述

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

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

部署流程大致为

  1. 在192.168.10.129服务器上部署docker、mysql一主一丛
  2. 在192.168.10.130服务器上部署docker、mysql一主一丛
  3. 192.168.10.129的Master1数据库和192.168.10.130的数据库互为主从
  4. 在两台服务器上安装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. 主机能启动，备机不能启动
    1. 关闭防火墙
    2. 两台机器的网卡uuid是否冲突（如果是vm ware复制的linux，因为网卡uuid一致导致的）
  2. 主机和备机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
keepalivedMySQL8mysqlcodemasterMySQL

有关一键部署MySQL8+keepalived双主热备高可用的更多相关文章

  1. ruby-on-rails - 如何使辅助方法在 Rails 集成测试中可用？ - 2

    我在app/helpers/sessions_helper.rb中有一个帮助程序文件，其中包含一个方法my_preference，它返回当前登录用户的首选项。我想在集成测试中访问该方法。例如，这样我就可以在测试中使用getuser_path(my_preference)。在其他帖子中，我读到这可以通过在测试文件中包含requiresessions_helper来实现，但我仍然收到错误NameError:undefinedlocalvariableormethod'my_preference'.我做错了什么？require'test_helper'require'sessions_hel

  2. ruby-on-rails - 每次我尝试部署时，我都会得到 - (gcloud.preview.app.deploy) 错误响应 : [4] DEADLINE_EXCEEDED - 2

    我是Google云的新手，我正在尝试对其进行首次部署。我的第一个部署是RubyonRails项目。我基本上是在关注thisguideinthegoogleclouddocumentation.唯一的区别是我使用的是我自己的项目，而不是他们提供的“helloworld”项目。这是我的app.yaml文件runtime:customvm:trueentrypoint:bundleexecrackup-p8080-Eproductionconfig.ruresources:cpu:0.5memory_gb:1.3disk_size_gb:10当我转到我的项目目录并运行gcloudprevie

  3. ruby-on-rails - Ruby on Rails 可以部署在 Azure 网站上吗？ - 2

    我可以在Azure网站上部署RubyonRails吗？ 最佳答案 还没有。目前仅支持.NET和PHP。 关于ruby-on-rails-RubyonRails可以部署在Azure网站上吗？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/12964010/

  4. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  5. jenkins部署1--jenkins+gitee持续集成 - 2

    前置步骤我们都操作完了，这篇开始介绍jenkins的集成。话不多说，看操作1、登录进入jenkins后会让你选择安装插件，选择第一个默认的就行。安装完成后设置账号密码，重新登录。2、配置JDK和Git都需要执行路径，所以需要先把执行路径找到，先进入服务器的docker容器，2.1JDK的路径root@69eef9ee86cf:/usr/bin#echo$JAVA_HOME/usr/local/openjdk-82.2Git的路径root@69eef9ee86cf:/#whichgit/usr/bin/git3、先配置JDK和Git。点击：ManageJenkins>>GlobalToolCon

  6. 深度学习部署：Windows安装pycocotools报错解决方法 - 2

    深度学习部署：Windows安装pycocotools报错解决方法1.pycocotools库的简介2.pycocotools安装的坑3.解决办法更多Ai资讯：公主号AiCharm本系列是作者在跑一些深度学习实例时，遇到的各种各样的问题及解决办法，希望能够帮助到大家。ERROR:Commanderroredoutwithexitstatus1:'D:\Anaconda3\python.exe'-u-c'importsys,setuptools,tokenize;sys.argv[0]='"'"'C:\\Users\\46653\\AppData\\Local\\Temp\\pip-instal

  7. Ruby，使用包含 TK GUI 的 ocra 部署一个 exe - 2

    Ocra无法处理需要“tk”的应用程序require'tk'puts'nope'用奥克拉http://github.com/larsch/ocra不起作用(如链接中的一个问题所述)问题:https://github.com/larsch/ocra/issues/29(Ocra是1.9的"new"rubyscript2exe，本质上它用于将rb脚本部署为可执行文件)唯一的问题似乎是缺少tcl的DLL文件我不认为这是一个问题据我所知，问题是缺少tk的DLL文件如果它们是已知的，则可以在执行ocra时将它们包括在内有没有办法知道tk工作所需的DLL依赖项？ 最佳答

  8. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  9. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

  10. ruby-on-rails - self 在 Rails 模型中的值(value)是什么？为什么没有明显的实例方法可用？ - 2

    我的rails3.1.6应用程序中有一个自定义访问器方法，它为一个属性分配一个值，即使该值不存在。my_attr属性是一个序列化的哈希，除非为空白，否则应与给定值合并指定了值，在这种情况下，它将当前值设置为空值。(添加了检查以确保值是它们应该的值，但为简洁起见被删除，因为它们不是我的问题的一部分。)我的setter定义为:defmy_attr=(new_val)cur_val=read_attribute(:my_attr)#storecurrentvalue#makesureweareworkingwithahash,andresetvalueifablankvalueisgiven

随机推荐