开篇
CSDN 推送有奖答题活动,点进去看到一个问题MySQLWorkbench在导入utf-8文件的时候报错Error Code: 1290,遂验证测试后回答一下,并整理记录于此。
1. 问题说明
遇到这三个报错的主要原因是某些人学艺不精,对 [secure_file_priv
]1 、 local_infile
2 系统参数,和 LOAD DATA
语句了解得不够深入、全面。有关这方面的问题,参见 6.1.6 Security Considerations for LOAD DATA LOCAL
3 。
再者,MySQL Workbench 使用的是 MySQL Connector/C++、MySQL 8.0 C API 。使用的连接选项为 OPT_LOCAL_INFILE
。详见 《MySQL Connector/C++ 1.1 Developer Guide》4 、《MySQL 8.0 C API Developer Guide》5 。
MySQL Server 在 Linux 平台中关于 secure_file_priv
和 local_infile
的默认配置为:
mysql> select @@secure_file_priv,@@local_infile;
+-----------------------+----------------+
| @@secure_file_priv | @@local_infile |
+-----------------------+----------------+
| /var/lib/mysql-files/ | 0 |
+-----------------------+----------------+
1 row in set (0.00 sec)
其他平台的默认值参考 MySQL 官方文档。
在解决问题前,你需要明确如下问题:
- 你的 MySQL Server 安装在哪?
- 你的 Client (比如 mysql 、MySQL Workbench) 安装在哪?是否和 Server 安装在同一台主机上。
- 你打算从客户端还是服务器端导入数据?
注意
从 MYSQL 客户端导入数据时与secure_file_priv
服务器系统变量的配置无关!仅与LOAD DATA LOCAL
语句和local_infile
选项有关。
2. 总解决办法
2.1 使用图形界面导入导出
这是对初学者最简单的方式,参考《MySQL Workbench Manual》7 ,使用表数据导入/导出向导。
笔者亲测可行,而无需修改任何服务器端或客户端配置。
这种方式实际执行的是预处理语句(Prepared Statements):
2.2 使用 mysql 客户端程序导入
使用独立安装的 mysql 客户端程序或 MySQL Workbench 捆绑安装的 mysql 客户端程序导入数据。注意要使用 local_file
,具体操作见 3.2.2 MySQL 客户端开启 local_infile
选项 。
MySQL Workbench 捆绑安装的 mysql 客户端程序位于 MySQL Workbench 的安装目录下。
./mysql.exe -uroot -p -hsource -P3307 --local-infile
2.3 使用 MySQL Workbench 客户端执行 LOAD DATA
导入
如果你非要通过在 MySQL Workbench 客户端执行 LOAD DATA LOCAL INFILE
导入,请参见 3.2.2.2 使用 MySQL Workbench 客户端 。
3. 解决 MySQL 报错
3.1 Error Code: 1290
完整报错信息为:
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
为了模拟这个报错产生的场景,笔者向选项文件中添加
[root@ic-source ~]$ vi /etc/my.cnf
secure_file_priv=NULL
mysql> select @@secure_file_priv,@@local_infile;
+--------------------+----------------+
| @@secure_file_priv | @@local_infile |
+--------------------+----------------+
| NULL | 0 |
+--------------------+----------------+
1 row in set (0.00 sec)
此报错产生的原因为错误使用 LOAD DATA
语句,导入位于客户端的数据文件时需要使用 LOCAL
关键字,不使用 LOCAL
关键字默认是在 MySQL 服务器端 导入文件。
load data infile 'I:/Develop/MySQL/Workspace/DataPumpDir/t1.csv' into table db5.t1 fields terminated by ',';
在 Windows 平台使用 mysql 测试:
使用 MySQL Workbench 测试:
3.2 Error Code: 3948
完整报错信息为:
Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides
这个报错是因为 MySQL 服务器和客户端 都没有开启 local_infile
选项。这是个动态全局系统变量,无需重启。这个参数在服务器端好配置。
3.2.1 MySQL 服务器开启 local_infile
选项
3.2.1.1 临时开启
SET GLOBAL local_infile=1;
3.2.1.2 永久开启
如果想要永久开启可以加到选项文件里(建议),也可以使用 SET PERSIST
8 。
[root@ic-source ~]$ vi /etc/my.cnf
#这是笔者的 MySQL 实例,默认的是“[mysqld]”
[mysqld@mycat]
local_infile=1
3.2.2 MySQL 客户端开启 local_infile
选项
3.2.2.1 使用 mysql 客户端程序
添加 --local-infile
命令行选项
PS I:\Develop\MySQL\MySQL Workbench> ./mysql.exe -uroot -p -hsource -P3307 --local-infile
向选项文件添加 local_infile
选项
Windows 平台选项文件6首选位置为 %WINDIR%\my.ini, %WINDIR%\my.cnf
,%WINDIR%
默认值为 C:\Windows
。笔者创建了 C:\Windows\my.cnf
选项文件,其内容如下:
[client]
loose_local_infile=1
[mysql]
#local_infile=1
no-auto-rehash
建议在 [client]
选项组下配置 loose_local_infile=1
,这样可以适用所有客户端,包括不支持 local_infile
选项的客户端。
3.2.2.2 使用 MySQL Workbench 客户端
MySQL Workbench 客户端可以使用 OPT_LOCAL_INFILE=1
,如下图所示:
依次点击 “Server” -> “Manage Access Settings” 。
再依次选择 “Connection” -> “Advanced” ,在 “Others” 文本框中添加 OPT_LOCAL_INFILE=1
。
修改完后,点击 “Close” 关闭即自动保存了配置。
然后需要重连服务器。
注意
目前 MySQL Workbench 客户端可以无法识别MYSQL_OPT_LOCAL_INFILE
选项,应该是还没有使用 MySQL 8.0 C API ,仍使用的是 MySQL Connector/C++ 。
3.3 Error Code: 2068
完整报错信息为:
Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
该报错的原因是在 MySQL 客户端会话时未启用 local_infile
选项,却在其中执行了 LOAD DATA LOCAL INFILE
语句。二者需配合使用。
例如我在 Win10 平台执行:
#Windows 平台二选一
load data local infile 'I:\\Develop\\MySQL\\Workspace\\DataPumpDir\\t1.csv' into table db5.t1 fields terminated by ',';
#这个语句适合 Linux、Mac、Windows 平台
load data local infile 'I:/Develop/MySQL/Workspace/DataPumpDir/t1.csv' into table db5.t1 fields terminated by ',';
3.4 Error Code: 2
完整报错信息为:
Error Code: 2. File 'I:DevelopMySQLWorkspaceDataPumpDir 1.csv' not found (OS errno 2 - No such file or directory)
此报错的原因是 MySQL 未找到指定文件或目录,可能是由于指定文件或目录不存在,也可能是文件或目录路径的格式不正确,比如 Windows 平台需要转义 \
,将 \
替换为 \\
或 /
。
测试结果如下:
参考文档:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv ↩︎
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile ↩︎
https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html ↩︎
https://dev.mysql.com/doc/connector-cpp/1.1/en/connector-cpp-connect-options.html ↩︎
https://dev.mysql.com/doc/c-api/8.0/en/mysql-options.html ↩︎
https://dev.mysql.com/doc/refman/8.0/en/option-files.html ↩︎ ↩︎ ↩︎
https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html ↩︎
https://dev.mysql.com/doc/refman/8.0/en/set-variable.html ↩︎