搞MySQL的谁没被“换库”“备份”“迁移”这事烦过。以前在项目上线前最怕的就是把测试库的数据整到生产环境或者接手一台新服务器要把旧库完整搬过去。用到的核心操作无非就是导出、导入再细一点就是表结构怎么导、数据怎么导、两个一起怎么导。今天这篇就把我这些年踩过的坑、验证过的命令、还有各种奇葩报错的解法一次性整理出来基本上你能遇到的导出导入场景都能在这找到答案。这篇东西适合谁看刚入行的运维、后端开发还有那些要频繁在本地/测试/生产库之间同步数据的人。不需要你有多深的MySQL功底只要你装了MySQL、会打开命令行就能跟着操作。我会从最容易犯迷糊的概念开始拆再给具体命令、参数解释、报错排查最后补一点我怎么写自动备份脚本的小经验。1. 导出导入的场景分析与核心思路1.1 为什么你迟早会碰到这需求很多人一开始觉得MySQL导出导入就是执行一条mysqldump命令而已真到用的时候才发现事情没那么简单。我见过太多种情况同事离职留下一台服务器数据要抢救出来开发机器换SSD要把整个库挪过去线上出了个诡异BugDBA让你把某几张表的数据捞出来做分析或者只是改表结构前做个保险。每一种场景对“导出什么、导入到哪、用什么方式”的要求都不一样。举几个真实的例子有一次我只是想把A库里一张订单表的数据同步到B库如果直接备份整个库导入的时候可能因为外键关系、自增ID冲突把两边数据搞乱。还有一次是从MySQL 5.6导出的备份往MySQL 8.0里面导碰到认证插件不兼容直接报错。这些都不是靠百度一条命令能解决的你至少要明白导出文件里装的是什么导入的时候数据库在做什么才能对症下药。1.2 先搞清楚表结构、数据、还是两者都要这是最基础也是最重要的一步。所谓“表结构”说白了就是建表语句包含字段定义、类型、默认值、索引、外键这些也就是CREATE TABLE那段东西。“数据”则是表里一条条的记录导出后一般是一堆INSERT INTO语句。有时候你只想要一个空壳子把库的表结构复制过去一份数据都不要有时候你只想要数据目标库的表结构已经建好了更多时候是两个都要直接搬一个完整的库。另外要分清逻辑备份和物理备份。我们平时用mysqldump导出来的SQL文件属于逻辑备份它里面是SQL语句拿到任何一台同版本或相近版本的MySQL上都能执行而直接拷贝数据目录下的 .ibd、.frm 文件属于物理备份速度快但对版本、操作系统、甚至MySQL配置都更敏感一般不推荐跨机器这么干。今天讲的主要是逻辑备份因为它最通用、最安全。2. 工具选型mysqldump、mysql命令与可视化工具2.1 mysqldump命令行永远的主角我始终认为 mysqldump 是MySQL官方工具里最该熟练掌握的一个没有之一。它随MySQL一起安装在bin目录下不需要额外配置。用它导出生成的是一个标准SQL文本文件里面会把建库语句、建表语句、插入数据的语句都按逻辑写好后期用mysql客户端执行这个文件就能还原。要注意一点mysqldump的输出是一堆SQL不是那种“二进制备份文件”。这既是优点也是缺点优点是可读、可改、可选择性导入缺点就是大库导出速度相对慢、文件体积大但绝大多数场景这是最稳的方案。装好MySQL后你可以在命令行直接输入mysqldump --version看看有没有这个命令如果提示找不到多半是没把MySQL的bin目录加到系统PATH里。2.2 mysql客户端导入数据的另一面有导出就得有导入导入最常用的不是专门的什么工具而是MySQL自带的mysql客户端命令。这个命令平时大家拿来执行 SQL其实它也能直接读取SQL文件并逐条执行实现导入功能。原理很简单SQL文件是一堆文本命令把文件内容“喂”给mysql客户端它就能在目标库上跑这些命令完成建表、写入数据等一系列操作。我用这个方式导入过好几百兆的SQL文件也导入过几十G的压缩备份关键是掌握管道和重定向的用法。如果你在图形界面里操作Navicat也有导入功能但底层承担的其实还是相近的原理而且图形界面导入大文件经常卡住或者内存占用爆炸遇到生产环境还是命令行稳。2.3 Navicat等可视化工具的局限与优势可视化工具不是不能用。Navicat的“数据传输”“转储SQL文件”确实对新手友好不熟悉命令行的可以点鼠标完成导出导入。但我个人的体会是它适合小库、临时操作真到了大数据量、自动化、定时备份这种场景就力不从心。Navicat导出时有个坑它默认的转储可能不包含某些对象比如触发器、事件等你如果不勾选迁移过去就会发现少了东西。而且它转储的SQL文件格式跟mysqldump略有差异虽然主流都能执行但遇到特殊字符或者老旧版本数据库偶尔会出问题。我现在的习惯是日常用命令行偶尔打开Navicat看看表结构、跑个查询真正备份迁移一律交给脚本。3. 实操导出MySQL表结构或数据3.1 只导出表结构一条命令的事如果你只是想把数据库的表结构导出来不包含任何数据mysqldump加--no-data参数就行mysqldump -uroot -p --no-data mydb mydb_structure.sql注意我写的是mydb实际用的时候替换成你的库名。执行后会提示输入密码然后生成一个SQL文件。打开这个文件你会发现里面就是一张张表的CREATE TABLE语句外加一些锁表、设置字符集的辅助语句真正建表的核心就在里面。这里得说一下表结构的完整性。--no-data并不是真的什么都不带它只是跳过了数据部分但像索引、约束、自增属性、注释这些都会保留。如果你的库里还有视图、存储过程、触发器默认的mysqldump在某些版本里不会全部导出需要额外加参数比如--routines导出存储过程和函数--triggers导出触发器。我建议只要建表结构就习惯性加这两个参数避免遗漏。mysqldump -uroot -p --no-data --routines --triggers mydb mydb_structure.sql3.2 只导出数据不要建表语句反过来目标库的表已经建好了你只想把数据导出来同步过去那么用--no-create-info这个参数。它表示导出时不包含建表信息只生成插入数据的SQLmysqldump -uroot -p --no-create-info mydb mydb_data.sql这样导出的文件里主要就是各种INSERT INTO语句。此时我还要推荐一个参数--complete-insert它的作用是让每条INSERT语句都完整列出字段名。默认mysqldump为了省空间会写简化的INSERT语句但如果两边的表字段顺序不完全一样简化的INSERT很容易插错列。加了--complete-insert后生成的是INSERT INTO table (col1, col2, ...) VALUES (...)匹配更安全代价是文件体积大一些但我觉得这点空间换来的安全完全值得。如果你只想要一张表的数据直接指定表名就行mysqldump -uroot -p --no-create-info mydb orders orders_data.sql3.3 同时导出表结构和数据最常用的完整备份大部分情况下你希望一个文件搞定所有事情那就什么参数都不用加直接备份整个库mysqldump -uroot -p mydb mydb_full.sql这个文件里既有建表语句又有insert语句导入到任何空库上基本都能还原。它还可以加很多增强参数比如--single-transaction对InnoDB表做一致性快照备份过程中不影响业务读写--set-gtid-purgedOFF在同步到某些从库时避免GTID问题这个在8.0版本里尤其要注意。为了稳妥我备份一个库通常会用这样的组合mysqldump -uroot -p --single-transaction --routines --triggers --set-gtid-purgedOFF mydb mydb_full.sql如果你想备份整个实例所有数据库用--all-databases但日常用得少一般按库备份就够了。3.4 导出单表或多表按需提取有时候要导的是几张表而不是整个库mysqldump支持在库名后直接跟表名列表mysqldump -uroot -p mydb user_info order_info selected_tables.sql这种按需导出的场景非常多比如开发让你把用户表和订单表发过去配合排查你不需要导整个库毕竟库里面可能有日志表、临时表又大又没价值。导出单表时同样可以搭配--no-data或--no-create-info灵活组合。另外提醒一句如果表特别多命令行写一堆表名会很乱建议先SHOW TABLES;查一遍再决定导哪些。在写脚本时也可以通过$(mysql -e SHOW TABLES ...)的方式把表名拼进去自动化按需导出后面会讲。3.5 压缩备份大表不再怕导出几百MB甚至几G的SQL文件时磁盘空间和传输带宽都很让人头疼。好在SQL文本压缩率特别高通常能压到原来的五分之一甚至更小。方法是用管道把mysqldump的输出直接交给gzipmysqldump -uroot -p mydb | gzip mydb_full.sql.gz这样不会在本地生成中间SQL文件而是直接输出压缩包非常省空间。导入的时候反向操作gunzip -c mydb_full.sql.gz | mysql -uroot -p mydb这套组合我用了很多年备份几百G的数据也基本靠这个思路。需要注意管道命令的退出状态如果gzip成功了但是mysqldump中途报错生成的gzip文件可能已经写出来了光看文件大小容易误判。稳妥起见脚本里一定要检查mysqldump这个环节是否报错。3.6 远程导出直接拉取线上库有的场景是你本地没有这个库数据在远程服务器上但你需要在本地生成备份直接在mysqldump命令里指定远程地址就好mysqldump -h192.168.1.100 -P3306 -uroot -p --single-transaction mydb remote_backup.sql这里有几个需要注意的点。远程导出必须确保目标机器允许你的IP通过MySQL端口访问如果报错Host xxx is not allowed to connect to this MySQL server那就是账号权限的host没开放需要在远程库上调整用户授权。另外远程备份走网络传输建议加上--compress参数让客户端和服务器之间传输时压缩数据能明显减少网络开销尤其跨机房的时候很有用。4. 实操导入MySQL表结构或数据4.1 命令行重定向导入最直接的方式导出得到的是一个SQL文件导入就是让它在一个空数据库里重新执行一遍。最常用的命令是mysql -uroot -p mydb mydb_full.sql这里mydb必须是已经存在的库导入时并不负责自动建库除非SQL文件里包含了CREATE DATABASE。如果你要导入到新环境通常得先建一个空库CREATE DATABASE mydb_new DEFAULT CHARACTER SET utf8mb4;然后再执行上面的导入命令。我吃过一次亏导入前没检查字符集结果老库是latin1新库默认utf8mb4导入后中文全部变成问号。所以建库时手动指定字符集、排序规则非常重要。4.2 source命令进mysql内部执行如果你已经打开了mysql命令行界面也可以用source命令导入mysql use mydb; mysql source /path/to/mydb_full.sql;source和我们平时用的\\. 文件路径是一个意思。这个方法适合在服务器上已经登录了MySQL、想边看边执行的情况也适合一些图形化终端不让你直接重定向的时候。区别不大但我个人更习惯用重定向因为可以配合管道、日志等做进一步封装source则更像“人工交互”场景。4.3 压缩SQL文件的导入别先解压如果你备份的是.sql.gz压缩包导入时没必要先解压成.sql再导入那样会多占一次磁盘还能直接一步到位gunzip -c mydb_full.sql.gz | mysql -uroot -p mydb如果没装gunzip用zcat也行效果一样。这种管道导入的好处是不产生中间文件尤其面对几十G的压缩包解压出来再导入不仅时间翻倍临时空间也可能不够。我用这条命令导过一个20多G的压缩包服务器磁盘剩30G差点出事后来改成管道导入稳得很。4.4 从SQL片段里单独导入某张表有时候你只想要一个超大备份文件里的某张表不想整库导入。如果备份文件是mysqldump生成的你可以用sed或者awk把相关段落捞出来再导入sed -n /CREATE TABLE orders/,/UNLOCK TABLES;/p mydb_full.sql orders.sql mysql -uroot -p mydb orders.sql这个做法看着有点土但实际排查问题时特别好用。注意行末尾的分号匹配有时候mysqldump生成的UNLOCK TABLES;前后有空行sed匹配/UNLOCK TABLES;/会漏掉最后一行建议用/UNLOCK TABLES;$/更精确。4.5 导入大文件的进度和日志问题命令行导入最大的痛点是没有进度条文件执行到哪儿了全靠猜。我的做法是用--verbose参数让它输出更多信息虽然不会逐条打印insert但能看到阶段内容。更实用的办法是把导入输出重定向到日志文件出错时直接看日志定位mysql -uroot -p mydb -v mydb_full.sql import.log 21导入完成后再对比一下源库和目标库的表行数比如SELECT table_name, table_rows FROM information_schema.tables WHERE table_schemamydb;table_rows对于InnoDB是一个估算值不完全精确所以更建议在导入前记录关键表的COUNT(*)导入后再跑一遍对比。虽然慢但最保险。5. 常见问题与排查技巧实录5.1 中文乱码99%是字符集问题这是我见过最多的报错。导出时库是utf8mb4导入时目标库连接字符集不对或者终端本身是latin1数据进去全乱。解决办法在导出导入命令里都加上--default-character-setutf8mb4mysqldump -uroot -p --default-character-setutf8mb4 mydb mydb.sql mysql -uroot -p --default-character-setutf8mb4 mydb mydb.sql另外建库的时候就要指定字符集我不太建议依赖数据库默认值毕竟不同版本默认值不一样。直接写清楚CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;如果你拿到的SQL文件已经很乱码了那就别挣扎重新按正确字符集导出一次最省事在乱码基础上修复往往是事倍功半。5.2 max_allowed_packet报错大字段写入失败导入超大SQL文件时偶尔会碰到这样报错Got a packet bigger than max_allowed_packet bytes。这是因为插入的包大小超过了MySQL服务端允许的最大值常见于表里有大的TEXT、BLOB字段或者mysqldump在导出时把很多行合并成了一条大INSERT。解决办法在导入前临时调大服务端参数SET GLOBAL max_allowed_packet 1073741824;或者在启动MySQL时配置max_allowed_packet1G。同时导出时可以在mysqldump命令里加--max-allowed-packet1073741824让导出的SQL本身也不往单条超大语句上靠。需要注意的是如果用的是云数据库RDS有些参数不能随便改那就只能找客服或者换一种分批导出的思路。5.3 外键约束导致导入失败导入时最头疼的错误之一就是外键关联顺序不对。比如A表引用了B表但SQL文件里B表的INSERT还没执行A表的INSERT就先到了于是报外键约束失败。mysqldump导出的文件默认会在开头用SET FOREIGN_KEY_CHECKS 0目的就是临时关闭外键检查正常情况下不会有这个问题。但如果你用工具导的SQL不包含这句或者手动截取过部分内容就会撞上。处理办法很简单在导入前手动执行SET FOREIGN_KEY_CHECKS 0;导入完再设回1。如果是自己拼的脚本一定记得加这一句。还有一种情况是导入时目标表里已有部分数据和导入数据的主键冲突那就不是外键的问题了要么清空目标表要么用--insert-ignore重新导出让冲突行自动跳过。5.4 权限问题导入表结构的账号不够格平时测试随便用root很爽到了正式环境往往给你一个只有部分权限的账号这时执行导入就可能报错Access denied for user xxx% to database yyy。建表至少需要CREATE权限插入数据至少需要INSERT权限如果涉及触发器、存储过程还需要相应权限。在个人开发环境这个问题不会暴露但在公司里给同事导出SQL文件时要考虑到对方可能只有某个库的读写权限。建议导出前先和对方确认目标库存在、账号权限够用。权限这事情最怕不是不够用而是你压根不知道不够用等到导入到一半才报错那才是真的尴尬。5.5 MySQL版本跨度过大导入报错或结果不对跨版本迁移是最容易踩暗坑的场景。从MySQL 5.7导出的SQL文件往MySQL 8.0导入大部分没问题但有些情况下会碰到认证插件不同导致连不上、sql_mode差异导致某些SQL在8.0里语法报错、utf8mb4_0900_ai_ci排序规则在5.7里不存在。我建议跨大版本迁移时先做一次小样验证。也就是只导出几张有代表性的表导入到目标库试试确认语法、字符集都OK后再全量执行。如果是从5.6直接蹦到8.0中间最好过一遍5.7不要嫌麻烦不然出了诡异问题排查成本比迁移本身还高。在导出时也可以加一个参数--compatiblemysql40或--compatibleansi来生成兼容性更好的SQL但那只适合特殊需求正常情况我还是靠测试来保证。6. 经验扩展跨环境同步与自动化备份6.1 跨库、跨服务器迁移的细节如果你需要做的是把A服务器的库搬到B服务器而不是本机导出本机导入那么可以在两台机器之间直接通过管道传输省掉中间文件减少磁盘占用mysqldump -h源IP -uroot -p mydb --single-transaction | mysql -h目标IP -uroot -p mydb这条命令看着简单但实际生产效率极高相当于边导出边导入。前提是两边网络允许、账号权限够并且目标库已经建好。如果数据量大建议放在内网跑跨公网传输容易断断了就要重新来。为了防止断线可以在外层套一个screen或者nohup这也算是我吃了多次亏后学到的经验。6.2 定时自动备份脚本我常用的方案手动敲命令适合一次性的活但数据库备份应该自动化。我在服务器上写了一个简单的shell脚本核心逻辑就是mysqldump加压缩然后按日期命名保留最近7天备份#!/bin/bash BACKUP_DIR/data/mysql_backup DATE$(date %Y%m%d_%H%M%S) DB_USERroot DB_PASS你的密码 DB_NAMEmydb mysqldump -u$DB_USER -p$DB_PASS --single-transaction --routines --triggers $DB_NAME | gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz find $BACKUP_DIR -name *.sql.gz -type f -mtime 7 -delete这个脚本配合crontab每天凌晨2点执行一次备份文件可以定期清理避免磁盘被堆满。注意数据库密码不要写在命令行里可以用--defaults-extra-file指定配置文件这样更安全。脚本里尽量用set -e和判断mysqldump退出码避免备份失败还傻乎乎保留一个坏文件。6.3 多实例和分库备份的建议一台服务器上跑多个MySQL实例不同端口或几十个库的情况我不建议写死库名。可以用循环读取库列表的方式备份每一个库这样新库添加后不用改脚本databases$(mysql -u$DB_USER -p$DB_PASS -e SHOW DATABASES; | grep -Ev ^(Database|information_schema|performance_schema|mysql|sys)$) for db in $databases; do mysqldump -u$DB_USER -p$DB_PASS --single-transaction --routines --triggers $db | gzip $BACKUP_DIR/${db}_${DATE}.sql.gz done这里排除系统库有没有必要看个人习惯我习惯单独备份系统库里的用户和权限表但不放在常规业务备份里免得restore的时候出幺蛾子。6.4 导入后的校验工作不能省导出导入并不是把文件复制过去就完事了。我每次做完迁移都会跑一轮校验数行数对比关键表的COUNT(*)看自增检查表的AUTO_INCREMENT是否合理避免之后插入ID冲突抽查数据随机查几条记录确认中文字符、金额、日期格式没乱检查存储过程SHOW PROCEDURE STATUS看看有没有丢失这些看起来很基础但做过一次全流程的人都知道数据没问题不代表一切OK对象的完整性和字符集的正确性同样重要。我把这一节放在最后就是想提醒你导入成功只是第一步验证完整才是收尾。最后聊一点个人的体会。前几年我总嫌mysqldump性能差研究过各种物理备份工具后来发现对大多数团队来说逻辑备份才是兜底的保命符——它能跨版本、能选择性恢复、能看懂内容关键时刻能救你。现在我的习惯是不管有没有更高级的备份方案mysqldump这条线始终保持每周至少做一次全量逻辑备份压缩后归档。你永远不知道哪一天会需要把一个库“原样”恢复到另一台机器上到时候你唯一能依赖的就是平时这些看起来枯燥的备份习惯。