1. 为什么需要MySQL到PostgreSQL的实时同步在企业级应用开发中数据库迁移和同步是常见的需求场景。我最近接手的一个电商项目就遇到了这样的挑战原有系统基于MySQL构建但新模块需要利用PostgreSQL的JSONB和GIS等高级特性。经过多种方案对比最终选择了实时同步的方案而不是全量迁移。MySQL和PostgreSQL作为两大主流开源关系型数据库各自有着鲜明的特点。MySQL以简单易用、高性能著称特别适合OLTP场景而PostgreSQL则以功能丰富、扩展性强见长支持自定义数据类型、窗口函数等高级特性。当业务发展到一定规模往往需要结合两者的优势。实时同步的核心价值在于业务连续性避免停机迁移带来的损失渐进式迁移可以按模块逐步切换降低风险数据冗余构建灾备系统提高可用性混合架构不同业务模块可以使用最适合的数据库2. 实时同步的技术方案选型实现数据库实时同步主要有以下几种技术路线2.1 基于触发器的方案这是最直观的解决方案通过在源表上创建触发器在数据变更时同步到目标库。我曾经在一个小型项目中使用过这种方式虽然实现简单但存在明显缺陷性能开销大每个写操作都会触发额外同步无法捕获DDL变更对源库有侵入性2.2 基于日志解析的方案这是更专业的解决方案通过解析数据库的binlogMySQL或WALPostgreSQL来实现同步。主流工具包括Debezium开源CDC平台支持多种数据库Maxwell专注于MySQL的轻量级方案AWS DMS云服务商的托管方案经过对比测试我最终选择了Debezium原因在于社区活跃文档完善支持多种连接器Kafka、JDBC等提供精确的变更事件顺序可以处理schema变更2.3 基于ETL工具的方案像Talend、Informatica这类ETL工具也提供数据库同步功能但更适合批处理场景实时性较差且资源消耗较大。3. 使用Debezium实现实时同步的详细步骤下面以Debezium Kafka Connect为例展示完整的配置过程。这是我经过多个项目验证的稳定方案。3.1 环境准备需要准备以下组件MySQL 5.7启用binlogPostgreSQL 9.6Kafka 2.8Zookeeper 3.6Debezium 1.7注意MySQL必须配置以下参数server-id 1 log_bin mysql-bin binlog_format ROW binlog_row_image FULL expire_logs_days 13.2 部署架构典型的部署架构如下MySQL - Debezium MySQL Connector - Kafka - Debezium PostgreSQL Connector - PostgreSQL这种架构的优势是解耦生产者和消费者可以添加多个目标库支持消息缓冲和重放3.3 详细配置步骤步骤1启动Kafka环境# 启动Zookeeper bin/zookeeper-server-start.sh config/zookeeper.properties # 启动Kafka bin/kafka-server-start.sh config/server.properties步骤2部署Debezium连接器# 下载Debezium MySQL连接器 curl -O https://repo1.maven.org/maven2/io/debezium/debezium-connector-mysql/1.7.1.Final/debezium-connector-mysql-1.7.1.Final-plugin.tar.gz tar -xzf debezium-connector-mysql-1.7.1.Final-plugin.tar.gz -C /path/to/kafka/connect/plugins/ # 下载JDBC Sink连接器用于写入PostgreSQL curl -O https://d1i4a15mxbxib1.cloudfront.net/api/plugins/confluentinc/kafka-connect-jdbc/versions/10.2.0/confluentinc-kafka-connect-jdbc-10.2.0.zip unzip confluentinc-kafka-connect-jdbc-10.2.0.zip -d /path/to/kafka/connect/plugins/步骤3配置MySQL连接器创建mysql-source.json:{ name: mysql-source-connector, config: { connector.class: io.debezium.connector.mysql.MySqlConnector, database.hostname: localhost, database.port: 3306, database.user: debezium, database.password: dbz, database.server.id: 184054, database.server.name: dbserver1, database.include.list: inventory, database.history.kafka.bootstrap.servers: localhost:9092, database.history.kafka.topic: schema-changes.inventory, include.schema.changes: true, transforms: unwrap, transforms.unwrap.type: io.debezium.transforms.ExtractNewRecordState, transforms.unwrap.drop.tombstones: false, transforms.unwrap.delete.handling.mode: drop } }步骤4配置PostgreSQL Sink连接器创建pg-sink.json:{ name: pg-sink-connector, config: { connector.class: io.confluent.connect.jdbc.JdbcSinkConnector, tasks.max: 1, connection.url: jdbc:postgresql://localhost:5432/postgres?userpostgrespasswordpostgres, topics: dbserver1.inventory.*, auto.create: true, auto.evolve: true, insert.mode: upsert, pk.mode: record_key, pk.fields: id, delete.enabled: true } }步骤5启动连接器curl -X POST -H Content-Type: application/json --data mysql-source.json http://localhost:8083/connectors curl -X POST -H Content-Type: application/json --data pg-sink.json http://localhost:8083/connectors4. 同步过程中的常见问题与解决方案在实际项目中我遇到过以下几个典型问题4.1 数据类型映射问题MySQL和PostgreSQL的数据类型并不完全一致常见问题包括MySQL的DATETIME与PostgreSQL的TIMESTAMPMySQL的TINYINT(1)与PostgreSQL的BOOLEANMySQL的ENUM类型解决方案在Debezium配置中添加转换器在Sink端使用transforms进行类型转换对于复杂类型可以编写自定义转换器4.2 主键冲突处理当目标表已存在数据时可能遇到主键冲突。我推荐以下策略使用upsert模式如配置中的insert.modeupsert对于大批量数据可以先禁用约束同步完成后再启用考虑使用ON CONFLICT DO UPDATE语法PostgreSQL特有4.3 性能优化技巧经过多次调优总结出以下经验批量处理调整batch.size参数默认3000并行处理增加tasks.max数量网络优化确保Kafka与数据库之间的低延迟监控指标关注offset.commit.timeout.ms和poll.interval.ms5. 生产环境部署建议对于关键业务系统建议采用以下高可用方案5.1 集群部署架构MySQL主从集群 - Debezium - Kafka集群 - 多个Sink Worker - PostgreSQL集群5.2 监控与告警必备的监控指标包括延迟时间Source到Sink吞吐量记录数/秒错误率连接器状态可以使用Prometheus Grafana搭建监控看板关键指标debezium_metrics_MilliSecondsBehindSource debezium_metrics_TotalNumberOfEventsSeen connect_task_metrics_batch_size_avg5.3 灾备方案建议实施定期备份offset主题配置Kafka镜像集群准备手动干预脚本用于断点续传6. 进阶应用场景掌握了基础同步后可以尝试以下高级应用6.1 数据转换与清洗在Kafka Streams中实现字段脱敏数据标准化多表关联6.2 多目标同步典型场景同时同步到PostgreSQL和Elasticsearch同步到数据仓库如Redshift同步到缓存系统如Redis6.3 零停机迁移方案完整迁移流程全量快照同步开启增量同步应用双写过渡期流量切换验证后下线旧库我在实际项目中验证过这套流程可以在业务无感知的情况下完成TB级数据库迁移。关键是要做好数据一致性校验推荐使用pt-table-checksum等工具进行校验。
