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.


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

#File location

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

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

SELECT t1.name AS User_Name, t2.user_id AS User_ID  FROM user_table INTO OUTFILE '$FILE' FIELDS TERMINATED BY ',' 

#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 ( 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  🙂  .


  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


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

  2. Venance Edson

    This is not working if database is remote, what needs to be done? error is ERROR 1045 (28000) at line 1: Access denied for user ‘crm’@’%’ (using password: YES)

Leave Your Thought Here