前言
数据库的数据是系统的灵魂，是系统的核心。故此，数据安全变得极为重要。系统对数据的操作包含查询、新增、修改删除，其中数据查询不影响数据原本内容，新增属于系统写入数据，而删除和修改就涉及到了数据库原本内容的变化。
为了防止误操作的发生，一般会采取二次确认、逻辑删除、物理备份、逻辑导出等方式。本文将讲述MySQL数据库逻辑导出的相关操作。
1. 数据库全量导出
全量导出，全部数据库的表结构和表数据
mysqldump -u [username] -p --all-databases > /path/all_databases_backup.sql
注意：命令中 [username] 替换为MySQL的用户名。执行该命令之后，会提示输入密码。在执行完成后，会将内容保存到 /path/all_databases_backup.sql 文件中。
2. 导出某个数据库
导出某个数据库，包含某个数据库的所有表结构和表数据
mysqldump -u [username] -p [database_name] > /path/database_backup.sql
注意：命令中 [username] 和 [database_name] 替换为MySQL的用户名和想要导出数据库的名称。执行该命令后，会提示输入密码。在执行完成后，会将内容保存到 /path/database_backup.sql 文件中。
3. 导出某个数据库的某张表
导出某个数据库的某张表，包含某个数据库的指定的表结构和表数据
mysqldump -u [username] -p [database_name] [table_name] > /path/table_backup.sql
注意：命令中 [username] 、[database_name] 和 [table_name] 分别替换为MySQL的用户名、想要导出的表所在的数据库名称和表名称。执行该命令后，会提示输入密码。在执行完成后，会将内容保存到 /path/table_backup.sql 文件中。
4. 导出某个数据库某张表的部分数据（含表结构）
导出某个数据库某张表的部分数据，仅仅只包含某个数据库的指定表的表结构和部分表数据
mysqldump -u [username] -p [database_name] [table_name] --where="[条件]" > /path/table_partion_backup.sql
注意：命令中 [username] 、[database_name] 、 [table_name] 和 [条件] 分别替换为MySQL的用户名、想要导出的表所在的数据库名称、表名称和对该表的筛选条件。执行该命令后，会提示输入密码。在执行完成后，会将内容保存到 /path/table_partion_backup.sql 文件中。
5. 导出某个数据库某张表的部分数据
导出某个数据库某张表的部分数据，仅仅只包含某个数据库的指定表的部分表数据
5.1 使用mysqldump命令
mysqldump -u [username] -p [database_name] [table_name] --where="[条件]" --no-create-info > /path/table_partion_backup.sql
注意：命令中 [username] 、[database_name] 、 [table_name] 和 [条件] 分别替换为MySQL的用户名、想要导出的表所在的数据库名称、表名称和对该表的筛选条件。执行该命令后，会提示输入密码。在执行完成后，会将内容保存到 /path/table_partion_backup.sql 文件中。
5.2 使用SELECT ... INTO OUTFILE语句
在MySQL的命令提示符中执行
SELECT * INTO OUTFILE '/var/lib/mysql-files/table_partion_backup.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name WHERE column1='some_value';
注意：
SELECT *: 选择所有列。INTO OUTFILE '/var/lib/mysql-files/table_partion_backup.csv': 将查询结果导出到指定的文件路径/var/lib/mysql-files/table_partion_backup.csvFIELDS TERMINATED BY ',': 定义字段之间的分隔符为逗号，这通常用于CSV格式。ENCLOSED BY '"': 定义字段值被双引号包围。LINES TERMINATED BY '\n': 定义每行记录的结束符为换行符。FROM table_name: 指定查询的表为table_name。WHERE column1='some_value': 过滤条件，只选择column1列值为some_value的记录。
6. 导出总结
mysqldump的部分常用参数含义：
| 参数 | 含义 |
|---|---|
-u, --user=name | 指定 MySQL 用户名。 |
-p, --password[=password] | 指定用户密码。如果仅使用 -p 而没有跟密码，则会提示输入密码。 |
--host=name 或 -h name | 指定连接的服务器主机名或 IP 地址。 |
--port=# 或 -P # | 指定连接的端口号。 |
--all-databases | 导出所有数据库。 |
--databases db_name ... | 指定要导出的数据库列表。 |
--tables db_name table_name ... | 指定要导出的数据库和表。 |
--no-data | 只导出表结构，不导出数据。 |
--no-create-info | 只导出数据，不导出表结构。 |
--add-drop-database | 在每个 CREATE DATABASE 语句之前添加 DROP DATABASE 语句。 |
--add-drop-table | 在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句。 |
--single-transaction | 对于支持事务的表（如 InnoDB），此选项会在一个单一的事务中执行整个 mysqldump 操作。 |
--lock-tables | 在导出数据之前，先锁定所有表。 |
--compact | 产生更少的输出，适合较小的输出文件。 |
--extended-insert | 使用多个 VALUES 行的 INSERT 语法。这通常使导出文件更小，并加速导入过程。 |
--complete-insert | 为每行使用完整的 INSERT 语句。 |
--hex-blob | 使用十六进制格式导出二进制字符串。 |
--set-gtid-purged=ON|OFF | 是否将 GTID 信息包含在输出中。 |
--compress | 使用 gzip 压缩输出。 |
--result-file=file_name 或 -r file_name | 将输出写入给定文件。 |
mysqldump 和 SELECT ... INTO OUTFILE 主要区别在于用途、输出格式、内容、灵活性、安全性等。
mysqldump主要用于导出和迁移数据库，而SELECT ... INTO OUTFILE主要用于数据导出。mysqldump输出的是SQL脚本，可以完整还原数据库；而SELECT ... INTO OUTFILE输出的是特定格式的数据文件。mysqldump提供更多选项，可以控制导出的细节；而SELECT ... INTO OUTFILE的选项相对较少。- 使用
SELECT ... INTO OUTFILE时需要注意文件权限和安全性，因为该命令可以在服务器上创建文件。
总的来说，这两个工具在数据库管理和数据迁移中都有其独特的用途。选择使用哪一个取决于你的具体需求，比如是否需要导出整个数据库结构，或者只需要导出某些数据。
如果你的目标是导出整个数据库结构，那么使用 mysqldump 是更好的选择，因为它可以导出数据库中的所有表结构，包括索引、约束和触发器。
如果你的目标是导出某些数据，那么使用 select ... into outfile 是更好的选择，因为它可以根据你的需求选择特定表或数据行，并将其导出为 CSV 或其他格式。
7. mysqldump导出数据还原
7.1 使用mysql工具
在命令行中执行
mysql -u [username] -p[password] [database_name] < /path/backup_file.sql
注意：命令中 [username] 、 [password] 、 [database_name] 和 /path/backup_file.sql 分别替换为MySQL的用户名、MySQL密码（注意 -p 和密码之间没有空格）、想要还原数据的数据库名称和导出文件所在的路径及名称。
7.2 使用source命令
在MySQL的命令提示符中执行
-- 选择数据库
use [database_name];
-- 执行SQL文件
source /path/backup_file.sql;
注意：[database_name] 替换为想要还原数据的数据库名称，/path/backup_file.sql 替换为导出文件所在的路径及名称。
8. select ... into outfile导出数据还原
在MySQL的命令提示符中执行
LOAD DATA INFILE '/var/lib/mysql-files/table_partion_backup.csv'
INTO TABLE your_table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
注意：
/var/lib/mysql-files/table_partion_backup.csv：数据文件在服务器上的路径。your_table_name：要导入数据的表名。FIELDS TERMINATED BY ',' ENCLOSED BY '"'：指定了字段是如何分隔和包围的，与导出时使用的格式相同。LINES TERMINATED BY '\n'：指定了行是如何终止的。IGNORE 1 ROWS：是一个可选的子句，用于跳过数据文件中的第一行（通常是标题行）。
9. select ... into outfile/load data infile总结
- 确保用户有导出路径的权限，以便能够执行
select ... into outfile和load data infile操作。 - 数据文件在客户端而不是服务器上，可能需要使用
load data local infile，也需要MySQL服务器允许从本地文件加载数据。
评论区