Exporting data from Amazon RDS MySQL database to .csv or viewable in Excel sheet.

Amazon RDS do not allow to export table content to a csv file using mysql command.

select * from desired_table
INTO outfile "/path/to/csv file/file.csv"
fields terminated by ',';

gives an error: “Error Code:1045 Access denied for user ***@***.rds.amazonaws.com using password=YES”

As it said access denied I tried to grant access using:
grant all privileges
on YOUR_DATABASE.*
to 'username'@'***.rds.amazonaws.com'
identified by 'your_password';
>flush privileges;

But this did not work too.

There’s no issue from your end. MySQL creates the file on database server. It does not give permission to the client to create a file.

So a simple way to export the data to csv is by using unix sed command.
mysql -u username -p database_name -h ***.rds.amazonaws.com --port=3306 --batch -e "select * from desired_table"  |  sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s