AWS RDS mysql LOAD DATA INFILE上的错误1045(28000) [英] ERROR 1045 (28000) on AWS RDS mysql LOAD DATA INFILE

问题描述

我在AWS RDS上使用MySQL 8.0.16. 当我运行SQL命令时:

I'm using MySQL 8.0.16 on AWS RDS. When I run SQL command:

mysql> LOAD DATA INFILE 't1.csv'  INTO TABLE t1 FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

从Shell运行mysqlimport:

Running mysqlimport from Shell:

$ mysqlimport --local --compress  -u admin -pXXXXXXX -h HOST.rds.amazonaws.com DB --verbose --lines-terminated-by="\n" --fields-terminated-by=, --fields-enclosed-by='"' t1.csv
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
Connecting to HOST.rds.amazonaws.com
Selecting database empresas
Loading data from LOCAL file: t1.csv into t1
empresas.cnaes: Records: 1209  Deleted: 0  Skipped: 0  Warnings: 1
Disconnecting from HOST.rds.amazonaws.com empresas

$ mysql -u admin -pXXXXXX-h HOST.rds.amazonaws.com DB -e "select count(*) from t1"   
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|        0 |
+----------+

我尝试过:

• 参数‘log_bin_trust_function_creators’= 1

• parameter ‘log_bin_trust_function_creators’ = 1

更改主密码

ref: https://forums.aws.amazon.com/message .jspa?messageID = 902265

摘要:

当我尝试从mysql提示符下导入数据时出现错误1045，并且当我尝试使用mysqlimport实用程序进行导入时没有错误，但是mysqlimport不会加载任何数据.

I had Error 1045 when I'd try to import data from mysql prompt, and I had no error when I'd tried to import with mysqlimport utility, but mysqlimport doesn't load no data.

有人知道发生了什么吗?

Someone knows wha's happening?

谢谢

推荐答案

tl; dr:使用此:

tl;dr: use this:

mysql [connect-options] --local-infile --execute "LOAD DATA LOCAL INFILE 'foo.csv' ...;"

LOAD DATA INFILE 用于加载数据来自MySQL服务器上文件的数据，而LOAD DATA LOCAL INFILE用于从运行客户端计算机(mysql CLI所在的计算机)上的文件加载数据.

LOAD DATA INFILE is used to load data from a file that is located on the MySQL server, while LOAD DATA LOCAL INFILE is used to load data from a file located on the client machine (where the mysql CLI) is running.

由于RDS是一项托管服务，因此您无法访问运行该服务的服务器，因此LOAD DATA INFILE是不可能的，因此需要LOCAL变体.

Because RDS is a managed service that gives you no access to the server where the service is running, LOAD DATA INFILE isn't possible, so the LOCAL variant is needed.

关于LOAD DATA LOCAL INFILE的一个重要细节是，所有工作仍由服务器而不是客户端完成，并且-在幕后-实际上，它们完全的工作方式与 c1>可以通过处理实际上位于服务器上的文件来工作...但是不同的是该文件如何在服务器上自行找到...该文件是客户端为此而流式传输到服务器的临时文件命令使用. (这意味着要用LOCAL加载大文件，服务器上仍需要足够的磁盘空间来存储原始文件和所生成行的表空间.)

An important detail about LOAD DATA LOCAL INFILE is that all of the work is still done by the server, not the client, and -- behind the scenes -- it actually works exactly the same way LOAD DATA INFILE works, by processing a file that's actually located on the server... but what is different is how that file came to find itself on the server... that file is a temporary file that the client streamed to the server for this command to use. (This means for loading large files with LOCAL, sufficient disk space is still required on the server for both the original file and the tablespace storage of the generated rows.)

从根本上讲，mysql CLI是一个程序，该程序建立与服务器进程的基于套接字的连接，并提供用于键入(或管道传递)SQL语句，将它们分别发送到服务器以执行并解压缩所有返回的SQL语句的外壳.结果...因此LOAD DATA LOCAL INFILE(一条SQL语句)需要客户端和服务器之间的奇怪交互作用，而这种交互作用并不像直觉所建议的那样起作用.实际上它是这样的:

Fundamentally, the mysql CLI is a program that establishes a socket-based connection to the server process and provides a shell for typing (or piping) SQL statements, sending them individually to the server for execution, and unpacking any returned results... so LOAD DATA LOCAL INFILE -- a SQL statement -- requires a curious interplay of client and server that does not work the way intuition might suggest. It actually works something like this:

(console) mysql> LOAD DATA LOCAL INFILE 'foo.csv' ...;
(socket) (client) "Hey, server, run this query: LOAD DATA LOCAL INFILE 'foo.csv' ...
(socket) (server) "Okay, client, I parsed that query without problems, so now I need you to start streaming me your local file 'foo.csv'.
(socket) (client) "Okay, server, here is that raw file..."

所以...很可能，服务器正在要求客户端流式传输文件，该文件的名称由服务器指定.显然，如果服务器代码是恶意的或客户端正在执行不受信任的查询，则这里有多种可能的利用方式.

So... yikes, the server is asking the client to stream a file, whose name is specified by the server. Clearly, there are multiple possible exploits here if the server code is malicious or the client is executing untrusted queries.

这就是为什么mysql CLI具有 --local-infile 选项.如果没有此选项，客户端代码将不会移交服务器请求的文件，服务器会发现并返回错误.

This is why the mysql CLI has the --local-infile option. Without this option, the client code won't hand over the file requested by the server, the server discovers this, and returns an error.

根据文档，在MySQL Server 8.0之前，这是您遇到的一般错误:

According to the docs, before MySQL Server 8.0, it's the generic error you encountered:

ERROR 1148 (42000): The used command is not allowed with this MySQL version.

从Server 8.0开始，应该更加具体:

Starting in Server 8.0, it should be more specific:

ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side

不清楚为什么MySQL 8.0的RDS返回旧错误.

It isn't clear why RDS for MySQL 8.0 is returning the old error.

另请参见 LOAD DATA LOCAL的安全性问题.

See also Security Issues with LOAD DATA LOCAL.

这篇关于AWS RDS mysql LOAD DATA INFILE上的错误1045(28000)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！