2

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

Enrique Becerra
  • 145
  • 3
  • 8
  • 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 Answers2

4

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.

Norky
  • 849
  • 4
  • 14
1

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
Mike
  • 22,310
  • 7
  • 56
  • 79