help with cron job and mysql export

Hi I have a series of tasks I need to perform to A: export data form a table to a csv file, B: strip the header of the file out, C: copy the file via SSH to another (linux) server.

I know all the commands and can complete all these task on their own from bash, but I want to string them together and be able to schedule a one time (as needed) cron job to execute each task in succession as these tasks need to be done over night.

The commands I use are:
A: mysql -u root -p --quick databasename -e "select * from tablename" | sed 's/\t/;/g' > file.csv
B: tail -n +2 file.csv > file_noheader.csv
C: rsync -e ssh -av file_noheader root@server:/path/file_noheader.csv
LVL 8
sharkbot221984Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kim RyanIT ConsultantCommented:
My suggestion would be to add your 3 commands to a bash script and then add a line in cron to run that bash script. Note that cron scripts run in a restricted shell so I would suggest
1. use full path names for files and programs (eg /usr/bun/tail)
2. start your bash script with the line #!/usr/bin/bash
3. Test your script manually before running in cron
4. Add some log statments to the script to check how far it gets
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ajay_mhasalCommented:
Try running following script

-----------------------------------------------------------------------------------------------------
#!/bin/bash
CVS=/tmp/file.cvs
NOHCVS=/tmp/file_noheader.csv

/usr/bin/mysql -u root -pxxxx --quick databasename -e "select * from tablename" | /bin/sed 's/\t/;/g' > $CVS
/usr/bin/tail -n +2 $CVS > $NOHCVS
/usr/bin/rsync -e ssh -av $NOHCVS root@server:/path/file_noheader.csv
--------------------------------------------------------------------------------------------------------

Note:

1. In the above example i have defined the location of file.cvs and file_noheader.csv in /tmp and you need to change it as per ur requirement.

2. In mysql command you need change "xxxx" with mysql root password otherwise script will halt at password prompt.

3. You need to configure key based login for rsync otherwise script will again halt at password prompt. (If you have not configures key based/passwordless auth then pl. visit http://www.cyberciti.biz/tips/ssh-public-key-based-authentication-how-to.html )

4. You need to change the destination server's path, so that the file will be copied in correct location.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux Distributions

From novice to tech pro — start learning today.