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
