Date: Wed, 1 Oct 2008 09:25:48 -0400 From: John Almberg <jalmberg@identry.com> To: freebsd-questions@freebsd.org Subject: Re: Best way to back up mysql database Message-ID: <81F44C16-59C8-4A44-AE2A-B9F233834383@identry.com> In-Reply-To: <94136a2c0810010201y6d561828lb125419de1613aee@mail.gmail.com> References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> <94136a2c0810010201y6d561828lb125419de1613aee@mail.gmail.com>
next in thread | previous in thread | raw e-mail | index | archive | help
So, I thought I would post my ruby script for doing this backup...
It's a little verbose for some tastes, but I like to be able to see
what's happening in a script, blow by blow.
This script rotates the backups according to the day of the month, so
you get roughly 30 days backup. It also moves the backup to a remote
backup server, keeping the latest backup on the local machine for one
day. It also sends emails in case of error, and one email for
success, to give you that warm and fuzzy feeling that comes from
having a good backup.
-- John
------------
#!/usr/local/bin/ruby
debug = true
day_of_month = Time.now.day
backup_file = "all.mysql."+day_of_month.to_s+".txt"
remote_backup_location = "user@backup_host.com:backup_dir" #
user must be able to login to backup_host.com without password
db_user = "username"
db_pass = "password"
db_host = "dbhost"
notify_email = "you@example.com"
# ----- no configuration below this line ----------------
# remove yesterday's local backup
puts "removing previous backups" if debug
`rm all.mysql.*.gz`
puts "remove status: #{$?.exitstatus}" if debug
# create backup file
backup_command = "/usr/local/bin/mysqldump -Q -u#{db_user} -p#
{db_pass} -h#{db_host} --all-databases >#{backup_file}"
puts backup_command if debug
`#{backup_command}`
puts "backup status: #{$?.exitstatus}" if debug
unless $?.exitstatus == 0
`echo "Mysql backup failed with status: #{$?.exitstatus}" | mail -
s "Mysql_backup Error" #{notify_email}`
exit
end
# zip it
zip_command = "/usr/bin/gzip #{backup_file}"
puts zip_command if debug
`#{zip_command}`
puts "zip status: #{$?.exitstatus}" if debug
unless $?.exitstatus == 0
`echo "Gzip failed with status: #{$?.exitstatus}" | mail -s
"Mysql_backup Error" #{notify_email}`
exit
end
# move to backup directory
move_command = "scp #{backup_file}.gz #{remote_backup_location}/#
{backup_file}.gz"
puts move_command if debug
`#{move_command}`
puts "move status: #{$?.exitstatus}" if debug
unless $?.exitstatus == 0
`echo "SCP failed with status: #{$?.exitstatus}" | mail -s
"Mysql_backup Error" #{notify_email}`
exit
end
`echo "Successfully backed up mysql to #{backup_file}" | mail -s
"Mysql_backup Success" #{notify_email}`
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?81F44C16-59C8-4A44-AE2A-B9F233834383>