MySQL数据库备份与还原常用命令指南

核心备份逻辑与语法规范

MySQL 原生提供的逻辑备份工具为 mysqldump，其基本原理是将数据库中的表结构定义与数据行导出为标准的 SQL 脚本文件。在使用命令前，通常需要具备相应数据库的管理权限，且需注意以下通用参数：-h 指定主机地址（默认 localhost），-u 指定用户（通常为 root），-p 后紧跟密码（注意 -p 与密码之间无空格，若有则需在引号中）。由于命令行明文显示密码存在安全隐患，生产环境中建议配合配置文件或使用交互提示输入。

常见备份场景实操

第1步：基础全库备份

目的：获取某单一数据库的完整快照，包含建表语句、触发器及存储过程。

操作：

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

该命令将生成的 .sql 文件保存至当前目录。此方法适用于开发测试环境的数据同步或小规模业务系统的定期存档。

第2步：带覆盖标记的备份

目的：确保备份文件在执行还原时，若目标库已有同名表，能自动添加删除旧表的指令，避免冲突报错。

操作：

mysqldump --add-drop-table -uusername -ppassword databasename > backupfile.sql

加入 --add-drop-table 参数可在每个 CREATE TABLE 语句前注入 DROP TABLE IF EXISTS 指令，提升还原过程的鲁棒性。

第3步：指定单表或多表备份

目的：针对大型数据库仅需恢复或迁移特定业务模块的场景，减少 I/O 压力与传输时间。

操作：

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

在库名之后依次列出需要备份的表名，中间以空格分隔。

第4步：多库与全量服务器备份

目的：一次性捕获多个独立业务库，或者在服务器迁移前导出所有实例数据。

操作 A（多库）：

mysqldump -hhostname -uusername -ppassword --databases databasename1 databasename2 > multibackupfile.sql

注意 --databases 关键字的使用，它能保留库切换逻辑，还原时无需手动切换上下文。

操作 B（全库）：

mysqldump --all-databases > allbackupfile.sql

此命令需高权限账号执行，建议配合严格的时间窗口进行。

第5步：仅备份数据结构

目的：用于版本控制中的 Schema 管理，或搭建空库框架时不携带实际业务数据。

操作：

mysqldump --no-data --databases databasename1 databasename2 > structurebackupfile.sql

参数 --no-data 会忽略 INSERT 语句，大幅减小文件体积。

数据压缩与空间优化

随着数据量的增长，纯文本 SQL 文件占用空间巨大。利用 Linux 管道特性结合压缩算法可显著降低存储成本。

使用 gzip 压缩备份

操作：

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

输出文件后缀通常命名为 .sql.gz。还原时需逆向处理：

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

使用 bzip2 更高压缩比备份

优势：bzip2 压缩率通常高于 gzip，适合长期归档冷数据。

操作：

mysqldump -hhostname -uusername -ppassword databasename | bzip2 > backupfile.sql.bz2

还原方式如下：

bunzip2 < backupfile.sql.bz2 | mysql -uusername -ppassword databasename

跨服务器实时迁移

在不经过本地磁盘中转的情况下，直接将源服务器的数据流推送到目标新服务器，是应对突发灾难恢复或快速克隆环境的极佳方案。

第6步：直连传输命令

前提条件：目标新服务器已安装同版本 MySQL，并已创建好对应的数据库，网络防火墙允许两端端口互通。

操作：

mysqldump -uusername -ppassword databasename | mysql --host=*.*.*.* -C databasename

此处使用了管道符 | 连接两个进程。左边负责导出数据，右边通过 -C (或 --compress) 启用压缩传输，有效降低带宽消耗。目标主机的 IP 需替换为实际地址。建议在低峰期操作，以免锁表影响正常业务查询。

风险管理与注意事项

• 事务一致性：mysqldump 默认使用 --single-transaction（针对 InnoDB），保证备份期间数据快照的一致性，不会阻塞写入操作。如果是 MyISAM 引擎，可能需要在备份前锁定表（LOCK TABLES）。
• 密码安全：-p 后面紧接密码虽然方便，但容易被 ps 等进程查看命令暴露。推荐使用 --defaults-file=/etc/mysql/my.cnf 读取配置或让程序自行提示输入。
• 验证恢复：所有备份必须定期进行恢复演练。仅有备份文件而无成功恢复记录，无法视为有效的备份策略。