MySQL数据的导入导出mysqldump、mysqlimport into outfile和load data

拼搏现实的明天。 2024-03-24 21:32 46阅读 0赞

## 0、概述 ##

MySQL数据的导入导出方案通常是配套的，例如：  
方案一：使用[mysqldump][]导出数据，再使用mysql客户端导入数据  
方案二：使用SELECT INTO OUTFILE命令导出数据，再使用LOAD DATA或mysqlimport导入数据  
方案三：使用mysql程序的批处理模式导出数据，再使用LOAD DATA或mysqlimport导入数据

## 1、mysqldump导出数据+mysql导入数据 ##

### 1.1、使用mysqldump导出数据 ###

#### 1.1.1、使用–tables导出指定表 ####

# 语法
    mysqldump  db_name --tables  tb1_name  tb2_name  >  filemname.sql
    # 实例
    mysqldump mytest --tables t1 t2 > t1_t2.sql

#### 1.1.2、使用–tab选项将表定义文件和数据文件分开导出 ####

# 语法
    mysqldump  db_name  --tab=dir
    # 实例
    mysqldump mytest1 --tab=/home/mysql/__test

![32512cd12fd6bad7712add84761ad46c.png][]

#### 1.1.3、使用–fields-terminated-by选项定义数据分隔符 ####

以下导出时，数据值以逗号分隔

mysqldump mytest1 --tab=/home/mysql/__test   --fields-terminated-by=','

#### 1.1.4、使用–databases选项导出整个库或多个库 ####

参数说明如下：

*  –complete-insert：导出的dump文件里，每条INSERT语句都包含列名。
 *  –force：即使出现错误，也要继续执行导出操作，会打印出错误。
 *  –insert-ignore：生成的INSERT语句是INSERT IGNORE的形式，如果导入此文件，即使出错了也仍然可以继续导入数据（当作警告）。
 *  –databases：类似–tables，后面可以跟多个值，即多个数据库名
 *  –compatible=name：导出的文件和其他数据库更兼容（但不确保），name的值可以是ANSI、MYSQL323、MYSQL40、POSTGRESQL、ORACLE、MSSQL、DB2、MAXDB、NO\_KEY\_OPTIONS、NO\_TABLE\_OPTIONS或NO\_FIELD\_OPTIONS。

mysqldump --complete-insert --force --add-drop-database --insert-ignore --hex-blob --databases mytest > mytest_db.sql

#### 1.1.5、使用–all-databases选项导出所有数据库 ####

mysqldump --all-databases --add-drop-database > db.sql

#### 1.1.6、使用–xml选项实现导出格式为XML ####

mysqldump  --xml  mytest1 >  /tmp/mytest1.xml

#### 1.1.7、使用–ignore-table选项实现导出时忽略指定表 ####

导出时可以选择忽略哪些表，即不导出哪些表，只需加上参数–ignore-table=db\_name.tbl\_name1、–ignore-table=db\_name.tbl\_name2。

mysqldump --databases=mytest,mytest1  --ignore-table=mytest.tb1,mytest1.tb2

#### 1.1.8、使用[mysql客户端][mysql]配合mysqldump实现通配符匹配表名的导出 ####

mysqldump不支持直接利用通配符导出多个表，但可以先用SELECT加通配符查询要导出的多张表的表名，将表名写到文件中，然后再用mysqldump读出表名再导出这些表。

#  1. 获得表名，写入文件
    mysql -N information_schema -e "select table_name from tables where table_name like 'prefix_%' " > tbs.txt
    #  2. 读取包含表名的文件，导出表
    mysqldump db 'cat tbs.txt' > dump.sql

#### 1.1.9、使用mysqldump导出数据的优化方式 ####

1.  选择MySQL服务器的I/O活动低的时候导出数据。
2.  I/O分离（数据盘和备份盘I/O分离）。
3.  输出到管道压缩（gzip）。
4.  –quick跳过内存缓冲（–opt默认启用）。
5.  从数据保留策略上想办法，把不需要修改的大量数据放到历史表中，而不是每次都备份。

### 1.2、使用mysql导入数据 ###

#### 1.2.1、基本导入方法 ####

*  mysqldump导出的SQL转储文件，可以用如下的形式将数据导入到数据库中：

mysql db_name < db_name.sql

#### 1.2.2、乱码问题 ####

