3

I have a simple master/slave setup where the slave is just a backup. Sometimes I notice that the relay log on the slave rotates too often and too soon, when it is still very small:

4:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860318633
               Relay_Log_File: vps-relay-bin.000350
                Relay_Log_Pos: 922

5:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860324526
               Relay_Log_File: vps-relay-bin.000351
                Relay_Log_Pos: 6176

7:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860331661
               Relay_Log_File: vps-relay-bin.000352
                Relay_Log_Pos: 7418

9:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860333988
               Relay_Log_File: vps-relay-bin.000353
                Relay_Log_Pos: 501

Notice that the master log is the same and its position didn't change much. But the relay log rotated several times, and didn't get very big. MySQL .err shows:

2014-03-23 14:35:14 4971 [Note] Slave I/O thread: connected to master '<removed>',replication started in log 'mysql-bin.000051' at position 860316289
2014-03-23 15:59:50 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 16:59:52 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 18:59:56 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 20:35:53 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 21:35:55 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 22:35:57 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

Master's log doesn't show anything.

What could be happening here?

1

You can verify by yourself as slave server creates (rotates) a new relay log file under the following conditions only:

  • Each time the I/O thread starts.
  • When the logs are flushed; for example, with FLUSH LOGS or mysqladmin flush-logs
  • When the size of the current relay log file becomes “too large,” determined as follows:

    If the value of max_relay_log_size is greater than 0, that is the maximum relay log file size.
    
    If the value of max_relay_log_size is 0, max_binlog_size determines the maximum relay log file size. 
    
  • The SQL thread automatically deletes each relay log file as soon as it has executed all events in the file and no longer needs it. There is no explicit mechanism for deleting relay logs because the SQL thread takes care of doing so. However, FLUSH LOGS rotates relay logs, which influences when the SQL thread deletes them.

  • Well, the replication threads on the slave and master are not dying. They are still the same number. The log sizes are the default, so it should rotate at 1G. I am not issuing these flush commands. Would they show on the log? – user34814 Mar 24 '14 at 16:24
  • I also noticed that the excessive rotation happens when the servers are idle. When they are busy, it seems to work fine and the relay log fills up to its limit. – user34814 Mar 24 '14 at 16:37
  • Also kill -HUP makes for an implicit FLUSH LOGS. Check if you have a crontab entry that does that – Shlomi Noach Nov 19 '14 at 7:29

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.