MySQL运维10-MySQL数据的导入导出

文章目录

0、概述

MySQL数据的导入导出方案通常是配套的，例如：
方案一：使用mysqldump导出数据，再使用mysql客户端导入数据
方案二：使用SELECT INTO OUTFILE命令导出数据，再使用LOAD DATA或mysqlimport导入数据
方案三：使用mysql程序的批处理模式导出数据，再使用LOAD DATA或mysqlimport导入数据

1、mysqldump导出数据+mysql导入数据

1.1、使用mysqldump导出数据

1.1.1、使用–tables导出指定表

# 语法
mysqldump  db_name --tables  tb1_name  tb2_name  >  filemname.sql
# 实例
mysqldump mytest --tables t1 t2 > t1_t2.sql

1.1.2、使用–tab选项将表定义文件和数据文件分开导出

# 语法
mysqldump  db_name  --tab=dir
# 实例
mysqldump mytest1 --tab=/home/mysql/__test

1.1.3、使用–fields-terminated-by选项定义数据分隔符

以下导出时，数据值以逗号分隔

mysqldump mytest1 --tab=/home/mysql/__test   --fields-terminated-by=','

1.1.4、使用–databases选项导出整个库或多个库

参数说明如下：

• –complete-insert：导出的dump文件里，每条INSERT语句都包含列名。
• –force：即使出现错误，也要继续执行导出操作，会打印出错误。
• –insert-ignore：生成的INSERT语句是INSERT IGNORE的形式，如果导入此文件，即使出错了也仍然可以继续导入数据（当作警告）。
• –databases：类似–tables，后面可以跟多个值，即多个数据库名
• –compatible=name：导出的文件和其他数据库更兼容（但不确保），name的值可以是ANSI、MYSQL323、MYSQL40、POSTGRESQL、ORACLE、MSSQL、DB2、MAXDB、NO_KEY_OPTIONS、NO_TABLE_OPTIONS或NO_FIELD_OPTIONS。

mysqldump --complete-insert --force --add-drop-database --insert-ignore --hex-blob --databases mytest > mytest_db.sql

1.1.5、使用–all-databases选项导出所有数据库

mysqldump --all-databases --add-drop-database > db.sql

1.1.6、使用–xml选项实现导出格式为XML

mysqldump  --xml  mytest1 >  /tmp/mytest1.xml

1.1.7、使用–ignore-table选项实现导出时忽略指定表

导出时可以选择忽略哪些表，即不导出哪些表，只需加上参数–ignore-table=db_name.tbl_name1、–ignore-table=db_name.tbl_name2。

mysqldump --databases=mytest,mytest1  --ignore-table=mytest.tb1,mytest1.tb2

1.1.8、使用mysql客户端配合mysqldump实现通配符匹配表名的导出

mysqldump不支持直接利用通配符导出多个表，但可以先用SELECT加通配符查询要导出的多张表的表名，将表名写到文件中，然后再用mysqldump读出表名再导出这些表。

#  1. 获得表名，写入文件
mysql -N information_schema -e "select table_name from tables where table_name like 'prefix_%' " > tbs.txt
#  2. 读取包含表名的文件，导出表
mysqldump db 'cat tbs.txt' > dump.sql 

1.1.9、使用mysqldump导出数据的优化方式

1. 选择MySQL服务器的I/O活动低的时候导出数据。
2. I/O分离（数据盘和备份盘I/O分离）。
3. 输出到管道压缩（gzip）。
4. –quick跳过内存缓冲（–opt默认启用）。
5. 从数据保留策略上想办法，把不需要修改的大量数据放到历史表中，而不是每次都备份。

1.2、使用mysql导入数据

1.2.1、基本导入方法

• mysqldump导出的SQL转储文件，可以用如下的形式将数据导入到数据库中：

mysql db_name < db_name.sql

1.2.2、乱码问题

• 字符集问题：转储文件（dump文件）里面一般指定了set names utf8，所以我们在导入的时候不再需要指定特殊的字符集。例外的情况是，有一些特殊的场合，SQL文件是以其他的字符集导出的，这个时候导入要注意保持文件的字符集、客户端字符集和连接的字符集的一致性。–default-character-set的意思是，客户端和连接都默认使用charset_name字符集。

# 语法
mysql --default-character-set=charset_name database_name < import_table.sql 
# 示例
mysql --default-character-set=gbk  < import_table.sql

• 如果mysql客户端输出的数据是乱码，那么请检查下客户端、连接的字符集配置。例如，我们使用SSH工具securecrt登录主机，然后使用mysql命令行工具连接MySQL服务器，mysql连接的默认配置可能是latin1，那么此时显示utf8的数据将会是乱码。这种情况下，可以在客户端运行set names utf8，并确认securecrt的字符编码是UTF-8，这样就可以正常显示utf8字符集的数据了。

2、SELECT INTO OUTFILE导出数据+LOAD DATA或mysqlimport导入数据

2.1、使用SELECT INTO OUTFILE导出数据

• 如果想要进行SQL级别的表备份，可以使用SELECT INTO OUTFILE命令语句。对于SELECT INTO OUTFILE，输出的文件不能先于输出存在。