*  字符集问题：转储文件（dump文件）里面一般指定了set names utf8，所以我们在导入的时候不再需要指定特殊的字符集。例外的情况是，有一些特殊的场合，SQL文件是以其他的字符集导出的，这个时候导入要注意保持文件的字符集、客户端字符集和连接的字符集的一致性。–default-character-set的意思是，客户端和连接都默认使用charset\_name字符集。

# 语法
    mysql --default-character-set=charset_name database_name < import_table.sql 
    # 示例
    mysql --default-character-set=gbk  < import_table.sql

*  如果mysql客户端输出的数据是乱码，那么请检查下客户端、连接的字符集配置。例如，我们使用SSH工具securecrt登录主机，然后使用mysql命令行工具连接MySQL服务器，mysql连接的默认配置可能是latin1，那么此时显示utf8的数据将会是乱码。这种情况下，可以在客户端运行set names utf8，并确认securecrt的字符编码是UTF-8，这样就可以正常显示utf8字符集的数据了。

## 2、SELECT INTO OUTFILE导出数据+LOAD DATA或mysqlimport导入数据 ##

### 2.1、使用SELECT INTO OUTFILE导出数据 ###

*  如果想要进行SQL级别的表备份，可以使用SELECT INTO OUTFILE命令语句。对于SELECT INTO OUTFILE，输出的文件不能先于输出存在。

SELECT * INTO OUTFILE '/tmp/testfile.txt' FROM t1;
    SELECT * INTO OUTFILE '/tmp/t1.txt'  FIELDS TERMINATED BY ':' OPTIONALLY ENCLOSED BY '+' ESCAPED BY '!'    FROM t1;

### 2.2、使用LOAD DATA导入数据 ###

#### 2.2.1、使用LOAD DATA导入数据的基本导出方法 ####

1.  一般来说，只要导出导入操作中使用的选项完全一致，用SELECT…INTO OUTFILE命令导出的文本文件就可以用LOAD DATA命令导入到数据表里去，不会发生任何变化。
2.  SELECT…INTO OUTFILE可以筛选记录，导出表数据到一个文件中，而LOAD DATA INFILE则是相反的操作，是读取这个文件导入表中。
3.  如果LOAD DATA命令导入的文件不在MySQL服务器上，而是想导入客户端所在的本地文件系统的文件时，则需要使用语法变体LOAD DATA…LOCAL INFILE…，也就是说，如果指定LOCAL关键词，则表明从客户主机读文件。如果没指定LOCAL，那么文件必须位于MySQL服务器上。

mysql> load data infile '/tmp/t2.txt' into table t2;

#### 2.2.2、导出导入csv格式文件 ####

*  导出导入csv格式的文本文件。csv格式的文件，即逗号分隔的数据文件。

# 生成csv文件
    mysql> select field_list from table_name  into outfile '/home/garychen/tmp/table_name_2.csv' fields terminated by ','  optionally enclosed by '"' lines terminated by '\n';
    # 导入文件
    mysql>  load data local infile '/home/garychen/tmp/table_name_2.csv' into table table_name fields terminated by ',' lines terminated by '\n'(field1,field2,field3);

#### 2.2.3、SELECT INTO OUTFILE导出+LOAD DATA导入方案的优势 ####

1.  相较于普通的mysql命令，LOAD DATA执行SQL文件导入的方式要快得多，一般可以达到每秒几万条记录的插入速度。
2.  如果有很多表，那么使用mysqldump会更简单。如果是导入个别大表，而且对于时间有很高的要求，那么LOAD DATA未尝不可。mysqldump默认的导出文件，其实已经包含了一些优化了，会有禁用key、启用key的操作，而且是一条INSERT语句包括多行记录的。

#### 2.2.4、LOAD DATA的优化 ####

*  将innodb\_buf fer\_pool\_size设置得更大些。
 *  将innodb\_log\_file\_size设置得更大些，如256MB。
 *  设置忽略二级索引的唯一性约束，SET UNIQUE\_CHECKS=0。
 *  设置忽略外键约束，SET FOREIGN\_KEY\_CHECKS=0。
 *  设置不记录二进制日志，SET sql\_log\_bin=0。
 *  按主键顺序导入数据。由于InnoDB使用了聚集索引，如果是顺序自增ID的导入，那么导入将会更快，我们可以把要导入的文件按照主键顺序先排好序再导入。
 *  对于InnoDB引擎的表，可以在导入前，先设置autocommit=0
 *  可以将大的数据文件切割为更小的多个文件，例如使用操作系统命令split切割文件，然后再并行导入数据。
 *  由于唯一索引（约束）对于我们导入数据的影响比较大，尤其对于大表导入，我们需要留意这一点。不要在大表上创建太多的唯一索引，主键、唯一索引不要包含太多列，否则导入数据将会很慢。

