侧边栏壁纸
博主头像
叩钉壹刻博主等级

7分技术,3分管理,2分运气

  • 累计撰写 28 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

MySQL数据库导出与还原

鹿心肺语
2024-03-18 / 0 评论 / 0 点赞 / 49 阅读 / 8592 字

前言

数据库的数据是系统的灵魂,是系统的核心。故此,数据安全变得极为重要。系统对数据的操作包含查询、新增、修改删除,其中数据查询不影响数据原本内容,新增属于系统写入数据,而删除和修改就涉及到了数据库原本内容的变化。

为了防止误操作的发生,一般会采取二次确认、逻辑删除、物理备份、逻辑导出等方式。本文将讲述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.csv
  • FIELDS 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将输出写入给定文件。

mysqldumpSELECT ... 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总结

  1. 确保用户有导出路径的权限,以便能够执行 select ... into outfileload data infile 操作。
  2. 数据文件在客户端而不是服务器上,可能需要使用 load data local infile ,也需要MySQL服务器允许从本地文件加载数据。
0

评论区