LOAD DATA INFILE '/var/www/upload/abc.csv.txt'
INTO TABLE alumni_import
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES;
-- 錯誤:
ERROR 29 (HY000): File '/var/www/upload/abc.csv.txt' not found (Errcode: 13)
Errcode: 13 = EACCES(權限被拒),非檔案不存在。
Ubuntu/Debian 預設啟用 AppArmor,MySQL 設定檔 /etc/apparmor.d/usr.sbin.mysqld 僅允許讀寫特定目錄(/var/lib/mysql/, /tmp/ 等),自定義目錄被阻擋。
vim /etc/apparmor.d/usr.sbin.mysqld
# 在檔案末尾 } 之前新增允許路徑
/var/www/upload/import/ r,
/var/www/upload/import/** rw,
重新載入設定:
systemctl reload apparmor
# 或
/etc/init.d/apparmor reload
aa-status | grep mysqld
# 應顯示 enforce 模式且包含新路徑
# 移到 /tmp 或 /var/lib/mysql-files/(MySQL 8.0 secure_file_priv 預設目錄)
cp /var/www/upload/abc.csv.txt /var/lib/mysql-files/
# SQL 改為
LOAD DATA INFILE '/var/lib/mysql-files/abc.csv.txt' ...
查看 secure_file_priv:
SHOW VARIABLES LIKE 'secure_file_priv';
ls -l /var/www/upload/abc.csv.txtsudo -u mysql cat /var/www/upload/abc.csv.txtchmod 755 /var/www/upload/importsecure_file_priv 未限制或目錄在允許列表內# 檢查狀態
getenforce
# 臨時放行
setsebool -P mysql_connect_any 1
# 或設定檔案上下文
semanage fcontext -a -t mysqld_db_t "/var/www/upload/import(/.*)?"
restorecon -Rv /var/www/upload/import