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:[EMAIL PROTECTED] > Sent: Thursday, May 10, 2007 12:24 PM > To: mysql@lists.mysql.com > 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) -- [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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
