运维 Mysql 主从复制

MySQL 主从设置和故障排除 SOP

场景 1：设置 MySQL 主从复制 适用：读取副本设置、跨区域数据库加速、分析查询隔离。 前提条件 主从运行相同的 MySQL 版本（推荐 5.7 或 8.0） 主从之间的网络连接 从库有足够的磁盘空间 标准步骤

• 配置主 MySQL

vim /etc/my.cnf 添加以下内容： [mysqld] server-id = 1 # 必须唯一 log-bin = mysql-bin binlog_format = ROW systemctl restart mysqld

• 配置从 MySQL

vim /etc/my.cnf 添加以下内容： [mysqld] server-id = 2 # 必须唯一，且与主不同 relay-log = relay-bin read-only = 1 # 推荐为从库 systemctl restart mysqld

• 在主库创建复制用户

CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON . TO 'repl'@'%'; FLUSH PRIVILEGES;

• 锁表并获取 binlog 位置

FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; -- 记录 File 和 Position 重要：不要关闭此会话或停止 MySQL 之前解锁。

• 从主库导出数据

# 导出所有数据库 mysqldump -u root -p --all-databases > /tmp/all_db.sql # 导出特定数据库 mysqldump -u root -p --databases db1 db2 > /tmp/db.sql

• 记录 binlog 信息并解锁

SHOW MASTER STATUS; -- 记录 mysql-bin.000001 和 Position UNLOCK TABLES;

• 在从库导入数据

mysql -u root -p < /tmp/all_db.sql

• 在从库建立复制

CHANGE MASTER TO MASTER_HOST = 'master_ip', MASTER_USER = 'repl', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 1234; START SLAVE; SHOW SLAVE STATUS\G;

• 验证复制

SHOW SLAVE STATUS\G; 检查 Slave_IO_Running 和 Slave_SQL_Running 是否均为 Yes。

场景 2：Percona XtraBackup 大容量迁移 适用：数据库大小太大（数百 GB），或需要最小化停机时间。 为什么选择 XtraBackup 热备份 — 无需数据库停机 支持并行压缩 基于 InnoDB崩溃恢复 — 保证数据一致性 环境设置 安装 XtraBackup（CentOS/RHEL 7） yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm yum install -y percona-xtrabackup-24 对于 Ubuntu/Debian： apt install percona-xtrabackup-24 数据库用户权限 GRANT BACKUP_ADMIN ON . TO 'backup_user'@'localhost'; GRANT PROCESS, RELOAD, REPLICATION CLIENT ON . TO 'backup_user'@'localhost'; -- 或使用 root 如果权限足够 备份步骤

• 全备 + 压缩

xtrabackup --backup \ --target-dir=/tmp/backup_full \ --compress \ --compress-threads=4 \ --user=root \ --password=password

• 转移到目标服务器

scp -r /tmp/backup_full target_server:/tmp/ 恢复步骤

• 解压（在目标服务器）

xtrabackup --decompress --target-dir=/tmp/backup_full

• 准备（一致性检查）

xtrabackup --prepare --target-dir=/tmp/backup_full 重要：未准备的备份是不一致的。启动 MySQL 时使用未准备的文件将失败。

• 恢复

执行前请确认：目标机器 IP 正确、数据库名正确、备份文件已 prepare 且可用。生产环境建议先快照或备份旧数据目录。 # 停止 MySQL systemctl stop mysqld # 清空数据目录 rm -rf /var/lib/mysql/ # 复制回 xtrabackup --copy-back --target-dir=/tmp/backup_full # 修复权限 chown -R mysql:mysql /var/lib/mysql # 启动 MySQL systemctl start mysqld

场景 3：复制延迟调查 诊断命令 SHOW SLAVE STATUS\G; 关键指标： 字段 含义 Slave_IO_Running IO 线程状态 Slave_SQL_Running SQL 线程状态 Seconds_Behind_Master 延迟秒数（0 = 无延迟） Read_Master_Log_Pos 主 binlog 位置读取 Relay_Log_Space 中继日志总大小 常见原因 原因 症状 解决方案 从机性能不足 高 CPU/IO 升级从机硬件 大事务阻塞 SQL 线程卡住 分割大事务 网络延迟 IO 线程慢 检查主从网络 复制停止 SQL 线程停止 START SLAVE 重试

跨云数据库加速 适用：远程服务器需要访问中心数据库，且有高延迟（例如，海外服务器访问国内数据库）。 远程服务器 → 云全球加速器 → 国内源 ECS 配置步骤： 创建全球加速实例在云提供商控制台 配置加速区域（远程）和源 IP（国内 MySQL） 获取加速 IP 配置安全组在源服务器允许加速器的后端节点（注意：IP 组可能是动态的） 测试：telnet 加速 IP 3306

场景 4：MySQL 内存使用调查 适用：自管理 MySQL 内存使用过高导致性能下降。 调查步骤

• 确认 MySQL 内存使用

ps -eo pid,rss,vsz,comm | grep mysql top -b -n 1 | grep mysql

• 缓冲池使用情况按数据库

SELECT SUBSTRING_INDEX(table_name, '/', 1) AS db, COUNT() * 16 / 1024 AS size_mb FROM