ERROR 1 (HY000): Can't create/write to file '/data

使用非DBA用戶進行l(wèi)oad data 操作，發(fā)現(xiàn)報錯。

MariaDB [temp_archive]> select * from t1 into outfile '/data/test.sql';

ERROR 1 (HY000): Can't create/write to file '/data/test.sql' (Errcode: 13 "Permission denied")

提示報錯。

報錯原因權(quán)限不足。

分析：

   權(quán)限有系統(tǒng)權(quán)限和數(shù)據(jù)庫權(quán)限。

/data 目錄權(quán)限 chown -R mysql:mysql /data/

實驗1：操作系統(tǒng)權(quán)限

創(chuàng)建hy用戶，使用root賬戶操作，文件保存目錄/data/：

useradd -u 1100 hy

password hy

su hy

[hy@XHY005116 /]$ mysql

MariaDB [(none)]> use test

Database changed

MariaDB [test]> select * from t111 into outfile '/data/t111.sql';

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> create table t112 like t111;

Query OK, 0 rows affected (0.08 sec)

MariaDB [test]> load data infile '/data/t111.sql' into table t112;

Query OK, 1 row affected (0.02 sec)                  

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

• 文件保存目錄/

MariaDB [test]> select * from t111 into outfile '/t111.sql';

ERROR 1 (HY000): Can't create/write to file '/t111.sql' (Errcode: 13 "Permission denied")

• 報錯：權(quán)限不足，此處可以分析出系統(tǒng)權(quán)限不足，導(dǎo)致報錯。

實驗2：

hy用戶登錄ya，ya賬戶讀寫權(quán)限,文件保存目錄 /data/。

MariaDB [test]> select * from t100 into outfile '/data/t100.sql';

ERROR 1045 (28000): Access denied for user 'ya'@'192.168.%' (using password: YES)

• 使用root用戶導(dǎo)出的文件，使用ya用戶導(dǎo)入

MariaDB [test]> load data infile '/data/t100.sql' into table t101;

ERROR 1045 (28000): Access denied for user 'ya'@'192.168.%' (using password: YES)

均報錯：判斷是數(shù)據(jù)庫權(quán)限問題。

嘗試授權(quán)file權(quán)限。

MariaDB [(none)]> grant file on *.* to 'ya'@'192.168.%';

MariaDB [(none)]> flush privileges;

[hy@XHY005116 data]$ mysql -uya -p123456 -h292.168.5.116

MariaDB [test]> select * from t101 into outfile '/data/t101.sql';

MariaDB [test]> load data infile '/data/t101.sql' into table t102;

Query OK, 6 rows affected (0.02 sec)          

成功：

文件保存目錄：/

MariaDB [test]> select * from t101 into outfile '/t101.sql';

ERROR 1 (HY000): Can't create/write to file '/t101.sql' (Errcode: 13 "Permission denied

報錯：權(quán)限問題。

• 結(jié)合：上兩個實驗可以得出“(Errcode: 13 "Permission denied” 錯誤是和操作系統(tǒng)目錄有關(guān)。

對應(yīng)mysql的權(quán)限