MySQL数据库逻辑备份详解

逻辑备份是数据库维护中的核心操作之一，通过将数据库中的数据和结构以SQL语句的形式导出，实现数据的安全保护与迁移。本文详细介绍MySQL中常用的逻辑备份工具及其使用方法。

一、mysqldump工具

mysqldump是MySQL官方提供的数据库备份工具，主要用于数据库的转储和迁移。支持从低版本MySQL升级到高版本，或者将MySQL数据迁移到其他数据库系统如Oracle、SQL Server等。

基本语法格式如下：

mysqldump [参数选项] > 输出文件名

备份所有数据库：

mysqldump --all-databases > full_backup.sql

备份指定数据库集合：

mysqldump --databases db1 db2 db3 > multiple_db.sql

备份单个数据库示例：

mysqldump --single-transaction sample_db > sample_backup.sql

上述命令使用--single-transaction参数确保备份一致性。生成的SQL文件包含表结构定义和数据插入语句，通过cat等文本查看工具即可阅读。

备份文件的标准结构包括：头部注释（包含MySQL参数配置）、CREATE TABLE语句、INSERT数据语句。通过注释区域可以优化还原操作的效率和准确性。

常用参数详解

• --single-transaction：备份启动前执行START TRANSACTION，获取一致性的数据快照。该参数仅对InnoDB存储引擎有效，备份期间应避免执行DDL操作。
• --lock-tables (-l)：按顺序锁定每个数据库的所有表。适用于MyISAM存储引擎，可保证单库备份一致性。InnoDB引擎建议使用--single-transaction。这两个参数互斥，不能同时使用。
• --lock-all-tables (-x)：备份期间锁定所有数据库的所有表，解决--lock-tables无法锁定全部表的问题。
• --add-drop-database：在CREATE DATABASE前先执行DROP DATABASE，需要与--all-databases或--databases配合使用。
• --master-data[=value]：用于主从复制场景。value=1时记录CHANGE MASTER语句，value=2时该语句被注释，默认值为空。
• --master-data：自动忽略--lock-tables，未使用--single-transaction时自动启用--lock-all-tables。
• --events (-E)：备份事件调度器。
• --routines (-R)：备份存储过程和函数。
• --triggers：备份触发器。
• --hex-blob：将BINARY、VARBINARY、BLOB和BIT类型数据以十六进制格式导出，避免特殊字符显示问题。
• --tab=path (-T path)：生成TAB分隔的数据文件。每张表对应一个.sql文件（包含表结构）和一个.txt文件（包含数据）。可通过--fields-terminated-by、--fields-enclosed-by等参数自定义分隔符。
• --where='条件' (-W '条件')：按条件导出数据。例如：mysqldump --single-transaction --where='id>100' sample_db users > filtered.sql

二、SELECT...INTO OUTFILE语句

SELECT...INTO OUTFILE是另一种逻辑备份方式，专门用于导出表数据。语法结构如下：

SELECT [字段列表] INTO OUTFILE '文件路径'
FIELDS TERMINATED BY '分隔符'
[OPTIONALLY] ENCLOSED BY '包裹字符'
ESCAPED BY '转义字符'
LINES STARTING BY '行前缀'
TERMINATED BY '行结束符'
FROM 表名;

各参数说明：

• FIELDS TERMINATED BY：列之间的分隔符
• [OPTIONALLY] ENCLOSED BY：字符串类型的包裹字符
• ESCAPED BY：转义字符
• STARTING BY：每行的起始字符串
• TERMINATED BY：每行的结束字符串

默认导出格式：

• 列分隔符：制表符(\t)
• 包裹符：空
• 转义符：反斜杠(\\)
• 行结束符：换行符(\n)
• 行前缀：空

重要提示：导出文件路径必须对mysql用户有写权限，否则会报错：

ERROR 1 (HY000): Can't create/write to file '/root/data.txt' (Errcode: 13)

如果目标文件已存在，也会报错：

ERROR 1086 (HY000): File '/home/mysql/data.txt' already exists

导出示例：

SELECT * INTO OUTFILE '/home/mysql/sample_db/t1.txt' FROM t1;

使用逗号作为分隔符导出：

SELECT * INTO OUTFILE '/home/mysql/t1.csv' 
FIELDS TERMINATED BY ',' 
FROM t1;

Windows平台需要指定行结束符：

SELECT * INTO OUTFILE '/home/mysql/t1.txt' 
LINES TERMINATED BY '\r\n' 
FROM t1;

三、逻辑备份恢复操作

mysqldump生成的备份文件本质上是SQL语句集合，恢复操作相对简单。

通过mysql命令直接恢复：

mysql -uroot -p < sample_backup.sql

通过SOURCE命令在mysql客户端内执行：

mysql> SOURCE /home/mysql/sample_backup.sql;

注意事项：如果备份文件包含DROP DATABASE语句，删除数据库时该目录不能包含其他相关文件，否则会报错：

ERROR 1010 (HY000): Error dropping database (can't rmdir ./sample_db, errno: 39)

特殊说明：mysqldump可以导出存储过程、触发器、事件和数据，但无法导出视图。包含视图的数据库需要额外备份视图定义，或备份视图对应的.frm文件，以确保完整恢复。

四、LOAD DATA INFILE语句

用于导入通过mysqldump --tab或SELECT...INTO OUTFILE导出的数据文件。语法格式：

LOAD DATA [LOCAL] INFILE '文件路径'
INTO TABLE 表名
[FIELDS TERMINATED BY '分隔符']
[OPTIONALLY] ENCLOSED BY '包裹字符']
[ESCAPED BY '转义字符']
[LINES STARTING BY '行前缀']
[TERMINATED BY '行结束符']
[IGNORE number LINES]
[(字段列表)];

导入示例（忽略第一行表头）：

LOAD DATA INFILE '/home/mysql/sample_db/t1.txt' 
INTO TABLE t1
IGNORE 1 LINES;

导入时需要FILE权限。对于导入格式选项，与SELECT...INFILE完全对应。

加速InnoDB表导入，可以暂时禁用外键检查：

SET @@foreign_key_checks = 0;
LOAD DATA INFILE '/home/mysql/t1.txt' INTO TABLE t1;
SET @@foreign_key_checks = 1;

指定列导入示例（c列由a、b列相加计算）：

LOAD DATA INFILE '/home/mysql/t1.txt' 
INTO TABLE t1
(a, b, @var_c)
SET c = a + b;

五、mysqlimport工具

mysqlimport是MySQL提供的命令行导入工具，本质上是LOAD DATA INFILE的封装接口。语法格式：

mysqlimport [选项] 数据库名 文件1 [文件2 ...]

与LOAD DATA INFILE的主要区别：支持同时导入多张表，可通过--use-threads参数并发导入多个文件。

并发导入示例：

mysqlimport --use-threads=2 sample_db /home/mysql/table1.txt /home/mysql/table2.txt

执行结果：

sample_db.table1.txt: Records: 5000000  Deleted: 0  Skipped: 0  Warnings: 0
sample_db.table2.txt: Records: 5000000  Deleted: 0  Skipped: 0  Warnings: 0

通过进程查看可以看到并发执行了多个LOAD DATA INFILE操作。注意：并发导入不同文件可以提升效率，但对同一张表并发导入通常不如串行方式效果好。