Question

Write a program to peform a database backup. Generate a public/private key pair (using GnuPG, or anything else). Your backup program/script must run daily, backup a table in a database into a .csv.gz file (comma delimited, gzip compressed [do not use database specific binary formats]). And encrypt the backup using your PUBLIC key.

Note that you can use any language, database, utility, configuration, etc. (cron script or windows scheduler is ok). The key is that the backup is comma delimited (do not export to database specific formats), gzip compressed, AND encrypted with public key---and is generated daily (without your intervention). Email me the program/script and instructions on how to set it up to run daily (for cron, I want the crontab line, etc., for windows scheduler, I want a batch file to setup to run and instruction on how to set it up in scheduler)

Answer 1

script that will backup your web directory and a MySQL database:

#!/bin/bash

date=`date '+%F_%H:%M:%S'`

mysqldump --skip-lock-tables -u backup --password='PASSWORD' mydb | gzip > /tmp/$date.sql.gz

rsync -e 'ssh -i /root/.ssh/backup_id' -a /tmp/$date.tar.gz /tmp/$date.sql.gz backups@server.com:~/

rm /tmp/$date.tar.gz /tmp/$date.sql.gz

mydb is the dbname and ‘PASSWORD’ is the password. Modify as per your convenience.

backups@server.com:~/ - is the backup server name. Modify as required.

create the db user for backups:

mysql -u root -p

> GRANT LOCK TABLES, SELECT ON mydb.* TO 'backup'@'localhost' IDENTIFIED BY 'PASSWORD';

> flush privileges;

set up SSH keys for the backup script

ssh-keygen -t rsa -f /root/.ssh/backup_id

ssh-copy-id -i /root/.ssh/backup_id backups@server.com