how to 'load data infile' on amazon RDS?

Question

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.

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.

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.html seems to suggest that I need to find a different way around this.

Help?

UPDATE I'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          |
 +----------+-----------+------------+------------+

Answer 1

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.

mysqldump MYDB | mysql -h rds-amazon-blah.com --user=youruser --pass=thepass

Answer 2

You can grant yourself FILE (and SUPER) privileges, just not with the GRANT command.

Instead, use a desktop application like MySQL Administrator or Querious.

In MySQL Administrator I was able to go to the user management section and assign myself SUPER privileges so I could create a TRIGGER. (Note that in order to do this you must first go into the MySQL Administrator Preferences (Mac) or Options (Windows) and turn on global privilege settings.)

Edit: March 15, 2010

I am once again taking a look at Amazon RDS and, sadly, it seems you are no longer able to GRANT yourself FILE or SUPER privileges any more. It was nice while it lasted.

Answer 3

You need to use LOAD DATA LOCAL INFILE as the file is not on the MySQL server, but is on the machine you are running the command from.

Answer 4

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 Maatkit provided what I needed with some excellent extra features. It might be a great match for your use case.