To generate dump + trasnfer over ssh to home pc:

ssh -t username@server "mysqldump --username=username --password=password database | gzip -c -9 -f" >  databse-`date %d-%m-%y`.gz 

To simply trasnfer a file:

scp user@password:path/to/datbase.gz database-`date %d-%m-%y`.gz

Hi,

I was trying to rsync the backup folder in my linode using this command:

rsync -av –progress --inplace --rsh='ssh -pxxxx' user@remote local

This works, but now when I schedule the cron in my Mac is doesn't work because the remote user is different than the local user who run the cron locally.

How am I able to run the cron?

Maybe using ssh keys?

Ok,

Generating keys solved the problem :)

Thanks for the tip! I now use it to backup my Linode database with mysqldump and download it to my computer automatically using scp (ssh key) with a local cron job.

Of course, this only works if my own computer is turned on at 12:15, but it normally is. Both Linode and my own computer are running Ubuntu 10.04 LTS.

ONLINE: create a cron job to dump my MySQL database to /tmp directory

every day, at 12:05

05 12 * * * mysqldump -u USERNAME -p'PASSWORD' DATABASENAME | gzip -c > /tmp/DATABASENAME_database.sql.gz

LOCALLY: download the file with scp

every day, at 12:15

15 12 * * * scp USERNAME@SERVER:/tmp/DATABASENAME_database.sql.gz ~/.mysql-dump/date +\%Y/\%m/DATABASENAME-online-date +\%Y-\%m-\%d-\%H.\%M.sql.gz

The local cron job puts the file in the folder ~/.mysql-dump/YEAR/MONTHNAME/

I manually create the years like this ~/.mysql-dump/2012 and have another cron job which creates the month when needed. It wont overwrite it if it already exists:

every day, at 12:00

00 12 * * * mkdir -p ~/.mysql-dump/date +\%Y/\%m/

@rn3000:

Of course, this only works if my own computer is turned on at 12:15, but it normally is. Both Linode and my own computer are running Ubuntu 10.04 LTS.

Anacron is a utility that works in conjunction with cron to make sure tasks like this get run the next time you turn on your computer if they were missed because it was off.

Thanks for sharing. I didn't subscribe to this thread, so I just saw your post today, a year later :)