• MySQL升级后报错:ERROR 3948 (42000): Loading local data is disabled
  • 发布于 1周前
  • 49 热度
    0 评论
问题现象:
MySQL 5.7.34升级到8.0.33后,LOAD DATA LOCAL INFILE 'XXX.csv';执行失败,报错如下,升级之前可以正常执行:
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

问题原因:
1.检查my.cnf配置文件,升级前后并没有配置local_infile参数;
2.检查当前local_infile参数值,默认值为OFF,而升级前5.7版本默认值是ON。
mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
可以看下官方文档:
MySQL 8.0 local_infile参数默认值:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile

此变量控制LOAD DATA语句的服务器端LOCAL功能。根据local_infile设置,服务器拒绝或允许在客户端启用了local的客户端加载本地数据。
MySQL 5.7 local_infile参数默认值:https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

解决方案:
local_infile参数可以动态调整
1.在线修改参数
mysql> set global local_infile=ON;
Query OK, 0 rows affected (0.00 sec)
2.修改my.cnf参数
vi my.cnf
###在secure-file-priv 参数下一行添加
local_infile=ON
参数测试过程如下:
数据库版本:MySQL 8.0.33
1.准备测试数据:
mysql> use cjc;
mysql> select * from t1;
+------+------+---------------------+
| id   | name | time                |
+------+------+---------------------+
|    1 | aaa  | 2024-06-03 15:18:00 |
|    2 | bbb  | 2024-06-03 15:18:06 |
|    3 | ccc  | 2024-06-03 15:18:10 |
+------+------+---------------------+
3 rows in set (0.00 sec)
2.检查参数
mysql> show variables like '%secure_file_priv%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| secure_file_priv | /db/mysqldata/3306/file/ |
+------------------+---------------------------+
1 row in set (0.00 sec)
3.导出csv,默认没有列名,通过UNION ALL手动添加列名
select 'id','name','time'
UNION ALL 
select id,name,time
INTO OUTFILE '/db/mysqldata/3306/file/t1a.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' 
from t1;
4.查看导出数据      
mysql> system cat /db/mysqldata/3306/file/t1a.csv
"id","name","time"
"1","aaa","2024-06-03 15:18:00"
"2","bbb","2024-06-03 15:18:06"
"3","ccc","2024-06-03 15:18:10"
5.导入测试
root用户可以直接导入
mysql -uroot -p cjc
检查local_infile参数为OFF
mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> create table t2 like t1;
mysql> select * from t2;
Empty set (0.00 sec)
不影响root用户的LOAD DATA导入操作:
mysql>
LOAD DATA INFILE '/db/mysqldata/3306/file/t1a.csv'
INTO TABLE t2
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from t2;
+------+------+---------------------+
| id   | name | time                |
+------+------+---------------------+
|    1 | aaa  | 2024-06-03 15:18:00 |
|    2 | bbb  | 2024-06-03 15:18:06 |
|    3 | ccc  | 2024-06-03 15:18:10 |
+------+------+---------------------+
3 rows in set (0.00 sec)
普通用户
mysql@cjc-db-01:/db/mysqldata/dbtmpfile$mysql -ucjc -p****** cjc
mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
需要添加LOCAL关键字
mysql>
LOAD DATA INFILE '/db/mysqldata/3306/file/t1a.csv'
INTO TABLE t2
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
ERROR 1045 (28000): Access denied for user 'cjc'@'localhost' (using password: YES)
默认没有导出权限
mysql>
LOAD DATA LOCAL INFILE '/db/mysqldata/3306/file/t1a.csv'
INTO TABLE t2
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
修改参数
mysql> set global local_infile=ON;
Query OK, 0 rows affected (0.00 sec)
需要先退出
mysql>
LOAD DATA LOCAL INFILE '/db/mysqldata/3306/file/t1a.csv'
INTO TABLE t2
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
指定--local-infile参数重新登录
mysql@cjc-db-01:/db/mysqldata/dbtmpfile$mysql -ucjc -p****** cjc --local-infile
可以正常导出了
mysql>
LOAD DATA LOCAL INFILE '/db/mysqldata/3306/file/t1a.csv'
INTO TABLE t2
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from  t2;
+------+------+---------------------+
| id   | name | time                |
+------+------+---------------------+
|    1 | aaa  | 2024-06-03 15:18:00 |
|    2 | bbb  | 2024-06-03 15:18:06 |
|    3 | ccc  | 2024-06-03 15:18:10 |
+------+------+---------------------+
3 rows in set (0.00 sec)

用户评论