»

May 21

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  :)  .

2 comments

  1. Casy Choate

    Hello, getting an error that the file can’t write due to “permission denied”.. I’ve given all access 777 to the folder. any reason you can think that would happen? Thanks!

    1. admin

      Hello,

      You may need to set mysql grant file permission for the user just like below
      ex:- GRANT FILE ON *.* TO ‘backupuser’@’localhost’;

Leave Your Thought Here