From owner-freebsd-questions@FreeBSD.ORG Wed Oct 1 13:25:51 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id C87E2106568C for ; Wed, 1 Oct 2008 13:25:51 +0000 (UTC) (envelope-from jalmberg@identry.com) Received: from mx1.identry.com (on.identry.com [66.111.0.194]) by mx1.freebsd.org (Postfix) with ESMTP id 7F6DC8FC12 for ; Wed, 1 Oct 2008 13:25:51 +0000 (UTC) (envelope-from jalmberg@identry.com) Received: (qmail 76547 invoked by uid 89); 1 Oct 2008 13:25:50 -0000 Received: from unknown (HELO ?192.168.1.110?) (jalmberg@75.127.142.66) by mx1.identry.com with ESMTPA; 1 Oct 2008 13:25:50 -0000 Mime-Version: 1.0 (Apple Message framework v752.3) In-Reply-To: <94136a2c0810010201y6d561828lb125419de1613aee@mail.gmail.com> References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> <94136a2c0810010201y6d561828lb125419de1613aee@mail.gmail.com> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <81F44C16-59C8-4A44-AE2A-B9F233834383@identry.com> Content-Transfer-Encoding: 7bit From: John Almberg Date: Wed, 1 Oct 2008 09:25:48 -0400 To: freebsd-questions@freebsd.org X-Mailer: Apple Mail (2.752.3) Subject: Re: Best way to back up mysql database X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 01 Oct 2008 13:25:51 -0000 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}`