This is my differential backup script. I've put it into cron so it runs every hour automatically. I've a master sql dump files in '/master/DBNAME' and I compare this file to the most recent dump and calculate diff. Basically it mysqldumps database without buffering (-q), by adding ' to table and field names. Then splits this file into pieces each containing 500.000 lines. If you don't have big SQL dumps (more than 700-800Mb) you can skip this step, this step is needed to reduce memory required by diff. I should say that I'm running this diff on a server with 2Gb ram. If you have less memory you can reduce number of lines per split file eg: instead of 500.000 line you can use 50.000. After splitting files I compare differences between directories /master and /target. Just make sure that you have your master directory containing master database dumps which are split using same number of lines. Last step compresses diff file using bzip2 and securely transfer to a designated backup server.
#!/bin/bash # Copyright (C) 2002-2003 Kayra Otaner <[EMAIL PROTECTED]> # This software may be freely redistributed under the terms of the GNU # public license. # # MySQL differential backup script. FILENAME=`date --date "today" +"%Y-%m-%d_%H"` clear cd /home/backup/db/ mysqldump -CQq --add-drop-table DBNAME -ubackup -pXXXXX > ./target/DBNAME.sql cd target split -l 500000 DBNAME.sql DBNAME cd .. rm ./target/DBNAME.sql diff -N -H -U 2 ./master ./target > diff cp diff $FILENAME bzip2 -9f $FILENAME scp $FILENAME.bz2 [EMAIL PROTECTED]:~/db/ As I told you, this script looks old fashioned but working perfectly for us. If there is any binary diff tool that you can use, try same with binary diff. Binary diffs are commercial and not %100 reliable. Don't forget to change your user name, password and dbname parameters for above script. Let me know if you're having trouble customizing it. Kayra Otaner On Wed, 2003-08-20 at 19:42, Miguel Perez wrote: > Do you mind being more explicit with your method please, I really appreciate > your help. > > Thanx in advanced... > > >From: Kayra Otaner <[EMAIL PROTECTED]> > >To: Miguel Perez <[EMAIL PROTECTED]> > >Subject: Re: DIFFERENTIAL BACKUPS > >Date: 20 Aug 2003 19:01:17 -0400 > > > >I do differential backups using old fashion but solid methods. Since > >there isn't safe binary diff, I sql dump database to a text file and > >unified diff it, bzip2 it and send it to another server. > >So, it is possible with this method. > > > >Let me know if you need details. > > > > > >Kayra Otaner > > > > > > > > > >On Wed, 2003-08-20 at 18:33, Miguel Perez wrote: > > > Hi list, > > > > > > Does anyone know how to do a differntial backup for mysql databases. Is > >it > > > possible?. > > > > > > I use mysql 4.0.12 on a redhat 7.3 box. > > > > > > Greetings and thnx in advanced > > > > > > _________________________________________________________________ > > > MSN Fotos: la forma más fácil de compartir e imprimir fotos. > > > http://photos.msn.es/support/worldwide.aspx > > > > > _________________________________________________________________ > Charla con tus amigos en línea mediante MSN Messenger: > http://messenger.microsoft.com/es > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
