Synchronize database - seqcode/pegr GitHub Wiki

  1. generate ssh key on production server

$ ssh-keygen -t rsa

  1. copy the public key to the backup server

$ ssh-copy-id username@backup_server

  1. create a shell script
mysqldump -u user -ppassword pegr_db > /path/to/pegr_sync.sql
rsync -avzhe 'ssh' /path/to/pegr_sync.sql username@backup_server:~
ssh username@backup_server 'mysql -u user -ppassword pegr_db < pegr_sync.sql'
rsync -avz /path/to/files/ username@backup_server:/path/to/files
  1. create a cron job to run the script, e.g.

$ crontab -e

5 1 * * * bash /path/to/sync.sh >> /path/to/sync.log 2>&1

The above cron job will run the sync.sh script at 1:05 am every day.