一、说明
在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 stringdirnameNULL |
Valid Values (<= 5.6.33) |
empty stringdirname |
二、 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",1something 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';
