export mysql query results to csv using shell script

Awesome, This is my very first post in WordPress blog. Here, I’m going to show you how to export MySQL query results to CSV using shell script. The advantage of using shell script is, you can automate and schedule the report generating process by adding script to cron job.

#!/bin/bash
MYSQLHOST="127.0.0.1"
MYSQLDB="testdb"
MYSQLUSER="testuser"
MYSQLPASS="testpassword"

#File name with date only
DATE=$(date + "%F")

#File location
FILE="/tmp/Usage_Report_$DATE.csv"

MYSQLOPTS="--user=${MYSQLUSER} --password=${MYSQLPASS} --host=${MYSQLHOST} ${MYSQLDB}"

#testing purposes, give echo output
echo "Report Begin: $(date)"

mysql ${MYSQLOPTS} << EOFMYSQL
SELECT t1.name AS User_Name, t2.user_id AS User_ID  FROM user_table INTO OUTFILE '$FILE' FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
EOFMYSQL

#add column title to the report
sed -i '1i User_Name,User_ID' $FILE

Now I’m going describe above shell script. you need to provide MySql credentials such as database name, user, password and ip address of server.If it is hosted on same server where shell script executing, then use local host ip (127.0.0.1) otherwise needs to provide MySQL server ip.
For demonstration purposes, I created table call ‘user_table‘ and added 2 columns ‘name’,’user_id‘ to it, and filled with some dummy data.
I selected data from two columns using following query. Your MySql query may be more complicated than this example.
SELECT t1.name AS User_Name, t2.user_id AS User_ID  FROM user_table
At the end, column title was added to csv file using ‘sed‘ command.

That’s it, try out this code and looking forwarding your comments and feedback  🙂  .