部署5.7.44二进制双主keepalive
部署5.7.44二进制双主keepalive
1. 环境信息
| 项目 | IP | 备注 |
| 节点 1 | 192.168.43.111 | (mysql-node1) |
| 节点 2 | 192.168.43.112 | (mysql-node2) |
| 192.168.43.113 | VIP | |
| MySQL 版本 | 5.7.44 | (二进制安装) |
| 服务器配置 | 8C16G | |
| 架构 | 双主模式 (互为主从) + GTID 复制 | |
| 高可用 | Keepalived 双 BACKUP 模式管理 VIP |
2. 准备工作
2.1 操作系统环境配置 (所有节点)
# 关闭SELinux sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config setenforce 0
# 关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
#安装系统依赖包 yum install -y wget telnet net-tools ntp vim compat-libstdc++-33 libaio numactl ncurses-compat-libs libstdc++ openssl perl pcre zlib e2fsprogs libgcc tzdata jemalloc cyrus-sasl-plain cyrus-sasl
# 配置主机名 # 节点1执行 hostnamectl set-hostname mysql-node1 # 节点2执行 hostnamectl set-hostname mysql-node2
# 添加hosts解析 cat >> /etc/hosts << EOF 192.168.43.111 mysql-node1 192.168.43.112 mysql-node2 192.168.43.113 mysql-vip EOF cat /etc/hosts
# 配置内核参数（针对8C16G服务器优化） cat >> /etc/sysctl.conf << EOF net.ipv4.ip_local_port_range = 1024 65535 net.ipv4.tcp_tw_recycle = 0 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_syncookies = 1 fs.file-max = 1048576 vm.swappiness = 0 vm.max_map_count = 262144 net.core.somaxconn = 65535 net.ipv4.tcp_max_syn_backlog = 65535 net.core.netdev_max_backlog = 32768 net.ipv4.tcp_no_metrics_save = 1 net.ipv4.tcp_synack_retries = 2 EOF sysctl -p
# 配置文件描述符限制 cat >> /etc/security/limits.conf << EOF * soft nofile 1048576 * hard nofile 1048576 * soft nproc 1048576 * hard nproc 1048576 * soft core unlimited * hard core unlimited EOF # 使配置生效 sysctl -p
2.2 创建 MySQL 用户和目录 (所有节点)
#先卸载原有环境 rpm -qa | grep mariadb | xargs -r yum remove -y --setopt=clean_requirements_on_remove=1 userdel -r mysql 2>/dev/null rm -rf /etc/my.cnf 2>/dev/null rm -rf /etc/my.cnf.d/mariadb-server.cnf 2>/dev/null
# 创建mysql用户和组 groupadd -r mysql useradd -r -g mysql -s /sbin/nologin mysql # 创建目录结构 mkdir -p /data/mysql/{data,logs,tmp} # 设置权限 chown -R mysql:mysql /data/mysql/
3. 安装 MySQL 5.7.44 (所有节点)
3.1 准备 my.cnf配置文件
cat >/etc/my.cnf <<"EOF" [client] port = 3306 socket = /tmp/mysql.sock [mysql] #prompt = "[\u@\h][\d]>\_" connect_timeout = 5 no-auto-rehash [mysqld] # 禁用不安全的 TLS 版本 tls_version = TLSv1.2,TLSv1.3 ssl_cipher = DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256 skip_slave_start = 0 #misc user = mysql basedir = /usr/local/mysql datadir = /data/mysql/data/ port = 3306 socket = /tmp/mysql.sock lc-messages = en_US lc-messages-dir = /usr/local/mysql/share #timeout interactive_timeout = 300 wait_timeout = 300 #character set character-set-server = utf8mb4 open_files_limit = 65535 max_connections = 1024 max_connect_errors = 100000 skip-name-resolve = 1 disable-partition-engine-check=1 #logs log-output=file slow_query_log = 1 slow_query_log_file = /data/mysql/logs/mysql.slow.log log-error = /data/mysql/logs/mysql.error.log log_error_verbosity = 3 pid-file = mysql.pid long_query_time = 1 log-slow-slave-statements = 1 log_queries_not_using_indexes=1 log_throttle_queries_not_using_indexes=10 log_slow_admin_statements=1 log_slow_slave_statements=1 log_bin_trust_function_creators=1 #tmp tmpdir=/data/mysql/tmp event_scheduler = 1 performance_schema = on max_allowed_packet = 32M character_set_server = utf8mb4 default-time-zone = system default-storage-engine = InnoDB explicit_defaults_for_timestamp = 1 #binlog binlog_format = row server-id = 2093306 log-bin = /data/mysql/logs/mysql-bin log-bin-index = /data/mysql/logs/mysql-bin.index binlog_cache_size = 4M max_binlog_size = 1G max_binlog_cache_size = 2G sync_binlog = 1 expire_logs_days = 7 #replicate-wild-ignore-table=mysql.% #replicate-wild-ignore-table=test.% #replicate-wild-ignore-table=information_schema.% #relay log relay_log=/data/mysql/logs/relay-bin relay_log_index=/data/mysql/logs/relay-bin.index max_relay_log_size = 1G relay_log_purge = 1 relay_log_recovery = 1 log_slave_updates = 1 #slave-skip-errors=1032,1053,1062 explicit_defaults_for_timestamp=1 #buffers & cache table_open_cache = 2048 table_definition_cache = 2048 table_open_cache = 2048 max_heap_table_size = 96M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 256 query_cache_size = 0 query_cache_type = 0 query_cache_limit = 256K query_cache_min_res_unit = 512 thread_stack = 192K tmp_table_size = 96M key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 32M #myisam myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G #myisam_repair_threads = 1 #innodb innodb_buffer_pool_size = 8G innodb_buffer_pool_instances = 8 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 64M innodb_log_file_size = 1G innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 50 innodb_file_per_table = 1 innodb_status_file = 1 innodb_io_capacity = 2000 innodb_read_io_threads=8 innodb_write_io_threads=8 innodb_io_capacity_max=4000 innodb_thread_concurrency=0 innodb_purge_threads=4 innodb_lru_scan_depth=2048 innodb_lock_wait_timeout=50 innodb_rollback_on_timeout=1 innodb_print_all_deadlocks=1 innodb_stats_on_metadata=0 innodb_autoinc_lock_mode=2 innodb_buffer_pool_dump_at_shutdown=1 innodb_buffer_pool_load_at_startup=1 innodb_flush_method = O_DIRECT gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay-log-info-repository = TABLE binlog_checksum = NONE log_slave_updates = ON binlog_row_image=FULL lower_case_table_names = 1 # semi sync replication settings # #plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径 #plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径 #loose_rpl_semi_sync_master_enabled = on #loose_rpl_semi_sync_master_timeout = 5000 #loose_rpl_semi_sync_master_trace_level = 32 #loose_rpl_semi_sync_master_wait_no_slave = on #loose_rpl_semi_sync_slave_enabled = on #loose_rpl_semi_sync_slave_trace_level = 32 #loose_rpl_semi_sync_master_enabled = 1 #loose_rpl_semi_sync_slave_enabled = 1 #loose_rpl_semi_sync_master_timeout = 5000 #loose_rpl_semi_sync_master_wait_for_slave_count=1 #loose_rpl_semi_sync_master_wait_point=AFTER_SYNC slave_preserve_commit_order = 1 slave_transaction_retries = 128 log_timestamps = system show_compatibility_56 = on slave_parallel_workers = 16 slave_parallel_type = LOGICAL_CLOCK #loose_innodb_numa_interleave = 1 innodb_buffer_pool_dump_pct = 40 innodb_page_cleaners = 16 innodb_undo_log_truncate = 1 innodb_max_undo_log_size = 2G innodb_purge_rseg_truncate_frequency = 128 # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE transaction_isolation = READ-COMMITTED sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY" [mysqld_safe] nice=-19 open-files-limit=65535 EOF
#替换server-id sed -i 's/2093306/111/g' /etc/my.cnf #另一个节点 sed -i 's/2093306/112/g' /etc/my.cnf #将innodb_buffer_pool_size 修改为物理内存的50%
3.2 下载并解压二进制包
#假定已经下载完毕,并上传至/opt/目录下 cd /opt/ tar -zxf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ cd /usr/local ln -s /usr/local/mysql-5.7.44-linux-glibc2.12-x86_64 mysql chown -R mysql.mysql /usr/local/mysql chown -R mysql.mysql /usr/local/mysql-5.7.44-linux-glibc2.12-x86_64/
3.3 初始化数据库
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
3.4 配置启动方式
cat > /usr/lib/systemd/system/mysqld.service <<"EOF" [Unit] Description=MySQL Server Documentation=https://dev.mysql.com/doc/ After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] # some limits # file size LimitFSIZE=infinity # cpu time LimitCPU=infinity # virtual memory size LimitAS=infinity # open files LimitNOFILE=65535 # processes/threads LimitNPROC=65535 # locked memory LimitMEMLOCK=infinity # total threads (user+kernel) TasksMax=infinity TasksAccounting=false User=mysql Group=mysql # 如果是 MySQL 5.7 版本，此处可以使用 simple 模式 # 如果是 MySQL 8.0 版本，可以使用 notify 模式 Type=simple TimeoutSec=10 PermissionsStartOnly=true ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf $MYSQLD_OPTS EnvironmentFile=-/etc/sysconfig/mysql LimitNOFILE = 65535 Restart=on-failure RestartPreventExitStatus=1 Environment=MYSQLD_PARENT_PID=1 PrivateTmp=false EOF
systemctl daemon-reload
systemctl enable mysqld.service
systemctl start mysqld.service
systemctl status mysqld.service
#初次登录,直接登录 echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile source /etc/profile # 这里先在 192.168.43.111上操作 mysql #回车 ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewStrongPass#2025'; FLUSH PRIVILEGES; # 创建用于主从复制的用户 CREATE USER 'repl'@'192.168.43.%' IDENTIFIED BY 'ReplPassword!2023'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.43.%'; CREATE USER 'healthcheck'@'127.0.0.1' IDENTIFIED BY 'StrongHealthCheckP@ssw0rd'; GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'healthcheck'@'127.0.0.1'; FLUSH PRIVILEGES; exit ln -s $(which mysqladmin) /usr/local/bin/mysqladmin
# 配置 从 192.168.43.112 到 192.168.43.111 方向的同步 # 登录节点2的MySQL mysql #回车 # 配置复制 CHANGE MASTER TO MASTER_HOST='192.168.43.111', MASTER_USER='repl', MASTER_PASSWORD='ReplPassword!2023', MASTER_PORT=3306, MASTER_AUTO_POSITION=1; # 启动从库 START SLAVE; # 查看状态 SHOW SLAVE STATUS\G mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.43.111 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1121 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 1326 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1121 Relay_Log_Space: 1519 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 111 Master_UUID: 8531e562-87d6-11f0-9f52-000c292d36c9 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 8531e562-87d6-11f0-9f52-000c292d36c9:1-5 Executed_Gtid_Set: 8531e562-87d6-11f0-9f52-000c292d36c9:1-5 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql> 确认输出中以下两个参数为 Yes: Slave_IO_Running: Yes Slave_SQL_Running: Yes
# 配置 从 192.168.43.111 到 192.168.43.112 方向的同步 # 登录节点1的MySQL mysql -uroot -p # 配置复制 CHANGE MASTER TO MASTER_HOST='192.168.43.112', MASTER_USER='repl', MASTER_PASSWORD='ReplPassword!2023', MASTER_PORT=3306, MASTER_AUTO_POSITION=1; # 启动从库 START SLAVE; # 查看状态 SHOW SLAVE STATUS\G mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.43.112 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1121 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 398 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1121 Relay_Log_Space: 591 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 112 Master_UUID: 85b9fd72-87d6-11f0-9cef-000c29d21606 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 8531e562-87d6-11f0-9f52-000c292d36c9:1-5 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql> 确认输出中以下两个参数为 Yes: Slave_IO_Running: Yes Slave_SQL_Running: Yes # 上述双主配置完毕
4. 安装配置 Keepalived（双 BACKUP 模式）
4.1 安装 Keepalived (所有节点)
安装 Keepalived (所有节点) # 安装Keepalived yum install -y keepalived # 设置开机启动 systemctl enable keepalived
4.2 配置节点 1 的 Keepalived (192.168.43.111)
cat >/etc/keepalived/keepalived.conf<<"EOF" #生产级 MySQL-HA 主节点 global_defs { router_id MYSQL_HA_NODE1 enable_script_security script_user keepalived_script # 如需 root 请改为 root } vrrp_script chk_mysql { script "/etc/keepalived/check_mysql.sh" interval 15 # 脚本执行间隔：给足MySQL启停时间 timeout 12 # 脚本超时时间：小于interval，预留3秒缓冲 weight -20 # 故障时降低20优先级，确保node2优先级更高 fall 2 # 连续2次失败判定为故障（减少误判） rise 3 # 连续3次成功判定为恢复（确保稳定） } vrrp_instance VI_1 { state BACKUP #由 priority 决定谁是 MASTER interface ens33 mcast_src_ip 192.168.43.111 virtual_router_id 131 # 两节点必须一致 priority 100 # node1初始优先级（高于node2的90） nopreempt # 故障恢复后不抢占VIP（双主架构推荐） advert_int 2 # VRRP通告间隔：从1秒改为2秒，减轻压力 authentication { auth_type PASS auth_pass OGmNTNBm # 两节点必须一致 } # 单播配置（VMware环境推荐，避免组播问题） unicast_src_ip 192.168.43.111 unicast_peer { 192.168.43.112 } # VIP配置（确保dev与网卡名一致） virtual_ipaddress { 192.168.43.113/24 dev ens33 #VIP } track_script { chk_mysql weight 0 # weight 0：仅触发状态判定，不叠加权重 } # 如需日志/告警，取消下面注释 # notify_master "/etc/keepalived/notify.sh master" # notify_backup "/etc/keepalived/notify.sh backup" # notify_fault "/etc/keepalived/notify.sh fault" } EOF
4.3 配置节点 2 的 Keepalived (192.168.43.112)
cat >/etc/keepalived/keepalived.conf<<"EOF" #! 生产级 MySQL-HA 备节点 global_defs { router_id MYSQL_HA_NODE2 enable_script_security script_user keepalived_script } vrrp_script chk_mysql { script "/etc/keepalived/check_mysql.sh" interval 15 # 脚本执行间隔：给足MySQL启停时间 timeout 12 # 脚本超时时间：小于interval，预留3秒缓冲 weight -20 # 故障时降低20优先级，确保node2优先级更高 fall 2 # 连续2次失败判定为故障（减少误判） rise 3 # 连续3次成功判定为恢复（确保稳定） } vrrp_instance VI_1 { state BACKUP interface ens33 mcast_src_ip 192.168.43.112 virtual_router_id 131 # 两节点必须一致 priority 90 # node1初始优先级（高于node2的90） nopreempt # 故障恢复后不抢占VIP（双主架构推荐） advert_int 2 # VRRP通告间隔：从1秒改为2秒，减轻压力 authentication { auth_type PASS auth_pass OGmNTNBm # 两节点必须一致 } # 单播配置（VMware环境推荐，避免组播问题） unicast_src_ip 192.168.43.112 unicast_peer { 192.168.43.111 } # VIP配置（确保dev与网卡名一致） virtual_ipaddress { 192.168.43.113/24 dev ens33 } track_script { chk_mysql weight 0 # weight 0：仅触发状态判定，不叠加权重 } # notify_master "/etc/keepalived/notify.sh master" # notify_backup "/etc/keepalived/notify.sh backup" # notify_fault "/etc/keepalived/notify.sh fault" } EOF
双 BACKUP 模式说明：
1) 两个节点都配置为 BACKUP 状态
2) 通过 priority 区分优先级，优先级高的节点会成为初始 MASTER
3) 启用 nopreempt（非抢占）模式，避免主节点恢复后自动抢占 VIP
4) 这种配置可以减少不必要的切换，提高系统稳定性
4.4 创建 MySQL 检查脚本 (所有节点)
cat >/etc/keepalived/check_mysql.sh<<"EOF" #!/bin/bash #mysql -hlocalhost -uhealthcheck -p'StrongHealthCheckP@ssw0rd' -P3306 -e "SELECT 1;" >/dev/null 2>&1 /usr/local/mysql/bin/mysqladmin -h127.0.0.1 -uhealthcheck -p'StrongHealthCheckP@ssw0rd' -P3306 ping >/dev/null 2>&1 if [ $? -eq 0 ]; then exit 0 else exit 1 fi EOF chmod +x /etc/keepalived/check_mysql.sh
4.5 创建状态通知脚本 (所有节点)[可选]
cat >/etc/keepalived/notify.sh<<"EOF" #!/bin/bash TYPE=$1 NAME=$2 STATE=$3 case $STATE in "MASTER") # 成为主节点时执行的操作 echo "$(date +'%Y-%m-%d %H:%M:%S') - Switching to MASTER state" >> /var/log/keepalived.log # 可以在这里添加成为主节点后需要执行的命令 ;; "BACKUP") # 成为备节点时执行的操作 echo "$(date +'%Y-%m-%d %H:%M:%S') - Switching to BACKUP state" >> /var/log/keepalived.log ;; "FAULT") # 进入故障状态时执行的操作 echo "$(date +'%Y-%m-%d %H:%M:%S') - Switching to FAULT state" >> /var/log/keepalived.log ;; *) echo "Unknown state: $STATE" exit 1 ;; esac EOF chmod +x /etc/keepalived/notify.sh
4.6 启动 Keepalived (所有节点)
# 先启动节点1的keepalived
systemctl start keepalived
# 再启动节点2的keepalived
systemctl start keepalived
[root@mysql-node1 ~]# tail -f /var/log/messages Sep 3 08:00:58 mysql-node1 Keepalived_vrrp[6614]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 08:00:58 mysql-node1 Keepalived_vrrp[6614]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 08:00:58 mysql-node1 avahi-daemon[621]: Registering new address record for 192.168.43.113 on ens33.IPv4. Sep 3 08:01:01 mysql-node1 systemd: Started Session 38 of user root. Sep 3 08:01:03 mysql-node1 Keepalived_vrrp[6614]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 08:01:03 mysql-node1 Keepalived_vrrp[6614]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.43.113 Sep 3 08:01:03 mysql-node1 Keepalived_vrrp[6614]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 08:01:03 mysql-node1 Keepalived_vrrp[6614]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 08:01:03 mysql-node1 Keepalived_vrrp[6614]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 08:01:03 mysql-node1 Keepalived_vrrp[6614]: Sending gratuitous ARP on ens33 for 192.168.43.113 [root@mysql-node2 ~]# tail -f /var/log/messages Sep 3 08:01:34 mysql-node2 Keepalived_healthcheckers[15541]: Opening file '/etc/keepalived/keepalived.conf'. Sep 3 08:01:34 mysql-node2 Keepalived_vrrp[15542]: Registering Kernel netlink reflector Sep 3 08:01:34 mysql-node2 Keepalived_vrrp[15542]: Registering Kernel netlink command channel Sep 3 08:01:34 mysql-node2 Keepalived_vrrp[15542]: Registering gratuitous ARP shared channel Sep 3 08:01:34 mysql-node2 Keepalived_vrrp[15542]: Opening file '/etc/keepalived/keepalived.conf'. Sep 3 08:01:34 mysql-node2 Keepalived_vrrp[15542]: VRRP_Instance(VI_1) removing protocol VIPs. Sep 3 08:01:34 mysql-node2 Keepalived_vrrp[15542]: Using LinkWatch kernel netlink reflector... Sep 3 08:01:34 mysql-node2 Keepalived_vrrp[15542]: VRRP_Instance(VI_1) Entering BACKUP STATE Sep 3 08:01:34 mysql-node2 Keepalived_vrrp[15542]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)] Sep 3 08:01:34 mysql-node2 Keepalived_vrrp[15542]: VRRP_Script(check_mysql) succeeded [root@mysql-node1 ~]# ip addr | grep 192 inet 192.168.43.111/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.43.113/24 scope global secondary ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node1 ~]# [root@mysql-node2 ~]# ip addr | grep 192 inet 192.168.43.112/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node2 ~]#
#检查mysql服务状态 [root@mysql-node1 ~]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled) Active: active (running) since Wed 2025-09-03 07:49:00 CST; 15min ago Docs: https://dev.mysql.com/doc/ Main PID: 5550 (mysqld) Tasks: 40 CGroup: /system.slice/mysqld.service └─5550 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf Sep 03 07:49:00 mysql-node1 systemd[1]: Started MySQL Server. [root@mysql-node1 ~]# [root@mysql-node2 ~]# ip addr | grep 192 inet 192.168.43.112/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node2 ~]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled) Active: active (running) since Wed 2025-09-03 07:48:36 CST; 16min ago Docs: https://dev.mysql.com/doc/ Main PID: 14375 (mysqld) Tasks: 40 CGroup: /system.slice/mysqld.service └─14375 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf Sep 03 07:48:36 mysql-node2 systemd[1]: Stopped MySQL Server. Sep 03 07:48:36 mysql-node2 systemd[1]: Started MySQL Server. [root@mysql-node2 ~]#
mysql --login-path=root -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running:|Slave_SQL_Running:|Seconds_Behind_Master:" # 把账号、密码、主机、端口一次性存到 ~/.mylogin.cnf（AES 加密） mysql_config_editor set --login-path=root --host=localhost --port=3306 --user=root --password mysql_config_editor set --login-path=healthcheck --host=localhost --port=3306 --user=healthcheck --password # 查看已有配置 mysql_config_editor print --all # 删除某个配置 mysql_config_editor remove --login-path=healthcheck # 登录 mysql --login-path=root
7. 故障切换测试
在节点 1 (当前主节点) 上停止 MySQL: [root@mysql-node1 ~]# systemctl stop mysqld [root@mysql-node1 ~]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled) Active: failed (Result: signal) since Wed 2025-09-03 08:38:07 CST; 8s ago Docs: https://dev.mysql.com/doc/ Process: 9743 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf $MYSQLD_OPTS (code=killed, signal=KILL) Main PID: 9743 (code=killed, signal=KILL) Sep 03 08:34:39 mysql-node1 systemd[1]: Stopped MySQL Server. Sep 03 08:34:39 mysql-node1 systemd[1]: Unit mysqld.service entered failed state. Sep 03 08:34:39 mysql-node1 systemd[1]: mysqld.service failed. Sep 03 08:34:39 mysql-node1 systemd[1]: Started MySQL Server. Sep 03 08:37:57 mysql-node1 systemd[1]: Stopping MySQL Server... Sep 03 08:38:07 mysql-node1 systemd[1]: mysqld.service stop-sigterm timed out. Killing. Sep 03 08:38:07 mysql-node1 systemd[1]: mysqld.service: main process exited, code=killed, status=9/KILL Sep 03 08:38:07 mysql-node1 systemd[1]: Stopped MySQL Server. Sep 03 08:38:07 mysql-node1 systemd[1]: Unit mysqld.service entered failed state. Sep 03 08:38:07 mysql-node1 systemd[1]: mysqld.service failed. [root@mysql-node1 ~]# 观察 Keepalived 日志: tail -f /var/log/messages | grep Keepalived useradd -r -s /sbin/nologin keepalived_script # -r 系统账号，-s /sbin/nologin 禁止登录 systemctl start keepalived systemctl enable keepalived systemctl start mysqld systemctl enable mysqld systemctl restart keepalived systemctl restart mysqld ip addr|grep 192 systemctl stop keepalived systemctl stop mysqld
[root@mysql-node1 ~]# ip addr|grep 192 inet 192.168.43.111/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.43.113/24 scope global secondary ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node1 ~]# [root@mysql-node2 ~]# ip addr|grep 192 inet 192.168.43.112/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node2 ~]# [root@mysql-node1 ~]# systemctl stop mysqld [root@mysql-node1 ~]# [root@mysql-node1 ~]# tail -f /var/log/messages Sep 3 17:14:01 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:14:01 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:15:01 mysql-node1 systemd: Started Session 60 of user root. Sep 3 17:17:53 mysql-node1 sz[22179]: [root] keepalived.conf.node1/ZMODEM: 1770 Bytes, 370 BPS Sep 3 17:17:53 mysql-node1 sz[22179]: [root] notify.sh/ZMODEM: 709 Bytes, 11159 BPS Sep 3 17:19:20 mysql-node1 sz[22201]: [root] keepalived.conf.node1/ZMODEM: getnak failed Sep 3 17:19:20 mysql-node1 sz[22201]: [root] ZMODEM/keepalived.conf.node1: error occured Sep 3 17:20:01 mysql-node1 systemd: Started Session 62 of user root. Sep 3 17:20:01 mysql-node1 systemd: Started Session 61 of user root. Sep 3 17:25:01 mysql-node1 systemd: Started Session 63 of user root. Sep 3 17:27:51 mysql-node1 systemd: Stopping MySQL Server... Sep 3 17:27:55 mysql-node1 systemd: Stopped MySQL Server. Sep 3 17:28:01 mysql-node1 Keepalived_vrrp[21638]: /etc/keepalived/check_mysql.sh exited with status 1 ^C [root@mysql-node1 ~]# ip addr|grep 192 inet 192.168.43.111/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.43.113/24 scope global secondary ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node1 ~]# ip addr|grep 192 inet 192.168.43.111/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node1 ~]# [root@mysql-node2 ~]# ip addr|grep 192 inet 192.168.43.112/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.43.113/24 scope global secondary ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node2 ~]# [root@mysql-node2 ~]# tail -f /var/log/messages Sep 3 17:29:32 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:32 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:32 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:32 mysql-node2 avahi-daemon[640]: Registering new address record for 192.168.43.113 on ens33.IPv4. Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 [root@mysql-node1 ~]# systemctl start mysqld [root@mysql-node2 ~]# systemctl stop mysqld [root@mysql-node2 ~]# tail -f /var/log/messages Sep 3 17:29:32 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:32 mysql-node2 avahi-daemon[640]: Registering new address record for 192.168.43.113 on ens33.IPv4. Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:29:37 mysql-node2 Keepalived_vrrp[32911]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:30:01 mysql-node2 systemd: Started Session 111 of user root. Sep 3 17:30:01 mysql-node2 systemd: Started Session 112 of user root. Sep 3 17:30:33 mysql-node2 systemd: Stopping MySQL Server... Sep 3 17:30:37 mysql-node2 Keepalived_vrrp[32911]: /etc/keepalived/check_mysql.sh exited with status 1 Sep 3 17:30:43 mysql-node2 systemd: mysqld.service stop-sigterm timed out. Killing. Sep 3 17:30:43 mysql-node2 systemd: mysqld.service: main process exited, code=killed, status=9/KILL Sep 3 17:30:43 mysql-node2 systemd: Stopped MySQL Server. Sep 3 17:30:43 mysql-node2 systemd: Unit mysqld.service entered failed state. Sep 3 17:30:43 mysql-node2 systemd: mysqld.service failed. Sep 3 17:30:52 mysql-node2 Keepalived_vrrp[32911]: /etc/keepalived/check_mysql.sh exited with status 1 Sep 3 17:30:52 mysql-node2 Keepalived_vrrp[32911]: VRRP_Script(chk_mysql) failed Sep 3 17:30:52 mysql-node2 Keepalived_vrrp[32911]: VRRP_Instance(VI_1) Entering FAULT STATE Sep 3 17:30:52 mysql-node2 Keepalived_vrrp[32911]: VRRP_Instance(VI_1) removing protocol VIPs. Sep 3 17:30:52 mysql-node2 Keepalived_vrrp[32911]: VRRP_Instance(VI_1) Now in FAULT state Sep 3 17:30:52 mysql-node2 avahi-daemon[640]: Withdrawing address record for 192.168.43.113 on ens33. [root@mysql-node2 ~]# ip addr|grep 192 inet 192.168.43.112/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node2 ~]# [root@mysql-node1 ~]# ip addr|grep 192 inet 192.168.43.111/24 brd 192.168.43.255 scope global noprefixroute ens33 inet 192.168.43.113/24 scope global secondary ens33 inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0 [root@mysql-node1 ~]# [root@mysql-node1 ~]# tail -f /var/log/messages Sep 3 17:30:54 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:30:54 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:30:54 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:30:54 mysql-node1 avahi-daemon[609]: Registering new address record for 192.168.43.113 on ens33.IPv4. Sep 3 17:30:59 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:30:59 mysql-node1 Keepalived_vrrp[21638]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.43.113 Sep 3 17:30:59 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:30:59 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:30:59 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113 Sep 3 17:30:59 mysql-node1 Keepalived_vrrp[21638]: Sending gratuitous ARP on ens33 for 192.168.43.113
经实践验证,可以正常切换,可用于生产环境部署.
浙公网安备 33010602011771号