MySQL relay log rotates too soon and too often

Question

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?

Answer 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.