Linux – How to dump a thesql database and transfer to remote ftp in one line -for crontab-

backupcronftplinuxMySQL

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

Best Answer

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.

Related Topic