how to 'load data infile' on amazon RDS?

user_39110 0
Viewed 34647

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          |
 +----------+-----------+------------+------------+
amazon-rds mysql-error-1045 amazon-web-services mysql
6 Answers

Also struggled with this issue, trying to upload .csv data into AWS RDS instance from my local machine using MySQL Workbench on Windows.

The addition I needed was adding OPT_LOCAL_INFILE=1 in: Connection > Advanced > Others. Note CAPS was required.

I found this answer by PeterMag in AWS Developer Forums.

For further info:

SHOW VARIABLES LIKE 'local_infile'; already returned ON and the query was:

LOAD DATA LOCAL INFILE 'filepath/file.csv' 
    INTO TABLE `table_name`
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS;

Copying from the answer source referenced above:

Apparently this is a bug in MYSQL Workbench V8.X. In addition to the configurations shown earlier in this thread, you also need to change the MYSQL Connection in Workbench as follows:

  1. Go to the Welcome page of MYSQL which displays all your connections
  2. Select Manage Server Connections (the little spanner icon)
  3. Select your connection
  4. Select Advanced tab
  5. In the Others box, add OPT_LOCAL_INFILE=1

Now I can use the LOAD DATA LOCAL INFILE query on MYSQL RDS. It seems that the File_priv permission is not required.*

user_120326480

Importing bulk data into Amazon MySQL RDS is possible two ways. You could choose anyone of below as per your convenience.

  1. 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.

  2. 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');
user_10734870
Related