mysql在线导出_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 

使用 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