mysql 主从架构详解

MySQL 主从架构详解MySQL 主从复制Master-Slave Replication是构建高可用、可扩展数据库系统的核心架构之一。它通过将主数据库Master的变更同步到从数据库Slave实现数据冗余、读写分离和故障恢复。本文将从原理、配置、实战代码和常见问题入手带你深入理解这一架构。## 主从复制的原理MySQL 主从复制基于**二进制日志binlog**机制。主库的所有数据变更如 INSERT、UPDATE、DELETE会记录在 binlog 中从库通过 I/O 线程拉取这些日志并写入中继日志relay log再由 SQL 线程重放从而保持数据一致。关键组件-Master开启 binlog授权复制用户。-Slave配置连接 Master 的信息启动复制线程。-I/O 线程从 Master 拉取 binlog 事件。-SQL 线程执行 relay log 中的 SQL 语句。工作流程1. Master 将数据变更写入 binlog。2. Slave 的 I/O 线程连接到 Master请求 binlog 事件。3. Master 的 dump 线程发送 binlog 给 Slave。4. Slave 将事件写入 relay log。5. Slave 的 SQL 线程重放 relay log更新本地数据。## 配置主从复制### 前提条件- 安装 MySQL 8.0示例使用 Docker 环境。- 确保主从库网络互通本文在同一台机器上演示使用不同端口。### 主库配置 (Master)主库需要开启 binlog 并设置唯一 server-id。以下为 Linux 环境下的配置示例。bash# 编辑主库配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf[mysqld]# 开启二进制日志log_bin /var/log/mysql/mysql-bin.log# 设置服务器 ID主库唯一server-id 1# 指定要复制的数据库可选留空表示所有库binlog-do-db test_db# 二进制日志格式ROW 比 STATEMENT 更可靠binlog_format ROW# 每执行 N 次事务写一次 binlog提升性能sync_binlog 1重启 MySQL 后进入主库创建复制用户sql-- 登录主库mysql -u root -p-- 创建用户仅允许从库 IP本例为 192.168.1.100连接CREATE USER replicator192.168.1.100 IDENTIFIED BY StrongPassword123!;-- 授予复制权限GRANT REPLICATION SLAVE ON *.* TO replicator192.168.1.100;-- 刷新权限FLUSH PRIVILEGES;-- 查看主库状态记录 File 和 Position 值SHOW MASTER STATUS;输出示例------------------------------------------------------------| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |------------------------------------------------------------| mysql-bin.000003 | 154 | test_db | |------------------------------------------------------------### 从库配置 (Slave)从库需要设置不同的 server-id并指定主库信息。bash# 编辑从库配置文件[mysqld]server-id 2# 开启中继日志relay_log /var/log/mysql/mysql-relay-bin.log# 只读模式避免从库写入冲突read_only 1# 可选设置复制过滤replicate-do-db test_db重启从库后执行以下 SQL 建立复制链路sql-- 登录从库mysql -u root -p-- 停止已有的复制如果有STOP SLAVE;-- 配置连接主库的信息CHANGE MASTER TO MASTER_HOST 192.168.1.100, MASTER_PORT 3306, MASTER_USER replicator, MASTER_PASSWORD StrongPassword123!, -- 使用前面 SHOW MASTER STATUS 得到的值 MASTER_LOG_FILE mysql-bin.000003, MASTER_LOG_POS 154;-- 启动复制START SLAVE;-- 查看复制状态SHOW SLAVE STATUS\G;关键字段解释-Slave_IO_Running: YesI/O 线程正常-Slave_SQL_Running: YesSQL 线程正常-Seconds_Behind_Master: 0延迟为 0## 实战代码读写分离演示以下 Python 脚本演示了在主库写入数据并在从库读取。假设主库连接为localhost:3307从库为localhost:3308通过不同端口模拟不同机器。pythonimport mysql.connectorfrom mysql.connector import Error# 主库连接配置写操作master_config { host: localhost, port: 3307, user: root, password: root_password, database: test_db}# 从库连接配置读操作slave_config { host: localhost, port: 3308, user: root, password: root_password, database: test_db}def write_to_master(): 在主库插入数据 try: conn mysql.connector.connect(**master_config) cursor conn.cursor() # 创建表如果不存在 cursor.execute( CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ) ) # 插入一条记录 sql INSERT INTO users (name, email) VALUES (%s, %s) cursor.execute(sql, (Alice, aliceexample.com)) conn.commit() print(f写入成功记录 ID: {cursor.lastrowid}) except Error as e: print(f写入错误: {e}) finally: if conn.is_connected(): cursor.close() conn.close()def read_from_slave(): 从从库读取数据 try: conn mysql.connector.connect(**slave_config) cursor conn.cursor() cursor.execute(SELECT * FROM users) rows cursor.fetchall() print(从库读取结果:) for row in rows: print(row) except Error as e: print(f读取错误: {e}) finally: if conn.is_connected(): cursor.close() conn.close()if __name__ __main__: # 先写入主库 write_to_master() # 等待复制完成实际环境可用轮询 import time time.sleep(1) # 从从库读取 read_from_slave()### 复制延迟监控脚本以下 Shell 脚本定期检查从库延迟并在延迟过大时告警。bash#!/bin/bash# 监控 MySQL 主从复制延迟# 用法: ./monitor_replication.shSLAVE_HOSTlocalhostSLAVE_PORT3308SLAVE_USERrootSLAVE_PASSroot_password# 获取 Seconds_Behind_Master 值LAG$(mysql -h $SLAVE_HOST -P $SLAVE_PORT -u $SLAVE_USER -p$SLAVE_PASS -e SHOW SLAVE STATUS\G | grep Seconds_Behind_Master | awk {print $2})if [[ $LAG NULL ]]; then echo 错误: 复制未启动或状态异常 exit 1fiif [[ $LAG -gt 60 ]]; then echo 警告: 复制延迟超过 60 秒当前延迟: ${LAG}秒 # 可在此处发送邮件或短信告警else echo 正常: 复制延迟 ${LAG}秒fi## 常见问题与优化### 1. 复制中断-IO 线程 No检查网络、防火墙、复制用户权限。-SQL 线程 No查看Last_Error字段可能是主从数据不一致如主库有记录从库没有。可跳过错 误SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START SLAVE;### 2. 延迟优化- 使用半同步复制rpl_semi_sync_master_enabled1。- 从库开启log_slave_updates用于级联复制。- 调整innodb_flush_log_at_trx_commit和sync_binlog参数。### 3. 安全性- 使用 SSL 加密复制链路。- 从库设置read_only1避免误写。- 定期备份 binlog 文件。## 总结MySQL 主从复制是保障数据库可用性和扩展性的基石。通过本文的配置演示和实战代码你可以在几分钟内搭建起一套基本的主从架构。关键点包括正确配置 binlog 和 server-id、使用正确的位置参数建立复制、通过监控脚本持续关注延迟。在生产环境中建议结合半同步复制、GTID全局事务标识符和自动故障切换工具如 Orchestrator来进一步提升可靠性。掌握这些技能你将能应对大多数数据库高可用场景。