MySQL数据导出导出的三种办法（13/16）

数据导入导出

基本概述

现在常用的有3中数据导入与导出方法：

• 利用mysqldump工具：
  
  
  
  • 优点：
    
    
    
    • 简单易用，只需一条命令即可完成数据导出。
      
    • 可以导出表结构和数据，方便完整备份。
      
    • 支持过滤条件，可以选择导出部门数据。
      
    • 天生的文件可以用于跨平台、跨版本的数据迁徙。
      
    
    
  • 缺点：
    
    
    
    • 导出的数据包含额外的INSERT语句，大概导致导入速率较慢。
      
    • 不能利用复杂的JOIN条件作为过滤条件。
      
    
    
  • 推荐场景：
    
    
    
    • 须要备份和迁徙表结构和数据。
      
    • 须要导出部门数据到其他系统或进行数据分析。
      
    
    
  
  
• 导出CSV文件：
  
  
  
  • 优点：
    
    
    
    • CSV格式通用，易于在不同应用程序间交换数据。
      
    • 可以利用文本编辑器查看和编辑数据。
      
    • 支持全部SQL写法的过滤条件。
      
    
    
  • 缺点：
    
    
    
    • 导出的数据保存在服务器本地，大概受到secure_file_priv参数限定。
      
    • 每次只能导出一张表的数据。
      
    • 须要单独备份表结构。
      
    
    
  • 推荐场景：
    
    
    
    • 须要将数据导出到本地文件系统或共享网络位置。
      
    • 须要将数据导入到其他非MySQL系统或应用程序。
      
    
    
  
  
• 物理拷贝表空间：
  
  
  
  • 优点：
    
    
    
    • 速率极快，尤其是对于大表数据的复制。
      
    • 可以直接复制整个表的数据，不须要逐条插入。
      
    
    
  • 缺点：
    
    
    
    • 须要服务器端操纵，无法在客户端完成。
      
    • 必须是全表拷贝，不能选择性导出数据。
      
    • 仅限于InnoDB引擎的表。
      
    
    
  • 推荐场景：
    
    
    
    • 须要快速复制大表数据到另一个数据库或服务器。
      
    • 源表和目标表都利用InnoDB引擎。
      
    • 有服务器文件系统的访问权限。
      
    
    
  
  

在选择利用哪种方法时，还须要思量数据的巨细、是否须要跨平台迁徙、是否有权限访问服务器文件系统、是否须要保存表结构等因素。通常，假如须要快速迁徙大量数据并且对数据的完整性有高要求，物理拷贝表空间是一个好选择。假如数据量较小或者须要跨平台迁徙，利用mysqldump或导出CSV文件大概更合适。
mysqldump工具

• 利用mysqldump导出数据：
  
  1. mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
  3. -h: 指定MySQL服务器的主机名。$host: 替换为实际的主机名。
  4. -P: 指定MySQL服务器的端口号。$port: 替换为实际的端口号。
  5. -u: 指定登录MySQL的用户名。`$user`: 替换为实际的用户名。
  6. --add-locks=0: 导出时不增加额外的锁。
  7. --no-create-info: 不导出表结构。
  8. --single-transaction: 在导出数据时不需要对表加表锁。
  9. --set-gtid-purged=OFF: 不输出与GTID相关的信息。
  10. db1: 指定要导出的数据库名。
  11. t: 指定要导出的表名。
  12. --where="a>900": 导出满足条件a>900的数据。
  13. --result-file=/client_tmp/t.sql: 指定导出结果的文件路径。

  复制代码
• 将数据导入到目标数据库：
  
  1. mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
  2. `-h`: 指定MySQL服务器的主机名。`root`: 使用root用户登录。
  3. `-P`: 指定MySQL服务器的端口号。
  4. `-u`: 指定登录MySQL的用户名。
  5. `db2`: 指定要导入数据的数据库名。
  6. `-e`: 后面跟随要执行的命令。
  7. `"source /client_tmp/t.sql"`: 执行source命令导入之前导出的SQL文件。

  复制代码

文件导入导出

• 导出为CSV文件：
  
  1. SELECT * FROM db1.t WHERE a > 900 INTO OUTFILE '/server_tmp/t.csv';
  4. SELECT * FROM db1.t: 指定要导出的查询。
  5. WHERE a > 900: 导出满足条件的数据。
  6. INTO OUTFILE '/server_tmp/t.csv': 指定导出结果的CSV文件路径。

  复制代码
• 导入CSV文件到目标表：
  
  1. LOAD DATA INFILE '/server_tmp/t.csv' INTO TABLE db2.t;
  3. LOAD DATA INFILE: 加载数据的命令。
  4. '/server_tmp/t.csv': 指定CSV文件的路径。
  5. INTO TABLE db2.t: 指定要导入数据的目标表。

  复制代码

在MySQL中secure_file_priv用于限定LOAD DATA INFILE和SELECT ... INTO OUTFILE这两个命令天生或读取文件的位置。这个参数的目的是为了增强安全性，防止不测或恶意地读取或写入服务器上的敏感文件。
假如secure_file_priv被设置为空字符串（''）或者NULL，则表示没有文件路径限定，可以利用任意文件路径。但是，这种设置降低了系统的安全性，因此不推荐在生产环境中利用。
物理拷贝表空间

• 物理拷贝表空间：
  
  
  
  • 首先创建一个相同结构的空表：
    1. CREATE TABLE db2.r LIKE db1.t;

    复制代码
  • 然后丢弃表空间：
    1. ALTER TABLE db2.r DISCARD TABLESPACE;

    复制代码
  • 导出表文件：
    1. FLUSH TABLES db1.t FOR EXPORT;

    复制代码
  • 拷贝文件：
    1. cp /path/to/db1/t.ibd /path/to/db2/r.ibd
    2. cp /path/to/db1/t.cfg /path/to/db2/r.cfg

    复制代码
  • 解锁表并导入表空间：
    1. UNLOCK TABLES;
    2. ALTER TABLE db2.r IMPORT TABLESPACE;

    复制代码
  
  

作者与版本更新计划

感谢您的阅读与支持！本文是《MySQL实战与优化》专栏中的一篇精选文章，该专栏共包含16篇文章，旨在为您提供实战中可直策应用的宝贵知识。
关注公众号【数舟】，获取作者最新动态，公众号后台回复【mysql2024】，即可免费领取这份包含16篇文章的完整的PDF专栏！

现在版本为v1.0，更新时间2024年4月10日。后续此文档更新与版本发布会同步到知识星球【数舟】中。

知识整理与创作不易，感谢大家明白与支持！
参加知识星球，您将获得更多独家内容、专栏更新以及与行业内专家和偕行的互动交流时机。我们在知识星球等您，一起探索MySQL的深层次世界！
星球内现在包含300+佳构文章，内容涵盖大数据、MySQL、运维、Python、调优、履历分享、数据分析等方向内容，会根据大家的学习需求更新更多方向的内容。