XNMS项目最近遇到一个绕不开的需求mysql数据库同步。项目本身是典型的JavaWeb后端服务数据库用的MySQL 8.0跑了一段时间后生产环境、报表环境、本地开发环境之间经常需要互相拉数据。刚开始我也以为同步就是mysqldump导出再导入一把梭真正上手才发现不同场景对应的同步方案完全不一样踩坑的姿势更是千奇百怪。这篇文章把我在XNMS项目里做mysql数据库同步的完整过程整理出来包括主从复制、DataX增量同步、常见报错排查适合正在做JavaWeb项目数据库同步的后端开发、DBA和运维同学参考。1. XNMS项目为什么绕不开数据库同步1.1 业务场景拆解从“整库备份”到“单表同步”XNMS项目不是一开始就需要同步的。前期业务量小一台MySQL实例既能支撑在线业务也能应付报表查询。等用户量和数据量上来之后几个矛盾就暴露了生产库的写压力大报表统计又经常跑复杂查询两者混在一起互相拖慢灾备环境需要实时拿到生产数据不能每次都靠人工导备份偶尔本地复现问题还得把远程库里的某张表同步到本地开发环境。这些需求看似都是“同步”但细拆下来完全不是一回事。第一类是整库级增量同步要求实时性高生产库的任何增删改都能几乎同步到灾备库这个场景我用的是MySQL官方主从复制。第二类是单表定制同步比如运营要的订单表、用户表从某个业务实例同步到另一个统计实例字段可能还要做裁剪和映射这个场景用DataX更合适。第三类是离线全量迁移比如新环境初始化直接把源库数据搬过去mysqldump加管道导入就够了。我的体会是拿到需求先别急着写代码先判断三个维度同步粒度是整库还是单表实时性要求是秒级还是分钟级数据是否需要转换。这三个问题回答完了方案基本就定了。1.2 方案选型主从复制、定时任务还是同步工具MySQL主从复制是目前XNMS项目里最核心的同步手段。它的原理不复杂主库把数据变更写进binlog从库通过一个复制账号拉取binlog并重放到本地。这个方案对业务代码零侵入应用层根本感知不到有从库存在特别适合整库级别的准实时同步。应用层定时任务则是简单粗暴路线。写个定时器每隔几分钟查一次源表里更新时间大于上次同步时间的记录插入或更新到目标表。优点是逻辑透明、好控制缺点也很明显滞后、无法捕获物理删除、还要自己在代码里维护同步游标。我在XNMS里只用它处理过配置表这类低频小表。DataX是阿里开源的离线数据同步工具把数据源抽象成Reader和Writer通过JSON文件描述同步作业。它本身不是实时同步工具但配合时间戳游标和定时调度能实现分钟级的多实例增量同步而且支持源表和目标表字段不一致时的映射转换这是主从复制做不到的。用一个简单的表格来对比选型时更直观方案实时性同步粒度复杂度适用场景MySQL主从复制秒级整库/多表中灾备、读写分离、整库增量同步应用层定时任务分钟级单表低低频小表、简单规则同步DataX分钟级/定时单表/多表中异构库、字段映射、多实例增量同步选型没有绝对的对错。XNMS项目最终的组合是核心业务库之间做主从复制数据分析场景用DataX做定向同步少量配置表用定时任务兜底。后面我会把主从复制和DataX两条主线的完整操作都写出来。2. 同步前的环境准备MySQL安装与参数配置2.1 本地开发环境的MySQL安装Linux RPM和Docker两种方式做同步实验得先有一主一从两个MySQL实例。如果手头没有现成的环境最省事的办法是Docker起两个容器其次是Linux下RPM安装。Docker方式最直接一条命令就能起一个MySQL 8.0实例docker run -d --name mysql-master \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDRoot123456 \ -e MYSQL_DATABASExnms \ -v /data/mysql-master:/var/lib/mysql \ mysql:8.0第二台从库把容器名改成mysql-slave宿主机端口改成3307数据目录也换掉避免冲突。需要提醒的是-v数据目录挂载一定要做否则容器一删数据全没了。我踩过这个坑当时图省事没挂载后来重建容器发现库里干干净净只能重新导数据。Linux下用RPM包安装也不复杂。先去官网下载对应系统版本的MySQL仓库包比如mysql80-community-release-el7-7.noarch.rpm然后执行rpm -ivh mysql80-community-release-el7-7.noarch.rpm yum install -y mysql-server systemctl start mysqldMySQL 8.0在CentOS上安装后会在日志里生成一个临时密码用grep temporary password /var/log/mysqld.log查看登录后马上改掉。如果用的是银河麒麟这类国产化系统安装方式类似只是仓库包要选对应架构的版本安装后同样从日志找初始密码。2.2 同步前必改的参数binlog、server-id、字符集主从复制能不能跑起来关键看主库有没有开启binlog以及整个实例的server-id配置是否正确。以MySQL 8.0为例编辑/etc/my.cnf在[mysqld]段下面加这些参数[mysqld] server-id1 log_binmysql-bin binlog_formatROW expire_logs_days15 max_binlog_size128M gtid_modeON enforce_gtid_consistencyONserver-id在主从环境中必须唯一主库设为1从库设为2不能一样。binlog_formatROW是我强烈建议的它记录的是每一行数据的实际变更而不是SQL语句本身同步时不会因为存储函数、触发器这类非确定性操作导致主从数据不一致。字符集统一用utf8mb4这是utf8的超集中文和Emoji都能存。在MySQL 8.0里默认就是utf8mb4如果是5.7迁移过来的老库要检查一下建表语句里的字符集否则同步到新库后中文显示成问号排查起来很磨人。MySQL 8.0其实默认会开启binlog但显式写上更稳妥特别是后续要接DataX或者做时间点恢复时binlog都是基础。改完my.cnf必须重启MySQLsystemctl restart mysqld然后登录执行SHOW VARIABLES LIKE log_bin;确认状态是ON。3. 方案AMySQL主从复制XNMS整库增量同步的基石3.1 主库配置开启binlog与创建复制账号配置主库分三步。第一步是改my.cnf并重启这个上面已经说过。第二步是登录主库创建一个专门给从库用的复制账号CREATE USER sync% IDENTIFIED BY Sync123456; GRANT REPLICATION SLAVE ON *.* TO sync%; FLUSH PRIVILEGES;复制账号只需要REPLICATION SLAVE权限千万别图省事直接给ALL PRIVILEGES。最小权限原则在数据库这里特别重要从库一旦被攻破损失范围也小。第三步是查看主库当前的binlog位点SHOW MASTER STATUS;执行结果会返回File和Position两列比如mysql-bin.000003和154。这两个值是从库启动复制时的起点一定要记下来。这里有句忠告记录位点之前先在主库执行FLUSH TABLES WITH READ LOCK;把写操作暂时锁住然后做一次全量备份这样备份出来的数据和记录的位点才是严格对齐的。实操中我见过不少新人直接SHOW MASTER STATUS然后去mysqldump结果备份期间又有新写入导入从库后数据前后错位。3.2 从库配置CHANGE MASTER与START SLAVE完整操作从库的my.cnf同样要配server-id2可以不开binlog但我建议还是开着方便以后从库再往下级联或者做备份。配置好后重启MySQL。如果主库已经有存量数据先用mysqldump做一次全量备份再导入从库mysqldump -uroot -p --single-transaction --master-data2 --all-databases master_backup.sql--single-transaction选项对InnoDB表做一致性快照不影响业务写入--master-data2会在备份文件的头部用注释记录主库当时的binlog位点。把备份文件传到从库机器上执行导入mysql -uroot -p master_backup.sql导入完成后登录从库执行复制配置。如果使用GTID模式命令是这样的CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_PORT3306, MASTER_USERsync, MASTER_PASSWORDSync123456, MASTER_AUTO_POSITION1;没有启用GTID的话需要手动指定之前记录的日志文件和位点CHANGE MASTER TO MASTER_HOST192.168.1.10, MASTER_PORT3306, MASTER_USERsync, MASTER_PASSWORDSync123456, MASTER_LOG_FILEmysql-bin.000003, MASTER_LOG_POS154;然后启动复制线程。MySQL 8.0.22之前的版本用START SLAVE;之后的版本更推荐START REPLICA;两者都能用。执行完立刻看状态SHOW REPLICA STATUS\G重点看三行Slave_IO_Running和Slave_SQL_Running都必须是YesSeconds_Behind_Master是0表示没有延迟。IO线程负责拉取binlogSQL线程负责执行重放任何一个不是Yes同步就处于中断状态。3.3 主从同步验证与日常监控配置完成后做一次真实的变更验证。在主库建一张测试表插入几行数据更新一行删掉一行然后到从库查询对应表确认增删改都正确同步过来了。这里我习惯额外做一次CHECKSUM TABLE对比CHECKSUM TABLE xnms.t_user;主从两边执行同样的命令返回的校验值一致说明数据完全一致。注意CHECKSUM TABLE在数据量大时会比较消耗资源建议在业务低峰期执行。日常监控最核心的指标就是Slave_IO_Running、Slave_SQL_Running和Seconds_Behind_Master。我在XNMS环境里写了一个简单的Shell脚本每两分钟执行一次SHOW REPLICA STATUS截取这三个字段异常时把状态写入日志并调用企业微信机器人告警。如果公司有Zabbix也可以直接用Zabbix监控MySQL的这几个指标配置起来更快。之前看到有人用centos9 zabbix 7.0 lts mysql 8.0 部署做监控思路是一样的主从延迟和中断都能纳入统一告警平台。4. 方案BDataX实现多实例增量同步与单表定制同步4.1 为什么有主从复制还要DataX主从复制虽然实时性好但有个限制它是库级或者说是实例级的方案没法在同步过程中做字段裁剪和转换。XNMS项目里有个典型场景业务库的订单表有三十多个字段统计库只需要其中五六个核心字段而且字段名还不完全一样。这种情况下用主从复制把整张表拉过去统计库里会塞满没用的字段纯属浪费。另一个场景是异构数据迁移。项目里有一套TDengine时序数据库需要把MySQL里的设备状态表定期同步过去。主从复制完全不支持跨数据库类型同步这种活只能交给DataX这类工具。我在实际项目里还遇到过直接把MySQL表结构自动转成TDengine超级表加子表的需求原理也是先通过元数据读取表结构再生成对应的建表语句数据迁移部分还是靠DataX完成。DataX解决的是三件事多实例之间按需同步、字段映射与裁剪、异构数据库搬迁。它的定位和主从复制不冲突反而是互补关系。4.2 DataX安装与MySQL到MySQL的同步Job配置DataX的安装比较粗暴去GitHub下载源码包或者Release包解压后就能用。解压完目录里有个bin/datax.py脚本执行python bin/datax.py能看到帮助信息就说明装好了。新版DataX依赖Python3老教程里写的Python2已经过时跑的时候会直接报语法错误。DataX的核心是Job配置文件一个JSON文件描述整条同步链路。下面是一个典型的MySQL到MySQL全量同步配置{ job: { content: [ { reader: { name: mysqlreader, parameter: { username: sync_user, password: Sync123456, column: [id, order_no, user_id, amount, create_time], splitPk: id, connection: [ { table: [t_order], jdbcUrl: [jdbc:mysql://192.168.1.10:3306/xnms?useSSLfalseserverTimezoneAsia/Shanghai] } ] } }, writer: { name: mysqlwriter, parameter: { username: sync_user, password: Sync123456, writeMode: insert, connection: [ { table: [t_order_report], jdbcUrl: [jdbc:mysql://192.168.1.11:3306/xnms_report?useSSLfalseserverTimezoneAsia/Shanghai] } ] } } } ], setting: { speed: { channel: 4 } } } }这个配置里重点说两点。splitPk设为id是为了让DataX在读取时按主键拆分并发查询channel是4表示用4个并发通道跑同步大表同步速度会明显提升。如果源表没有主键splitPk可以不填但并发性能会差很多。写端writeMode用insert适合目标表为空或者每次全量覆盖的场景如果目标表已经有数据建议改成replace会按主键去做覆盖更新。配置保存成t_order_sync.json执行python bin/datax.py job/t_order_sync.json跑完后DataX会打印同步总条数、吞吐量、耗时和错误记录数这些统计信息在调优同步性能时非常有用。4.3 时间戳游标增量同步把远程库的指定表同步到本地DataX本身是离线批量同步工具每次跑都是全量。要实现增量需要自己维护一个同步游标。我在XNMS项目里的做法是建一张同步状态表CREATE TABLE sync_state ( table_name VARCHAR(64) PRIMARY KEY, last_sync_time DATETIME, sync_status TINYINT, update_time DATETIME );每次同步前先从状态表读出这张表上一次同步的时间然后构造增量读条件。比如源表有update_time字段就把JSON配置里reader的connection下加一个where条件where: update_time 2025-01-10 10:00:00同步完成后把本次同步的最大update_time回写到状态表作为下一次的起点。整个过程可以交给调度程序来做JavaWeb项目里最常用的调度框架是XXL-Job写一个同步任务方法先查游标再动态生成DataX的Job JSON执行DataX最后回写游标。也可以简单点用crontab每分钟触发一个Shell脚本效果一样。这个方案对“把远程库的这张表同步到本地”的需求特别合适。只同步指定的某张表字段还能随便挑本地表可以按业务需要重新设计。要提醒的是这种基于时间戳的增量同步有先天盲区源表如果物理删除了记录删除动作不会触发update_time更新目标表里就会残留已删除的数据。业务上能接受软删除标记就无所谓如果必须物理删除同步还是得换binlog监听方案或者直接用主从复制。5. XNMS实操中常见问题与排查技巧实录5.1 MySQL SSL连接错误驱动与连接串处理项目热词里有个“mysql ssl连接错误”这个我太熟悉了。JavaWeb项目报错通常是这样的Communications link failure后面跟着一堆SSL相关字样。第一次遇到时我以为是网络问题排查了半天才发现是JDBC连接串的问题。MySQL 8.0默认开启SSL而JDBC驱动在服务端不支持SSL协议协商时就会抛出握手失败的错误。解决办法有两个方向。第一在连接串里显式关闭SSLjdbc:mysql://192.168.1.10:3306/xnms?useSSLfalseserverTimezoneAsia/Shanghai。第二把mysql-connector-java驱动升级到8.0.x版本新版驱动对SSL协商兼容性好很多内部环境没有证书时也能正常连接。另一个相关问题是MySQL 8.0默认的认证插件是caching_sha2_password旧版驱动不认识这个插件会报Unable to load authentication plugin。如果没法升级驱动可以在创建用户时指定兼容旧版的插件CREATE USER app% IDENTIFIED WITH mysql_native_password BY App123456;这样老项目也能顺畅连接。但要注意这属于兼容性方案新项目还是优先用最新驱动安全性更高。5.2 ERROR 2002 (HY000)通过socket连不上本地MySQL这个报错是ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock。从字面上理解客户端尝试通过Unix socket文件连接本地MySQL但连不上。原因有几种MySQL服务没启动、socket文件位置不对、客户端尝试的socket路径和服务器实际路径不一致。排查顺序我建议这样来。先确认服务状态systemctl status mysqld服务没起来就启动它。服务正常的话看socket文件是不是真的在/tmp/mysql.sock很多发行版把socket定义在其他路径比如/var/lib/mysql/mysql.sock。如果路径不一致两种办法一是连接时用-S参数指定socket路径mysql -uroot -p -S /var/lib/mysql/mysql.sock二是用TCP方式绕过socketmysql -uroot -p -h 127.0.0.1 -P 3306这里提一句Docker里跑MySQL经常出现宿主机上执行mysql命令连不上就是因为容器里的socket和宿主机路径不一致。既然用Docker就直接用TCP加端口连接不要依赖socket文件。5.3 主从同步中断Slave_SQL_RunningNo的排查思路主从复制跑一段时间后最怕看到Slave_IO_RunningYes但Slave_SQL_RunningNo。第一次遇到这个情况我心里也慌后来摸清了套路就淡定了。先执行SHOW REPLICA STATUS\G看Last_SQL_Error字段里面会有具体的错误代码和SQL语句。最常见的错误是1062 Duplicate entry也就是主键冲突。原因是主库执行了一条insert从库重放时发现目标表里已经有同一主键的记录。可能是从库被手动修改过数据也可能是之前同步中断后续传的binlog和已存在的数据冲突。处理办法是跳过这一条错误让同步继续STOP REPLICA; SET GLOBAL SQL_SLAVE_SKIP_COUNTER 1; START REPLICA;跳过之后再看状态如果Slave_SQL_Running恢复Yes说明只是偶发冲突。如果后续不断出现新的1062错误说明从库数据已经严重不一致这时候别一个个跳了老老实实重建同步在从库上STOP REPLICA然后重新全量备份导入再RESET REPLICA并重新CHANGE MASTER。还有一种情况是Slave_IO_Running直接变No说明从库连不上主库拉取binlog。排查方向是网络是否通、防火墙是否放行3306端口、主库复制账号密码是否正确、MASTER_LOG_FILE和MASTER_LOG_POS是否还有效。主库做过RESET MASTER或者binlog被清理后之前记录的位点就失效了这时候只能重建复制关系。我把常见主从问题整理成了一张速查表方便值班时快速定位问题现象可能原因快速处理Slave_IO_RunningNo网络不通、账号密码错、binlog位点失效检查网络和账号必要时重建复制Slave_SQL_RunningNo1062主键冲突SQL_SLAVE_SKIP_COUNTER跳过或重建同步延迟越来越大从库性能不足、大事务优化从库配置、拆分大事务、用ROW格式数据对不上binlog_formatSTATEMENT导致非确定性语句改成ROW格式并重建同步5.4 数据一致性校验与同步监控同步跑得再顺也一定要定期做数据校验。我常用的校验手段有三种。最轻量的是对单表执行SELECT COUNT(*)对比行数速度快但查不出内容差异。中等粒度是用CHECKSUM TABLE对表做校验和两边比对值是否一致。最重的办法是逐行对比抽样查询只对核心业务表做。校验频率按表重要程度划分交易核心表每天校验一次普通业务表每周校验一次。校验脚本一旦发现不一致先对比主从两边的binlog位点和最后变更时间定位是同步中断还是有人改了从库数据。如果是同步延迟造成的等延迟追上再校验一次如果确认数据不一致直接重建这张表的同步关系。监控方面秒级实时性要求高的环境可以用Zabbix采集MySQL状态指标加上对Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master的自定义监控项。已经有Kubernetes环境的话用KubeSphere部署MySQL时可以直接挂Prometheus监控主从状态和延迟都能拉出来做告警。在XNMS项目里我就是把这三项指标接到告警平台上的一分钟内Seconds_Behind_Master连续三次超过30秒就告警值班同学能第一时间介入。最后聊几点实操体会做同步方案时最容易犯的错误是一上来就抄别人的配置。主从复制和DataX的选型一定要结合自己的业务场景去定。XNMS项目里我有一次图省事想用主从复制一把梭解决所有同步问题结果遇到报表库和业务库表结构差异比较大的场景主从复制直接满足不了字段映射需求最后又补了DataX作业。后来我形成的判断标准很简单整库优先主从单表定制优先DataX异构优先DataX实时要求高优先主从。还有一点是环境版本差异。网上的教程很多是MySQL 5.7的命令和参数到了8.0会有变动比如START SLAVE在8.0里还可以用但推荐用START REPLICA认证插件也不同。遇到报错先看官方文档别急着用老办法硬跳很可能是版本行为差异。最后送一个小技巧所有同步相关的配置、账号、binlog位点、DataX任务配置一定要在项目文档里留档尤其是CHANGE MASTER那几条命令。主从出问题要重建的时候翻文档直接复制的效率远高于现场敲命令回忆参数。这个习惯帮我省了很多下班后的紧急排查时间。