Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need to do a weekly backup of a mySQL table and then transfer it to a remote machine using ftp.
This is the command I have now in crontab:

0 18 * * 1 /usr/bin/mysqldump --add-drop-table --extended-insert -user -ppass db [table] > /home/myuser/backups/bdd-`date +%d-%m-%Y`.sql

I would need to replace the last part of the command so the generated sql file could be transferred to another server using ftp protocol.

I've searched and found something about "mounting permanently the remote ftp server using curlftpfs under /mnt/ftpserver/", but didn't find the steps for doing this in my linux

Thanks a lot in advance for any tip you can give me

share|improve this question
1  
You fail to mention what is "my linux". What Linux distribution are you using? –  Norky May 18 '11 at 12:15
2  
Doing this with FTP will invariable leave you saving a clear text username and password somewhere. If you can use SFTP or SCP we could point out how to use key based authentication which is considerably safer. –  Caleb May 18 '11 at 12:17

2 Answers 2

up vote 4 down vote accepted

If you have some task which requires more than one line of shell, a common approach is to put it in a small shell script, and call that shell script from cron.

0 18 * * 1 /home/myuser/backups/backup-and-ftp-db.sh

where /home/myuser/backups/backup-and-ftp-db.sh contains something like

#!/bin/sh
dumpfile=/home/myuser/backups/bdd-$(date +%d-%m-%Y).sql
/usr/bin/mysqldump --add-drop-table --extended-insert -user -ppass db [table] > $dumpfile
lftp -e "put $dumpfile backupdir/$dumpfile ; quit" -u username,password backupserver

It's also worth noting that multiple invocations of date in command substitution as Mike has done can lead to different results. This more likely if date includes hours/minutes/seconds, however, consider what happens if your cron job starts just before midnight - your first date invocation gives, say bdd-19-05-11.sql, and if the mysqldump takes long enough that midnight passes and you go into the next day, your second command will expect a file name bdd-20-05-11.sql. For this reason I invoke date once and store it in a variable.

share|improve this answer

You can use ncftpput if you are ok with putting a ftp password in your crontab.. or the path to the ncftpput cfg file

0 18 * * 1 /usr/bin/mysqldump --add-drop-table --extended-insert -user -ppass db [table] > /home/myuser/backups/bdd-`date +%d-%m-%Y`.sql && ncftpput -u user -p pass host.remote.com /home/myuser/backups/bdd-`date +%d-%m-%Y`.sql remotePath//bdd-`date +%d-%m-%Y`.sql
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.