List:General Discussion« Previous MessageNext Message »
From:mark addison Date:August 10 2006 4:26pm
Subject:RE: MySQL Replication Binary Logs - How Long to Keep?
View as plain text  
On Thu, 2006-08-10 at 14:47 +1000, Logan, David (SST - Adelaide) wrote:
> Hi Warren,
> 
> I would generally keep the binary log until you have done a full backup.
> Once that is done, all the transactions that are contained in those logs
> are now committed and saved in your database backup.
<snip>

You also need to keep any binary logs until all slaves to that server
have finished with them. You can find that out by running 
 SHOW SLAVE STATUS;  # on the slave(s) look for Master_Log_File
 SHOW MASTER STATUS; # on the master
and comparing the binary log filenames. You can delete any numbered less
than the lowest numbered log listed in the slave status output.

I found that when I set this up by default mysql didn't create a new bin
log until the old one was over a gig, which meant it couldn't be cleared
up for weeks (not a busy database). So I set the max_binlog_size option
in my.cnf to a 100 meg so I could clean up more often.

Note that you should not just delete the bin logs. Instead use PURGE
MASTER LOGS. See
http://dev.mysql.com/doc/refman/4.1/en/purge-master-logs.html

hth,
mark

> 
 





MARK ADDISON
WEB DEVELOPER

200 GRAY'S INN ROAD
LONDON
WC1X 8XZ
UNITED KINGDOM
T +44 (0)20 7430 4678
F 
E MARK.ADDISON@stripped
WWW.ITN.CO.UK

-----Original Message-----

> From: Warren Crigger [mailto:warren@stripped] 
> Sent: Thursday, 10 August 2006 9:52 AM
> To: mysql@stripped
> Cc: warren@stripped
> Subject: MySQL Replication Binary Logs - How Long to Keep?
> 
> I've just recently set up MySQL replication amongst two servers so I'm
> not
> too familiar with it.  I was cleaning up my /var filesystem and found
> the
> binary data below being stored in /var/lib/mysql, taking up 1.5gb.  
> 
> I did a little reading on mysql.org docs.  My interpretation was that
> you
> can have it replicate every so often, then you can purge these after
> that
> happens..however, my replication is instantaneous.  I can insert a
> record on
> the master and then go select it on the slave immediately.  Is there any
> reason to keep this data?  It's replicated to the 2nd server, in
> addition to
> dumps of the more important databases nightly, and dumps of the entire
> server weekly, which go to different physical drives and then ultimately
> off-site.  I like redudancy obviously, however, this seems pretty
> useless to
> me.
> 
> If it is of use, is there a way I can put this on a different
> filesystem,
> keeping the databases themselves within /var/lib/mysql?
> 
> Thanks,
> Warren
> 
> [root@hosting mysql]# pwd
> /var/lib/mysql
> [root@hosting mysql]# ls -lart |grep repl
> -rw-rw----    1 mysql    mysql     3088434 Jun 11 04:02 repl.001
> -rw-rw----    1 mysql    mysql         107 Jun 11 04:02 repl.002
> -rw-rw----    1 mysql    mysql    188387006 Jun 18 04:02 repl.003
> -rw-rw----    1 mysql    mysql         107 Jun 18 04:02 repl.004
> -rw-rw----    1 mysql    mysql         107 Jun 25 04:02 repl.006
> -rw-rw----    1 mysql    mysql    156749380 Jun 25 04:02 repl.005
> -rw-rw----    1 mysql    mysql    153489679 Jul  2 04:02 repl.007
> -rw-rw----    1 mysql    mysql         107 Jul  2 04:02 repl.008
> -rw-rw----    1 mysql    mysql         107 Jul  9 04:02 repl.010
> -rw-rw----    1 mysql    mysql    140922795 Jul  9 04:02 repl.009
> -rw-rw----    1 mysql    mysql    58638790 Jul 11 17:30 repl.011
> -rw-rw----    1 mysql    mysql       38410 Jul 11 17:46 repl.012
> -rw-rw----    1 mysql    mysql     5927431 Jul 12 09:00 repl.013
> -rw-rw----    1 mysql    mysql    80007235 Jul 16 04:02 repl.014
> -rw-rw----    1 mysql    mysql         107 Jul 16 04:02 repl.015
> -rw-rw----    1 mysql    mysql         107 Jul 23 04:02 repl.017
> -rw-rw----    1 mysql    mysql    155468996 Jul 23 04:02 repl.016
> -rw-rw----    1 mysql    mysql         107 Jul 30 04:02 repl.019
> -rw-rw----    1 mysql    mysql    155806419 Jul 30 04:02 repl.018
> -rw-rw----    1 mysql    mysql         107 Aug  6 04:02 repl.021
> -rw-rw----    1 mysql    mysql    159420166 Aug  6 04:02 repl.020
> -rw-rw----    1 mysql    mysql     6366383 Aug  6 13:33 repl.022
> -rw-rw----    1 mysql    mysql     1138297 Aug  6 17:36 repl.023
> -rw-rw----    1 mysql    mysql         264 Aug  6 17:40 repl.index
> -rw-rw----    1 mysql    mysql    43014905 Aug  9 00:03 repl.024
> 
>
Please Note:

 

Any views or opinions are solely those of the author and do not necessarily represent 
those of Independent Television News Limited unless specifically stated. 
This email and any files attached are confidential and intended solely for the use of the
individual
or entity to which they are addressed. 
If you have received this email in error, please notify postmaster@stripped 

Please note that to ensure regulatory compliance and for the protection of our clients and
business,
we may monitor and read messages sent to and from our systems.

Thank You.

Thread
MySQL Replication Binary Logs - How Long to Keep?Warren Crigger10 Aug
  • RE: MySQL Replication Binary Logs - How Long to Keep?SST - Adelaide)10 Aug
    • RE: MySQL Replication Binary Logs - How Long to Keep?mark addison10 Aug
      • RE: MySQL Replication Binary Logs - How Long to Keep?Warren Crigger12 Aug
        • RE: MySQL Replication Binary Logs - How Long to Keep?mark addison14 Aug