c. scott andreas

If you’re not making daily backups of your databases, you’re either not doing anything important or living dangerously.

Here’s a little Ruby script I wrote to remotely back up a MySQL database each night.

This script is designed to log into your server via SSH, back up a database of your choice using mysqldump, compress it using tar+gzip, and download the result before cleaning up after itself.

This method is preferable to one that might run on the server then push the backup to an off-site location.  In the event that your server became compromised, the attacker would be able to remotely erase the backups you’ve created.  By logging in to pull them rather than push, this vulnerability is mitigated.

Steps to install and use:

• Make sure you’re running Ruby 1.8.x or higher and that you have the net/ssh and net/sftp gems installed (If not, do a quick (sudo) gem install net-ssh net-sftp)
• Fill in the SSH and MySQL Database configuration sections of this script.
• Create a directory on your computer to store the backups, and create another on the server for the temporary files to be stored.  Then, enter these as absolute paths without trailing slashes in the “Backup File Locations” section.
• You should be good to go.  Type “ruby backup_db.rb” to give it a whirl.
• If you’d like to run this automatically, add it to your crontab.  A sample entry might look like this:  0 0 * * * ruby /home/user/scripts/backup_db.rb
• Protip: Replace the “/home/user/scripts/” section with the actual path to the script.