MySQL双主+Keepalived高可用：搭建指南

阿国运维网技术分享平台：桌面运维、网络运维、系统运维、服务器运维(及云服务器)，精品软件分享、阿国网络、尽在北京运维网

MySQL 双主 + Keepalived 高可用架构是一种常见的数据库高可用性解决方案，旨在通过双主复制和 Keepalived 实现故障自动切换，确保数据库服务的连续性和稳定性。

本文描述的环境为模拟环境，仅用于测试方案可行性。  

• 操作系统： Red Hat Enterprise Linux release 8.10 (Ootpa)

• 数据库版本：8.0.40 MySQL Community Server - GPL

• keepalived：v2.1.5 

3.1. 创建用户、组

以 root 用户登录，执行以下命令创建 MySQL 数据库专用的用户和组

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

3.2. 规划安装目录

为方便管理和提高性能，根据文件的不同读写特性，将 MySQL 的各类数据和日志分开存放，以分散 IO 压力。推荐的目录结构如下：  

3.3. 创建目录并更改目录属主，确保 MySQL 用户具有目录权限

mkdir /mysqldata
mkdir /redolog
mkdir /undolog
mkdir /binlog

chown -R mysql:mysql /mysqldata
chown -R mysql:mysql /redolog
chown -R mysql:mysql /undolog
chown -R mysql:mysql /binlog

3.4. 配置MySQL参数文件（双主配置）

编辑MySQL 配置文件 (/etc/my.cnf) 添加数据库参数配置，以下为 mm-host01 的参数配置。

[client]
port    = 3306
socket  = /tmp/mysql.sock

[mysql]
prompt = "\u@mysqldb \R:\m:\s [\d]> "
no_auto_rehash

[mysqld]
user    = mysql
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
port    = 3306
server_id = 31
datadir = /mysqldata
socket  = /tmp/mysql.sock
pid_file = mysqldb.pid
character_set_server = UTF8MB4
skip_name_resolve = 1
default_time_zone = "+8:00"
admin_address = '127.0.0.1'
admin_port = 4444
lower_case_table_names = 1

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 2000
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 3600
wait_timeout = 3600
tmp_table_size = 1024M
max_heap_table_size = 32M

#log settings
log_timestamps = SYSTEM
log_error = /mysqldata/error.log
log_error_verbosity = 3
slow_query_log = 1
log_slow_extra = 1
slow_query_log_file = /mysqldata/slow.log
long_query_time = 1
min_examined_row_limit = 10000
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_bin = /binlog/bin
binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE
log-bin-trust-function-creators = 1

#replication settings
relay_log_recovery = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 2
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = 1
slave_checkpoint_period = 2
log-slave-updates=1
auto_increment_increment=2
auto_increment_offset=1

#innodb settings
transaction_isolation = READ-COMMITTED
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 4
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_group_home_dir = /redolog
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_undo_directory = /undolog
innodb_max_undo_log_size = 1G
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 4G
innodb_print_ddl_logs = 1
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 67108864
innodb_adaptive_hash_index = OFF

#innodb monitor settings
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"

#pfs settings
performance_schema = 1
performance_schema_instrument = '%lock%=on'

[mysqldump]
quick

mm-host02 不同的部分参数

server_id = 32
auto_increment_increment=2
auto_increment_offset=2

3.5.   检查glibc 版本  

MySQL 的二进制发行版是基于特定的glibc版本编译的，例如glibc 2.28。如果系统中的glibc版本低于此版本，MySQL将无法正常运行 。

ldd --version
ldd (GNU libc) 2.28

安装MySQL部分操作需要在所有节点上执行。

4.1. 上传安装文件

将安装文件上传至服务器的/usr/local目录。

4.2. 登录服务器并执行安装

登录服务器并执行安装，使用root用户登录服务器，依次执行以下命令：

# 进入目标目录
cd /usr/local
# 解压安装包
tar xf mysql-8.0.40-linux-glibc2.28-x86_64.tar.xz
# 创建符号链接
ln -s /usr/local/mysql-8.0.40-linux-glibc2.28-x86_64 mysql
# 进入 MySQL 目录
cd mysql
# 设置目录权限
chown -R root:mysql .
# 初始化 MySQL
bin/mysqld --initialize --user=mysql
# 复制启动脚本
cp support-files/mysql.server /etc/init.d/mysqld
# 配置环境变量
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
# 使环境变量生效
source /etc/profile

4.3. 启动 MySQL 服务

执行以下命令启动 MySQL 服务：

# 启动 MySQL
/etc/init.d/mysqld start

4.4. 验证安装

检查 MySQL 是否正常运行：

# 查看 MySQL 进程
ps -ef | grep mysql

# 查看 MySQL 端口监听状态
netstat -tuln | grep 3306

4.5. 修改 root 密码

在初始化后需要修改 root 用户的密码，执行以下命令进行修改：

# 使用初始化时生成的临时密码登录 MySQL
mysql -uroot -p
# 修改 root 密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'strong_password';
# 配置mysql开机自启
chkconfig mysqld on
chkconfig --list mysqld

