List:General Discussion« Previous MessageNext Message »
From:Ofer Inbar Date:May 10 2007 7:23pm
Subject:dbnightly maintenance & backup script
View as plain text  
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
Thread
dbnightly maintenance & backup scriptOfer Inbar10 May
  • RE: dbnightly maintenance & backup scriptDaevid Vincent11 May
  • Re: dbnightly maintenance & backup scriptOfer Inbar17 May