第4章数据库集群部署

4、数据库集群部署：MySQL主从复制与读写分离、MyCAT/ShardingSphere中间件配置、数据库初始化脚本与数据迁移

数据库集群这块，说实话是交易系统的命门。我见过太多系统，应用层写得花里胡哨，结果数据库一挂全完蛋。今天咱们就把MySQL主从、读写分离、中间件配置、数据迁移这些硬骨头啃下来。

4.1 MySQL主从复制原理与搭建

主从复制，说白了就是让一台MySQL（主库）把数据变更实时同步到另一台或多台MySQL（从库）。我刚开始搞这个的时候，以为多复杂，其实核心就三个线程在工作。

核心流程：

主库的binlog线程：记录所有写操作到二进制日志
从库的I/O线程：拉取主库的binlog，写入本地中继日志
从库的SQL线程：重放中继日志，完成数据同步

嗯，这里要注意：binlog格式一定要选对。我个人习惯用ROW模式，虽然日志量大一点，但数据一致性最好。STATEMENT模式我曾经踩过坑，存储过程或者随机函数会导致主从不一致。

搭建步骤其实不复杂，我直接给出一套生产环境验证过的配置：

# 主库 my.cnf 关键配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync-binlog = 1
innodb_flush_log_at_trx_commit = 1
binlog-do-db = trade_db

# 从库 my.cnf 关键配置
[mysqld]
server-id = 2
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1
skip-slave-start = 0

配置完重启MySQL，然后执行：

-- 主库创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 从库设置主库信息
CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPass123!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;

-- 检查状态
SHOW SLAVE STATUS\G

看到Slave_IO_Running: Yes和Slave_SQL_Running: Yes，就说明搞定了。我曾经遇到过IO线程连不上的情况，排查了半天，结果是防火墙没放行3306端口。你想想看，这种低级错误多耽误事。

4.2 读写分离架构设计

主从搭好了，怎么用？读写分离。说白了就是：写操作走主库，读操作走从库。但这里有个坑——主从延迟。

交易系统里，用户刚下单成功，刷新页面却看不到订单。为什么？因为写操作在主库，读操作去了从库，但数据还没同步过来。这就是典型的读写分离一致性问题。

避坑指南：我曾经在一个支付系统中，用户支付成功后跳转订单页，结果页面显示「待支付」。排查发现就是从库延迟了2秒。解决方案：支付成功的请求强制走主库读，或者用Redis缓存写结果。

我一般推荐两种读写分离方案：

方案	优点	缺点	适用场景
应用层路由	灵活、可控	代码侵入性强	中小型项目
中间件代理	透明、易维护	增加网络延迟	大型分布式系统

我个人更倾向中间件方案，尤其是交易系统这种对稳定性要求极高的场景。应用层路由虽然灵活，但每个业务都要写一遍判断逻辑，维护成本太高。

4.3 MyCAT中间件配置实战

MyCAT，老牌中间件了。虽然现在ShardingSphere更火，但很多银行、证券的老系统还在用MyCAT。我去年还帮一个券商升级过MyCAT集群。

核心配置文件就两个：schema.xml和server.xml。我直接给出一份生产可用的配置：

<!-- schema.xml -->
<schema name="trade_db" checkSQLschema="true" sqlMaxLimit="100">
    <table name="orders" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" />
    <table name="users" primaryKey="id" dataNode="dn1" />
</schema>

<dataNode name="dn1" dataHost="host_master" database="trade_db" />
<dataNode name="dn2" dataHost="host_slave1" database="trade_db" />

<dataHost name="host_master" maxCon="100" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc">
    <writeHost host="master" url="jdbc:mysql://192.168.1.10:3306" user="mycat" password="pass123" />
</dataHost>

<dataHost name="host_slave1" maxCon="100" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc">
    <writeHost host="master" url="jdbc:mysql://192.168.1.10:3306" user="mycat" password="pass123">
        <readHost host="slave1" url="jdbc:mysql://192.168.1.11:3306" user="mycat" password="pass123" />
        <readHost host="slave2" url="jdbc:mysql://192.168.1.12:3306" user="mycat" password="pass123" />
    </writeHost>
</dataHost>

这里balance="1"表示开启读写分离，所有select查询会轮询到从库。写操作自动路由到主库。嗯，这里要注意：balance参数有0、1、2、3四种模式，我一般用1，兼顾了负载均衡和故障转移。

小技巧：MyCAT启动后，可以用mysql -h127.0.0.1 -P8066 -umycat -p连接管理端口，执行show @@datasource查看数据源状态。我曾经在线上用这个命令快速定位了一个从库宕机的问题。

4.4 ShardingSphere配置与对比

ShardingSphere是后起之秀，功能比MyCAT强不少。它支持分库分表、读写分离、数据加密、影子库压测等。我最近几个新项目都用的它。

