MYSQL数据导入导出

作为DBA，经常会碰到导入导出数据的需求，本篇就介绍了mysqldump和load data这两种方法。使用mysqldump时可以加上一些参数，dump需要的数据。重点介绍了使用load data导入时遇到的2个问题及解决方法。

1、 整个库的导出导入

用mysqldump命令，可以只导出表结构，也可以连数据一起导出，看需求。可以用mysqldump 导出整个库，也可以导出单个表。

例如：将整个test库导出，包括表结构、数据：

[mysql@my101 data]$ mysqldump -uroot test > test.txt 

将test库中导出的所有表、数据，导入到pyt库：

[mysql@my101 data]$ mysql -uroot pyt < test.txt

只导出表结构：

[mysql@my101 data]$ mysqldump -uroot –no-data test > test.txt

只导出a表的数据：

[mysql@my101 data]$ mysqldump -uroot –no-create-info test a> test.txt

【注意】：用mysqldump，如果不加任何参数，dump出来的文件，如果存在表则会先drop table，然后再create table ，最后insert数据。所以要特别注意。可以通过添加参数去掉drop或者直接去掉create，如mysqldump –no-create-info 、mysqldump –add-drop-table=’false’ ，当然最安全最正确的做法是导入之前先检查文件，是否存在drop等命令会破坏原有表。

2、 单表的导出导入

Mysqldump就不说了；说说load data。

一般数据导出txt格式：

Select * from … into outfile ‘a.txt’ fields terminated by ‘,’ optionally enclosed by ‘”‘;

也可以导出csv格式，方便用户以excel表格形式查看数据（这里字段间以逗号分隔）：

select * from xy2 into outfile ‘/tmp/xy2.csv’ fields terminated by ‘,’;

数据导入：

1）load data infile ‘/tmp/tab_a.txt’ into table tab_a fields terminated by ‘,’ optionally enclosed by ‘”‘ lines terminated by ‘\n’ (id,name,gmt_create) set gmt_modified=gmt_create;

其中：fields terminated by ‘,’是指字段间以逗号分隔；

optionally enclosed by ‘”‘表示：字符型日期型的字段会加””。如果是ENCLOSED BY ‘”‘的话，所有的字段都会加”， ENCLOSED BY描述的是字段的括起字符。比方以引号括起每一个字段。

Load时还可以加上括号指定字段，比如文件里没有gmt_modified值，还可以使用set来解决。

2）当要忽视原来表中的数据时，可以用replace 或者ignore：

load data infile ‘/tmp/w6.txt’ replace into table tab_b fields terminated by ‘\t’;

3）将excel文件导入：

先将excle文件保存为.csv文件；然后上传到目录下；通过load data infile ‘…’ into table …  fields terminated by ‘,’;导入数据。这里数据库的表可以不是CSV引擎的。

3、 使用load data时，遇到的2个问题

1）在windows下导出文件，要导入linux表出现警告。

在windows下导出的数据文件mod0.txt，上传到Linux，查看文件：

“10371313″,”t_maybe”,”07/17/2009 07:28:50″,”12/07/2009 20:20:25″,”abc”

“10812978″,”t_read”,”07/06/2009 21:32:16″,”12/03/2009 21:22:55″,”def”

………………………………………………………………

在Linux下load data 一直有警告：

mysql> load data infile ‘/tmp/mod0.txt’ into table a0  FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;

Query OK, 836644 rows affected, 65535 warnings (22.42 sec)

Records: 836644  Deleted: 0  Skipped: 0  Warnings: 2509933

mysql> select * from a0 limit 2;

+———–+——————+————+—————-+—————-+

| user_id   | real_name        | gmt_create          | gmt_modified        | nick              |

+———–+—————-+—————-+—————-+————-+

| 10371313 | t_maybe | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | abc”

“10812978 |

|         0 | t_read | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |def”

“238102432 |  

+———–+————-+—————-+—————–+————–+

2 rows in set (0.00 sec)

解决：windows下导出的数据行的结束多了符号，要用【dos2unix】命令转换成Linux格式

mysql@my101 tmp]$ dos2unix -o mod1.txt

dos2unix: converting file mod1.txt to UNIX format …

再导入就正确了：

mysql> load data infile ‘/tmp/mod0.txt’ into table a0  FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;

Query OK, 1673286 rows affected, 65535 warnings (33.74 sec)

Records: 1673286  Deleted: 0  Skipped: 0  Warnings: 3346572

（这里是时间格式不对，所以导致警告，没关系的。）

mysql> select * from a0 limit 2;

+———–+——————+—————-+—————+——–+

| user_id   | real_name        | gmt_create          | gmt_modified        | nick   |

+———–+——————+————-+——————-+——–+

| 10371313 | t_maybe | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | abc   |

| 10812978 | t_read  | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | def |

+———–+——————+—————–+—————-+——–+

 2）导出的文件和要导入的表字符集不一致时出现警告

一开始导入文件一直有警告：

root@pyt 04:36:49>load data infile ’/tmp/xy.txt’ into table xy  fields terminated by ’,' optionally enclosed by ’\” lines terminated by ’\n’;
Query OK, 188 rows affected, 906 warnings (0.00 sec)
Records: 188  Deleted: 0  Skipped: 0  Warnings: 607

root@pyt 04:37:08>show warnings;
+———+——+—————————————————————————————+
| Level   | Code | Message                                                                               |
+———+——+—————————————————————————————+
| Warning | 1366 | Incorrect string value: ’\xBC\xD3\xD6\xAE’ for column ’create_user’ at row 1          |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column ’update_time’ at row 1  |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column ’update_user’ at row 1  |
| Warning | 1366 | Incorrect string value: ’\xC0\xED\xB9\xA4\xBF\xC6…’ for column ’obj_title’ at row 1 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column ’memo’ at row 1 

主要就2个警告，一个是因为将空的字段导入到not null约束的列中，无大碍。另一个警告是字符串不正确，想到可能是因为字符集不一致造成的。

在linux直接less打开文件，发现是乱码，SecureCRT终端字符集是gbk的。查看linux环境字符集，

$locale

LANG=en_US.UTF-8

发现是utf8，通过export LANG=zh_CN.GBK 改成gbk后再看该文件，中文显示正常，说明该文件确实是gbk字符集的。

现在要将gbk文件导入utf8表怎么办呢？

有2个办法：一是将gbk文件转换成utf8文件再导入

用【iconv】命令改变文件的字符类型：

$iconv -f=gbk -t=utf-8 xy.txt

然后将终端外观字符集编码改成utf8就能看到中文显示正常，再执行load data就可以了。

另外一个方法是：直接将文件导入utf8表，加上【CHARACTER SET gbk】：

load data infile ‘/tmp/xy.txt’ into table xy CHARACTER SET gbk fields terminated by ‘,’ optionally enclosed by ‘\” lines terminated by ‘\n’; 

改变终端字符集，查看中文显示正常。

导出整个数据库结构和数据
mysqldump -h localhost -uroot -p123456 database > dump.sql
     
导出单个数据表结构和数据
mysqldump -h localhost -uroot -p123456  database table > dump.sql
      
导出整个数据库结构（不包含数据）
mysqldump -h localhost -uroot -p123456  -d database > dump.sql
     
导出单个数据表结构（不包含数据）
mysqldump -h localhost -uroot -p123456  -d database table > dump.sql
     
只导出数据(不包含表结构)
mysqldump -t database -h localhost -uroot -p123456 > dump.sql