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

 

今天在学习MySQL时候，想要将文本文件的数据导入到数据库中，却发现一直报错，换了导入文本的路径也还是同样的错误，错误显示ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。

 select ......into outfile 是一种逻辑备份方法，它的恢复速度非常之快，比insert的插入速度还要快。它只能备份表中的数据，并不能包含表的结构。

然后在网上找解决办法，找的方法在Linux 下也不怎么好用，最后找到了解决Linux下MySQL文件导入出错的方法

出错的原因是因为在MySQL 5.7.6版本之后，导入文件只能在secure_file_priv指定的文件夹下（也有原因是因为权限不够）

方法一：

我们可以用show variables like '%secure%';命令显示文件目录

这样将导入文件放在 /var/lib/mysql-files/文件夹下，之后再从这里导入就可以了

导出文件时候，也是将 文件导出到这个文件夹里。

root@localhost:mysql3306.sock [(none)]>show global variables like '%secure%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /data/mysql/mysql3306/tmp/ |

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost:mysql3306.sock [(none)]>select * from qt into outfile '/data/mysql/mysql3306/tmp/qt.sql';
ERROR 1046 (3D000): No database selected
root@localhost:mysql3306.sock [(none)]>select * from qq.qt into outfile '/data/mysql/mysql3306/tmp/qt.sql'; //备份表
Query OK, 8 rows affected (0.01 sec)

[root@node1 ~]# cd /data/mysql/mysql3306/tmp/
[root@node1 tmp]# ll
total 4
-rw-rw-rw- 1 mysql mysql 56 Aug 13 06:06 qt.sql
[root@node1 tmp]# pwd
/data/mysql/mysql3306/tmp

恢复：

 

root@localhost:mysql3306.sock [(none)]>use qq;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

 

Database changed
root@localhost:mysql3306.sock [qq]>select * from qt;
Empty set (0.01 sec)

 

root@localhost:mysql3306.sock [qq]>LOAD DATA INFILE '/data/mysql/mysql3306/tmp/qt.sql' into table qq qt;
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 'qt' at line 1
root@localhost:mysql3306.sock [qq]>LOAD DATA INFILE '/data/mysql/mysql3306/tmp/qt.sql' into table qq.qt;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

 

root@localhost:mysql3306.sock [qq]>

 

 

如果显示ERROR 1261 (01000): Row 1 doesn't contain data for all columns
这个错误，是因为数据行不匹配，默认不能有空，用下列命令解决set sql_modul = 0;