配置方式有两种：YAML配置和Spring Boot Starter。我个人推荐用YAML，清晰明了：

# sharding-jdbc.yml
dataSources:
  master:
    url: jdbc:mysql://192.168.1.10:3306/trade_db
    username: root
    password: pass123
  slave0:
    url: jdbc:mysql://192.168.1.11:3306/trade_db
    username: root
    password: pass123
  slave1:
    url: jdbc:mysql://192.168.1.12:3306/trade_db
    username: root
    password: pass123

rules:
  - !READWRITE_SPLITTING
    dataSources:
      trade_db:
        writeDataSourceName: master
        readDataSourceNames:
          - slave0
          - slave1
        loadBalancerName: round_robin
    loadBalancers:
      round_robin:
        type: ROUND_ROBIN

ShardingSphere有个很实用的功能——Hint强制路由。比如用户刚下单，我想强制走主库读：

// Java代码示例
HintManager hintManager = HintManager.getInstance();
hintManager.setWriteRouteOnly();
// 执行查询，强制走主库
orderMapper.selectOrderById(orderId);
hintManager.close();

这个功能我经常用在「下单后立即查询订单」的场景，完美解决主从延迟问题。

对比一下MyCAT和ShardingSphere：

特性	MyCAT	ShardingSphere
部署方式	独立代理	客户端/代理
分片策略	有限	丰富（精确、范围、复合等）
分布式事务	弱支持	强支持（XA、SEATA）
社区活跃度	较低	高（Apache顶级项目）

4.5 数据库初始化脚本与数据迁移

这部分是实操的重头戏。我见过太多人，中间件配得漂漂亮亮，结果初始化脚本写得一塌糊涂。数据库初始化，我总结了三步走：

结构初始化：建库、建表、建索引、建存储过程
数据初始化：基础数据、字典表、初始配置
权限初始化：创建用户、授权、设置密码策略

我习惯用一个init.sql搞定所有，但分模块注释清楚：

-- ============================================
-- 1. 创建数据库
-- ============================================
CREATE DATABASE IF NOT EXISTS trade_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE trade_db;

-- ============================================
-- 2. 核心业务表
-- ============================================
CREATE TABLE IF NOT EXISTS `orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `order_no` varchar(32) NOT NULL COMMENT '订单号',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `amount` decimal(18,2) NOT NULL COMMENT '订单金额',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0待支付 1已支付 2已取消',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- ============================================
-- 3. 初始化基础数据
-- ============================================
INSERT INTO `sys_config` (`config_key`, `config_value`, `remark`) VALUES
('order_timeout', '1800', '订单超时时间（秒）'),
('max_retry_times', '3', '支付重试最大次数');

-- ============================================
-- 4. 创建应用用户并授权
-- ============================================
CREATE USER IF NOT EXISTS 'trade_app'@'%' IDENTIFIED BY 'AppPass2024!';
GRANT SELECT, INSERT, UPDATE, DELETE ON trade_db.* TO 'trade_app'@'%';
FLUSH PRIVILEGES;

注意：生产环境千万不要用root用户连接应用。我曾经接手过一个项目，所有应用都用root连数据库，吓得我连夜改了。权限最小化原则，这是底线。

数据迁移这块，我推荐用mysqldump + 管道的方式，效率高且可控：

# 全量迁移
mysqldump -h源库IP -u用户名 -p密码 --single-transaction --routines --triggers trade_db | mysql -h目标库IP -u用户名 -p密码 trade_db

# 增量迁移（基于GTID）
mysqldump -h源库IP -u用户名 -p密码 --single-transaction --master-data=2 --set-gtid-purged=ON trade_db > dump.sql
# 然后导入目标库
mysql -h目标库IP -u用户名 -p密码 trade_db < dump.sql

嗯，这里有个关键点：--single-transaction参数一定要加，它能在不锁表的情况下导出数据。我刚开始做DBA时不知道这个参数，大表导出直接锁了半小时，业务全停了。那次教训太深刻了。

4.6 知识体系总览

最后，我用一张图把本章的核心逻辑串起来。你一看就明白整个数据库集群的架构脉络了。

这张图把整个数据库集群的脉络理清楚了。应用层通过中间件路由，写操作走主库，读操作走从库，主从之间通过复制保持数据同步。数据迁移和初始化脚本是地基，一定要打好。

好了，数据库集群这部分就聊到这儿。记住一句话：中间件是骨架，数据一致性是灵魂。配置再花哨，数据对不上，一切都是白搭。

📚 课程目录

4、数据库集群部署：MySQL主从复制与读写分离、MyCAT/ShardingSphere中间件配置、数据库初始化脚本与数据迁移

4.1 MySQL主从复制原理与搭建

4.2 读写分离架构设计

4.3 MyCAT中间件配置实战

4.4 ShardingSphere配置与对比

4.5 数据库初始化脚本与数据迁移

4.6 知识体系总览