参考：/u011677147/article/details/64129606
参考：/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 -pEnter 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/f[root@nfs ~]# egrep -v "^$|#" /etc/f[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidsecure_file_priv=''
重启mysql
[root@nfs ~]# systemctl restart mysqld
就可以了：
[root@nfs ~]# cat /tmp/2.txt 008,berry,john,example@,010222567,admin,0302[root@nfs ~]# mysql -uroot -pEnter 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: 0mysql> select * from hr.employees;+-------------+------------+-----------+-------------------+------------+------------+------------+| employee_id | first_name | last_name | e_mail | telephone | department | hire_date |+-------------+------------+-----------+-------------------+------------+------------+------------+| 1 | eric | william | test@ | 1065103488 | tech | -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@ | 1065103488 | tech | -01-13 || 7 | ellis| jim | example@ | 1065103488 | sale | -01-02 || 8 | berry| john| example@ | 10222567 | admin| -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)