### 2.3、使用mysqlimport导入数据 ###

*  mysqlimport命令的语法格式如下：

# 语法
    mysqlimport databasename tablename.txt
    # 实例
    mysqlimport mytest /tmp/t2.txt

## 3、使用mysql程序的批处理模式导出数据 ##

使用mysql程序的批处理模式，支持比较灵活的导出数据，因为可以利用SQL语句。

1.  可以基于mysql的批处理模式，做语句级别的导出，以下两种方式等价：

# 方式一，-e选项
    mysql --batch --default-character-set=utf8 -e "SELECT * FROM t2;" mytest > t2.txt
    # 方式二，--execute
    mysql --batch --default-character-set=utf8 "--execute=SELECT * FROM t2;" mytest > t3.txt

1.  vertical选项，将查询结果按纵向导出：

mysql --batch --default-character-set=utf8  --vertical  -e "SELECT * FROM t2;" mytest > t2.txt

1.  html选项：将查询结果按html格式导出：

mysql --batch --default-character-set=utf8  --html  -e "SELECT * FROM t2;" mytest > t2.txt

1.  xml选项，将查询结果按xml格式导出：

mysql --batch --default-character-set=utf8  --xml  -e "SELECT * FROM t2;" mytest > t2.txt

## 4、使用Linux的split切割文件，加速导入数据 ##

*  split命令的作用是切割文件，如果不加入任何参数，默认情况下是以1000行的大小来分割的。

split [OPTION] [INPUT [PREFIX]]

*  以下以每个文件10000行记录进行切割，生成的文件名以test\_spl i t\_sub\_为前缀，因为文件有15万多条记录，最后且分为16个文件

split -l 10000 /tmp/t1.txt t1_split_sub_

![6dba49583b8ac1480b6dc7b3f7f9c6c9.png][]

## 5、总结 ##

MySQL导出导入数据(即数据转储)主要有以下三种方式：

1.  mysqldump导出+mysql导入：这种方式下导出的是SQL语句而非数据本身，所以导入时效率相对较低，但是胜在可以整库甚至多个库、多个表一起导出，适合整库的转储。
2.  SELECT INTO OUTFILE导出+LOAD DATA或mysqlimport导入：这种方式下导出的是纯数据，所以导入时效率会很高。适合单个大表的转储。
3.  mysql批处理模式导出+LOAD DATA或mysqlimport导入：这种方式下导出的也是纯数据，所以导入时效率会很高。优点除了导入效率高，由于是用SQL语句选择数据，所以很灵活，缺点则是使用门槛高。

注：设置MYSQL数据导出与导入，secure\_file\_priv参数设置

通过show variables like ‘%secure\_file\_priv%’;  
secure\_file\_priv参数说明

这个参数用来限制数据导入和导出操作的效果，例如执行LOAD DATA、SELECT … INTO OUTFILE语句和LOAD\_FILE()函数。这些操作需要用户具有FILE权限。  
secure\_file\_priv=null 不允许导入导出  
secure\_file\_priv=具体文件路径 只允许从这个路径导入导出  
secure\_file\_priv=空 可以从任意路径导入导出。

此时修改secure\_file\_priv的参数只需要修改mysql

### 此时,需要修改: ###

### **/etc/mysql/mysql.conf.d/mysqld.cnf** 文件，如图 ###

### 在末尾添加一句secure\_file\_priv=”/”即可将数据导出到任意目录 ###

[mysqldump]: https://so.csdn.net/so/search?q=mysqldump&spm=1001.2101.3001.7020
[32512cd12fd6bad7712add84761ad46c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/ef873f9c008842d5adb6cb42ccb81a2f.png
[mysql]: https://so.csdn.net/so/search?q=mysql%E5%AE%A2%E6%88%B7%E7%AB%AF&spm=1001.2101.3001.7020
[6dba49583b8ac1480b6dc7b3f7f9c6c9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/97fb7550b8e34ac586874b770b2f04eb.png