GreatSQL用户手册

§ OUTFILE备份恢复

本文介绍GreatSQL数据库如何采用 OUTFILE 进行备份恢复。

OUTFILE 是指在GreatSQL数据库中执行select查询，并将查询结果直接输出到外部文件中的做法。

OUTFILE 导出的文件，可以利用 LOAD DATA 再恢复到数据库中。

§ 1. OUTFILE导出备份

执行 SELECT ... INTO OUTFILE 可以将本次查询结果导出到外部文件中，例如：

greatsql> SELECT * INTO OUTFILE '/tmp/OUTFILE-t1.txt' FROM t1;
1

指定的导出文件必须放在选项 secure_file_priv 指向的目录下，否则会报告类似下面的错误：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
1

如果目标文件已经存在，则会报告类似下面的错误：

ERROR 1086 (HY000): File '/tmp/OUTFILE-t1.txt' already exists
1

这时需要先将目标文件删除或改名以避免冲突。

导出的文件是纯文本格式，每列数据间默认用"\t"分隔开，如果有需要还可以自行指定行、列数据的分隔符，例如：

greatsql> SELECT * INTO OUTFILE '/tmp/OUTFILE-t1.txt' 
 FIELDS TERMINATED BY '$$$' 
 OPTIONALLY ENCLOSED BY '"' 
 ESCAPED BY '\'' 
 LINES TERMINATED BY '\n' 
 FROM t1;
1
2
3
4
5
6

上述几个参数的作用分别是：

  • 各列之间的间隔符是 $$$
  • 各列的数据用"引用起来
  • 遇到需要转义的地方加上\进行转移
  • 行数据间用\n分隔

表 t1 的DDL定义如下：

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(10) NOT NULL,
  `c2` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
1
2
3
4
5
6

下面是用默认模式，以及加上各个参数后不同导出结果对比：

1    c1    0.4356319591734856
2    c2    0.1"199'1518'5412
3    c3    0.2928499125808942
1
2
3

vs

1$$$1$$$"c1"$$$"0.4356319591734856"
2$$$2$$$"c2"$$$"0.1\"199'1518'5412"
3$$$3$$$"c3"$$$"0.2928499125808942"
1
2
3

注意到两个字符串列数据用引号"前后包围起来了，并且原数据中的单引号'、双引号"都做了转义（在其前面加上\，因为列之间已用双引号"包围，所以只需要转义双引号"，无需对单引号'做转义）。

另外，由于 OUTFILE 是采用SELECT查询方式备份的，因此可以指定只备份部分列，或加上WHERE条件只备份部分数据。例如：

greatsql> SELECT c1,c2 INTO OUTFILE '/tmp/OUTFILE-t1.txt' FROM t1;
1

对表t1只备份其中的 c1,c2 两列数据，不备份 id 列数据，那么在后续的 LOAD DATA 导入恢复时就需要做额外处理了。

§ 2. LOAD DATA导入恢复

可以通过 LOAD DATA 将 OUTFILE 导出的文件恢复到数据库中。

在上面的例子中，导出数据存储在文件 /tmp/OUTFILE-t1.txt 中，可以执行下面的命令完成导入恢复：

greatsql> LOAD DATA INFILE '/tmp/outfile-t1.txt' INTO TABLE t1;
1

如果导入的目标表中已有数据，这可能会造成重复冲突，产生类似下面的报错：

ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'
1

这就需要先将目标表中的数据清空后再导入。

如上面例子所示，导出时如果执行了一些分隔符参数，导入时也要再加上这些参数：

greatsql> LOAD DATA INFILE '/tmp/outfile-t1.txt' INTO TABLE t1
 FIELDS TERMINATED BY '$$$'
 OPTIONALLY ENCLOSED BY '"'
 ESCAPED BY '\\'
 LINES TERMINATED BY '\n';
1
2
3
4
5

提醒： 和OUTFILE一样，LOAD DATA指定的导入文件必须放在选项 secure_file_priv 指向的目录下，否则会报告类似下面的错误：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
1

在上面我们演示了只备份部分列数据的场景，这个备份文件在导入时需要做额外处理，需要指定导入的对象列，例如：

greatsql> LOAD DATA INFILE '/tmp/outfile-t1.txt' INTO TABLE t1(c1, c2);
1

因为主键列id定义成自增INT列，导入时如果不指定值也会实现自动填充。

还可以在 LOAD DATA 导入时对某列进行动态赋值，例如：

greatsql> LOAD DATA INFILE '/tmp/outfile-t1.txt' INTO TABLE t1(c1, c2) SET id=RAND()*10240;
1

那么id列填充的就是随机INT值了，例如下面这样的：

greatsql> SELECT * FROM t3;
+------+----+--------------------+
| id   | c1 | c2                 |
+------+----+--------------------+
| 1346 | c3 | 0.2928499125808942 |
| 1965 | c1 | 0.4356319591734856 |
| 3496 | c2 | 0.1"199'1518'5412  |
+------+----+--------------------+
1
2
3
4
5
6
7
8

§ 3. LOAD DATA并行导入

从GreatSQL 8.0.32-25版本开始，LOAD DATA执行并行导入，只需在导入时加上HINT SET_VAR(gdb_parallel_load=ON) 即可，例如：

greatsql> LOAD /*+ SET_VAR(gdb_parallel_load=ON) */ DATA INFILE '/tmp/outfile-t1.txt' INTO TABLE t1;
1

除此外，还支持指定并行线程数，以及每个分片文件大小，例如：

greatsql> LOAD /*+ SET_VAR(gdb_parallel_load=ON) SET_VAR(gdb_parallel_load_chunk_size=65536) SET_VAR(gdb_parallel_load_workers=16) */ DATA INFILE '/tmp/outfile-t1.txt' INTO TABLE t1;
1

更多关于并行LOAD DATA的详细信息请参考文档：并行LOAD DATA。

参考资料：

greatsql-wx