Periodic Database Backups

When you have a fairly stable web application running in production with reasonable user traffic (or even otherwise) backing up your production database provides you with a security net against unexpected failures or goof ups in the production environment. Now most people prefer doing it through a crontab calling a shell script which creates the backups for you and mails the report to your team but I prefer doing it with Ruby.

In case you are just starting out and need something going quick this should help you get started.

The net/smtp module is to allow you to connect to the SMTP server running on your machine or allows you to connect to a remote SMTP server

More useful info available at http://www.tutorialspoint.com/ruby/ruby_sending_email.htm

require 'rubygems'
require 'net/smtp'
def run_back_up
timestamp =  "#{Time.now.to_i.to_s}"
puts system("sudo mysqldump -uroot -padmin my_database > /home/sid/backups/my_database_#{timestamp}.sql")
filename = "my_database_#{timestamp}.sql"
return [true, filename]
rescue =>e
return [false, nil]
end

This method runs a simple mysql dump command to create a dump file and places it in the backups folder. If space is not an issue for you then you may leave it on your disk and have your day-wise backups on the server storage (like S3 or on an EBS Volume)

def send_email(backup_status, cleanup_status)
message = <<MESSAGE_END
From: Backup <backup@mydomain.com>
To: Siddharth <sid@toemailid.com>
Subject: Backup Report
New Backup as on: #{Time.now} : #{backup_status}
Old Backup cleanup: #{Time.now} : #{cleanup_status}
MESSAGE_END
Net::SMTP.start('localhost') do |smtp|
smtp.send_message message, 'backup@localhost','sid@toemailid.com'
end
end

This bit sends a small email report to the users about the success/failure of the backup.

def cleanup_old_file(current_filename)
puts "Cleaning up old file"
files = ['/home/sid/backups/*'] - [current_file_name]
puts files
files.each{|filename| File.delete(filename) }
return true
rescue=>e
return false
end

I also added a small method to delete the old file once the new backup is created because I don’t have the luxury of infinite space.

status, current_filename = run_back_up
cleanup_status = cleanup_old_file(current_filename) if current_filename
send_email(status, cleanup_status)

Next we write the actual cron job. Now on linux the commands to list your crons is

crontab -l

So let begin by creating a new crontab

sudo env EDITOR=vim crontab -e

this specifies the editor you would like to create your cronjob in.
This is article beautifully explains the crontab syntax and generating basic crontabs.
http://www.cyberciti.biz/faq/how-do-i-add-jobs-to-cron-under-linux-or-unix-oses/

This is the basic syntax for crontab

* * * * * ruby /home/sid/back_and_mail.rb

This runs the cron every minute and can be handy to test your setup. Our production cron will look like

0 0 * * * ruby /home/sid/back_and_mail.rb

This runs every day at midnight.

Just in case your crontab is not running, try

sudo /etc/init.d/cron start

OR

service cron start

to check if your crontab is actually running.

Thats it hope this is helpful. Here are some useful links I found.
http://www.linuxquestions.org/questions/red-hat-31/how-to-change-the-default-crontab-editor-395750/

http://www.cyberciti.biz/faq/howto-linux-unix-start-restart-cron/

http://objectliteral.blogspot.com/2010/03/running-ruby-scripts-as-cron-jobs-in.html

Advertisements