技术教程 编程教程 一键部署MySQL8+keepalived双主热备高可用 正文

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

2023-02-13,,,,

概述

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

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

      关闭防火墙
      两台机器的网卡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双主热备高可用的相关教程结束。

《一键部署MySQL8+keepalived双主热备高可用.doc》

下载本文的Word格式文档，以方便收藏与打印。

Copyright 2023. 昆居客 - IT编程,互联网信息技术文档大全!