MySQL双主复制实现读写分离方案

MySQL双主复制(Master-Master Replication)是一种常见的数据库高可用架构，可以用于实现读写分离。以下是实现方案和注意事项：

基本实现原理

双主配置：两台MySQL服务器互为主从
读写分离：应用层将写操作定向到一个主库，读操作分散到两个库
自动故障转移：当一台主库宕机，另一台可继续提供服务

详细配置步骤

1. 环境准备

两台MySQL服务器(假设为MasterA和MasterB)
相同版本的MySQL(建议5.7+或8.0+)
网络互通，防火墙开放3306端口

2. 配置文件修改(my.cnf)

MasterA配置:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
auto-increment-increment = 2
auto-increment-offset = 1
replicate-do-db = your_database_name
binlog-do-db = your_database_name

MasterB配置:

[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW 
auto-increment-increment = 2  
auto-increment-offset = 2
replicate-do-db = your_database_name  
binlog-do-db = your_database_name

3. 创建复制账户

在每台服务器上执行：

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

4. 配置复制关系

在MasterA上：

CHANGE MASTER TO 
MASTER_HOST='MasterB_IP',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=Position_Number;
START SLAVE;

在MasterB上：

CHANGE MASTER TO  
MASTER_HOST='MasterA_IP',  
MASTER_USER='repl',  
MASTER_PASSWORD='password',  
MASTER_LOG_FILE='mysql-bin.000001',  
MASTER_LOG_POS=Position_Number;  
START SLAVE;

5. 验证复制状态

在两台服务器上执行：

SHOW SLAVE STATUS\G;

确保Slave_IO_Running和Slave_SQL_Running都为Yes。

读写分离实现方式

应用层实现
- DAO层根据SQL类型选择数据源（写操作用主库A，读操作用主库B）
- Spring框架可使用AbstractRoutingDataSource动态切换数据源
中间件方案
- ProxySQL:高性能MySQL代理，支持读写分离和负载均衡
- MySQL Router:官方提供的轻量级中间件
客户端驱动
- Connector/J的ReplicationDriver支持自动路由读写请求

ProxySQL配置示例（推荐）

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'MasterA_IP',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'MasterB_IP',3306);

INSERT INTO mysql_replication_hostgroups VALUES (10,20,'read_only','双主集群');

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- 配置读写规则
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) 
VALUES (1,1,'^SELECT.*FOR UPDATE',10,1), (2,1,'^SELECT',20,1), (3,1,'^INSERT',10,1), (4,1,'^UPDATE',10,1), (5,1,'^DELETE',10,1);

注意事项

自增ID冲突问题
- auto-increment-increment和auto-increment-offset确保ID不冲突
数据一致性风险
- DDL语句需谨慎执行，建议先在单节点执行后观察同步情况
性能考虑
- Binlog格式建议使用ROW模式减少不一致风险
监控与维护
- SHOW SLAVE STATUS定期检查复制延迟
故障处理
- STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;可跳过错误事件
  
  内容由零声教学AI助手提供，问题来源于学员提问