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

By | 2018年5月18日
目录
[隐藏]

1.导出、导入数据

load data infileselect 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'

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注