1.load数据遇到报错1

mysql> load data infile "/root/mysql_test/mysql_t1data" into table t1;ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statementmysql>

secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

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

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

查看现在的配置

mysql> show global variables like '%secure_file_priv%';+------------------+-----------------------+| Variable_name    | Value                 |+------------------+-----------------------+| secure_file_priv | /var/lib/mysql-files/ |+------------------+-----------------------+1 row in set (0.00 sec)

不能直接修改

mysql> set global secure_file_priv='';ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variablemysql>

打开my.cnf，加入以下语句后重启mysql。

secure_file_priv=''

重启后进去查看

mysql> show global variables like '%secure_file_priv%';+------------------+-------+| Variable_name    | Value |+------------------+-------+| secure_file_priv |       |+------------------+-------+1 row in set (0.02 sec)

2.load数据遇到报错2

mysql> load data infile "/root/mysql_test/mysql_t1data" into table t1;ERROR 13 (HY000): Can't get stat of '/root/mysql_test/mysql_t1data' (OS errno 13 - Permission denied)mysql>

检索下数据目录在哪

mysql> show variables like '%datadir%';+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| datadir       | /var/lib/mysql/ |+---------------+-----------------+1 row in set (0.03 sec)mysql>

检查下这个目录的权限情况

[root@k8s-node3 mysql_test]# ll /var/lib/mysqltotal 168048-rw-r----- 1 mysql mysql       56 Nov 26 03:22 auto.cnf-rw-r----- 1 mysql mysql      178 Nov 26 03:49 binlog.000001-rw-r----- 1 mysql mysql      661 Nov 26 04:49 binlog.000002-rw-r----- 1 mysql mysql      155 Nov 27 01:30 binlog.000003-rw-r----- 1 mysql mysql      178 Nov 27 03:03 binlog.000004-rw-r----- 1 mysql mysql      178 Nov 27 03:03 binlog.000005-rw-r----- 1 mysql mysql      178 Nov 27 03:07 binlog.000006

在看看

/root/mysql_test/mysql_t1data的权限情况

[root@k8s-node3 mysql_test]# lltotal 8-rw-r--r-- 1 root root 117 Dec 13 02:09 mysql_t1-rw-r--r-- 1 root root  28 Dec 13 02:10 mysql_t1data[root@k8s-node3 mysql_test]#修改权限看看[root@k8s-node3 mysql_test]# chmod 777 *[root@k8s-node3 mysql_test]# lltotal 8-rwxrwxrwx 1 root root 117 Dec 13 02:09 mysql_t1-rwxrwxrwx 1 root root  28 Dec 13 02:10 mysql_t1data[root@k8s-node3 mysql_test]#

权限都改好了还是不对

mysql> load data infile '/root/mysql_test/mysql_t1data' into table t1;ERROR 13 (HY000): Can't get stat of '/root/mysql_test/mysql_t1data' (OS errno 13 - Permission denied)mysql>

网上查找资料发现mysql默认设置了使用/tmp作为临时读写目录

检索见下

mysql> show variables like '%tmp%';+----------------------------------+-----------+| Variable_name                    | Value     |+----------------------------------+-----------+| default_tmp_storage_engine       | InnoDB    || innodb_tmpdir                    |           || internal_tmp_disk_storage_engine | InnoDB    || internal_tmp_mem_storage_engine  | TempTable || slave_load_tmpdir                | /tmp      || tmp_table_size                   | 16777216  || tmpdir                           | /tmp      |+----------------------------------+-----------+7 rows in set (0.00 sec)mysql>

把文件移到/tmp目录下试试

[root@k8s-node3 ~]# mv mysql_test/ /tmp[root@k8s-node3 ~]# ll /tmp/mysql_test/total 8-rwxrwxrwx 1 mysql mysql 117 Dec 13 02:09 mysql_t1-rwxrwxrwx 1 mysql mysql  28 Dec 13 02:10 mysql_t1data[root@k8s-node3 ~]#

再load看看

mysql> load data infile '/tmp/mysql_test/mysql_t1data' into table t1;ERROR 1261 (01000): Row 1 doesn't contain data for all columnsmysql>

成功了。这个错误是别的问题了。

下一个问题来处理