MySQL数据导出导入_vlambda技术博客

vlambda
2020-04-08

MySQL数据导出导入

MySQL 提供了好几种导出导入数据的方法：

导出

mysqldump
SELECT…INTO OUTFILE
mysql

导入

mysqlimport
LOAD DATA INFILE
mysql

其中， mysqldump 和 mysqlimport 是相反的操作， SELECT…INTO OUTFILE 和 LOAD DATA INFILE 是相反的操作。

使用 mysqldump 导出

1)导出指定的表

mysqldump test --tables tmp >tmp.dump

2) 分别导出 sql 文件和数据文件（数据值以 tab 分隔）

mysqldump --tab=/home/mysql/dump test

报错:

mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv 
option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

secure-file-priv参数是用来限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。

当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
当secure_file_priv的值没有具体值时，表示不对mysqld 的导入、导出做限制

查看数据库当前该参数的值：

mysql> show global variables like '%secure%'; 
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

修改配置文件my.cnf

#vi /usr/local/mysql/my.cnf 
[mysqld]
port = 13306
secure_file_priv =

重启数据库，此时可以正常导出了

[mysql@sdw3 dump]$ mysqldump --tab=/home/mysql/dump test
[mysql@sdw3 dump]$ ll -tr
-rw-rw-r-- 1 mysql mysql 1369 4月   6 10:40 tmp.sql
-rw-rw-rw- 1 mysql mysql   36 4月   6 10:40 tmp.txt
-rw-rw-rw- 1 mysql mysql   46 4月   6 10:40 tmp2.txt
-rw-rw-r-- 1 mysql mysql 1336 4月   6 10:40 tmp2.sql

3)导出某个库

mysqldump --complete-insert --force --add-drop-database --insert-ignore \
--hex-blob --databases test > test_db.sql

mysqldump 不能利用通配符导出多个表,如果表比较多时，可以把表放到一个文件，再用mysqldump导出:

# vi tbs.txt 
tmp 
tmp2

导出命令：

mysqldump test `cat tbs.txt` > dump.sql

使用mysql导入

mysqldump导出的文件，命令mysql导入

创建数据库

mysql> create database aaaa;
Query OK, 1 row affected (0.01 sec)

执行导入命令

mysql aaaa --default-character-set=utf8 < dump.sql

使用 SELECT INTO OUTFILE 导出

一般来说，只要导出导入操作中使用的选项完全一致，用 SELECT…INTO OUTFILE 命令导出的文本文件就可以用 LOAD DATA 命令导入到数据表里去，不会发生任何变化。

使用SELECT INTO OUTFILE导出数据文件时，之前存在文件的话会报错

示例1:

mysql -e "use test;SELECT * INTO OUTFILE '/tmp/tmp.txt' FROM tmp;"

示例2:

mysql -e "use test;
SELECT * INTO OUTFILE '/tmp/tmp2.txt' 
FIELDS TERMINATED BY ':' 
OPTIONALLY ENCLOSED BY '+' 
ESCAPED BY '!' 
from tmp2
"

示例3(数据库里执行导出csv文件):

SELECT * INTO OUTFILE '/tmp/tmp.csv' 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
from tmp;

使用 LOAD DATA 导入

示例1：

LOAD DATA INFILE '/tmp/tmp.txt'
INTO TABLE tmp;

示例2:

LOAD DATA INFILE '/tmp/tmp2.txt' 
INTO TABLE tmp2
FIELDS TERMINATED BY ':' 
OPTIONALLY ENCLOSED BY '+' 
ESCAPED BY '!' 
;

示例3（导入CSV文件）：

LOAD DATA INFILE '/tmp/tmp.csv'
INTO TABLE tmp
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n
;

LOAD DATA 的优化

LOAD DATA 的优化　相较于普通的 mysql 命令， LOAD DATA 执行 SQL 文件导入的方式要快得多，有时对于大表，我们仍然期望获得更高的导入速度，对于 InnoDB 的优化有：

将 innodb_buffer_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 ，例如如下语句

truncate table_name;
set autocommit = 0;
load data infile /path/to/file into table table_name...
commit;

可以将大的数据文件切割为更小的多个文件，例如使用操作系统命令 split 切割文件，然后再并行导入数据。

使用mysqlimport导入

mysqlimport 程序是一个将以特定格式存放的文本数据（如通过“select * into OUTFILE from …”所生成的数据文件）导入到指定的MySQL Server 中的工具程序，比如将一个标准的csv 文件导入到某指定数据库的指定表中。mysqlimport 工具实际上也只是“load data infile”命令的一个包装实现。

mysqlimport --local test /tmp/tmp.txt
test.tmp: Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

vlambda博客
学习文章列表