mysql select .... into outfile 数据迁移方式

mysql> select * from tx1
+----+------+-------+
| id | c1 | c2 |
+----+------+-------+
| 1 | aaa | aaa2 |
| 2 | bbb2 | bbb2 |
| 3 | ccc5 | ccc2 |
| 4 | ddd | ddd4 |
| 8 | hhh | hhhh8 |
+----+------+-------+
5 rows in set (0.01 sec)

mysql> select * from tx1 into outfile '/tmp/tx1.sql' ;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决方法如下：

查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。

secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。

secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

 

 该参数不能动态修改，那只能修改配置参数文件my.cnf

在my.cnf 最后添加一行 :  secure_file_priv=''

 

 您看数据又回来了鸟

mysql> delete from tx1 ;
Query OK, 5 rows affected (0.01 sec)

mysql> select * from tx1 ;
Empty set (0.00 sec)

mysql> load data infile '/tmp/tx1.sql' into table test.tx1 ;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from tx1 ;
+----+------+-------+
| id | c1 | c2 |
+----+------+-------+
| 1 | aaa | aaa2 |
| 2 | bbb2 | bbb2 |
| 3 | ccc5 | ccc2 |
| 4 | ddd | ddd4 |
| 8 | hhh | hhhh8 |
+----+------+-------+
5 rows in set (0.00 sec)

且load data 相比较insert 插入数据快10倍 。