MariaDB/MySQL备份和恢复(二)：数据导入、导出

MariaDB/MySQL备份恢复系列：
备份和恢复(一)：mysqldump工具用法详述
备份和恢复(二)：导入、导出表数据
备份和恢复(三)：xtrabackup用法和原理详述

---

1.导出、导入数据

load data infile和select into outfile语句是配套的。select into outfile语句是将检索出来的数据按格式导出到文件中，数据迁移跨数据库系统时，该选项很有用，因为它可以指定分隔符。load data infile是将带有格式的数据文件导入到表中。

导出、导入数据时需要指定格式(如不指定，则使用默认)。格式涉及几个方面：字段分隔符、行分隔符、引用符号、转义符号。

还需注意一点，默认情况下(MySQL 5.6.34之后)这两个语句无法执行成功，因为全局变量secure_file_priv的默认值为null，它表示禁用这两种语句的导入导出。

所以应该将其设置为空(不指定任何值)或者指定一个目录，将来该目录中的所有文件都可以进行mysql file类的交互。当然，变量指定的目录必须已经存在，且mysql系统用户和组必须对该目录有读写权限。

mkdir /data
chown -R mysql.mysql /data

这个变量是全局静态变量，只能在mysqld实例未启动的时候才能修改。所以将其写入配置文件。

[mysqld]
secure-file-priv=/data
# 或者
# secure-file-priv=

查看变量。

select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /data/                    |
+---------------------------+

