参考：https://blog.csdn.net/u011677147/article/details/64129606
参考：http://blog.itpub.net/31015730/viewspace-2152273/
导入数据报错
mysql> LOAD DATA INFILE '/tmp/1.txt' INTO TABLE hr.employees;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
原因：安装MySQL的时候限制了导入与导出的目录权限
查看secure_file_priv该变量的设置：
mysql> SHOW GLOBAL VARIABLES LIKE '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.01 sec)
secure_file_priv的值为/var/lib/mysql-files/，即导入导出的目录必须是/var/lib/mysql-files/
解决办法一：
临时修改这个值
mysql> SET GLOBAL secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
解决办法二：
把要导入的文件复制到/var/lib/mysql-files/目录下，然后导入，显然麻烦
[root@nfs ~]# cp /tmp/1.txt /var/lib/mysql-files/
[root@nfs ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> LOAD DATA INFILE '/var/lib/mysql-files/1.txt' INTO TABLE hr.employees;
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
解决办法三：
修改my.ini配置文件
选项说明：
secure_file_prive=null 限制mysqld 不允许导入导出
secure_file_priv=/var/lib/mysql-files/ 限制mysqld的导入导出只能发生在/var/lib/mysql-files/目录下
secure_file_priv='' 不对mysqld的导入导出做限制
[root@nfs ~]# vim /etc/my.cnf
[root@nfs ~]# egrep -v "^$|#" /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
secure_file_priv=''
重启mysql
[root@nfs ~]# systemctl restart mysqld
就可以了：
[root@nfs ~]# cat /tmp/2.txt
008,berry,john,example@qq.com,010222567,admin,20110302
[root@nfs ~]# mysql -uroot -p
Enter password:
mysql> LOAD DATA INFILE '/tmp/2.txt' INTO TABLE hr.employees FIELDS TERMINATED BY ',';
Query OK, 1 row affected (0.15 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from hr.employees;
+-------------+------------+-----------+-------------------+------------+------------+------------+
| employee_id | first_name | last_name | e_mail | telephone | department | hire_date |
+-------------+------------+-----------+-------------------+------------+------------+------------+
| 1 | eric | william | test@gmail.com | 1065103488 | tech | 2011-01-13 |
| 2 | quan | hope | NULL | NULL | NULL | NULL |
| 3 | wei | shen | NULL | NULL | NULL | NULL |
| 4 | boyi | liu | NULL | NULL | NULL | NULL |
| 5 | lucy | black | NULL | NULL | NULL | NULL |
| 6 | tangsh | quan | test@gmail.com | 1065103488 | tech | 2012-01-13 |
| 7 | ellis | jim | example@gmail.com | 1065103488 | sale | 2013-01-02 |
| 8 | berry | john | example@qq.com | 10222567 | admin | 2011-03-02 |
+-------------+------------+-----------+-------------------+------------+------------+------------+
8 rows in set (0.00 sec)
再次查看secure_file_priv的值
mysql> show global variables like "%secure%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | |
+--------------------------+-------+
3 rows in set (0.01 sec)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
