4

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?

4

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.

  • "log-slave-updates must not be enabled on the Slave" seems to imply that Otherwise Bad Things Will Happen™, which isn't the case in any sense I can think of. The expire_logs_days system variable can prevent unbounded binlog growth, too. I don't have a slave anywhere in my infrastructure that doesn't use log_slave_updates, regardless of its role. In less I'm missing something, "should probably not be enabled" seems more fitting. – Michael - sqlbot Sep 18 '15 at 1:16
2

This is correct:

  1. The master writes to its "binary log" files
  2. The slave reads its master's binary files and write them to its "relay log" files
  3. The slave executes the statements in its "relay log" files.

Therefore, for replication purposes, slave's binary files are not needed.

And, yes, you can disable binary logging or purge these files if not needed for other purposes like "backup".

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.