select … into outfile 备份恢复（load data）以及mysqldump时

select … into outfile 'path' 备份

此种方式恢复速度非常快，比insert的插入速度要快的多，他跟有备份功能丰富的mysqldump不同的是，他只能备份表中的数据，并不能包含表的结构。如果备份完成之后，表被drop,是无法实现恢复操作的（除非有表结构）。

mysql> select * from t1 into outfile '/mydata/mysql/mysql3307/data/t1.sql';

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

mysql> show variables like '%secure%';

+--------------------------+-------+

| Variable_name??????????? | Value |

+--------------------------+-------+

| require_secure_transport | OFF?? |

| secure_auth????????????? | ON??? |

| secure_file_priv???????? | NULL? |

+--------------------------+-------+

3 rows in set (0.00 sec)

mysql> set secure_file_priv='/tmp';

ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

mysql> set session secure_file_priv=='/tmp';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=='/tmp'' at line 1

mysql>

关闭MYSQL数据库，设置secure_file_priv 目录。

[root@mysql5 ~]# mysqladmin -S /tmp/mysql3307.sock -uroot -pmysql shutdown

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

[root@mysql5 ~]#

[root@mysql5 ~]#

[root@mysql5 ~]# ps -ef |grep mysql

root????? 3506? 2071? 0 01:24 pts/1??? 00:00:00 grep mysql

[root@mysql5 ~]#

vi /etc/my3307.cnf

[mysqld]里面加入

secure_file_priv=/tmp

启动后查看

mysql> show global variables like '%secu%';

+--------------------------+-------+

| Variable_name??????????? | Value |

+--------------------------+-------+

| require_secure_transport | OFF?? |

| secure_auth????????????? | ON??? |

| secure_file_priv???????? | /tmp/ |

+--------------------------+-------+

3 rows in set (0.01 sec)

select * from t1 into outfile '/tmp/t1_onlydata.sql';

mysql> select * from t1 into outfile '/tmp/t1_onlydata.sql';

Query OK,972864 rows affected (1.12 sec)

mysql> desc t1;

+-------+--------+------+-----+---------+-------+

| Field | Type?? | Null | Key | Default | Extra |

+-------+--------+------+-----+---------+-------+

| id??? | int(4) | YES? | MUL | NULL??? |?????? |

+-------+--------+------+-----+---------+-------+

1 row in set (0.00 sec)

?

全是文本文件数据。

select … into outfile 'path' 恢复

清除t1表数据，并进行恢复

mysql> truncate table t1;

Query OK,0 rows affected (0.04 sec)

mysql> desc t1;

+-------+--------+------+-----+---------+-------+

| Field | Type?? | Null | Key | Default | Extra |

+-------+--------+------+-----+---------+-------+

| id??? | int(4) | YES? | MUL | NULL??? |?????? |

+-------+--------+------+-----+---------+-------+

1 row in set (0.00 sec)

?

LOAD DATA恢复数据（只能恢复数据）

语法：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

??? [REPLACE | IGNORE]

??? INTO TABLE tbl_name

??? [PARTITION (partition_name [,partition_name] ...)]

??? [CHARACTER SET charset_name]

??? [{FIELDS | COLUMNS}

??????? [TERMINATED BY 'string']

??????? [[OPTIONALLY] ENCLOSED BY 'char']

??????? [ESCAPED BY 'char']

??? ]

??? [LINES

??????? [STARTING BY 'string']

??????? [TERMINATED BY 'string']

??? ]

??? [IGNORE number {LINES | ROWS}]

??? [(col_name_or_user_var

??????? [,col_name_or_user_var] ...)]

??? [SET col_name={expr | DEFAULT},

??????? [,col_name={expr | DEFAULT}] ...]

?

load data INFILE '/tmp/t1_onlydata.sql'? INTO TABLE test.t1;

?

查看表数据：

?

恢复成功。

load data 与insert的插入速度对比

于是，我猜想可以用mysqldump进行表结构的备份，用select …into outfile 备份数据，load data 恢复数据，测试两者速度。

创建一张表。

?

delimiter //

create procedure per2()

begin

declare i int;

set i=1;

while i <= 1000000 do

insert into test.L values(i,'aaaaa');

set i=i+1;

end while;

end

?//

执行存储过程：

call per2();

//

查看数据（实际我只插入仅14万行数据）

?

mysqldump备份元数据文件

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --single-transaction --set-gtid-purged=OFF test L -d > /tmp/L_meta.sql

?

?select … into outfile 'path' 备份

select * from test.L into outfile '/tmp/20180525test_Ldata.sql'

?

?mysqldump备份整个表

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --single-transaction --set-gtid-purged=OFF test L? > /tmp/L_table.sql

?

恢复时间对比

1、MYSQLdump先恢复表结构（或者直接建表）

先删除，恢复表结构。

?

恢复表结构：

?

这核时间很快，就是建一张表的时间，不计算进去对整体时间没有影响。

例如我建表：

?

建表时间0.03s。

2、load data恢复时间

load data INFILE '/tmp/20180525test_Ldata.sql' INTO TABLE test.L;

?

时间是1.59s。

??

3、MYSQLDUMP这种插入方式恢复

time mysql -S /tmp/mysql3307.sock -uroot -pmysql test < /tmp/L_table.sql

?

将近2s MYSQLDUMP恢复时间。

总结

元数据恢复+LOAD DATA时间一共

T1=0.03+1.59=1.62s

MYSQLDUMP恢复一共花了

T2=1.99s

对于14万数据，load data比mysqldump快了近1/4，对于大数据量，应该快更多，在允许的情况下，可以利用元数据（或者表结构），配合LOAD DATA恢复单表。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!