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

一、说明

在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 备份全表

示例：

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 备份其中几列

示例：

1	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 备份处理之后的列

示例：

1	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 按照条件备份

示例：

1	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 恢复案例

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

1	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 恢复全表

前提条件：列数相同，类型符合要求

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 恢复文件中其中几列

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

1	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类型做数学运算

1	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 对字符类型做拼接

1	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 对时间字段做变化

1	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 对多个变量做处理

1	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 使用查询值作为列

1	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导入本地文件。对文件需要

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