一、环境准备
1. MySQL 主从复制配置
主库配置 (master.cnf):
[mysqld] server-id = 1 log-bin = mysql-bin binlog-format = ROW binlog-do-db = your_database expire_logs_days = 7 max_binlog_size = 100M从库配置 (slave.cnf):
[mysqld] server-id = 2 relay-log = mysql-relay-bin read-only = 1 replicate-do-db = your_database2. 建立主从复制
在主库执行：
-- 创建复制用户 CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; -- 查看主库状态 SHOW MASTER STATUS; -- 记录 File 和 Position在从库执行：
-- 配置主从复制 CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; -- 启动复制 START SLAVE; -- 查看复制状态 SHOW SLAVE STATUS\G;二、Mycat 2 配置
1. 安装 Mycat 2
# 下载 Mycat 2 wget https://github.com/MyCATApache/Mycat2/releases/download/{version}/mycat2-{version}.tar.gz # 解压 tar -zxvf mycat2-{version}.tar.gz -C /usr/local/ cd /usr/local/mycat22. 配置数据源 (datasource.json)
{ "dbType": "mysql", "idleTimeout": 60000, "initSqls": [], "initSqlsGetConnection": true, "instanceType": "READ_WRITE", "maxCon": 1000, "maxConnectTimeout": 3000, "maxRetryCount": 5, "minCon": 1, "name": "masterDataSource", "password": "password", "type": "JDBC", "url": "jdbc:mysql://master_ip:3306/your_database?useUnicode=true&characterEncoding=UTF-8", "user": "root", "weight": 0 }, { "dbType": "mysql", "idleTimeout": 60000, "initSqls": [], "initSqlsGetConnection": true, "instanceType": "READ", "maxCon": 1000, "maxConnectTimeout": 3000, "maxRetryCount": 5, "minCon": 1, "name": "slaveDataSource", "password": "password", "type": "JDBC", "url": "jdbc:mysql://slave_ip:3306/your_database?useUnicode=true&characterEncoding=UTF-8", "user": "root", "weight": 0 }3. 配置集群 (cluster.json)
{ "clusterType": "MASTER_SLAVE", "heartbeat": { "heartbeatTimeout": 10000, "maxRetryCount": 3, "minSwitchTimeInterval": 60000, "slaveThreshold": 0 }, "masters": [ "masterDataSource" ], "maxCon": 200, "name": "mysqlCluster", "readBalanceType": "BALANCE_ALL", "readBalanceType": "BALANCE_ALL_READ", "slaves": [ "slaveDataSource" ], "switchType": "SWITCH" }4. 配置逻辑库和表 (schema.json)
{ "customTables": {}, "globalTables": {}, "normalProcedures": {}, "normalTables": { "user": { "createTableSQL": "CREATE TABLE `user` (id INT PRIMARY KEY, name VARCHAR(50))", "locality": { "schemaName": "your_database", "tableName": "user", "tableUnit": "mysqlCluster" } } }, "schemaName": "mycat_db", "shardingTables": {}, "views": {} }5. 配置读写分离策略
在mycat.yml中配置：
interceptors: - className: io.mycat.plug.loadBalance.LoadBalanceStrategy name: loadBalance enabled: true properties: defaultLoadBalance: balanceAllRead writeCloudBalance: masterOnly readCloudBalance: slaveFirst - className: io.mycat.plug.sequence.SequenceHandler name: sequenceHandler enabled: true properties: sequenceHandlerType: distributed - className: io.mycat.plug.router.Router name: router enabled: true properties: routerStrategy: read_write_split6. 配置分片规则（可选）
如果需要分片，配置rule.json：
{ "function": { "partitionByLong": { "clazz": "io.mycat.router.function.PartitionByLong", "partitionCount": "2", "partitionLength": "256" } }, "tableRules": { "user": { "ruleName": "user", "rules": [ { "columns": [ "id" ], "algorithm": "partitionByLong" } ] } } }三、读写分离策略详解
1. 负载均衡策略
在mycat.yml中配置：
loadBalance: defaultLoadBalance: balanceAll writeCloudBalance: masterOnly readCloudBalance: slaveFirst可用策略：
balanceAll：所有读请求随机分配到所有从节点balanceAllRead：所有读请求随机分配到所有读节点balanceMasterSlave：优先主节点，主节点不可用才用从节点balanceSlaveFirst：优先从节点，从节点不可用才用主节点masterOnly：只使用主节点
2. SQL 路由规则
Mycat 2 默认的读写分离规则：
写操作：INSERT、UPDATE、DELETE、CREATE、ALTER、DROP 等路由到主库
读操作：SELECT 路由到从库
事务中的读：自动路由到主库保证一致性
强制读主：
/*+ mycat:db_type=master */ SELECT ...
四、启动和测试
1. 启动 Mycat 2
# 启动 ./bin/mycat start # 查看状态 ./bin/mycat status # 查看日志 tail -f logs/wrapper.log2. 连接测试
# 连接 Mycat mysql -umycat -p123456 -P8066 -h127.0.0.1 # 创建测试表 CREATE TABLE test ( id INT PRIMARY KEY, name VARCHAR(50) ); # 插入数据（会路由到主库） INSERT INTO test VALUES (1, 'test'); # 查询数据（会路由到从库） SELECT * FROM test;3. 监控读写分离状态
在 Mycat 管理端查看：
-- 查看数据源状态 SHOW DATASOURCES; -- 查看集群状态 SHOW CLUSTERS; -- 查看连接信息 SHOW CONNECTIONS; -- 查看SQL统计 SHOW SQL_STAT;五、高级配置
1. 主从延迟处理
# 在 mycat.yml 中配置 heartbeat: slaveThreshold: 60 # 从库延迟超过60秒则跳过 checkSlaveLag: true lagCheckInterval: 50002. 事务一致性保证
// 对于需要强一致性的读操作，使用Hint强制走主库 /*+ mycat:db_type=master */ SELECT * FROM account WHERE id = 1 FOR UPDATE;3. 多从库负载均衡
{ "clusterType": "MASTER_SLAVE", "masters": ["master1"], "slaves": ["slave1", "slave2", "slave3"], "readBalanceType": "WEIGHT_RANDOM", "weights": { "slave1": 1, "slave2": 2, "slave3": 1 } }4. 故障转移配置
{ "heartbeat": { "heartbeatTimeout": 5000, "maxRetryCount": 3, "slaveThreshold": 0, "switchType": "SWITCH" }, "switch": { "errorRetryTimeout": 300000, "enableMasterSlaveSwitch": true, "slaveFailoverRetryDelay": 10000 } }六、监控和维护
1. 监控脚本示例
#!/bin/bash # 监控主从同步状态 mysql -h slave_ip -uroot -ppassword -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master" # 监控Mycat连接数 mysql -umycat -p123456 -P8066 -h127.0.0.1 -e "SHOW @@CONNECTION"2. 常见问题解决
主从延迟问题：
增加从库配置
使用半同步复制
业务端读主库
连接池问题：
{ "maxCon": 100, "minCon": 10, "maxConnectTimeout": 3000, "idleTimeout": 60000 }内存优化：
server: bufferPool: pageSize: 8192 chunks: 2048
七、完整部署架构
应用程序 ↓ Mycat 2 (负载均衡+SQL路由) ↓ MySQL 主库 (写) ←→ 主从复制 ↓ MySQL 从库 (读) MySQL 从库 (读)
这种架构提供了：
自动的读写分离
透明的故障转移
灵活的负载均衡策略
完善的监控机制
易于扩展的架构
建议在生产环境部署前，充分测试各种场景，确保数据一致性和系统稳定性。
