1

We have MySQL replication setup from site-to-site. The slave is just a backup used for reporting etc(No write). Everything is going well as far as I can tell.

However, the server is quickly running out of space on the partition mysql replication logs to.

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 
                  Master_User: 
                  Master_Port: 
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 43158527
               Relay_Log_File: mysql-relay-bin.000015
                Relay_Log_Pos: 43158672
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
           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: 43158527
              Relay_Log_Space: 43158870
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

Here is where the confusion comes in. I want to implement expire_logs_days to 3 days or so...

However I am afraid of breaking replication because;

Master_log_file = 006 and it's date is Nov 18.

-rw-rw----. 1 mysql mysql  20K Nov  3 15:36 mysql-bin.000001
-rw-rw----. 1 mysql mysql 748K Nov  3 15:36 mysql-bin.000002
-rw-rw----. 1 mysql mysql 1.1G Nov 18 09:06 mysql-bin.000003
-rw-rw----. 1 mysql mysql 216M Nov 18 09:22 mysql-bin.000004
-rw-rw----. 1 mysql mysql  125 Nov 18 09:39 mysql-bin.000005
-rw-rw----. 1 mysql mysql 1.1G Nov 18 11:34 mysql-bin.000006
-rw-rw----. 1 mysql mysql 1.1G Nov 18 11:41 mysql-bin.000007
-rw-rw----. 1 mysql mysql 1.1G Nov 18 14:23 mysql-bin.000008
-rw-rw----. 1 mysql mysql 1.1G Nov 18 14:29 mysql-bin.000009
-rw-rw----. 1 mysql mysql 1.1G Nov 19 08:57 mysql-bin.000010
-rw-rw----. 1 mysql mysql 366M Nov 20 09:37 mysql-bin.000011
-rw-rw----. 1 mysql mysql  60M Nov 20 12:09 mysql-bin.000012
-rw-rw----. 1 mysql mysql  23K Nov 20 12:10 mysql-bin.000013
-rw-rw----. 1 mysql mysql 1.1G Nov 23 00:43 mysql-bin.000014
-rw-rw----. 1 mysql mysql 1.1G Nov 26 12:03 mysql-bin.000015
-rw-rw----. 1 mysql mysql 251M Nov 27 11:33 mysql-bin.000016
-rw-rw----. 1 mysql mysql  304 Nov 26 12:03 mysql-bin.index

Questions

  • Do I need to set a higher days threshold for log file expiry?

  • IE; will "SET GLOBAL expire_logs_days = 3;" break my replication because mysql-bin.000006 is the current master_log_file?

  • Perhaps setting the log file size limit lower in addition to expiry is a solution?

I've looked into these for the original idea but I want to be 100% sure: https://dba.stackexchange.com/questions/41050/is-it-safe-to-delete-mysql-bin-files http://dev.mysql.com/doc/refman/5.1/en/replication-administration-status.html

Thanks in advance

4

You are looking at your slave's own binary logs. Your slave has clearly the log_bin variable set, and it's filling its disks with it's own binary logs (not the master's logs, their names probably contain a relay word, since you are using defaults in binary and relay log names). You can view them with show master status. To solve this problem (if you don't have any slaves attached to this slave) you can set the log_bin variable to OFF. Yup, you can also set the expire_logs_days variable on slave and it won't break anything (because it controls binary logs expiration date on a server it's configured on) but in case you don't need them at all - why keep 'em ?

  • Thanks for this! I was wrong with the way I thought replication worked! You are spot on, there is indeed relay logs. Perfect answer. I would upvote but I cant :( – Epxs Nov 28 '14 at 6:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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