Mysql系列-Mysql数据导入导出

引言

Mysql数据的导入导出是我们十分常见的场景， 常常用于进行数据迁移以及数据备份，了解常见的导入导出方式以及注意事项是十分必要的，Mysql提供了多种方式支持数据的导入和导出。

一、使用SELECT … INTO OUTFILE导出数据

select ...into outfile语法是mysql提供的一种数据导出的方案，支持自定义导出数据格式。
最简单的使用方法如下,将test表的所有数据全部导出到test.txt文件。

select * from test  into outfile '/tmp/test.txt';
1

更常用的方法是将表数据导出成csv格式，方便数据的查看以及重新导入

select a,b,c into outfile '/tmp/test.text' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from test;
1

数据使用""括起来，使用逗号分隔，数据记录使用\n作为换行符。

二、使用mysqldump导出数据

mysqldump是mysql官方提供的用于数据导出的程序，其导出的被包含create、insert等语句，可以用于重新执行将数据导入新的库中。常见的用法有:

• 导出包含创建表信息的数据表
  以下语句导出了数据库condingway的test_table表格，–tab指定了导出的文件存放的目录

mysqldump -u root -p --no-create-info --tab=/tmp codingway test_table > codingway_test_dump.sql
1

• 导出不包含创建表信息的数据表

mysqldump -u root -p --tab=/tmp codingway test_table > codingway_test_dump.sql
1

• 导出单个数据库的全部数据

mysqldump -u root -p codingway > codingway_dump.sql
1

• 导出所有数据库的全部数据

mysqldump -u root -p --all-databases > all_databases_dump.sql
1

• 导出数据到另外一台服务器

mysqldump -u root -p codingway | mysql -h other-host.com codingway
1

三、原表数据导入备份表

• 同库导入全部数据

insert into target_table select * from origin_table;
1

• 同库导入部分数据

insert into target_table (column1,column2, ...) select column1,column2, ... from origin_table;
1

• 同库导入全部数据

create table target_table select * from origin_table;
1

• 同库导入部分数据

create table target_table select column1,column2,...from origin_table;
1

• 不同库导入全部数据

create table  target_table select * from orign_db.origin_table;
1

• 不同库导入部分数据

create table target_table select column1,column2,...from origin_db.origin_table;
1

四、使用mysqlimport导入数据

mysqlimport一般用于将select …into file导出的数据文件导入到数据表中。

• 将数据导入到表中

mysqlimport -u root -p --local test test.txt
1

• 指定格式将数据导入到表中

mysqlimport -u root -p --local --fields-terminated-by=","  --lines-terminated-by="\n"  test test.txt
1

• 指定字段顺序将数据导入到表中

mysqlimport -u root -p --local --columns=b,c,a test test.txt
1

mysqlimport支持的参数选项如下:

五、使用mysql命令导入数据

导入codingway整个数据库

mysql -uUser -pPassword < codingway.sql
1

六、使用source命令导入数据

导入codingway整个数据库

source /home/codingway.sql
1

七、使用load data导入数据

• 将数据导入到当前数据库的test表

load data local infile 'test.txt' into table test;
1

• 指定格式将数据导入到当前数据库的test表

load data local infile 'test.txt' into table(a,b,c) test fields terminated by ',' lines terminated by '\n';
1

八、数据导入的注意事项

• 注意导出和导入的数据格式必须保持一致，例如均使用逗号分隔字段，使用\n分隔行，使用双引号包含数据
• 需要注意数据库集群的事务特性，例如MGR(Mysql Group Replication)集群适应小事务的情况，则导入数据时不宜导入过于大量的数据，应分多次小数据量导入。