MySQL 数据库升级到8.0，注意这些参数的坑！

原创陈举超 2024-06-22

739

问题现象:

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
准备测试数据:

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)

检查参数

mysql> show variables like '%secure\_file\_priv%';  
+------------------+---------------------------+  
| Variable\_name | Value |  
+------------------+---------------------------+  
| secure\_file\_priv | /db/mysqldata/3306/file/ |  
+------------------+---------------------------+  
1 row in set (0.00 sec)

导出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;

查看导出数据

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"

导入测试
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)

其他需要注意的参数:

除了local_infile参数外，升级到MySQL 8.0后，还有哪些参数默认值发生了变化:

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-server-defaults

Changed Server Defaults
Server changes

InnoDB changes

Performance Schema changes

Replication changes

Group Replication changes

###chenjuchao 20240622###
欢迎关注我的公众号《IT小Chen》