再看这两个语句的语法：

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]
 
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
        [export_options]
    [IGNORE number {LINES|ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]
 
 
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

其中'char'表示只能使用一个字符，'string'表示可以指定多个字符。

fields terminated by 'string'指定字段分隔符；enclosed by 'char'指定所有字段都使用char符号包围，如果指定了optionally则只用在字符串和日期数据类型等字段上，默认未指定；escaped by 'char'指定转义符。

lines starting by 'string'指定行开始符，如每行开始记录前空一个制表符；lines terminated by 'string'为行分隔符。

要注意，在几种情况下需要使用转义符：数据中含有转义符本身或者字段分隔符。当指定了字段引用符enclosed by时，如果数据中含有字段引用符，则也需要转义，若未指定enclosed by，则默认不使用字段引用符，所以无需转义。

以下为它们的默认值：

fileds terminated by '\t' enclosed by '' escaped by '\\'
lines terminated by '\n' starting by ''

看上去语法还挺复杂的，使用示例来说明就很清晰易懂了。

给定如下表结构和数据。

create or replace table t(id int primary key,sex char(3),name char(20),ins_day date);

insert into t values(1,'nan','longshuai1','2010-04-19'),
                    (2,'nan','longshuai2','2011-04-19'),
                    (3,'nv','xiaofang1','2012-04-19'),
                    (4,'nv','xiaofang2','2013-04-19'),
                    (5,'nv','xiaofang3','2014-04-19'),
                    (6,'nv','xiaofang4','2015-04-19'),
                    (7,'nv','tun\'er','2016-04-19'),
                    (8,'nan','longshuai3','2017-04-19');

1.1 select into outfile导出数据

使用默认设置：

select * from t into outfile '/data/t_data.sql';

\! cat /data/t_data.sql
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

指定字段分隔符","，使用单引号包围各字段，每行前加上制表符。

select * from t into outfile '/data/t_data1.sql' fields terminated by ',' enclosed by '\'' lines starting by '\t' terminated by '\n';

\! cat /data/t_data1.sql
        '1','nan','longshuai1','2010-04-19'
        '2','nan','longshuai2','2011-04-19'
        '3','nv','xiaofang1','2012-04-19'
        '4','nv','xiaofang2','2013-04-19'
        '5','nv','xiaofang3','2014-04-19'
        '6','nv','xiaofang4','2015-04-19'
        '7','nv','tun\'er','2016-04-19'
        '8','nan','longshuai3','2017-04-19'

1.2 load data infile导入数据

要导入格式化后的纯数据，可以使用load data infile，加载纯数据的插入方式比直接执行insert插入至少快20多倍。但在内部，它们其实是等价行为，load data infile也会触发insert相关触发器。

其中可以使用local关键字表示从客户端主机读取文件，如果没有指定local则表示从服务端主机读取文件。

fields和lines的相关选项和select ... into outfile是一样的，只不过load data infile多了几个选项。其中ignore N lines|rows表示忽略前N行数据不导入，col_name_or_user_var表示按此处给定的字段和顺序来导入数据，set col_name=expr表示对列进行一些表达式运算，如给某数值字段加5，给某字符串列尾部加上@qq.com字符等。

例如要加载如下文件到test.t表中。

cat /data/t_data.txt
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

首先删除表中数据，再导入。

truncate test. t;
load data infile '/data/t_data.sql' into table test.t fields terminated by '\t';

将如下包含字段分隔符","，字段引用符"'"，转义符"\"，行前缀"\t"的文件加载到test.t表中。

[root@xuexi ~]# cat /data/t_data1.sql
        '1','nan','longshuai1','2010-04-19'
        '2','nan','longshuai2','2011-04-19'
        '3','nv','xiaofang1','2012-04-19'
        '4','nv','xiaofang2','2013-04-19'
        '5','nv','xiaofang3','2014-04-19'
        '6','nv','xiaofang4','2015-04-19'
        '7','nv','tun\'er','2016-04-19'
        '8','nan','longshuai3','2017-04-19'

首先删除表中数据，然后加载。

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n';

若要忽略前两行，则：

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' ignore 2 rows;

如果想在id列值加上5，则：

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set id=id+5;

如果想name列后加上"@qq.com"字符串，则：

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set name=concat(name,'@qq.com');

如果想同时执行上面两个set，则：

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set name=concat(name,'@qq.com'), id=id+5;

1.3 mysqldump导出数据

和select into outfile功能类似的语句还有：此方法导出的数据中还包含了列名。

mysql -uroot -p123456 -e "select * from test.t">/tmp/t_data2.sql

cat /tmp/t_data2.sql
id      sex     name    ins_day
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

虽说select ... into outfile导出数据后可修改性和加载性非常强，但是毕竟没有导出结构。要导出结构，可以使用mysqldump的"--tab"选项，它既会导出表的结构定义语句到同表名的.sql文件中，还会导出数据到同表名的.txt文件中。

mysqldump -uroot -p123456 --tab /data test t;

ls -l /data/t.*
-rw-r--r-- 1 root  root  1408 Apr 19 14:46 /data/t.sql   # test.t表定义语句
-rw-rw-rw- 1 mysql mysql  211 Apr 19 14:46 /data/t.txt   # test.t表内数据

mysqldump的"--tab"选项同样可以指定各种分隔符。如"--fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by=..."。以下是指定字段分隔符为","。

mysqldump -uroot -p123456 --tab /data --fields-terminated-by=',' test t;

cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19

1.4 mysqlimport导入数据

mysqlimport和load data infile的本质是一样的。mysqlimport在执行时会像服务端发送load data infile来加载数据，并且mysqlimport支持多进程并行导入多张表的数据。

mysqlimport的语法和load data infile基本一致。不同的是它在MySQL/MariaDB的外部执行，且可以一次性并行多线程导入多张表(并非并行导入一张表)，所以能更快地导入所有数据。

mysqlimport [OPTIONS] database textfile...

注意：mysqlimport只能指定数据库名来导入，所以导入的文件名必须和数据库中的表名相对应(文件名后缀无所谓)。例如文件名为stu2.sql，而表名为student则无法导入，它会找stu2这个表。

例如，将以下格式的文件t.txt使用mysqlimport导入到test.t表中：

[root@xuexi ~]# cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19

[root@xuexi ~]# mysqlimport -uroot -p123456 --fields-terminated-by=',' test '/data/t.txt'

使用"--use-threads"选项可以指定导入线程数。

例如，下面指定两个线程，导入两张表到数据库test库中的t1和t2表中。

mysqlimport -uroot -p123456 --use-threads=2 --fields-terminated-by=',' test '/data/t1.txt' '/data/t2.txt'