{"msg":"操作成功","code":200,"data":{"createBy":"admin","createTime":"2025-03-01 15:32:01","updateBy":"admin","updateTime":"2025-03-01 15:32:01","remark":null,"id":101,"articleTitle":"MySQL（十）主从同步与主主架构","articleUrl":"mysql_master_slave","articleThumbnail":"https://www.asumimoe.com/imgfiles/20220908/9e192744fab244d39d3b15fca035c9f7.jpg","articleFlag":"0","draftStatus":"1","reprintStatement":"1","articleSummary":"MySQL 主从同步（Replication）是数据库高可用、高性能架构的核心技术，通过将主库（Master）的数据变更复制到从库（Slave），实现数据冗余、负载均衡和灾难恢复。下面从原理到实践全面解析主从同步机制。","articleContent":"MySQL 主从同步（Replication）是数据库高可用、高性能架构的核心技术，通过将主库（Master）的数据变更复制到从库（Slave），实现数据冗余、负载均衡和灾难恢复。下面从原理到实践全面解析主从同步机制。\n\n## 主从同步原理\n\nmaster服务器将数据的改变都记录到二进制binlog日志中，只要master上的数据发生改变，则将其改变写入二进制日志；salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。\n\n![](img/f66abe8f226e47cfa49a0b74e1f71d08.png)\n\n## 主从集群搭建\n\n### 1.修改主库配置文件\n\n```shell\nvim /etc/my.cnf\n\n[mysqld]\nserver-id = 1                # 唯一ID，主从数据库必须保持不一致\nlog-bin = /var/lib/mysql/mysql-bin  # 启用Binlog\nbinlog_format = ROW          # 推荐ROW格式\nexpire_logs_days = 7         # 日志保留天数\nmax_binlog_size = 100M       # 单个Binlog大小\nbinlog_do_db = mydb          # 指定复制数据库\n# 主从复制忽略数据库\nbinlog-ignore-db = mysql\nbinlog-ignore-db = information_schema\nbinlog-ignore-db = performance_schema\nbinlog-ignore-db = sys\n```\n\n### 2.重启数据库服务\n\n```bash\nsystemctl restart mysqld\n```\n\n### 3.主库中创建主从连接账号并授权\n\n```sql\nCREATE USER 'slave'@'%' IDENTIFIED BY 'slave';\n\nGRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';\n\nflush privileges;\n```\n\n### 4.获取主库状态\n\n记录 **File** (mysql-bin.000001) 和 **Position** (120)\n\n```sql\nmysql> show master status\\G;\n*************************** 1. row ***************************\n             File: binlog.000001\n         Position: 120\n     Binlog_Do_DB: \n Binlog_Ignore_DB: \nExecuted_Gtid_Set: \n1 row in set (0.00 sec)\n```\n\n### 5.从库初始数据同步\n\n```sql\n# 主库备份\nmysqldump -uroot -p --master-data --single-transaction --routines --triggers --all-databases > full_backup.sql\n\n# 传输到从库\nscp full_backup.sql user@slave:/path/\n\n# 从库导入\nmysql -uroot -p < full_backup.sql\n```\n\n### 6.修改从库配置文件\n\n```shell\nvim /etc/my.cnf\n\n[mysqld]\nserver-id = 2                # 唯一ID\nrelay-log = /var/lib/mysql/mysql-relay-bin\nread_only = ON               # 设置只读\nreplicate_do_db = mydb       # 指定复制数据库\n```\n\n### 7.创建主从数据库连接\n\n```sql\nCHANGE MASTER TO MASTER_HOST='192.168.100.10', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;\n\nstart slave;\n```\n\n### 8.查看从库状态\n\n```sql\nmysql> show slave status\\G;\n*************************** 1. row ***************************\n               Slave_IO_State: Waiting for master to send event\n                  Master_Host: 10.10.118.25\n                  Master_User: mysync\n                  Master_Port: 3306\n                Connect_Retry: 60\n              Master_Log_File: mysql-bin.000023\n          Read_Master_Log_Pos: 39913\n               Relay_Log_File: mysql-relay-bin.000031\n                Relay_Log_Pos: 40059\n        Relay_Master_Log_File: mysql-bin.000023\n             Slave_IO_Running: Yes   #Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；\n            Slave_SQL_Running: Yes   #具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。 \n              Replicate_Do_DB: \n          Replicate_Ignore_DB: \n           Replicate_Do_Table: \n       Replicate_Ignore_Table: \n      Replicate_Wild_Do_Table: \n  Replicate_Wild_Ignore_Table: mysql.%,test.%,information_schema.%\n                   Last_Errno: 0\n                   Last_Error: \n                 Skip_Counter: 0\n          Exec_Master_Log_Pos: 39913\n              Relay_Log_Space: 40361\n              Until_Condition: None\n               Until_Log_File: \n                Until_Log_Pos: 0\n           Master_SSL_Allowed: No\n           Master_SSL_CA_File: \n           Master_SSL_CA_Path: \n              Master_SSL_Cert: \n            Master_SSL_Cipher: \n               Master_SSL_Key: \n        Seconds_Behind_Master: 0     #判断主动同步延时的参考值，是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值； \nMaster_SSL_Verify_Server_Cert: No\n                Last_IO_Errno: 0\n                Last_IO_Error: \n               Last_SQL_Errno: 0\n               Last_SQL_Error: \n  Replicate_Ignore_Server_Ids: \n             Master_Server_Id: 18\n1 row in set (0.00 sec)\n```\n\n**关键指标：**\n\n- `Slave_IO_Running`: Yes\n- `Slave_SQL_Running`: Yes\n- `Seconds_Behind_Master`: 0\n- `Last_IO_Errno`: 0\n- `Last_SQL_Errno`: 0\n\n## 主主架构\n\n主主架构是 MySQL 高可用解决方案的核心模式之一，它允许两个或多个数据库节点同时作为主库，实现双向数据同步，提供更高的可用性和写入能力。\n\n主主架构常见与`Keepalived+MySQL`的集群方式，一台数据库服务器提供读写服务，并做双向数据同步，保证集群VIP发生切换后仍然可以保证两个数据库的数据相同。\n\n### 工作流程：\n\n1. **写入请求**：应用将写请求分发到任一主节点\n2. **本地执行**：主节点在本地执行写操作\n3. **Binlog生成**：写操作记录到Binlog\n4. **同步传播**：Binlog事件通过复制线程发送到对端节点\n5. **对端重放**：对端节点的SQL线程重放事件\n\n### 主主架构核心价值\n\n| 优势             | 说明                     |\n| ---------------- | ------------------------ |\n| **高可用性**     | 单节点故障不影响服务     |\n| **写扩展**       | 分散写负载（需应用配合） |\n| **故障快速切换** | 无需重建从库             |\n| **灵活维护**     | 滚动升级不影响服务       |\n| **灾备就绪**     | 天然异地多活基础         |\n\n### 主主架构配置详解\n\n#### 1. 环境准备\n\n- 两个MySQL节点（NodeA和NodeB）\n- 相同MySQL版本（推荐5.7+）\n- 初始数据一致\n- 专用复制网络\n\n#### 2. 配置NodeA (192.168.1.101)\n\n```ini\n# my.cnf\n[mysqld]\nserver-id = 101\nlog-bin = /var/lib/mysql/mysql-bin\nrelay-log = /var/lib/mysql/mysql-relay-bin\nauto_increment_increment = 2  # 自增步长\nauto_increment_offset = 1     # 自增起始值\nbinlog_format = ROW\nexpire_logs_days = 7\ngtid_mode = ON\nenforce_gtid_consistency = ON\nlog_slave_updates = ON\n```\n\n#### 3. 配置NodeB (192.168.1.102)\n\n```ini\n# my.cnf\n[mysqld]\nserver-id = 102\nlog-bin = /var/lib/mysql/mysql-bin\nrelay-log = /var/lib/mysql/mysql-relay-bin\nauto_increment_increment = 2  # 自增步长\nauto_increment_offset = 2     # 自增起始值\nbinlog_format = ROW\nexpire_logs_days = 7\ngtid_mode = ON\nenforce_gtid_consistency = ON\nlog_slave_updates = ON\n```\n\n#### 4. 创建复制账户（两节点执行）\n\n```sql\nCREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'SecurePass123!';\nGRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';\nFLUSH PRIVILEGES;\n```\n\n#### 5. 配置双向复制\n\n**在NodeA上执行：**\n\n```sql\nCHANGE MASTER TO\n  MASTER_HOST='192.168.1.102',\n  MASTER_USER='repl',\n  MASTER_PASSWORD='SecurePass123!',\n  MASTER_AUTO_POSITION=1;\n\nSTART SLAVE;\n```\n\n**在NodeB上执行：**\n\n```sql\nCHANGE MASTER TO\n  MASTER_HOST='192.168.1.101',\n  MASTER_USER='repl',\n  MASTER_PASSWORD='SecurePass123!',\n  MASTER_AUTO_POSITION=1;\n\nSTART SLAVE;\n```\n\n#### 6. 验证复制状态\n\n```sql\nSHOW SLAVE STATUS\\G\n/* 关键指标：\nSlave_IO_Running: Yes\nSlave_SQL_Running: Yes\nSeconds_Behind_Master: 0\nAuto_Position: 1\n*/\n```\n\n### 主主架构核心挑战与解决方案\n\n#### 1. 自增主键冲突\n\n**问题**：双主写入导致自增ID冲突 \n**解决方案**：错开自增序列\n\n```ini\n# NodeA\nauto_increment_increment = 2  # 步长\nauto_increment_offset = 1     # 起始值 → 1,3,5,7...\n\n# NodeB\nauto_increment_increment = 2\nauto_increment_offset = 2     # 起始值 → 2,4,6,8...\n```\n\n#### 2. 数据冲突\n\n**问题**：同时更新同一行数据 \n**解决方案**：\n\n- **业务层**：分区写入（如按用户ID分片）\n\n- **数据库层**：\n\n  ```sql\n  /* 启用冲突检测 */\n  SET GLOBAL slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN';\n  ```\n\n- **架构层**：使用中间件控制写路由\n\n#### 3. 循环复制\n\n**问题**：A→B→A无限循环 \n**解决方案**：`server-id`过滤\n\n```sql\n/* 在my.cnf中配置 */\n[mysqld]\nreplicate-same-server-id = 0  # 禁止复制相同server-id\nlog_slave_updates = ON        # 从库记录binlog\n```\n\n#### 4. 脑裂问题\n\n**问题**：网络分区导致双主同时写入 \n**解决方案**：\n\n- **仲裁机制**：第三方仲裁节点\n- **Fencing**：存储级隔离\n- **中间件**：ProxySQL自动检测","categoryId":4,"viewCount":101,"categoryName":"MySQL","author":"球接子","authorAvatar":null,"tagIds":[2,17,11],"tagNames":["MySQL","数据库","高可用"]}}