MySQL 如何在亚马逊 RDS 上“加载数据文件”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1641160/
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
how to 'load data infile' on amazon RDS?
提问by eyberg
not sure if this is a question better suited for serverfault but I've been messing with amazon RDS lately and was having trouble getting 'file' privileges to my web host mysql user.
不确定这是否是一个更适合 serverfault 的问题，但我最近一直在使用 amazon RDS，并且无法获得我的网络主机 mysql 用户的“文件”权限。
I'd assume that a simple:
我假设一个简单的：
grant file on *.* to 'webuser@'%';
would work but it does not and I can't seem to do it with my 'root' user as well. What gives? The reason we use load data is because it is super super fast for doing thousands of inserts at once.
会工作，但它不会，而且我似乎也无法使用我的“root”用户来执行此操作。是什么赋予了？我们使用加载数据的原因是因为它一次执行数千次插入非常快。
anyone know how to remedy this or do I need to find a different way?
任何人都知道如何解决这个问题，还是我需要找到不同的方法？
This page, http://docs.amazonwebservices.com/AmazonRDS/latest/DeveloperGuide/index.html?Concepts.DBInstance.htmlseems to suggest that I need to find a different way around this.
这个页面http://docs.amazonwebservices.com/AmazonRDS/latest/DeveloperGuide/index.html?Concepts.DBInstance.html似乎表明我需要找到一种不同的方法来解决这个问题。
Help?
帮助？
UPDATEI'm not trying to import a database -- I just want to use the file load option to insert several hundred-thousand rows at a time.
更新我不想导入数据库——我只想使用文件加载选项一次插入几十万行。
after digging around this is what we have:
在挖掘之后，这就是我们所拥有的：
mysql> grant file on *.* to 'devuser'@'%';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
mysql> select User, File_priv, Grant_priv, Super_priv from mysql.user;
+----------+-----------+------------+------------+
| User | File_priv | Grant_priv | Super_priv |
+----------+-----------+------------+------------+
| rdsadmin | Y | Y | Y |
| root | N | Y | N |
| devuser | N | N | N |
+----------+-----------+------------+------------+
回答by chris finne
You need to use LOAD DATA LOCAL INFILEas the file is not on the MySQL server, but is on the machine you are running the command from.
您需要使用，LOAD DATA LOCAL INFILE因为该文件不在 MySQL 服务器上，而是在您运行命令的机器上。
As per comment below you may also need to include the flag:
根据下面的评论，您可能还需要包含标志：
--local-infile=1
回答by user1493124
For whatever it's worth... You can add the LOCAL operand to the LOAD DATA INFILE instead of using mysqlimportto get around this problem.
不管它的价值如何......您可以将 LOCAL 操作数添加到 LOAD DATA INFILE 而不是mysqlimport用来解决这个问题。
LOAD DATA LOCALINFILE ...
加载数据本地文件...
This will work without granting FILE permissions.
这将在不授予 FILE 权限的情况下工作。
回答by David Snabel-Caunt
Pretty sure you can't do it yet, as you don't have the highest level MySQL privileges with RDS. We've only done a little testing, but the easiest way to import a database seems to be to pipe it from the source box, e.g.
很确定您还不能这样做，因为您没有 RDS 的最高级别 MySQL 权限。我们只做了一点测试，但导入数据库的最简单方法似乎是从源框中通过管道传输它，例如
mysqldump MYDB | mysql -h rds-amazon-blah.com --user=youruser --pass=thepass
回答by Brian Luft
I ran into similar issues. I was in fact trying to import a database but the conditions should be the same - I needed to use load data due to the size of some tables, a spotty connection, and the desire for a modest resume functionality.
我遇到了类似的问题。我实际上是在尝试导入数据库，但条件应该是相同的 - 由于某些表的大小、不稳定的连接以及对适度恢复功能的渴望，我需要使用加载数据。
I agree with chris finne that not specifying the local option can lead to that error. After many fits and starts I found that the mk-parallel-restore tool from Maatkitprovided what I needed with some excellent extra features. It might be a great match for your use case.
我同意 chris finne 的观点，即不指定本地选项会导致该错误。经过多次反复试验，我发现Maatkit的 mk-parallel-restore 工具提供了我需要的一些出色的额外功能。它可能非常适合您的用例。
回答by Red Boy
Importing bulk data into Amazon MySQL RDS is possible two ways. You could choose anyone of below as per your convenience.
可以通过两种方式将批量数据导入 Amazon MySQL RDS。您可以根据自己的方便选择以下任何一个。
Using Import utility.
mysqlimport --local --compress -u <user-name> -p<password> -h <host-address> <database-name> --fields-terminated-by=',' TEST_TABLE.csv --Make sure, here the utility will be inserting the data into TEST_TABLE only.Sending a bulk insert SQL by piping into into mysql command.
mysql -u <user-name> -p<password> -h <host-address> <database-name> < TEST_TABLE_INSERT.SQL --Here file TEST_TABLE_INSERT.SQL will have bulk import sql statement like below --insert into TEST_TABLE values('1','test1','2017-09-08'),('2','test2','2017-09-08'),('3','test3','2017-09-08'),('3','test3','2017-09-08');
使用导入实用程序。
mysqlimport --local --compress -u <user-name> -p<password> -h <host-address> <database-name> --fields-terminated-by=',' TEST_TABLE.csv --Make sure, here the utility will be inserting the data into TEST_TABLE only.通过管道进入 mysql 命令发送批量插入 SQL。
mysql -u <user-name> -p<password> -h <host-address> <database-name> < TEST_TABLE_INSERT.SQL --Here file TEST_TABLE_INSERT.SQL will have bulk import sql statement like below --insert into TEST_TABLE values('1','test1','2017-09-08'),('2','test2','2017-09-08'),('3','test3','2017-09-08'),('3','test3','2017-09-08');
