Export MySQL Table to .tsv from a remote server without using `INTO INFILE`

Question

I have a very large table on an Amazon RDS instance that I need to export as a .tsv with specific settings. I cannot use INTO OUTFILE on the RDS instance. So I must export the table onto the local drive of the server I'm logging into the RDS MySQL instance with.

I have specific settings I need to specify for the .tsv. They are:

1. terminate with \t
2. wrap with nothing
3. escape with a backslash
4. null values are blank

How do I do this from the command line?

Answer 1

You can do this-

mysql -uroot -proot -h "mysql.host.url" -N -B -e "select * from world.city" | sed 's/NULL/ /g' > test.tsv

-N tells it not to print column headers. -B is "batch mode", and uses tabs to separate fields. NULL is replaced by space.

For 100 GB databases , this may help-

 mysql -uroot -proot -h "mysql.host.url" -N -B -e "select * from world.city" > test.tsv
 sed 's/NULL/ /g' < test.tsv > new.tsv

But i recommend For huge databases you should use ETL Tools.