Croned MySQL Database Backup Script and Transfer to an Offsite Location
This bash script regularly (from a cron) backs up a production database to an offsite server into a date-stamped compressed (gzip) format.
It is intended to be run from the production server. You need to generate a public key (ssh-keygen) for the production server and add it to the offsite server’s authorized keys file (/home/username/.ssh/authorized_keys). Be sure to chmod 600 the public and private key after creating them.
Also make sure that you use the same username for the key generation, the connection string key location and the cron entry.
#
# http://codingsnippets.com/croned-mysql-database-backup-script
#
# This bash script regularly (from a cron) backs up a production
# database to an offsite server into a date-stamped compressed
# (gzip) format.
#
# It is intended to be run from the production server.
#
#
# Please report any comments, bugs or improvements at
# http://codingsnippets.com/croned-mysql-database-backup-script
# and please leave this message intact so others can
# do the same, improve the script, or download the
# most current version. Thanks.
#
LOCALFILE_PATH="/home/www/backups"
FILENAME="livedb-`date "+%Y-%m-%d"`.sql.gz"
SSH="$(which ssh)"
GZIP="$(which gzip)"
SCP="$(which scp)"
MYSQL_DUMP="$(which mysqldump)"
DB_HOSTNAME="localhost"
DB_USERNAME="USERNAME"
DB_PASSWORD="password"
DATABASE="database_name"
#Offsite location
SSH_PORT="22"
SSH_HOSTNAME="yourhostname.com"
SSH_USERNAME="yourusername"
SSH_KEY_LOCATION="~/.ssh/id_rsa"
BACKUP_LOCATION="/home/user/backups"
echo $FILENAME
$MYSQL_DUMP -u$DB_USERNAME -p$DB_PASSWORD -h$DB_HOSTNAME $DATABASE | $GZIP -9 > $LOCALFILE_PATH/$FILENAME
$SCP -i $SSH_KEY_LOCATION -P $SSH_PORT $LOCALFILE_PATH/$FILENAME $SSH_USERNAME@$SSH_HOSTNAME:$BACKUP_LOCATION/$FILENAME
Time-saving practices for software developers « Software Cubes Said,
February 9, 2010 @ 12:16 am
[...] Set up automatic database backups. Find out how to set up an automatic, full database backup. Having snapshots of your development database is sometimes important. This takes very little time to set up and it'll pay for itself the first time you accidentally delete an entire table off your database. This is a quick backup script for MySQL. [...]