MySQL备份select.....into outfile

一、首先查看参数，确定导出的目录

(root@localhost) [(none)]> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+
1 row in set (0.03 sec)

可以在配置文件中写入，写入完成重启数据库生效

[mysqld]
secure_file_priv=/tmp

二、导出表

(root@localhost) [dbt3]> select * from nation into outfile '/tmp/nation.sql';
Query OK, 25 rows affected (0.00 sec)

三、查看导出的数据，发现只是表数据

[root@mysql tmp]# cat nation.sql 
0    ALGERIA    0    final accounts wake quickly. special reques
1    ARGENTINA    1    idly final instructions cajole stealthily. regular instructions wake carefully blithely express accounts. fluffi
2    BRAZIL    1    always pending pinto beans sleep sil
3    CANADA    1    foxes among the bold requests
4    EGYPT    4    pending accounts haggle furiously. furiously bold accounts detect. platelets at the packages haggle caref
5    ETHIOPIA    0    fluffily ruthless requests integrate fluffily. pending ideas wake blithely acco
6    FRANCE    3    even requests detect near the pendin
7    GERMANY    3    blithely ironic foxes grow. quickly pending accounts are b

四、删除表

(root@localhost) [dbt3]> select * from nation_c;
Empty set (0.01 sec)

恢复测试

(root@localhost) [dbt3]> load data infile '/tmp/nation.sql' into table nation_c;
Query OK, 25 rows affected (0.01 sec)
Records: 25  Deleted: 0  Skipped: 0  Warnings: 0