ITPub博客

首页 > 数据库 > MySQL > MySQL备份与恢复——基于OUTFILE /LOAD DATA 逻辑备份恢复

MySQL备份与恢复——基于OUTFILE /LOAD DATA 逻辑备份恢复

原创 MySQL 作者:龙山游龙 时间:2021-07-19 17:14:36 2 删除 编辑

一、说明

在OUTFILE /LOAD DATA 逻辑备份中强烈建议备份恢复均明确指定字符集CHARACTER SET charset_name,避免乱码的出现发生。默认读取character_set_database参数。

1.1 必须参数

使用该方式进行备份恢复,必须设置--secure-file-priv参数!!导出导入文件必须在指定目录下。

该参数为静态参数,重启生效!

下表为官方文档对该参数介绍:

Property

Value

Command-Line Format

--secure-file-priv=dir_name

System Variable

secure_file_priv

Scope

Global

Dynamic

No

Type

string

Default Value (>= 5.6.34)

platform specific

Default Value (<= 5.6.33)

empty string

Valid Values (>= 5.6.34)

empty string

dirname

NULL

Valid Values (<= 5.6.33)

empty string

dirname

二、 OUTFILE 备份

Outfile备份是服务端命令,导出的文件只能在服务器端。

2.1命令参数


参数 含义
1 --fields-terminated-by=str   列与列之间分隔
2 --fields-enclosed-by=char    在每一个字段的前后加上char这个字符     
3 --fields-optionally-enclosed-by=char   在每一个非数字的字段前后加上char这个字符

4

--fields-escaped-by=char     使用char去转义特殊字符
5 --lines-terminated-by=str     行与行之间分隔   #linux \n   windows \r\n

注:2与3不能同时使用

2.2备份案例   

2.1.1 备份全表

示例:

select * from test into outfile '/home/mysql/test.txt' CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

2.1.2 备份其中几列

示例:

select id,name from test into outfile '/home/mysql/test.txt' CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

2.1.3 备份处理之后的列

示例:

select id,name,id+1 from test into outfile '/home/mysql/test.txt' CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

2.1.4 按照条件备份

示例:

SELECT * into outfile '/home/mysql/test.txt ' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' FROM trans_transreq_20180828 a WHERE a.id > 999473433;

三、LOAD DATA 恢复

load data infile是MySQL中用来批量、快速导入数据的一种方式。必须在mysqld进程(服务)启动时运行。另外需要注意编码方式避免出现乱码现象。

由于安全原因,当读取位于服务器上的文件时,文件必须处于数据库目录或可被所有人读取。另外,为了对服务器上文件使用load data infile,在服务器主机上必须有file的权限。

3.1关键字说明

关键字

作用
LOW_PRIORITY
等到没有其他人读这个表的时候,才把数据插入
LOCAL 指定local表明从客户主机读文件
REPLACE | IGNORE 对唯一键记录的处理,不指定直接报错,后续忽略
PARTITION (partition_name,...) 将数据插入到指定分区
CHARACTER SET 字符集设置  默认读取character_set_database
FIELDS TERMINATED BY 字段值的分隔符,若不指定则默认为 '\t'
FIELDS ENCLOSED BY 字段值的包括符,若不指定则默认为 ''
FIELDS ESCAPED BY 字段值的转义字符,若不指定则默认为'\\'

LINES TERMINATED BY

指定行分隔符,若不指定则默认为为系统的默认行分隔符(‘\r\n‘ on windows,'\n' on linux)

LINES STARTING BY

若指定该值为xxx,则MySQL会自动去掉xxx及其前面的字符,若某行不包含xxx,则该行将被忽略,若不指定默认为' '

如:

xxx"abc",1  

something xxx"def",2  

"ghi",3  

实际:

("abc", 1), ("def", 2)

IGNORE LINES / ROWS

忽略文件开头的指定行,比如指定为2,那么MySQL只会解析并插入第三行及后面的数据

3.2 恢复案例

导入文件均由该导出命令生成

select * from test.test into outfile '/home/mysql/test.txt' CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

文件内容(截取)

 

3.2.1 恢复全表

前提条件:列数相同,类型符合要求

load data infile '/home/mysql/test.txt' into table test CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

3.2.2 恢复文件中其中几列

不需要第二列,即忽略第二列数据

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (id,@dummy,create_time);

3.2.3 对INT类型做数学运算

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (@dummy,name,create_time) set id= @dummy*2;

3.2.4 对字符类型做拼接

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (id,@dummy,create_time) set name=  concat (@dummy,'a');

3.2.5 对时间字段做变化

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (id,name,@dummy) set create_time=CURRENT_TIMESTAMP;

3.2.6 对多个变量做处理

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (@dummy1,@dummy2,create_time) set id = @dummy1*2 ,name=concat(@dummy2,'a');

3.2.7 使用查询值作为列

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (id,@dummy,create_time) set name= ( select 1 from dual);

3.2.8 使用查询值作为列

关键字LOCAL导入本地文件。对文件需要进行转义

LOAD DATA LOCAL INFILE 'D:\\test.txt' INTO TABLE test CHARACTER SET UTF8 FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30310891/viewspace-2782079/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
拥有超过9年以上的MySQL/Oracle数据库领域从业经验,TB级高并发数据库的管理经验。对于MySQL/Oracle内部原理、体系结构,数据页/块结构原理有着深刻认识;对于MySQL/ORACLE新特性、分布式高可用架构和性能调优有着丰富的的实战经验。擅长故障诊断以及数据库异常打开及数据灾难挽救。

注册时间:2015-06-28

  • 博文量
    77
  • 访问量
    109247