MySQL + Mycat 实现主从复制与读写分离¶
目录¶
一、主从复制与读写分离架构¶
主从复制是 MySQL 高可用架构的基础，通过同步主库数据到从库，实现数据备份、故障转移和读写分离。
读写分离通过中间件（如 Mycat）将写操作路由到主库，读操作路由到从库，减轻主库压力，提高查询性能。
架构图¶
[客户端] → [Mycat] → [主库(M1)] ←→ [从库(S1)]
↓
[从库(S2)]
二、环境准备¶
服务器规划¶
| 角色 | IP地址 | 操作系统 |
|---|---|---|
| Mycat 中间件 | 192.168.204.121 | CentOS 7 |
| MySQL 主库 1 | 192.168.204.122 | CentOS 7 |
| MySQL 从库 1 | 192.168.204.123 | CentOS 7 |
| MySQL 主库 2 | 192.168.204.124 | CentOS 7 |
| MySQL 从库 2 | 192.168.204.125 | CentOS 7 |
依赖安装¶
- MySQL：安装 5.7+ 版本，配置
yum源或编译安装。 - Mycat：下载 Mycat-server，解压后配置环境变量。
三、MySQL 主从复制配置¶
1. 主库（M1: 192.168.204.122）配置¶
编辑配置文件 /etc/my.cnf¶
[mysqld]
server-id=1 # 唯一标识
log-bin=mysql-bin # 开启二进制日志
binlog-ignore-db=mysql # 忽略系统库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
log-slave-updates=1 # 允许从库写入二进制日志（用于双主）
expire_logs_days=7 # 日志保留7天
max_binlog_size=100M # 二进制日志大小
[mysqld_safe]
log-error=/var/log/mysqld.log
重启 MySQL 并创建复制用户¶
systemctl restart mysqld
mysql -u root -p -e "CREATE USER 'repl'@'192.168.204.%' IDENTIFIED BY 'Repl@123'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.204.%';"
记录主库二进制日志位置¶
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
2. 从库（S1: 192.168.204.123）配置¶
编辑配置文件 /etc/my.cnf¶
[mysqld]
server-id=2 # 唯一标识（与主库不同）
relay_log=mysql-relay-bin # 中继日志
read_only=1 # 只读模式（除超级用户）
log_slave_updates=1 # 允许从库写入二进制日志
[mysqld_safe]
log-error=/var/log/mysqld.log
启动从库并配置复制¶
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.204.122',
MASTER_USER='repl',
MASTER_PASSWORD='Repl@123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
mysql> START SLAVE;
验证从库状态¶
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.204.122
Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Error:
Seconds_Behind_Master: 0
3. 主库（M2: 192.168.204.124）和从库（S2: 192.168.204.125）配置¶
重复上述步骤，主库（M2） 配置 server-id=3，从库（S2） 配置 server-id=4，并指向 M2 作为主库。
四、Mycat 读写分离配置¶
1. 配置 schema.xml（核心配置文件）¶
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<!-- 定义逻辑库 -->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<!-- 定义表：employee 按 sharding_id 分片 -->
<table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" />
</schema>
<!-- 数据节点：指定物理库和表 -->
<dataNode name="dn1" dataHost="hostM1" database="TESTDB" />
<dataNode name="dn2" dataHost="hostM2" database="TESTDB" />
<!-- 数据主机：配置读写分离 -->
<dataHost name="hostM1" maxCon="1000" minCon="10" balance="1" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<!-- 写节点：主库 M1 -->
<writeHost host="M1" url="192.168.204.122:3306" user="root" password="Root@123">
<!-- 读节点：从库 S1 -->
<readHost host="S1" url="192.168.204.123:3306" user="root" password="Root@123"/>
</writeHost>
</dataHost>
<!-- 数据主机：配置 M2 和 S2 -->
<dataHost name="hostM2" maxCon="1000" minCon="10" balance="1" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="M2" url="192.168.204.124:3306" user="root" password="Root@123">
<readHost host="S2" url="192.168.204.125:3306" user="root" password="Root@123"/>
</writeHost>
</dataHost>
</mycat:schema>
2. 配置 server.xml（用户与权限）¶
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="useHandshakeV10">1</property>
<property name="serverPort">8066</property>
</system>
<user name="root" defaultAccount="true">
<property name="password">Mycat@123</property>
<property name="schemas">TESTDB</property>
</user>
</mycat:server>
3. 配置分片规则（rule.xml）¶
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">0</property>
<property name="defaultNode">0</property>
</function>
4. 分片规则映射文件（partition-hash-int.txt）¶
10000=0 # sharding_id=10000 映射到 dn1
10010=1 # sharding_id=10010 映射到 dn2
5. 启动 Mycat¶
cd /usr/local/mycat/bin
./mycat start
五、测试验证¶
1. 连接 Mycat¶
mysql -u root -p -h 192.168.204.121 -P 8066
2. 创建测试表并插入数据¶
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(100),
sharding_id INT
);
INSERT INTO employee(id, name, sharding_id) VALUES
(1, 'Alice', 10000),
(2, 'Bob', 10010);
3. 验证分片效果¶
- 插入
sharding_id=10000的数据到dn1（M1/S1）。 - 插入
sharding_id=10010的数据到dn2（M2/S2）。
4. 验证读写分离¶
- 写操作：在 Mycat 执行 INSERT，数据写入主库（M1/M2）。
- 读操作：执行 SELECT，数据从从库（S1/S2）返回。
六、双主双从高可用架构¶
在上述基础上，通过配置 M1 ↔ M2 互为主从，实现高可用。
关键配置修改¶
- 主库 M2 配置
log-slave-updates，使其作为 M1 的从库。 - 主库 M1 配置为 M2 的从库：
CHANGE MASTER TO
MASTER_HOST='192.168.204.124',
MASTER_USER='repl',
MASTER_PASSWORD='Repl@123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
- Mycat 配置：
balance=3（所有读请求随机分发到主从库）。
故障转移测试¶
- 手动停止主库 M1，Mycat 自动切换到 M2 作为主库。
- 重新启动 M1，数据自动同步到 M1，恢复后自动切换回主从。
七、参考资料¶
注意：生产环境需优化参数（如连接池、日志清理、监控告警），并定期备份数据。