cmdline

Many a times, while working with reports there are some situations where we need to save/upload csv files, generated from MySql query(Reports), on FTP server on regular basis.
To acheive this task, on Linux we can create bash scripts which automatically execute the query, retrieve data and export it on FTP in csv format and after every definite time span.
Here is an example to do so:
Create a bash file as follows and save it as mybashscript.sh in /usr/bin/
Here we will extract data from Mysql server and will save the report in some temporary folder and then will upload it on FTP server. So here is the script:

#!/bin/bash
#DB server details
server_ip=’localhost’;
db_user=’root’;
db_pass=”pass”;
db_name=”mydb”;

#Delete all previous csv files
rm /var/www/tmp/*.csv

#create new file
csv_file=”/var/www/tmp/$(date -d now +%y-%m-%d)_report.csv”;

#Query to be execute in MySql and save data in csv file
mysql -u$db_user -p$db_pass -h$server_ip -e “SELECT CONVERT_TZ(date_start,’GMT’,’EST’) AS date_start,CONVERT_TZ(date_end,’GMT’,’EST’) AS date_end,name,duration_minutes,duration_seconds,
agent_name,call_disposition,number_dialed,parent_id AS contact_id,campaign_id,dialer_call_id FROM calls WHERE dialer_call_id LIKE ‘ark_%’
AND (DATE(date_start) > DATE_SUB( CURDATE( ) , INTERVAL 1 DAY ) ) LIMIT 50;” $db_name | sed ‘s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ > $csv_file

#Open FTP and put files there
HOST=”xx.xx.xx.xx” #Your FTP IP or URL
USER=”ftpuser” #FTP user name
PASS=”passwd” #FTP password

cd /var/www/tmp
ftp -n $HOST <