在 MySQL 中加载数据 infile 的访问被拒绝
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2221335/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
access denied for load data infile in MySQL
提问by Brian
I use MySQL queries all the time in PHP, but when I try
我一直在 PHP 中使用 MySQL 查询，但是当我尝试时
LOAD DATA INFILE
I get the following error
我收到以下错误
#1045 - Access denied for user 'user'@'localhost' (using password: YES)
#1045 - 用户 'user'@'localhost' 访问被拒绝（使用密码：YES）
Does anyone know what this means?
有谁知道这是什么意思？
回答by jeremysawesome
I just ran into this issue as well. I had to add LOCALto my SQL statement.
我也刚遇到这个问题。我不得不添加LOCAL到我的 SQL 语句中。
For example, this gives the permission problem:
例如，这给出了权限问题：
LOAD DATA INFILE '{$file}' INTO TABLE {$table}
Add LOCALto your statement and the permissions issue should go away. Like so:
添加LOCAL到您的声明中，权限问题应该会消失。像这样：
LOAD DATA LOCAL INFILE '{$file}' INTO TABLE {$table}
回答by Yamir Encarnacion
I had this problem. I searched around and did not find a satisfactory answer. I summarise below the results of my searches.
我有这个问题。我四处寻找，没有找到满意的答案。我总结了我的搜索结果。
The access denied error could mean that:
访问被拒绝错误可能意味着：
- 'user'@'localhost' does not have the FILE privilege (
GRANT FILE on *.* to user@'localhost'); or, - the file you are trying to load does not exist on the machine running mysql server (if using LOAD DATA INFILE); or,
- the file you are trying to load does not exist on your local machine (if using LOAD DATA LOCAL INFILE); or,
- the file you are trying to load is not world readable (you need the file andall parent directories to be world-readable: chmod 755 directory; and, chmod 744 file.dat)
- 'user'@'localhost' 没有 FILE 权限 (
GRANT FILE on *.* to user@'localhost'); 或者， - 您尝试加载的文件在运行 mysql 服务器的机器上不存在（如果使用 LOAD DATA INFILE）；或者，
- 您尝试加载的文件在本地计算机上不存在（如果使用 LOAD DATA LOCAL INFILE）；或者，
- 您尝试加载的文件不是全局可读的（您需要该文件和所有父目录都是全局可读的：chmod 755 目录；以及 chmod 744 file.dat）
回答by shreyas-agrawal
Try using this command:
尝试使用此命令：
load data local infile 'home/data.txt' into table customer;
This should work. It worked in my case.
这应该有效。它在我的情况下有效。
回答by tanerkay
Ensure your MySQL user has the FILE privilege granted.
确保您的 MySQL 用户已授予 FILE 权限。
If you are on shared web hosting, there is a chance this is blocked by your hosting provider.
如果您使用共享网络托管，则您的托管服务提供商可能会阻止。
回答by Matthias Wuttke
The string from Lyon gave me a very good tip: On Windows, we need to use slahes and not backslashes. This code works for me:
Lyon 的字符串给了我一个很好的提示：在 Windows 上，我们需要使用斜杠而不是反斜杠。这段代码对我有用：
File tempFile = File.createTempFile(tableName, ".csv");
FileUtils.copyInputStreamToFile(data, tempFile);
JdbcTemplate template = new JdbcTemplate(dataSource);
String path = tempFile.getAbsolutePath().replace('\', '/');
int rows = template.update(MessageFormat
.format("LOAD DATA LOCAL INFILE ''{0}'' INTO TABLE {1} FIELDS TERMINATED BY '',''",
path, tableName));
logger.info("imported {} rows into {}", rows, tableName);
tempFile.delete();
回答by user7996813
I ran into the same issue, and solve it by folowing those steps :
我遇到了同样的问题，并通过以下步骤解决它：
- activate load_infile variable
- grand file permission to my custom mysql user
- deactivate secure_file_priv variable (my file was uploaded by the webserver to the /tmp folder which is of course not the secured directory of myslq /var/lib/mysql-file)
- 激活 load_infile 变量
- 我的自定义 mysql 用户的大文件权限
- 停用 secure_file_priv 变量（我的文件由网络服务器上传到 /tmp 文件夹，这当然不是 myslq /var/lib/mysql-file 的安全目录）
For this 3rd point, you can refer to : https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv
对于这第三点，您可以参考：https: //dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv
BR,
BR,
AD
广告
回答by Giacomo
This happened to me as well and despite having followed all the steps described by Yamir in his post I couldn't make it work.
这也发生在我身上，尽管按照 Yamir 在他的帖子中描述的所有步骤，我还是无法让它工作。
The file was in /tmp/test.csv with 777 permissions. The MySQL user had file permissions, LOCAL option was not allowed by my MySQL version, so I was stuck.
该文件位于 /tmp/test.csv 中，具有 777 权限。MySQL 用户有文件权限，我的 MySQL 版本不允许 LOCAL 选项，所以我被卡住了。
Finally I was able to solve the problem by running:
最后我能够通过运行来解决问题：
sudo chown mysql:mysql /tmp/test.csv
回答by Shantanu Khond
I found easy one if you are using command line
如果您使用命令行，我发现了一个简单的方法
Login asmysql -u[username] -p[password] --local-infile
登录为mysql -u[username] -p[password] --local-infile
then SET GLOBAL local_infile = 1;
然后 SET GLOBAL local_infile = 1;
select your database by use [db_name]
选择您的数据库 use [db_name]
and finally LOAD DATA LOCAL INFILE 'C:\\Users\\shant\\Downloads\\data-1573708892247.csv' INTO TABLE visitors_final_test FIELDS TERMINATED BY ','LINES TERMINATED BY '\r \n' IGNORE 1 LINES;
最后 LOAD DATA LOCAL INFILE 'C:\\Users\\shant\\Downloads\\data-1573708892247.csv' INTO TABLE visitors_final_test FIELDS TERMINATED BY ','LINES TERMINATED BY '\r \n' IGNORE 1 LINES;
回答by magula
I discovered loading MySQL tables can be fast and painless (I was using python / Django model manager scripts):
我发现加载 MySQL 表既快速又轻松（我使用的是 python/Django 模型管理器脚本）：
1) create table with all columns VARCHAR(n) NULL e.g.:
1）创建包含所有列 VARCHAR(n) NULL 的表，例如：
mysql> CREATE TABLE cw_well2( api VARCHAR(10) NULL,api_county VARCHAR(3) NULL);
mysql> CREATE TABLE cw_well2( api VARCHAR(10) NULL,api_county VARCHAR(3) NULL);
? 2) remove headers (first line) from csv, then load (if you forget the LOCAL, you'll get “#1045 - Access denied for user 'user'@'localhost' (using password: YES)”):
? 2) 从 csv 中删除标题（第一行），然后加载（如果您忘记了 LOCAL，您将收到“#1045 - 用户 'user'@'localhost' 访问被拒绝（使用密码：YES）”）：
mysql> LOAD DATA LOCAL INFILE "/home/magula6/cogswatch2/well2.csv" INTO TABLE cw_well2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
? ??-> ;
Query OK, 119426 rows affected, 19962 warnings? (3.41 sec)
mysql> LOAD DATA LOCAL INFILE "/home/magula6/cogswatch2/well2.csv" INTO TABLE cw_well2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
? ??-> ;
Query OK, 119426 rows affected, 19962 warnings? (3.41 sec)
? 3) alter columns:
? 3）改变列：
mysql> ALTER TABLE cw_well2 CHANGE spud_date spud_date DATE;?
mysql> ALTER TABLE cw_well2 CHANGE spud_date spud_date DATE;?
mysql> ALTER TABLE cw_well2 CHANGE latitude latitude FLOAT;
mysql> ALTER TABLE cw_well2 CHANGE latitude latitude FLOAT;
voilà!
瞧！
回答by David Grant
It probably means that the password you supplied for 'user'@'localhost'is incorrect.
这可能意味着您提供的密码'user'@'localhost'不正确。
