MariaDB/MySQL备份和恢复(二):数据导入、导出
MariaDB/MySQL备份恢复系列:
备份和恢复(一):mysqldump工具用法详述
备份和恢复(二):导入、导出表数据
备份和恢复(三):xtrabackup用法和原理详述
本文目录:
1.导出、导入数据
1.1 select into outfile导出数据
1.2 load data infile导入数据
1.3 mysqldump导出数据
1.4 mysqlimport导入数据
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'
回到Linux系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到网站架构系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到数据库系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7586194.html
转载请注明出处:http://www.cnblogs.com/f-ck-need-u/p/9013643.html
注:若您觉得这篇文章还不错请点击右下角推荐,您的支持能激发作者更大的写作热情,非常感谢!
推荐阅读
-
mysql数据库和oracle数据库之间互相导入备份
-
MariaDB/MySQL备份和恢复(一):mysqldump工具用法详述
-
MariaDB/MySQL备份和恢复(二):数据导入、导出
-
MariaDB/MySQL备份和恢复(三):xtrabackup用法和原理详述
-
oracle数据库导出和oracle导入数据的二种方法(oracle导入导出数据)
-
MariaDB/MySQL备份和恢复(二):数据导入、导出
-
MariaDB/MySQL备份和恢复(三):xtrabackup用法和原理详述
-
MariaDB/MySQL备份和恢复(一):mysqldump工具用法详述
-
MySQL:如何导入导出数据表和如何清空有外建关联的数据表
-
MySQL数据库备份和恢复