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
>
>