5.1. 在主库创建主从复制专用账号

首先，在主库上创建一个用于主从复制的专用账号，并授予必要的权限。

CREATE USER 'repl'@'192.168.3.%' IDENTIFIED with mysql_native_password BY 'strong_password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.3.%';

5.2. 主库端执行备份

在主库上执行全库备份，并确保备份文件中包含 GTID 信息。

mysqldump -uroot -p --all-databases --single-transaction --triggers --routines --events > /tmp/alldb.sql

5.3. 将备份文件传输到从库

使用 scp 或其他文件传输工具将备份文件传输到从库。

scp /tmp/alldb.sql root@192.168.3.32:/tmp/

5.4. 从库端执行恢复

在从库上执行以下操作来恢复备份文件。

mysql -uroot -p
slave> stop slave;
slave> reset slave all;
slave> reset master;
slave> set @@global.gtid_purged='';  
slave> source /tmp/alldb.sql;
slave> show databases;

5.5. 配置 Master 备机成为 Master 主机的从机

配置 Master 备机从 Master 主机同步数据：

CHANGE MASTER TO
MASTER_HOST='192.168.3.31',
MASTER_USER='repl',
MASTER_PASSWORD='strong_password',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

5.6. 启动从机并查看状态

启动从机并查看同步状态：

START SLAVE;
SHOW SLAVE STATUS\G;

5.7. 配置 Master 主机成为 Master 备机的从机

配置 Master 主机从 Master 备机同步数据：

master> stop slave;
master> reset slave all;

CHANGE MASTER TO
MASTER_HOST='192.168.3.32',
MASTER_USER='repl',
MASTER_PASSWORD='strong_password',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

5.8. 启动从机并查看状态

启动从机并查看同步状态：

START SLAVE;
SHOW SLAVE STATUS\G;

6.1. 配置 hosts 解析

首先，在所有服务器上配置 hosts 文件，确保主机名与 IP 地址的映射正确。

vi /etc/hosts
192.168.3.31 mm-host01
192.168.3.32 mm-host02

6.2. 安装 Keepalived

在每台服务器上安装 Keepalived。

yum install keepalived

6.3.  配置 Keepalived

以下是主从节点的 Keepalived 配置文件示例，红色字部分需要根据服务器实际情况进行修改。

主节点配置（mm-host01）

cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   mm-host01             # 路由器标识，建议使用主机名
   script_user root
}

vrrp_script chk_mysql {
    script "/etc/keepalived/mysql_check.sh"
    interval 2
    weight 15                # 此值需要大于MASTER的priority减去BACKUP的priority
}

vrrp_instance VI_1 {
    state BACKUP                # 主节点为：MASTER，备节点为：BACKUP
    interface ens160
    virtual_router_id 100
    unicast_src_ip 192.168.3.31
    unicast_peer {
        192.168.3.32
    }
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_mysql
    }
    virtual_ipaddress {
        192.168.3.30
    }
}

从节点配置（mm-host02）

cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   mm-host02            # 路由器标识，建议使用主机名
   script_user root
}

vrrp_script chk_mysql {
    script "/etc/keepalived/mysql_check.sh"
    interval 2
    weight 20                # 此值需要大于MASTER的priority减去BACKUP的priority
    fall 2
    rise 1
}

vrrp_instance VI_1 {
    state MASTER                # 主节点为：MASTER，备节点为：BACKUP
    nopreemt
    interface ens192
    virtual_router_id 100
    unicast_src_ip 192.168.3.32
    unicast_peer {
        192.168.3.31
    }
    priority 110
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_mysql
    }
    virtual_ipaddress {
        192.168.3.30
   }
}

6.4. MySQL 检查脚本

创建一个 MySQL 检查脚本，用于监控 MySQL 服务的状态。

vi /etc/keepalived/mysql_check.sh

#!/bin/bash

counter=$(netstat -na |grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl stop keepalived
fi

6.5. Keepalived检查脚本

创建一个Keepalived 检查脚本，定期检查 Keepalived 服务的运行状态。

vi /etc/keepalived/keepalived_check.sh

#!/bin/bash
CMD=$(ps -ef |grep 'keepalived -D'|grep -v grep |wc -l)
count=$(netstat -na |grep "LISTEN"|grep "3306"|wc -l)
if [ "${count}" -ne 0 ]; then
    if [ "${CMD}" -eq 0 ]; then
        echo "重新执行keepalvied"
        # systemctl restart keepalived
    fi
fi

6.6. 授权脚本执行权限

chmod +x keepalived_check.sh
chmod +x mysql_check.sh

6.7. 启动keepalived

systemctl start keepalived
systemctl status keepalived

6.8. 查看vip信息

如果需要查看虚拟 IP（VIP）信息，可以使用以下命令：

ip addr show

如果需要更详细的信息，可以查看 keepalived 的日志，了解 VIP 切换或错误信息：

journalctl -u keepalived

6.9. 定时任务

配置一个定时任务，定期检查 Keepalived 服务的运行状态。

crontab -l
*/3 * * * * /etc/keepalived/keepalived_check.sh