SELECT * INTO OUTFILE '/tmp/testfile.txt' FROM t1;
SELECT * INTO OUTFILE '/tmp/t1.txt'  FIELDS TERMINATED BY ':' OPTIONALLY ENCLOSED BY '+' ESCAPED BY '!'    FROM t1;

2.2、使用LOAD DATA导入数据

2.2.1、使用LOAD DATA导入数据的基本导出方法

1. 一般来说，只要导出导入操作中使用的选项完全一致，用SELECT…INTO OUTFILE命令导出的文本文件就可以用LOAD DATA命令导入到数据表里去，不会发生任何变化。
2. SELECT…INTO OUTFILE可以筛选记录，导出表数据到一个文件中，而LOAD DATA INFILE则是相反的操作，是读取这个文件导入表中。
3. 如果LOAD DATA命令导入的文件不在MySQL服务器上，而是想导入客户端所在的本地文件系统的文件时，则需要使用语法变体LOAD DATA…LOCAL INFILE…，也就是说，如果指定LOCAL关键词，则表明从客户主机读文件。如果没指定LOCAL，那么文件必须位于MySQL服务器上。

mysql> load data infile '/tmp/t2.txt' into table t2; 

2.2.2、导出导入csv格式文件

• 导出导入csv格式的文本文件。csv格式的文件，即逗号分隔的数据文件。

# 生成csv文件
mysql> select field_list from table_name  into outfile '/home/garychen/tmp/table_name_2.csv' fields terminated by ','  optionally enclosed by '"' lines terminated by '\n';
# 导入文件
mysql>  load data local infile '/home/garychen/tmp/table_name_2.csv' into table table_name fields terminated by ',' lines terminated by '\n'(field1,field2,field3);

2.2.3、SELECT INTO OUTFILE导出+LOAD DATA导入方案的优势

1. 相较于普通的mysql命令，LOAD DATA执行SQL文件导入的方式要快得多，一般可以达到每秒几万条记录的插入速度。
2. 如果有很多表，那么使用mysqldump会更简单。如果是导入个别大表，而且对于时间有很高的要求，那么LOAD DATA未尝不可。mysqldump默认的导出文件，其实已经包含了一些优化了，会有禁用key、启用key的操作，而且是一条INSERT语句包括多行记录的。

2.2.4、LOAD DATA的优化

• 将innodb_buf fer_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
• 可以将大的数据文件切割为更小的多个文件，例如使用操作系统命令split切割文件，然后再并行导入数据。
• 由于唯一索引（约束）对于我们导入数据的影响比较大，尤其对于大表导入，我们需要留意这一点。不要在大表上创建太多的唯一索引，主键、唯一索引不要包含太多列，否则导入数据将会很慢。

2.3、使用mysqlimport导入数据

• mysqlimport命令的语法格式如下：

# 语法
mysqlimport databasename tablename.txt
# 实例
mysqlimport mytest /tmp/t2.txt 

3、使用mysql程序的批处理模式导出数据

使用mysql程序的批处理模式，支持比较灵活的导出数据，因为可以利用SQL语句。

1. 可以基于mysql的批处理模式，做语句级别的导出，以下两种方式等价：

# 方式一，-e选项
mysql --batch --default-character-set=utf8 -e "SELECT * FROM t2;" mytest > t2.txt
# 方式二，--execute
mysql --batch --default-character-set=utf8 "--execute=SELECT * FROM t2;" mytest > t3.txt

1. vertical选项，将查询结果按纵向导出：

mysql --batch --default-character-set=utf8  --vertical  -e "SELECT * FROM t2;" mytest > t2.txt

1. html选项：将查询结果按html格式导出：

mysql --batch --default-character-set=utf8  --html  -e "SELECT * FROM t2;" mytest > t2.txt

1. xml选项，将查询结果按xml格式导出：

mysql --batch --default-character-set=utf8  --xml  -e "SELECT * FROM t2;" mytest > t2.txt

4、使用Linux的split切割文件，加速导入数据

• split命令的作用是切割文件，如果不加入任何参数，默认情况下是以1000行的大小来分割的。

split [OPTION] [INPUT [PREFIX]]

• 以下以每个文件10000行记录进行切割，生成的文件名以test_spl i t_sub_为前缀，因为文件有15万多条记录，最后且分为16个文件

split -l 10000 /tmp/t1.txt t1_split_sub_

5、总结

MySQL导出导入数据(即数据转储)主要有以下三种方式：

1. mysqldump导出+mysql导入：这种方式下导出的是SQL语句而非数据本身，所以导入时效率相对较低，但是胜在可以整库甚至多个库、多个表一起导出，适合整库的转储。
2. SELECT INTO OUTFILE导出+LOAD DATA或mysqlimport导入：这种方式下导出的是纯数据，所以导入时效率会很高。适合单个大表的转储。
3. mysql批处理模式导出+LOAD DATA或mysqlimport导入：这种方式下导出的也是纯数据，所以导入时效率会很高。优点除了导入效率高，由于是用SQL语句选择数据，所以很灵活，缺点则是使用门槛高。