List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 10 2007 10:25pm
Subject:RE: dbnightly maintenance & backup script
View as plain text  
Thanks for sharring Ofer.

I'll throw the one I wrote and use into the mix too.
http://daevid.com/examples/daily_backup_tgz.sh

Simply put it in your /etc/cron.daily/ 

And then every so often monitor /backups/ and delete stuff that's getting old. (it does
some cleanup)

d

> -----Original Message-----
> From: Ofer Inbar [mailto:cos@stripped] 
> Sent: Thursday, May 10, 2007 12:24 PM
> To: mysql@stripped
> Subject: dbnightly maintenance & backup script
> 
> I wrote a perl script to handle all of our regular mysql maintenance
> tasks, which I thought might be useful to others.  It's meant for an
> enviroment with binary logging turned on, but is fairly flexible.
> Although if you're backing up multiple databases you'll have to modify
> it a bit, since in our case we only care about backing up one database
> (if you do modify it for multidatabase, please send me your diffs).
> 
>   http://thwip.sysadmin.org/dbnightly
> 
> The syntax is: dbnightly [action [action ...]]
> 
> It will perform the actions in the order you give them on its 
> commandline.
> Actions it knows how to do are:
> 
> 1. maint - Run a bunch of SQL queries for nightly maintenace
>   (put the queries you want in the DBMAINT section of the script)
> 
> 2. full - a full mysqldump, into the backup directory, gzip'ed and
>    with the database name and datetimestamp in the filename
> 
> 3. partial - a partial mysqldump of a list of tables you choose,
>    into the backup directory, gzip'd
> 
> 4. flush - flush binary logs
> 
> 5. logs - copy new or modified binary logs to the backup directory and
>    gzip them, delete any that have been deleted from the 
> mysql directory,
>    and don't copy & gzip ones that haven't changed since last 
> backed up
> 
> The resulting backup directory is all gzip'ed and suitable 
> for rsync'ing.
> 
> We run it from crontab, and it produces output like this:
> 
> 2007-05-10 06:00 dbnightly: Database maintenace
> Table   Op      Msg_type        Msg_text
> databasename.tablename   optimize        status  OK
> 2007-05-10 06:00 dbnightly: Database maintenance done
> 2007-05-10 06:00 dbnightly: Partial dump of databasename to 
> /home/maintusr/backups
> 2007-05-10 06:01 dbnightly: Partial dump complete: 
> databasename-partial.sql
> 2007-05-10 06:02 dbnightly: Flushing binary logs
> 2007-05-10 06:02 dbnightly: Copying 
> /var/lib/mysql/binlogfile.000090 to /home/maintusr/backups
> 2007-05-10 06:02 dbnightly: Copying 
> /var/lib/mysql/binlogfile.000091 to /home/maintusr/backups
> 2007-05-10 06:02 dbnightly: Done
> 
> It also syslogs, like this:
> 
> May 10 05:00:01 hostname dbnightly: Database maintenace 
> May 10 05:00:04 hostname dbnightly: Database maintenance done 
> May 10 05:00:04 hostname dbnightly: Partial dump of 
> databasename to /home/maintusr/backups 
> May 10 05:01:18 hostname dbnightly: Partial dump complete: 
> databasename-partial.sql 
> May 10 05:02:14 hostname dbnightly: Flushing binary logs 
> May 10 05:02:15 hostname dbnightly: Copying 
> /var/lib/mysql/binlogfile.000090 to /home/maintusr/backups 
> May 10 05:02:38 hostname dbnightly: Copying 
> /var/lib/mysql/binlogfile.000091 to /home/maintusr/backups 
> May 10 05:02:39 hostname dbnightly: Done 
> 
> Both of these are from "dbnightly maint partial flush logs", which we
> run 6 nights a week.  On the other night, we run "dbnightly 
> maint full logs"
> (no need to flush because --flush-logs is in the $fulldump options).
> 
> Note: the "dirsyncgz" script I posted recently was a modified version
> of the binlogs subroutine from this script (dbnightly was not 
> complete yet)
> 
>   --  Cos (Ofer Inbar)  --  cos@stripped  http://thwip.sysadmin.org/
>   "cos, is perl God?" 'No, Larry Wall is God.  Perl is the 
> Language of God."
>   "But I thought you don't believe in God?"  "That's OK, I 
> don't believe
>    in Larry Wall either."  -- a conversation with Mike 
> Sackton over lunch
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 
> 

Thread
dbnightly maintenance & backup scriptOfer Inbar10 May
  • RE: dbnightly maintenance & backup scriptDaevid Vincent11 May
  • Re: dbnightly maintenance & backup scriptOfer Inbar17 May