最近两年问我要“MySQL导出导入”相关方案的人比问索引优化的还多。场景翻来覆去就那么几种搭测试环境要一份生产库的副本、给别的团队导一张表的数据、或者把整个库从旧服务器搬到新实例。第一反应都是在Navicat里点两下可一旦表数据过GB图形界面要么卡死要么导出来的文件在目标库怎么都导不进去。MySQL导出导入这事说简单是真简单一条mysqldump就能跑说坑多也真不少字符集乱码、大表锁死、触发器丢光、GTID权限报错随便一个都能卡你半天。这篇文章把我自己这些年搬数据踩过的坑以及验证过可行的命令按“导什么、怎么导、怎么进、报错怎么查”的顺序整理出来新手照着抄作业老手也能对照看看有没有漏掉的细节。1. 先想清楚要导什么再决定用什么命令1.1 表结构和数据按需拆分理解MySQL导出的内容其实可以拆成好几层表结构CREATE TABLE定义、数据行INSERT语句、视图CREATE VIEW、存储过程与函数、触发器、事件。很多人默认“导出就是备份全库”一条mysqldump不带参数就跑结果在“只要结构不要数据”的时候拿到的是几百MB的INSERT在“只要数据不要结构”的时候又把一堆DROP TABLE执行到线上库差点把业务表删了。所以我每次拿到迁移需求第一件事不是敲命令而是问清楚目标环境是否已存在表结构目标库是否允许DROP和CREATE需要保留哪些对象举个实际例子你要把一张订单表的数据发给合作方对方已经在他们库里建好了相同结构的表。这时候如果你默认全量导出文件里开头就是DROP TABLE IF EXISTS接一句CREATE TABLE最后才是INSERT。对方环境跑文件时可能因为权限限制没有CREATE权限或者库里已经有关联数据直接被DROP语句搞出问题。正确的做法是只用-t参数导数据不要结构和删除语句。另一个常见误区是把“表结构”等同于“建表语句”。真正的“完整结构”还包括索引、外键、触发器、存储过程、视图定义。我见过有人拿-d导出的文件去新环境建库建出来的表字段都对但触发器一个没有结果业务上线后数据同步逻辑完全不触发排查了大半天才找到原因。这个细节后面第二部分会专门说明。1.2 四种高频场景和工具选型根据我接触过的需求可以把导出导入场景归纳成四类每一类的工具选择完全不同场景典型需求推荐方案不推荐的理由全库复制到测试环境拿生产库完整副本给开发联调mysqldump全量导出带结构数据对象用物理文件拷贝对跨版本和跨平台不友好只要表结构结构评审、生成文档、对比环境差异mysqldump -d另加--routines等参数图形工具导出的结构不完整只要数据目标表已建好迁移订单表、日志表到已有库mysqldump -t --complete-insert直接全量导出会导致DDL冲突大表搬迁需要速度快单表超过50GB数据迁移SELECT INTO OUTFILE导出CSV再LOAD DATA导入mysqldump生成SQL文本效率低且占用空间大工具层面的选择其实还要考虑跨环境问题。mysqldump导出的SQL文本是逻辑备份元数据跨版本兼容性最好5.7导出的文件导入8.0基本没问题反过来8.0导出文件导入5.7就可能碰到字符集或SQL模式不兼容。物理层面直接拷贝data目录里的.ibd文件虽然快但要求两边的MySQL版本小版本尽量一致而且目标实例的表空间结构不能有差异操作风险高。第三种是客户端工具自带的“数据传输”功能本质上也是读源库再写目标库适合小表但大表对内存和网络都是考验长事务拖久了还容易打断。所以我自己在实际操作里绝大多数场景优先走mysqldump只有单表特别大的时候才考虑CSV方案。2. mysqldump实操从全量备份到精准导出2.1 全量导出一条命令参数一个都不能少要导出整个库的结构加数据同时把存储过程、触发器、事件都带走我会用这组参数mysqldump -h 127.0.0.1 -P 3306 -u root -p \ --single-transaction \ --default-character-setutf8mb4 \ --set-gtid-purgedOFF \ --routines --triggers --events \ --hex-blob \ your_db your_db_$(date %F).sql逐个解释我为什么非加不可--single-transaction这个参数对InnoDB至关重要它在导出开始时开启一个REPEATABLE READ的一致性快照整个导出过程读到的都是同一个时间点的数据不会锁住业务表的写操作。但如果你的表是MyISAM引擎这个参数就不生效导出时仍然会锁表。所以导出前先查一遍引擎类型还是很有必要的SELECT table_name, engine FROM information_schema.tables WHERE table_schemayour_db;--default-character-setutf8mb4如果库里已经有emoji或者生僻字字符集不指定导出文件里很可能出现“”或者导入时报Incorrect string value。用了utf8mb4基本可以覆盖绝大多数场景。--set-gtid-purgedOFF源库如果开启了GTID默认导出文件会包含SET GLOBAL.GTID_PURGED语句导入到不带GTID的实例时直接报ERROR 1227权限不足。纯粹做数据迁移不是搭建从库的话建议统一关掉。--routines --triggers --events存储过程、函数、触发器、事件调度器默认不会随表导出必须显式声明。这个不是可选项是必选项。--hex-blob二进制字段如BLOB、BINARY在导出时以十六进制输出。不加这个参数碰到特殊字节时容易在传输过程中被转义或截断导入后数据对不上。另外mysqldump默认导出的INSERT语句是合并成多值一条的形式比如INSERT INTO t VALUES (1,a),(2,b)...导入效率高但肉眼检查数据不直观。如果你想拿到每条一行的形式方便diff可以加--skip-extended-insert代价是导入性能和文件体积都会更大一般只在排查数据问题时使用。2.2 只要表结构--no-data 参数和它的隐藏细节只导出表结构时用-d或--no-datamysqldump -d your_db schema.sql这组命令生成的SQL只包含DROP TABLE IF EXISTS、CREATE TABLE、CREATE VIEW之类的DDL语句不含任何数据行。适合的场景我总结过几个项目上线前的结构评审、生成数据库设计文档、在两个环境之间对齐表字段。但这里隐藏着一个大坑-d不会导出存储过程、触发器和事件。很多人以为“结构表结构”结果拿着导出的文件去另一个环境重建库建出来的库表都在却没有任何存储过程。如果你要的是完整结构迁移正确的命令是mysqldump -d --routines --triggers --events your_db full_schema.sql另外-d导出的CREATE TABLE语句里会带AUTO_INCREMENT当前值这在结构评审时有点误导因为你看到的自增值是导出那一刻的不代表表定义本身。如果只想看纯粹的建表语句可以加--no-autoincrement不过这个参数不是所有版本都支持8.0没问题5.7部分小版本可能不认识。低版本环境下我一般是导出后手动把AUTO_INCREMENT那段替换掉。2.3 只要数据--no-create-info 和 --complete-insert目标表已经存在只需要把源表数据搬过去时用-t或--no-create-infomysqldump -t --complete-insert --single-transaction --default-character-setutf8mb4 your_db orders orders_data.sql-t表示只导数据不导表结构文件里只有INSERT语句。这里一定要加--complete-insert它会让INSERT语句显式写出列名比如INSERT INTO orders (id, order_no, created_at) VALUES (...)。不加的话INSERT不带列名导入时完全依赖目标表的物理字段顺序。万一目标表在末尾加了一个新字段或者两边字段顺序不一致数据就会整体错位严重时直接把字符串插进INT列导致报错。这个参数就是防止错位的保险。大数据量导出时我还会追加--quick。mysqldump默认会先把查询结果全部读取到客户端内存再写文件表一旦上千万行内存就可能爆掉。--quick让mysqldump逐行从服务器读取并写入文件内存占用大幅降低。实测一张2000万行的表不加quick时客户端内存吃到3GB多加了之后稳定在200MB以内。如果导出文件很大还可以配合压缩来减少磁盘占用和传输时间mysqldump -t --quick --single-transaction your_db big_table | gzip big_table.sql.gz导入时先解压再执行gunzip -c big_table.sql.gz | mysql -u root -p target_dbgunzip -c不会在磁盘上生成解压后的临时文件直接通过管道把解压结果送给mysql命令这点对磁盘空间紧张的环境特别友好。2.4 指定表导出、按条件导出和排除表的技巧只需要导出部分表时直接在库名后面罗列表名mysqldump your_db orders order_items orders_tables.sql只导出符合条件的数据行用--wheremysqldump your_db orders \ --wherestatuspaid AND create_time 2024-01-01 \ --no-create-info --complete-insert paid_orders.sql这在我们给运营导“已支付且在某时间之后”的订单数据时非常有用。需要注意--where的SQL条件是在源库上执行的条件里的列名必须是源表真实存在的字段如果条件涉及JOIN或子查询还会受到mysqldump生成SQL方式的限制尽量保持条件简单。排除某些表是另一个高频需求可惜mysqldump没有--exclude-table这样的直接参数。我的做法是先查INFORMATION_SCHEMA得到表名列表后手工拼参数用bash循环处理tables$(mysql -N -B -u root -p -e \ SELECT GROUP_CONCAT(table_name SEPARATOR ) \ FROM information_schema.tables \ WHERE table_schemayour_db AND table_name NOT IN (log,audit_log);) mysqldump your_db $tables db_no_log.sql这里有个细节GROUP_CONCAT默认有长度限制group_concat_max_len表特别多时可以临时调大会话变量。另外表名如果包含数字或特殊字符拼接时需要加反引号否则mysqldump识别不了。3. 导入环节从备份文件到目标库的完整流程3.1 命令行导入和source的正确用法导出只是第一步导入环节才是真正检验备份文件是否可靠的地方。命令行导入有两种常见方式我平时主要用的是shell重定向mysql -h 127.0.0.1 -P 3306 -u root -p -e \ CREATE DATABASE IF NOT EXISTS new_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci; mysql -h 127.0.0.1 -P 3306 -u root -p new_db backup.sql第一条命令先确保目标库存在第二条命令把SQL文件内容喂给mysql客户端执行。这里特别要强调重定向导入时备份文件里的USE语句未必会执行如果没有指定数据库名而文件内部又没有USE new_db导入会直接报ERROR 1046 (3D000) No database selected。所以不管文件里有没有USE命令行都要显式带上库名养成习惯比依赖运气可靠。第二种方式是进入mysql客户端后执行source命令mysql use new_db; mysql source /tmp/backup.sqlsource是mysql客户端的内建命令它会逐条执行文件里的SQL并打印结果。好处是可以看到每条语句的执行状态中途报错时能看到具体卡在哪一行。坏处是文件特别大时终端打印刷屏会影响性能而且如果某条SQL报错后续语句默认继续执行容易造成“部分导入成功但不知道哪里失败”的情况。我个人习惯是文件超过500MB就走shell重定向小文件或需要逐步调试时才用source。3.2 导入前调整会话参数速度能差好几倍大文件导入时有几个会话级设置能大幅提升速度我会在导入前先执行一遍SET SESSION FOREIGN_KEY_CHECKS0; SET SESSION UNIQUE_CHECKS0; SET SESSION sql_log_binOFF; SET GLOBAL max_allowed_packet1073741824;FOREIGN_KEY_CHECKS0关闭外键检查。备份文件里的INSERT顺序按导出时的表格顺序排列可能子表数据先于父表插入开着外键检查会直接报错。关闭后导入完成再恢复。UNIQUE_CHECKS0关闭唯一索引检查InnoDB在插入时可以做批量处理速度提升明显。前提是你确信数据没有重复否则导入完成后要自己再查一遍。sql_log_binOFF当前会话不记录binlog。导入过程本质是重放数据如果目标实例不参与主从复制关掉binlog可以显著减少IO开销。但注意如果这个SQL是通过root在非交互模式执行的可能没有权限关闭binlog需要目标库设置log_bin并给用户SESSION_VARIABLES_ADMIN权限。max_allowed_packet这个参数控制客户端能接收的最大SQL包。mysqldump默认生成的多行INSERT可能单条超过默认4MB导入时经常报“Got a packet bigger than max_allowed_packet”。导入前先调大避免中途报错。导入完成后把外键检查和唯一检查恢复原值再跑一遍统计对比验证数据量一致。这个验证习惯很重要关掉外键检查导入时如果中间某条INSERT失败最终数据可能少了一部分但SQL文件整体执行完仍会显示成功。3.3 图形化工具导入的实用要点Navicat这类图形工具对小文件导入很方便我偶尔会用但超过一定体积我就不碰了。图形化导入的核心问题是工具会把整个SQL文件读入内存文件一大内存就炸而且报错信息被封装在进度条里很难定位具体位置。如果一定要用三个要点值得注意一是导入前核对目标数据库。工具默认把SQL文件执行在“当前选中的连接”上而不是文件里的USE语句指定的库选错库的后果很严重。我亲眼见过同事本想在测试库执行备份结果当前连接选的是生产库文件跑完才发现生产库多了一堆表。二是字符集选择。图形工具一般在导入向导里有字符集选项跟源库导出时指定的字符集保持一致常见的是UTF-8MySQL 8.0环境建议直接选UTF-8mb4。三是大文件先切割。如果图形工具实在跑不动1GB以上的文件可以用split命令把SQL按大小切成多个小文件比如split -l 100000 backup.sql part_保证每个文件在工具能承载的范围内再逐个导入。这样做不够优雅但确实是应急方案里最稳的。3.4 高频导入错误速查表导入时报错是最让人头疼的我把这些年遇到的高频错误整理成一张速查表报错信息原因解决方案ERROR 1046 (3D000): No database selected导入时未指定目标库文件也没有USE语句命令行显式带库名mysql db_name file.sqlERROR 1062 (23000): Duplicate entry目标表已有相同主键或唯一键数据先清空目标表或改用INSERT IGNORE方式导入ERROR 1366 (HY000): Incorrect string value字符集不一致比如目标表非utf8mb4统一库表字符集重新导出时带--default-character-setERROR 1418创建存储过程/函数缺少权限或DELIMITER处理不当确保用户有CREATE ROUTINE权限文件内部用DELIMITERERROR 1227 (42000): Access denied备份文件带GTID_PURGED语句但用户权限不足导出时加--set-gtid-purgedOFF或授权SUPERERROR 2026 (HY000): SSL connection error客户端启用了SSL但证书不匹配或服务端不支持连接串加--ssl-modeDISABLED或排查证书配置ERROR 1153 (HY000): Got a packet bigger than max_allowed_packet单条INSERT语句超过传输上限导入前调大max_allowed_packet以ERROR 1062为例它通常出现在目标表已经有部分数据、而我们导入的是全量备份时。处理方式要按业务判断如果目标表数据可以清空就先TRUNCATE再导入速度最快如果目标表存在需要保留的数据建议把导出条件改成只导增量部分或者导入时给INSERT语句做冗余处理。MySQL本身支持INSERT IGNORE但mysqldump默认生成的INSERT语句没有这个关键词需要导出后替换或者改用其他ETL工具这里就不展开讲了。4. 经典问题排查乱码、锁表、GTID这些坑怎么绕4.1 乱码问题从库到客户端的完整排查链路乱码的根因是字符集在“源库存储 - 导出文件编码 - 导入客户端编码”这条链路上某个环节断掉了。排查时我会按顺序检查三处第一处看源库。执行SHOW VARIABLES LIKE character_set%;确认数据库字符集和表字符集。如果表本身就是latin1存储的导出时强行指定utf8mb4也救不回来因为数据在源物理存储层面已经是latin1编码导出程序按latin1读取再按utf8mb4输出只会加剧乱码。第二处看导出命令。mysqldump导出时必须加--default-character-setutf8mb4不加这个参数工具按系统默认字符集输出文件编码可能是latin1也可以是UTF-8但缺了4字节字符支持这直接决定了SQL文件里的中文、emoji能不能完整保留。第三处看导入端。命令行导入时mysql客户端会按系统默认字符集解释文件内容。稳妥做法是在导入前先执行SET NAMES utf8mb4;再执行source或重定向导入。排查时还可以用file命令直接看文件编码file backup.sql输出里会标明文件的字符集比如UTF-8 Unicode text。如果导出文件是ISO-8859那基本可以确定导出命令没加字符集参数重新导一次比手工转换靠谱。4.2 锁表问题--single-transaction到底怎么工作很多人问导出时会不会把业务表锁住答案取决于引擎和参数。--single-transaction在InnoDB下通过开启REPEATABLE READ隔离级别拿到一个一致性快照导出期间不会对表加锁业务读写照常进行。原理是MVCC多版本并发控制InnoDB用undo log保存历史版本导出进程读的是快照版本不会阻塞其他事务。但有几个例外必须知道第一--single-transaction只对InnoDB有效MyISAM表不支持事务导出时照样会加全局锁。所以导出前检查一下引擎有MyISAM表的情况下最好在低峰期操作。第二--single-transaction与DDL不兼容。如果你在导出过程中对表执行ALTER TABLE或DROP TABLE导出进程可能拿到的是不一致的元数据或者直接报错“Table definition has changed, please retry transaction”。所以导出大库前最好和开发团队打声招呼避免有人正好在改表。第三mysqldump导出过程中会在开头执行FLUSH TABLES WITH READ LOCK加一个短暂的全局只读锁拿到一致性位点后立刻释放。这个锁定时间非常短但如果你在主从环境里跑还是要注意避免和已有备份任务撞车。4.3 GTID环境下的导出导入权限报错怎么处理MySQL开启GTID后mysqldump导出的文件默认会带一段GTID_PURGED设置语句。导入到普通实例时因为目标库没有开启GTID或者当前用户没有SUPER权限就会报ERROR 1227。这个问题我在帮业务迁移时几乎每次都会遇到处理方式很简单导出时加--set-gtid-purgedOFF告诉mysqldump不要把GTID信息写进文件。但如果你是搭从库情况正好相反需要保留GTID信息让从库知道从哪个位点追赶主库。这时候应该用--set-gtid-purgedON配合--master-data2mysqldump --single-transaction --set-gtid-purgedON --master-data2 your_db replica_init.sql--master-data2会在导出文件注释里记录主库当前的binlog文件名和位置这样搭建从库时CHANGE MASTER TO的位点可以直接从注释里取不用再单独查主库。注意--master-data还会额外执行FLUSH TABLES WITH READ LOCK所以生产环境使用时同样建议选在低峰期。还有一个小细节源库开了GTID目标库也开了GTID但目标库已经执行过部分事务GTID范围与源库重叠时导入会因为GTID已存在而跳过部分事务造成数据缺失。这种情况下我会先确认目标库是全新的空实例再进行全量导入避免GTID交集问题。5. 导出的数据不一定只回MySQL异构迁移场景5.1 从MySQL导到TDengine结构要重设计热词里有个“mysql表结构自动转tdengine超级表子表”这个需求我实际在物联网项目里做过。MySQL导出的建表SQL在TDengine里基本不能用因为TDengine是时序数据库模型是超级表加子表。比如一张采集表在MySQL里可能长这样device_id、ts、temperature、humidity。导出MySQL表结构后如果要迁移到TDengine需要把device_id设计成TAG把ts作为主时间戳temperature和humidity作为FIELD列。这个过程完全不是MySQL的CREATE TABLE语句能直接转换的需要写脚本解析MySQL的字段清单和类型再生成TDengine的CREATE STABLE语句。导数据时也建议用CSV方案而不是SQL文件。mysqldump导出的INSERT语句TDengine的写入接口不认识反而用SELECT ... INTO OUTFILE导出CSV再走TDengine的taosimport或者数据接入工具写入更顺畅。类似的思路也适用于往ClickHouse、Doris这类分析型数据库迁SQL文本格式的备份在异构场景下通用性很差CSV或JSON更友好。5.2 大数据同步链路里全量导出怎么和增量配合如果你在做Flink CDC同步MySQL到ClickHouse这类任务第一步通常不是直接开流而是先做一次全量快照。原因很简单CDC只能捕获启动之后的增量变更启动之前MySQL里已存在的数据需要靠全量导出初始化到目标端。标准的初始化流程是先用mysqldump导出全量数据导入目标库然后从binlog位点开始起增量任务保证“全量先到、增量无缝衔接”。这里就要求全量导出时记录binlog位点也就是前面说的--master-data2参数。还有一点经验CDC任务启动前做的全量导出最好也加上--single-transaction确保导出的是一个一致性的快照。如果导出期间业务还在写不带这个参数的表会读到中间状态全量和增量衔接时会出现重复或漏数据。5.3 备份文件的可用性验证否则等于白备份导出导入折腾完千万别忘了验证。我的习惯是检查三个点文件完整性、行数一致性、抽样数据正确性。文件完整性看尾部。mysqldump正常结束时文件最后一行是-- Dump completed如果文件被截断这一行会缺失。可以用tail -n 5 backup.sql行数一致性通过对比源库和目标库的COUNT来验证。导入完成后抽几张关键表执行SELECT COUNT(*) FROM source_db.orders; SELECT COUNT(*) FROM target_db.orders;两条结果一致基本可以确定数据没有丢。抽样数据正确性要结合业务。比如订单表导完以后抽查一笔金额较大的订单比对订单号、金额、状态几个关键字段是否一致。如果源库有逻辑删除标记还要确认导入后标记还在。最后再分享一个这些年收获的习惯核心库的表结构我会定期导出来提交到Git仓库和代码一起做版本管理。某次线上变更给大表加了冗余列后来程序批量插入总是报错翻Git历史里的建表SQL一眼就看出是谁在哪天改坏了。这个习惯救过我很多次建议每一个长期维护MySQL业务的团队都养成。还有个小技巧不管导出还是导入正式操作前一定要先在临时实例上跑一遍完整流程。我在线上执行大表迁移前都会先在本地用同样版本的MySQL建一个库把备份文件导入一遍确认无报错后再对正式环境操作。这个习惯虽然多花半小时但能避免在生产上反复试错。
最近两年问我要“MySQL导出导入”相关方案的人比问索引优化的还多。场景翻来覆去就那么几种搭测试环境要一份生产库的副本、给别的团队导一张表的数据、或者把整个库从旧服务器搬到新实例。第一反应都是在Navicat里点两下可一旦表数据过GB图形界面要么卡死要么导出来的文件在目标库怎么都导不进去。MySQL导出导入这事说简单是真简单一条mysqldump就能跑说坑多也真不少字符集乱码、大表锁死、触发器丢光、GTID权限报错随便一个都能卡你半天。这篇文章把我自己这些年搬数据踩过的坑以及验证过可行的命令按“导什么、怎么导、怎么进、报错怎么查”的顺序整理出来新手照着抄作业老手也能对照看看有没有漏掉的细节。1. 先想清楚要导什么再决定用什么命令1.1 表结构和数据按需拆分理解MySQL导出的内容其实可以拆成好几层表结构CREATE TABLE定义、数据行INSERT语句、视图CREATE VIEW、存储过程与函数、触发器、事件。很多人默认“导出就是备份全库”一条mysqldump不带参数就跑结果在“只要结构不要数据”的时候拿到的是几百MB的INSERT在“只要数据不要结构”的时候又把一堆DROP TABLE执行到线上库差点把业务表删了。所以我每次拿到迁移需求第一件事不是敲命令而是问清楚目标环境是否已存在表结构目标库是否允许DROP和CREATE需要保留哪些对象举个实际例子你要把一张订单表的数据发给合作方对方已经在他们库里建好了相同结构的表。这时候如果你默认全量导出文件里开头就是DROP TABLE IF EXISTS接一句CREATE TABLE最后才是INSERT。对方环境跑文件时可能因为权限限制没有CREATE权限或者库里已经有关联数据直接被DROP语句搞出问题。正确的做法是只用-t参数导数据不要结构和删除语句。另一个常见误区是把“表结构”等同于“建表语句”。真正的“完整结构”还包括索引、外键、触发器、存储过程、视图定义。我见过有人拿-d导出的文件去新环境建库建出来的表字段都对但触发器一个没有结果业务上线后数据同步逻辑完全不触发排查了大半天才找到原因。这个细节后面第二部分会专门说明。1.2 四种高频场景和工具选型根据我接触过的需求可以把导出导入场景归纳成四类每一类的工具选择完全不同场景典型需求推荐方案不推荐的理由全库复制到测试环境拿生产库完整副本给开发联调mysqldump全量导出带结构数据对象用物理文件拷贝对跨版本和跨平台不友好只要表结构结构评审、生成文档、对比环境差异mysqldump -d另加--routines等参数图形工具导出的结构不完整只要数据目标表已建好迁移订单表、日志表到已有库mysqldump -t --complete-insert直接全量导出会导致DDL冲突大表搬迁需要速度快单表超过50GB数据迁移SELECT INTO OUTFILE导出CSV再LOAD DATA导入mysqldump生成SQL文本效率低且占用空间大工具层面的选择其实还要考虑跨环境问题。mysqldump导出的SQL文本是逻辑备份元数据跨版本兼容性最好5.7导出的文件导入8.0基本没问题反过来8.0导出文件导入5.7就可能碰到字符集或SQL模式不兼容。物理层面直接拷贝data目录里的.ibd文件虽然快但要求两边的MySQL版本小版本尽量一致而且目标实例的表空间结构不能有差异操作风险高。第三种是客户端工具自带的“数据传输”功能本质上也是读源库再写目标库适合小表但大表对内存和网络都是考验长事务拖久了还容易打断。所以我自己在实际操作里绝大多数场景优先走mysqldump只有单表特别大的时候才考虑CSV方案。2. mysqldump实操从全量备份到精准导出2.1 全量导出一条命令参数一个都不能少要导出整个库的结构加数据同时把存储过程、触发器、事件都带走我会用这组参数mysqldump -h 127.0.0.1 -P 3306 -u root -p \ --single-transaction \ --default-character-setutf8mb4 \ --set-gtid-purgedOFF \ --routines --triggers --events \ --hex-blob \ your_db your_db_$(date %F).sql逐个解释我为什么非加不可--single-transaction这个参数对InnoDB至关重要它在导出开始时开启一个REPEATABLE READ的一致性快照整个导出过程读到的都是同一个时间点的数据不会锁住业务表的写操作。但如果你的表是MyISAM引擎这个参数就不生效导出时仍然会锁表。所以导出前先查一遍引擎类型还是很有必要的SELECT table_name, engine FROM information_schema.tables WHERE table_schemayour_db;--default-character-setutf8mb4如果库里已经有emoji或者生僻字字符集不指定导出文件里很可能出现“”或者导入时报Incorrect string value。用了utf8mb4基本可以覆盖绝大多数场景。--set-gtid-purgedOFF源库如果开启了GTID默认导出文件会包含SET GLOBAL.GTID_PURGED语句导入到不带GTID的实例时直接报ERROR 1227权限不足。纯粹做数据迁移不是搭建从库的话建议统一关掉。--routines --triggers --events存储过程、函数、触发器、事件调度器默认不会随表导出必须显式声明。这个不是可选项是必选项。--hex-blob二进制字段如BLOB、BINARY在导出时以十六进制输出。不加这个参数碰到特殊字节时容易在传输过程中被转义或截断导入后数据对不上。另外mysqldump默认导出的INSERT语句是合并成多值一条的形式比如INSERT INTO t VALUES (1,a),(2,b)...导入效率高但肉眼检查数据不直观。如果你想拿到每条一行的形式方便diff可以加--skip-extended-insert代价是导入性能和文件体积都会更大一般只在排查数据问题时使用。2.2 只要表结构--no-data 参数和它的隐藏细节只导出表结构时用-d或--no-datamysqldump -d your_db schema.sql这组命令生成的SQL只包含DROP TABLE IF EXISTS、CREATE TABLE、CREATE VIEW之类的DDL语句不含任何数据行。适合的场景我总结过几个项目上线前的结构评审、生成数据库设计文档、在两个环境之间对齐表字段。但这里隐藏着一个大坑-d不会导出存储过程、触发器和事件。很多人以为“结构表结构”结果拿着导出的文件去另一个环境重建库建出来的库表都在却没有任何存储过程。如果你要的是完整结构迁移正确的命令是mysqldump -d --routines --triggers --events your_db full_schema.sql另外-d导出的CREATE TABLE语句里会带AUTO_INCREMENT当前值这在结构评审时有点误导因为你看到的自增值是导出那一刻的不代表表定义本身。如果只想看纯粹的建表语句可以加--no-autoincrement不过这个参数不是所有版本都支持8.0没问题5.7部分小版本可能不认识。低版本环境下我一般是导出后手动把AUTO_INCREMENT那段替换掉。2.3 只要数据--no-create-info 和 --complete-insert目标表已经存在只需要把源表数据搬过去时用-t或--no-create-infomysqldump -t --complete-insert --single-transaction --default-character-setutf8mb4 your_db orders orders_data.sql-t表示只导数据不导表结构文件里只有INSERT语句。这里一定要加--complete-insert它会让INSERT语句显式写出列名比如INSERT INTO orders (id, order_no, created_at) VALUES (...)。不加的话INSERT不带列名导入时完全依赖目标表的物理字段顺序。万一目标表在末尾加了一个新字段或者两边字段顺序不一致数据就会整体错位严重时直接把字符串插进INT列导致报错。这个参数就是防止错位的保险。大数据量导出时我还会追加--quick。mysqldump默认会先把查询结果全部读取到客户端内存再写文件表一旦上千万行内存就可能爆掉。--quick让mysqldump逐行从服务器读取并写入文件内存占用大幅降低。实测一张2000万行的表不加quick时客户端内存吃到3GB多加了之后稳定在200MB以内。如果导出文件很大还可以配合压缩来减少磁盘占用和传输时间mysqldump -t --quick --single-transaction your_db big_table | gzip big_table.sql.gz导入时先解压再执行gunzip -c big_table.sql.gz | mysql -u root -p target_dbgunzip -c不会在磁盘上生成解压后的临时文件直接通过管道把解压结果送给mysql命令这点对磁盘空间紧张的环境特别友好。2.4 指定表导出、按条件导出和排除表的技巧只需要导出部分表时直接在库名后面罗列表名mysqldump your_db orders order_items orders_tables.sql只导出符合条件的数据行用--wheremysqldump your_db orders \ --wherestatuspaid AND create_time 2024-01-01 \ --no-create-info --complete-insert paid_orders.sql这在我们给运营导“已支付且在某时间之后”的订单数据时非常有用。需要注意--where的SQL条件是在源库上执行的条件里的列名必须是源表真实存在的字段如果条件涉及JOIN或子查询还会受到mysqldump生成SQL方式的限制尽量保持条件简单。排除某些表是另一个高频需求可惜mysqldump没有--exclude-table这样的直接参数。我的做法是先查INFORMATION_SCHEMA得到表名列表后手工拼参数用bash循环处理tables$(mysql -N -B -u root -p -e \ SELECT GROUP_CONCAT(table_name SEPARATOR ) \ FROM information_schema.tables \ WHERE table_schemayour_db AND table_name NOT IN (log,audit_log);) mysqldump your_db $tables db_no_log.sql这里有个细节GROUP_CONCAT默认有长度限制group_concat_max_len表特别多时可以临时调大会话变量。另外表名如果包含数字或特殊字符拼接时需要加反引号否则mysqldump识别不了。3. 导入环节从备份文件到目标库的完整流程3.1 命令行导入和source的正确用法导出只是第一步导入环节才是真正检验备份文件是否可靠的地方。命令行导入有两种常见方式我平时主要用的是shell重定向mysql -h 127.0.0.1 -P 3306 -u root -p -e \ CREATE DATABASE IF NOT EXISTS new_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci; mysql -h 127.0.0.1 -P 3306 -u root -p new_db backup.sql第一条命令先确保目标库存在第二条命令把SQL文件内容喂给mysql客户端执行。这里特别要强调重定向导入时备份文件里的USE语句未必会执行如果没有指定数据库名而文件内部又没有USE new_db导入会直接报ERROR 1046 (3D000) No database selected。所以不管文件里有没有USE命令行都要显式带上库名养成习惯比依赖运气可靠。第二种方式是进入mysql客户端后执行source命令mysql use new_db; mysql source /tmp/backup.sqlsource是mysql客户端的内建命令它会逐条执行文件里的SQL并打印结果。好处是可以看到每条语句的执行状态中途报错时能看到具体卡在哪一行。坏处是文件特别大时终端打印刷屏会影响性能而且如果某条SQL报错后续语句默认继续执行容易造成“部分导入成功但不知道哪里失败”的情况。我个人习惯是文件超过500MB就走shell重定向小文件或需要逐步调试时才用source。3.2 导入前调整会话参数速度能差好几倍大文件导入时有几个会话级设置能大幅提升速度我会在导入前先执行一遍SET SESSION FOREIGN_KEY_CHECKS0; SET SESSION UNIQUE_CHECKS0; SET SESSION sql_log_binOFF; SET GLOBAL max_allowed_packet1073741824;FOREIGN_KEY_CHECKS0关闭外键检查。备份文件里的INSERT顺序按导出时的表格顺序排列可能子表数据先于父表插入开着外键检查会直接报错。关闭后导入完成再恢复。UNIQUE_CHECKS0关闭唯一索引检查InnoDB在插入时可以做批量处理速度提升明显。前提是你确信数据没有重复否则导入完成后要自己再查一遍。sql_log_binOFF当前会话不记录binlog。导入过程本质是重放数据如果目标实例不参与主从复制关掉binlog可以显著减少IO开销。但注意如果这个SQL是通过root在非交互模式执行的可能没有权限关闭binlog需要目标库设置log_bin并给用户SESSION_VARIABLES_ADMIN权限。max_allowed_packet这个参数控制客户端能接收的最大SQL包。mysqldump默认生成的多行INSERT可能单条超过默认4MB导入时经常报“Got a packet bigger than max_allowed_packet”。导入前先调大避免中途报错。导入完成后把外键检查和唯一检查恢复原值再跑一遍统计对比验证数据量一致。这个验证习惯很重要关掉外键检查导入时如果中间某条INSERT失败最终数据可能少了一部分但SQL文件整体执行完仍会显示成功。3.3 图形化工具导入的实用要点Navicat这类图形工具对小文件导入很方便我偶尔会用但超过一定体积我就不碰了。图形化导入的核心问题是工具会把整个SQL文件读入内存文件一大内存就炸而且报错信息被封装在进度条里很难定位具体位置。如果一定要用三个要点值得注意一是导入前核对目标数据库。工具默认把SQL文件执行在“当前选中的连接”上而不是文件里的USE语句指定的库选错库的后果很严重。我亲眼见过同事本想在测试库执行备份结果当前连接选的是生产库文件跑完才发现生产库多了一堆表。二是字符集选择。图形工具一般在导入向导里有字符集选项跟源库导出时指定的字符集保持一致常见的是UTF-8MySQL 8.0环境建议直接选UTF-8mb4。三是大文件先切割。如果图形工具实在跑不动1GB以上的文件可以用split命令把SQL按大小切成多个小文件比如split -l 100000 backup.sql part_保证每个文件在工具能承载的范围内再逐个导入。这样做不够优雅但确实是应急方案里最稳的。3.4 高频导入错误速查表导入时报错是最让人头疼的我把这些年遇到的高频错误整理成一张速查表报错信息原因解决方案ERROR 1046 (3D000): No database selected导入时未指定目标库文件也没有USE语句命令行显式带库名mysql db_name file.sqlERROR 1062 (23000): Duplicate entry目标表已有相同主键或唯一键数据先清空目标表或改用INSERT IGNORE方式导入ERROR 1366 (HY000): Incorrect string value字符集不一致比如目标表非utf8mb4统一库表字符集重新导出时带--default-character-setERROR 1418创建存储过程/函数缺少权限或DELIMITER处理不当确保用户有CREATE ROUTINE权限文件内部用DELIMITERERROR 1227 (42000): Access denied备份文件带GTID_PURGED语句但用户权限不足导出时加--set-gtid-purgedOFF或授权SUPERERROR 2026 (HY000): SSL connection error客户端启用了SSL但证书不匹配或服务端不支持连接串加--ssl-modeDISABLED或排查证书配置ERROR 1153 (HY000): Got a packet bigger than max_allowed_packet单条INSERT语句超过传输上限导入前调大max_allowed_packet以ERROR 1062为例它通常出现在目标表已经有部分数据、而我们导入的是全量备份时。处理方式要按业务判断如果目标表数据可以清空就先TRUNCATE再导入速度最快如果目标表存在需要保留的数据建议把导出条件改成只导增量部分或者导入时给INSERT语句做冗余处理。MySQL本身支持INSERT IGNORE但mysqldump默认生成的INSERT语句没有这个关键词需要导出后替换或者改用其他ETL工具这里就不展开讲了。4. 经典问题排查乱码、锁表、GTID这些坑怎么绕4.1 乱码问题从库到客户端的完整排查链路乱码的根因是字符集在“源库存储 - 导出文件编码 - 导入客户端编码”这条链路上某个环节断掉了。排查时我会按顺序检查三处第一处看源库。执行SHOW VARIABLES LIKE character_set%;确认数据库字符集和表字符集。如果表本身就是latin1存储的导出时强行指定utf8mb4也救不回来因为数据在源物理存储层面已经是latin1编码导出程序按latin1读取再按utf8mb4输出只会加剧乱码。第二处看导出命令。mysqldump导出时必须加--default-character-setutf8mb4不加这个参数工具按系统默认字符集输出文件编码可能是latin1也可以是UTF-8但缺了4字节字符支持这直接决定了SQL文件里的中文、emoji能不能完整保留。第三处看导入端。命令行导入时mysql客户端会按系统默认字符集解释文件内容。稳妥做法是在导入前先执行SET NAMES utf8mb4;再执行source或重定向导入。排查时还可以用file命令直接看文件编码file backup.sql输出里会标明文件的字符集比如UTF-8 Unicode text。如果导出文件是ISO-8859那基本可以确定导出命令没加字符集参数重新导一次比手工转换靠谱。4.2 锁表问题--single-transaction到底怎么工作很多人问导出时会不会把业务表锁住答案取决于引擎和参数。--single-transaction在InnoDB下通过开启REPEATABLE READ隔离级别拿到一个一致性快照导出期间不会对表加锁业务读写照常进行。原理是MVCC多版本并发控制InnoDB用undo log保存历史版本导出进程读的是快照版本不会阻塞其他事务。但有几个例外必须知道第一--single-transaction只对InnoDB有效MyISAM表不支持事务导出时照样会加全局锁。所以导出前检查一下引擎有MyISAM表的情况下最好在低峰期操作。第二--single-transaction与DDL不兼容。如果你在导出过程中对表执行ALTER TABLE或DROP TABLE导出进程可能拿到的是不一致的元数据或者直接报错“Table definition has changed, please retry transaction”。所以导出大库前最好和开发团队打声招呼避免有人正好在改表。第三mysqldump导出过程中会在开头执行FLUSH TABLES WITH READ LOCK加一个短暂的全局只读锁拿到一致性位点后立刻释放。这个锁定时间非常短但如果你在主从环境里跑还是要注意避免和已有备份任务撞车。4.3 GTID环境下的导出导入权限报错怎么处理MySQL开启GTID后mysqldump导出的文件默认会带一段GTID_PURGED设置语句。导入到普通实例时因为目标库没有开启GTID或者当前用户没有SUPER权限就会报ERROR 1227。这个问题我在帮业务迁移时几乎每次都会遇到处理方式很简单导出时加--set-gtid-purgedOFF告诉mysqldump不要把GTID信息写进文件。但如果你是搭从库情况正好相反需要保留GTID信息让从库知道从哪个位点追赶主库。这时候应该用--set-gtid-purgedON配合--master-data2mysqldump --single-transaction --set-gtid-purgedON --master-data2 your_db replica_init.sql--master-data2会在导出文件注释里记录主库当前的binlog文件名和位置这样搭建从库时CHANGE MASTER TO的位点可以直接从注释里取不用再单独查主库。注意--master-data还会额外执行FLUSH TABLES WITH READ LOCK所以生产环境使用时同样建议选在低峰期。还有一个小细节源库开了GTID目标库也开了GTID但目标库已经执行过部分事务GTID范围与源库重叠时导入会因为GTID已存在而跳过部分事务造成数据缺失。这种情况下我会先确认目标库是全新的空实例再进行全量导入避免GTID交集问题。5. 导出的数据不一定只回MySQL异构迁移场景5.1 从MySQL导到TDengine结构要重设计热词里有个“mysql表结构自动转tdengine超级表子表”这个需求我实际在物联网项目里做过。MySQL导出的建表SQL在TDengine里基本不能用因为TDengine是时序数据库模型是超级表加子表。比如一张采集表在MySQL里可能长这样device_id、ts、temperature、humidity。导出MySQL表结构后如果要迁移到TDengine需要把device_id设计成TAG把ts作为主时间戳temperature和humidity作为FIELD列。这个过程完全不是MySQL的CREATE TABLE语句能直接转换的需要写脚本解析MySQL的字段清单和类型再生成TDengine的CREATE STABLE语句。导数据时也建议用CSV方案而不是SQL文件。mysqldump导出的INSERT语句TDengine的写入接口不认识反而用SELECT ... INTO OUTFILE导出CSV再走TDengine的taosimport或者数据接入工具写入更顺畅。类似的思路也适用于往ClickHouse、Doris这类分析型数据库迁SQL文本格式的备份在异构场景下通用性很差CSV或JSON更友好。5.2 大数据同步链路里全量导出怎么和增量配合如果你在做Flink CDC同步MySQL到ClickHouse这类任务第一步通常不是直接开流而是先做一次全量快照。原因很简单CDC只能捕获启动之后的增量变更启动之前MySQL里已存在的数据需要靠全量导出初始化到目标端。标准的初始化流程是先用mysqldump导出全量数据导入目标库然后从binlog位点开始起增量任务保证“全量先到、增量无缝衔接”。这里就要求全量导出时记录binlog位点也就是前面说的--master-data2参数。还有一点经验CDC任务启动前做的全量导出最好也加上--single-transaction确保导出的是一个一致性的快照。如果导出期间业务还在写不带这个参数的表会读到中间状态全量和增量衔接时会出现重复或漏数据。5.3 备份文件的可用性验证否则等于白备份导出导入折腾完千万别忘了验证。我的习惯是检查三个点文件完整性、行数一致性、抽样数据正确性。文件完整性看尾部。mysqldump正常结束时文件最后一行是-- Dump completed如果文件被截断这一行会缺失。可以用tail -n 5 backup.sql行数一致性通过对比源库和目标库的COUNT来验证。导入完成后抽几张关键表执行SELECT COUNT(*) FROM source_db.orders; SELECT COUNT(*) FROM target_db.orders;两条结果一致基本可以确定数据没有丢。抽样数据正确性要结合业务。比如订单表导完以后抽查一笔金额较大的订单比对订单号、金额、状态几个关键字段是否一致。如果源库有逻辑删除标记还要确认导入后标记还在。最后再分享一个这些年收获的习惯核心库的表结构我会定期导出来提交到Git仓库和代码一起做版本管理。某次线上变更给大表加了冗余列后来程序批量插入总是报错翻Git历史里的建表SQL一眼就看出是谁在哪天改坏了。这个习惯救过我很多次建议每一个长期维护MySQL业务的团队都养成。还有个小技巧不管导出还是导入正式操作前一定要先在临时实例上跑一遍完整流程。我在线上执行大表迁移前都会先在本地用同样版本的MySQL建一个库把备份文件导入一遍确认无报错后再对正式环境操作。这个习惯虽然多花半小时但能避免在生产上反复试错。
