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

痛定思痛。 2022-04-12 05:23 490阅读 0赞

转载自[骏马金龙][Link 1]的[http://www.cnblogs.com/f-ck-need-u/p/9013643.html][http_www.cnblogs.com_f-ck-need-u_p_9013643.html]

**MariaDB/MySQL备份恢复系列：**

[备份和恢复(一)：mysqldump工具用法详述][mysqldump]

[备份和恢复(二)：数据导入、导出][Link 2]

[备份和恢复(三)：xtrabackup用法和原理详述][xtrabackup]

# 1.导出、导入数据 #

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

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

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

![733013-20180422151719888-1512342209.png][]

所以应该将其设置为空(不指定任何值)或者指定一个目录，将来该目录中的所有文件都可以进行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，则：

## 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'

[Link 1]: http://www.cnblogs.com/f-ck-need-u/
[http_www.cnblogs.com_f-ck-need-u_p_9013643.html]: http://www.cnblogs.com/f-ck-need-u/p/9013643.html
[mysqldump]: https://blog.csdn.net/yuioplv/article/details/84563394
[Link 2]: https://blog.csdn.net/yuioplv/article/details/84676041
[xtrabackup]: https://blog.csdn.net/yuioplv/article/details/84676226
[733013-20180422151719888-1512342209.png]: /images/20220412/d45d766a1eb24f0492ebb26ca555c903.png