Re: Best way to back up mysql database
- From: John Almberg <jalmberg@xxxxxxxxxxx>
- Date: Wed, 1 Oct 2008 09:25:48 -0400
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@xxxxxxxxxxxxxxx: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@xxxxxxxxxxx"
# ----- 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}`
_______________________________________________
freebsd-questions@xxxxxxxxxxx mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to "freebsd-questions-unsubscribe@xxxxxxxxxxx"
- Follow-Ups:
- Re: Best way to back up mysql database
- From: Sebastian Tymków
- Re: Best way to back up mysql database
- References:
- Best way to back up mysql database
- From: John Almberg
- Re: Best way to back up mysql database
- From: Zbigniew Szalbot
- Best way to back up mysql database
- Prev by Date: Re: Setting up gmirror
- Next by Date: Re: Setting up gmirror
- Previous by thread: Re: Best way to back up mysql database
- Next by thread: Re: Best way to back up mysql database
- Index(es):
Relevant Pages
|
