MySQL / MariaDB 主从复制架构实战指南

在生产环境中数据库单点故障是最大的噩梦之一。本文将从最基础的 MariaDB 主从复制出发逐步演进到 MySQL 双主同步最终搭建一套基于 Keepalived 双主架构的完整高可用方案。所有配置均来自生产环境实战验证可直接落地使用。环境说明:角色主机名IP数据库Masterrhel-efserver192.168.1.25MariaDB / MySQLSlave / Backuprhel-efserver-bak192.168.1.28MariaDB / MySQLVIP—192.168.1.100Keepalived 虚拟漂移 IP一、MariaDB 主从复制MariaDB 是 MySQL 的分支复制原理完全一致Master 将数据变更写入二进制日志BinlogSlave 通过 I/O 线程读取 Binlog 写入中继日志Relay Log再由 SQL 线程重放完成同步。1. Master 节点配置编辑 MariaDB 服务配置文件开启 Binlog 并设置唯一的server-id# nano /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id 1 log_bin mysql-bin binlog_ignore_db mysql参数说明server-id集群内每台节点必须唯一建议使用 IP 尾数或递增数字。log_bin启用二进制日志文件名前缀为mysql-bin。binlog_ignore_db忽略 mysql 系统库的同步减少不必要的日志量。修改完成后重启 MariaDB 服务。2. 创建复制账号在 Master 上创建专用的复制用户仅授予最小权限-- 创建用户 repl允许从 rhel-efserver-bak 连接 GRANT REPLICATION SLAVE ON *.* TO replrhel-efserver-bak IDENTIFIED BY Ins1234;3. 记录 Master 状态MariaDB [(none)] SHOW MASTER STATUS; ------------------------------------------------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ------------------------------------------------------------ | mysql-bin.000001 | 655 | | mysql | ------------------------------------------------------------提示记下File和Position的值Slave 连接时需要用到。4. Slave 节点配置# nano /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id 2 log_bin mysql-bin binlog_ignore_db mysql5. 配置并启动复制指定 Master 的连接信息并启动同步进程CHANGE MASTER TO MASTER_HOSTrhel-efserver, MASTER_USERrepl, MASTER_PASSWORDIns1234; MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS655; START SLAVE;6. 验证复制状态# 查询状态 show slave status\G; MariaDB [(none)] show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: rhel-efserver Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 655 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 954 Relay_Master_Log_File: mysql-bin.000001 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: 655 Relay_Log_Space: 1265 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: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 2 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row inset (0.000 sec)重点关注以下两个指标均为Yes即表示复制正常运行Slave_IO_Running: Yes ← I/O 线程已连接 Master正常读取 Binlog Slave_SQL_Running: Yes ← SQL 线程正常运行正在重放中继日志 Seconds_Behind_Master: 0 ← 从库延迟秒数0 表示完全同步二、MySQL 主从复制MySQL 8.x 的主从配置与 MariaDB 基本相同主要区别在于用户认证方式的变化。以下是核心步骤1. 全量数据导出主库执行使用--single-transaction参数保证 InnoDB 数据的一致性快照mysqldump -uroot -pIns1234 --single-transaction --all-databases /var/lib/mysql/efab.sql2. 创建复制用户CREATE USER repl% IDENTIFIED BY Ins1234; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl%; FLUSH PRIVILEGES;提示生产环境中建议使用mysql_native_password认证插件以兼容旧客户端或配置 SSL 加密复制通道。3. 从库导入数据将主库的全量备份导入从库mysql -uroot -p /var/lib/mysql/efab-bak.sql4. 启动复制CHANGE MASTER TO MASTER_HOSTrhel-efserver, MASTER_USERrepl, MASTER_PASSWORDIns1234, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS655; START SLAVE;三、双主Master-Master架构传统主从架构中Slave 只能读不能写Master 故障时需要手动切换。双主架构让两台节点互为 Master各自承担写操作配合 Keepalived 实现自动故障转移才是真正的生产级高可用。1. 架构示意192.168.1.100 (VIP) │ ┌──────────┴──────────┐ ▼ ▼ ┌─────────┐ ┌─────────┐ │ Master A│◄──────► │ Master B│ │ .25 │ 互为主从 │ .28 │ └─────────┘ └─────────┘ │ │ read_only0 read_only1 (当前可写) (当前只读) │ │ ┌──┴─────────────────────┴──┐ │ Keepalived 自动切换 │ └───────────────────────────┘2. Master A 配置192.168.1.25[mysqld] # --- 基本设置 --- server-id 25 # 必须唯一建议用IP尾数 port 3306 datadir /var/lib/mysql socket /var/lib/mysql/mysql.sock # --- 复制核心配置 --- log-bin mysql-bin # 启用二进制日志 binlog-ignore-db mysql binlog_format row # 推荐使用 ROW 格式数据最安全 relay-log relay-bin log_slave_updates 1 # 关键从库执行完同步后也记入自己的binlog # --- 防止主键冲突 (双主必备) --- auto_increment_increment 2 # 步长为2 auto_increment_offset 1 # 初始值为1 (生成 ID 为 1, 3, 5...) # --- 高可用与安全设置 --- read_only 0 # 主库初始为可读写 skip_name_resolve 1 # 禁用DNS解析提高连接速度 innodb_flush_log_at_trx_commit 1 sync_binlog 1核心参数解析log_slave_updates 1双主架构的关键没有它A 的变更通过复制到 B 后不会写入 B 的 Binlog无法再同步回 A。auto_increment_increment 2auto_increment_offset两台节点分别生成奇数 ID1, 3, 5…和偶数 ID2, 4, 6…从根本上避免自增主键冲突。3. Master B 配置192.168.1.28[mysqld] # --- 基本设置 --- server-id 28 # 必须唯一 port 3306 datadir /var/lib/mysql socket /var/lib/mysql/mysql.sock # --- 复制核心配置 --- log-bin mysql-bin binlog-ignore-db mysql binlog_format row relay-log relay-bin log_slave_updates 1 # 关键允许数据级联同步 # --- 防止主键冲突 (双主必备) --- auto_increment_increment 2 # 步长为2 auto_increment_offset 2 # 初始值为2 (生成 ID 为 2, 4, 6...) # --- 高可用与安全设置 --- read_only 1 # 备库初始设为只读由 Keepalived 脚本控制开关 skip_name_resolve 1 innodb_flush_log_at_trx_commit 1 sync_binlog 14. 建立互为主从关系Step 1在两台节点分别创建复制用户以 1.25 为例1.28 同理-- 在 192.168.1.25 上执行 CREATE USER repl% IDENTIFIED WITH mysql_native_password BY Ins1234; GRANT REPLICATION SLAVE ON *.* TO repl%;Step 2互相指定对方为 Master 并启动复制-- 在 192.168.1.28 上执行指定 1.25 为自己的 Master CHANGE MASTER TO MASTER_HOST192.168.1.25, MASTER_USERrepl, MASTER_PASSWORDIns1234; START SLAVE; -- 在 192.168.1.25 上执行指定 1.28 为自己的 Master CHANGE MASTER TO MASTER_HOST192.168.1.28, MASTER_USERrepl, MASTER_PASSWORDIns1234; START SLAVE;5. 验证双主状态在两台节点上分别执行SHOW SLAVE STATUS\G确认Slave_IO_Running和Slave_SQL_Running均为Yes。四、Keepalived 自动故障转移光有双主还不够——应用需要通过一个固定的 VIP 访问数据库主节点挂掉时 VIP 自动漂移到备节点。这就是 Keepalived 的职责。1. 故障切换脚本to_master.sh节点升为 Master 时执行检查同步延迟延迟合格后解除只读接管写操作。#!/bin/bash # --- 配置项 --- MYSQL_USERroot MYSQL_PASSIns1234 MYSQL_CONNmysql -u${MYSQL_USER} -p${MYSQL_PASS} -e MAX_DELAY5 # 允许的最大主从延迟秒数 echo$(date) [INFO]: Keepalived 状态切换为 MASTER正在检查同步状态... /var/log/keepalived_mysql.log # 1. 检查 MySQL 服务是否存活 if ! mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} ping /dev/null 21; then echo$(date) [ERROR]: MySQL 服务未运行无法接管 /var/log/keepalived_mysql.log exit 1 fi # 2. 等待数据同步完成针对双主架构恢复后的数据补齐 RETRY0 while [ $RETRY -lt 10 ]; do DELAY$($MYSQL_CONNSHOW SLAVE STATUS\G | grep Seconds_Behind_Master | awk {print $2}) if [[ $DELAY NULL ]] || [[ -z $DELAY ]]; then echo$(date) [WARN]: Slave 状态异常或未连接请检查主从复制状态。 /var/log/keepalived_mysql.log break elif [ $DELAY -le $MAX_DELAY ]; then echo$(date) [INFO]: 数据同步完成 (延迟: ${DELAY}s)准备开启读写。 /var/log/keepalived_mysql.log break else echo$(date) [INFO]: 等待同步中当前延迟: ${DELAY}s... /var/log/keepalived_mysql.log sleep 2 let RETRY fi done # 3. 解除只读状态正式接管业务 $MYSQL_CONNSET GLOBAL read_only 0; SET GLOBAL super_read_only 0; if [ $? -eq 0 ]; then echo$(date) [SUCCESS]: 成功切换为读写模式 (Master)。 /var/log/keepalived_mysql.log else echo$(date) [ERROR]: 切换读写模式失败 /var/log/keepalived_mysql.log exit 1 fito_backup.sh节点降为 Backup 时执行立即开启只读防止脑裂写入。#!/bin/bash MYSQL_USERroot MYSQL_PASSIns1234 echo $(date) [INFO]: Keepalived 状态切换为 BACKUP开启只读模式。 /var/log/keepalived_mysql.log mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e SET GLOBAL read_only 1; SET GLOBAL super_read_only 1;安全机制super_read_only连拥有 SUPER 权限的用户也只能读了彻底杜绝脑裂写入的可能。2. Keepalived 配置Master 节点192.168.1.25global_defs { router_id m01 vrrp_skip_check_adv_addr # vrrp_strict # 注释掉否则在某些网络环境下VIP无法ping通 vrrp_garp_interval 0 vrrp_gna_interval 0 } # 脚本1检查 Nginx 和 Broker 服务 vrrp_script check_services { script pidof nginx systemctl is-active dcv-session-manager-broker interval 2 weight -20 # 失败则权重减 20 } # 脚本2检查 MySQL 服务状态 vrrp_script check_mysql { script mysqladmin ping -u root -pIns1234 interval 2 weight -30 # 失败则权重减 30 } vrrp_instance VI_1 { state MASTER # 主节点初始状态 interface eth2 # 请确认网卡名称正确如 ens33, eth0 等 virtual_router_id 51 # 主备必须一致 priority 100 # 主节点优先级需高于备节点 advert_int 1 authentication { auth_type PASS auth_pass dcv_ha_pwd } virtual_ipaddress { 192.168.1.100 # 虚拟IP (VIP) } track_script { check_services check_mysql } # 状态切换触发脚本 notify_master /etc/keepalived/to_master.sh# 变为MASTER时关闭只读开启写 notify_backup /etc/keepalived/to_backup.sh# 变为BACKUP时开启只读 notify_fault /etc/keepalived/to_backup.sh# 发生故障时强制只读 }Backup 节点192.168.1.28global_defs { router_id m02 # 建议与主节点不同 vrrp_skip_check_adv_addr # vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script check_services { script pidof nginx systemctl is-active dcv-session-manager-broker interval 2 weight -20 } vrrp_script check_mysql { script mysqladmin ping -u root -pIns1234 interval 2 weight -30 } vrrp_instance VI_1 { state BACKUP # 备节点初始状态 interface eth2 # 必须与物理网卡一致 virtual_router_id 51 priority 90 # 优先级低于主节点 advert_int 1 authentication { auth_type PASS auth_pass dcv_ha_pwd } virtual_ipaddress { 192.168.1.100 } track_script { check_services check_mysql } # 状态切换触发脚本 notify_master /etc/keepalived/to_master.sh notify_backup /etc/keepalived/to_backup.sh notify_fault /etc/keepalived/to_backup.sh }3. 故障转移流程┌─────────────────────────────────────────────────────┐ │ 正常状态 │ │ Master A (.25) ←── VIP ──→ Backup B (.28) │ │ read_only0 (读写) read_only1 (只读) │ └─────────────────────────────────────────────────────┘ │ Master A 故障 ▼ ┌────────────────────────────────────────────────────┐ │ 故障转移 │ │ 1. check_mysql 检测失败priority 100-30 70 │ │ 2. Backup B priority(90) 70接管 VIP │ │ 3. 触发 to_master.sh检查延迟 → 解除只读 │ │ 4. 触发 to_backup.shA 恢复后开启只读 │ └─────────────────────────────────────────────────────┘ ▼ ┌─────────────────────────────────────────────────────┐ │ 恢复后状态 │ │ Old Master (.25) New Master (.28) ←── VIP │ │ read_only1 (只读) read_only0 (读写) │ └─────────────────────────────────────────────────────┘五、用户管理与认证生产环境中MySQL 8.x 默认使用caching_sha2_password认证如果应用驱动不兼容需要手动切换认证方式-- 修改现有用户的认证插件为 mysql_native_password ALTER USER dcvbklocalhost IDENTIFIED WITH mysql_native_password BY Ins1234; -- 创建新用户并授权 CREATE USER dcvbklocalhost IDENTIFIED WITH mysql_native_password BY Ins1234; GRANT ALL PRIVILEGES ON dcvdb.* TO dcvbklocalhost; -- 验证用户信息 SELECT user, host, plugin FROM mysql.user WHERE user dcvbk; SHOW GRANTS FOR dcvtest%;六、总结本文覆盖了 MySQL / MariaDB 复制架构从入门到生产的完整路径方案适用场景优势不足MariaDB 主从读写分离、报表查询配置简单上手快手动故障切换MySQL 主从数据备份、读扩展8.x 原生支持 GTID手动故障切换双主 Keepalived生产级高可用自动故障转移零停机架构复杂度更高生产部署建议网络层面主备节点部署在同一机房、同网段尽量降低网络延迟监控告警务必监控Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running三个指标定期演练每季度至少一次手动故障切换演练确保预案有效数据校验使用pt-table-checksum/pt-table-syncPercona Toolkit定期校验主备数据一致性备份兜底主从复制不是备份方案仍需配合定期全量备份 Binlog 增量备份最后提醒没有银弹——双主架构解决了单点故障但引入了脑裂和数据一致性的新挑战。根据业务对 RPO / RTO 的要求选择最适合的方案而不是最复杂的方案。