mysql备份之select ...into outfile

4.2.2 select ...into outfile

Select ...into oufile 也是一种逻辑备份方式，恢复速度比较快，但是只能备份表中的数据，不能包含表结构，完成备份后，表如果被删了，是无法恢复的，它把备份的数据导出到一个文本文件中，通过load data的方式，实现回复还原操作。

一般语法为：select coll,co12...from table_name into outfile ‘/path/备份名称’。

命令的使用可以自行去查找。

 

 

 

实验过程，

测试前需要查看当前mysql是否在使用secure_file_priv，不然可能会不被允许导出操作，如下图为null表示禁止在任何目录进行文件导出导入操作。需要修改配置文件my.cnf，在mysqld模块下加入一句secure_file_priv=，意思是允许把数据导出到任何目录，且注意权限问题，不然会被拒绝，目录可以改，然后重启mysql。

 

 

 

修改后为

 

 

 

 

进行测试操作

 

select * from test into outfile '/tmp/test.sql'; 

 

 

 

查看文件，已导出到目标目录。并且能查看到数据。

 

 

 

 

恢复测试，删除掉test表中所有数据。如下图

 

 

 

准备恢复，如下图：

load data infile '/tmp/test.sql' into table test.test;

 

 

 

 

 

发现，test表的数据恢复了。

 

 

 

4.2.3.load data与insert的插入速度对比

1，首先要准备一个表并插入十万条数据。

2，建表 create table L(id int,name varchar(8));

3，创建一个循环插入100000条数据程序在进入mysql执行。

delimiter //

 create procedure per2()

begin

declare i int;

set i=1;

while i<= 100000 do insert into test.L values(i,'aaaaa');

set i=i+1;

end while;

end//

4，执行存储过程

call per2();

//

5，等待一会查询select count（*） from test.L;

过程如下图所示

 

 

 

 

 

进行测试环节

1，先用select...into outfile 把十万条数据dump出来

 

 

 

 

2，模拟故障，truncate删掉L表数据，检查数据量已变成0

 

 

 

3，进行load data，导入数据：用时可以看到是0.373s

 

 

 

 

查看数据有没有恢复完整，如下图知道数据恢复完整。

 

 

 

Insert速度

接下来是进行mysqldump备份这个表，其恢复的过程实际就是insert过程

1，首先备份L表

 

 

 

 

 

2，开始恢复,在这里不用再去把L表删掉恢复，因为mysqldump备份的时候会在备份文件加入删掉表的语句，然后执行建表，insert语句

 

 

 总结：可以看到insert插入比select...into outfile慢了近两倍。

可知在大数据量的情况下的优势了。