Export mysql query data in csv file using command line

We can easily export MySQL select query records into  CSV file using shell command. Following are couple of ways to do that.

  1. By using mysql command – LOAD DATA IN FILE
  2. By using “mysql” and “sed” command

 

Mysql Load data infile

Here is command for export data using mysql infile. Please keep in mind that, “filename.csv” file will generate it on mysql remote server not on the local server from you are running the command.watch movie Chasing Coral now

mysql --host=$HOST--user=$USER--password=$PASS $DBNAME -e "select * from tablename   INTO OUTFILE  '/tmp/filename.csv'  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"

Mysql and Sed Command.

It will fetch records using mysql select command and replace each TAB with COMMA(,) and put that replace comma seperate string into file named “filename.csv”. Please keep in mind that it will replace TAB from data returned query as well.

mysql --host=$HOST--user=$USER--password=$PASS $DBNAME -e "select   * from tablename" | sed 's/\t/,/g' > filename.csv

 

 

Categories: Linux, MySql