MySQL binary log - write operation

Question

Consider a scenario, where you have a MySQL master and slave server in replication. As we know, binary log enabled on the master and the slave reads from the binary log of the master. From MySQL documentation I understand that, only the fully committed transaction will be written to binary log, so there is no chance of missing any transaction in replication (if everything works well). So my assumption is that, once the transaction is completed and written to tablespace, it will be written to binary log.

My doubt is whether the write operation of the binary log is concurrent or sequential? It means, for example, before writing to binary log and after written to tablespace, if the server shuts down or crashes, what happen to the transactions which happened in mean time?

Also, I am using mysqlbinlog utility and set with stop-never parameter. I need to know, when stop-never enabled the binary log will be in sync on the destination server (I tested it with flush logs - it creates new log on destination too).

QUESTIONS

1. How the copying of binary log works?
2. Is it first writes to master binary log and copied to destination log?
3. Is it writing on both the source and destination binary log simultaneously?
4. What will be impact of MySQL performance and disk IO performance?

If I misunderstood the concept, kindly correct me. Thanks in advance.

Answer 1

Here are the answers to each of your question

1 How the copying of binary log works?

I wrote a detailed list of the steps taken by MySQL Replication in my answer to Is MySQL Replication Affected by a High-Latency Interconnect?.

Someone else answered that same post, but from the point of view of DB Client

2 Is it first writes to master binary log and copied to destination log?

There are many middle steps

• Master completes a SQL update (single update or group of updates (AKA group commit))
• Master writes one of the following to its binary log
  • the SQL command if binlog_format=STATEMENT
  • the row changes if binlog_format=ROW
• IO Thread from the Slave copies the SQL Update from Master's binary log to its relay logs
• SQL Thread on slave reads updates from its relay log and processes. If log_slave_updates is enabled, then the Slave writes that SQL command to its local binary log.

3 Is it writing on both the source and destination binary log simultaneously?

Never. Not with semisync replication, not with GTID replication either. Just look at the above steps

4 What will be impact of MySQL performance and disk IO performance?

That depends where binary logs and relay logs are stored. If data and logs are on the same disk mount, you will both random writes for data and sequential writes for logs. Both will be competing for the same disk space. You should separate them (See my post from last year Should the Redo log on a production MySQL server be configured to another location?)