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) -- [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
