最新消息:时间抓起来说是金子,抓不住就是流水。

MYSQL数据导入导出

MySql 老子黑牵翻 30214浏览 6评论

作为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

转载请注明:LINUX服务器运维架构技术分享 » MYSQL数据导入导出

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

网友最新评论 (6)

  1. Very good submit, i definitely love this website, keep on it!
    Irmgard Polnau12年前 (2012-03-25)回复
  2. Wow, amazing blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your web site is magnificent, let alone the content!. Thanks For Your article about 301 Moved Permanently .
    Inspirational Quotes12年前 (2012-03-25)回复
  3. 301 Moved Permanently I was suggested this blog by my cousin. I'm not sure whether this post is written by him as no one else know such detailed about my difficulty. You are incredible! Thanks! your article about 301 Moved PermanentlyBest Regards Rolf
    Android Marketplace12年前 (2012-03-15)回复
  4. Apple now has Rhapsody as an app, which is a great start, but it is currently hampered by the inability to store locally on your iPod, and has a dismal 64kbps bit rate. If this changes, then it will somewhat negate this advantage for the Zune, but the 10 songs per month will still be a big plus in Zune Pass' favor.
    Kitchen Set Murah12年前 (2012-03-14)回复
  5. I appreciate the useful post!
    Learn Russian12年前 (2011-12-15)回复