Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a question about deleting binary logs in the replication environment:

We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy processing times, whereby bin log directory gets full. Logs are expired every 3 days. I was wondering, is there a reason why logs should be kept for 3 days on all the boxes – master and both slaves? Would it make sense to, for example, keep logs for 3 days on a master, but for 1 day on slaves? What is the best way to go about it?

Thank you!

share|improve this question
    
Welcome to the DBA.SE. This question deserves a +1 because binary log and relay log growth is often taken for granted, and can be the source many problems if left unchecked. –  RolandoMySQLDBA Aug 2 '12 at 23:36

1 Answer 1

SLAVE

If your Slaves are not Masters, then Slaves do not need binary logging at all. You can put a cap on the amount of relay log space accumulated by a Slave. In order to throttle relay logs at 4G, add relay_log_space_limit to /etc/my/.cnf on every Slave

[mysqld]
relay_log_space_limit=4G

and restart mysql

If you cannot set this, at least you should have some kind of alerting that does SHOW SLAVE STATUS\G and check the value of Relay_Log_Space (total bytes consumed by relay logs).

MASTER

As for the Master, you could set expire_logs_days to 1, but there is a severe warning I have for you...

If replication breaks, you have 1 day to fix it. Otherwise, a binary log on the Master may rotate away and you cannot run any CHANGE MASTER TO command to realign replication. I would leave expire_logs_days at 3 on the Master.

SUGGESTION #1

If you have any overnight bulk processing to do, maybe should run the bulk processes on on the Master with SET SQL_LOG_BIN=0; at the Start of the Session. This, of course, will not replicate to the Slave. You can perform the Same Bulk Load in Parallel to both Slaves.

SUGGESTION #2

Another thing you could do to manage the Master binary logs accumulation is this.

Run SHOW SLAVE STATUS\G on both Slaves. Look at Relay_Master_Log_File. That represents the binary log on the Master whose last command was executed on the Slave.

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.4.92.250
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.009677
        Read_Master_Log_Pos: 855227755
             Relay_Log_File: relay-bin.000674
              Relay_Log_Pos: 757296783
      Relay_Master_Log_File: mysql-bin.009590
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 757296646
            Relay_Log_Space: 94274010765
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 80561
1 row in set (0.00 sec)

In this example, Relay_Master_Log_File is mysql-bin.009590. All binary logs before this one can be removed from Master. You could run this on the Master:

PURGE BINARY LOGS TO 'mysql-bin.009590';

This will erase older logs and still leave replication in tact.

CAVEAT

Binary Logs are files that serially compiles (like a FIFO queue) all completed SQL transactions as either a SQL statement or a row change. A relay log is a file that collects binary log entries from a remote server (aka Master).

In MySQL Replication

  1. Master must have its binary logs enabled
  2. Slave compiles relay logs
  3. When all SQL in a relay log is processed, it is deleted
  4. On a Slave, when there is more that one relay log on a DB Server, it may indicate replication is falling behind because the IO thread is collecting SQL from a Master faster that the SQL thread can process the relay logs.
  5. Using relay_log_space_limit prevents replication from piling up and potentially filling up a disk. Relay logs rotate out based on rule #3
  6. It is possible for a DB Server to be both a Master and Slave. That's the only circumstance under which a Slave must have binary logs enabled. In that scenario, a DB Server will have both Binary Logs and Relay Logs.

If you failover to a Slave, and you want to Make it a Master

  • service mysql stop
  • Add log-bin=mysql-bin to /etc/my.cnf on the Slave
  • service mysql start

You will have to setup replication of other Slaves to the newly promoted Master and make sure the data on the Slave match up with the newly promoted Master

UPDATE 2012-08-13 17:47 EDT

According to the MySQL Documentation on relay-log option, you should define it. Here is why:

Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.3, “Specifying Program Options”.

share|improve this answer
    
Rolando, thank you! I am a bit confused (I am comming from an Oracle world, mysql is still new to me). What is a difference between relay logs and bin logs? We have one master, and the other two are slaves. But after switching over, one of the slaves will become master. And if we dont keep any logs there at all, wouldnt it be a problem? So are relay logs the same as bin logs for slaves? Thank you! –  anna Aug 3 '12 at 14:01
    
One more thing - If we set 4G gap for slaves (that's what relay_log_space_limit) means correct? So if we set this limit to 4G, does it mean that once 4G limit is reached, previous logs will be deleted? During heavy processing we might have over 50G of logs generated in about 8 hours. So if we set this limit to 4G on slaves, that means that logs of that day will be deleted. Will that cause an issue do you think when we have to failover for some reason? Thank you!!! –  anna Aug 3 '12 at 14:06
    
I updated my answer with a caveat for you to read –  RolandoMySQLDBA Aug 3 '12 at 15:08
    
THank you! It is weird, but I could not find anything with RELAY in in in my.cnf in master or two slaves. All contain log-bin=/app/mysql/binlogs/mysql-bin and all generating bin logs. Slaves are not generating relay logs. The thing is that on top of these 3 we have a load balancer which can switch to slaves whenever needed. So we don't usually do any manual changes for the switch to happen. Is there a way to check if slaves are also master? I am trying to look for a specific seetting. Thank you! –  anna Aug 3 '12 at 19:07
    
If the slave is working then there are relay logs. If you do not configure a relay log name, the default relay log name will be something like relay-hostname.*. Just run SHOW SLAVE STATUS\G on the Slave and look for line 8 (Relay_Log_File). By default, they should appear in /var/lib/mysql or wherever datadir is. –  RolandoMySQLDBA Aug 3 '12 at 19:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.