MySQL – Are Binary Logs Required on Slave for Replication?

MySQLreplication

I have a MySQL database setup with a master and a slave. As far as I understand, the master writes transaction events to a binary log, which is read and executed by the slave, thus making the slave a continuous copy of the master.

I was under the impression that I needed to make sure that the slave had consumed a specific binary log file before I could safely purge it. But now I have read documents indicating that the binary log isn't really used by the slave in order to facilitate replication. It is instead the relay log which is of interest on the slave. The binary log is only interesting to keep if I need to do point-in-time restores. Is this correct?

If correct, does this mean that I can either turn off binary logs on the slave or purge them all at any time?

Best Answer

Other than doing point-in-time restores, there are two additional reasons a Slave could have binary logging enabled.

REASON #1

If a Slave is also a Master

  • binary logging must be enabled on Slave
  • log-slave-updates must be enabled on the Slave

This would allow another Slave to connect to a Slave with binary logging enabled as a Master

REASON #2

If a Slave is not a Master, but you want to failover to the Slave to become a new Master

  • binary logging must be enabled on Slave
  • log-slave-updates must not be enabled on the Slave (this prevents binary logs from growing on a Slave as long as the DB Server has the role of a Slave)

That way, after failing over your application to the Slave to become the Master, you do not have to restart mysql to enable it later. You could then setup replication to the new Master.