1

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.

0

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
  • 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?)

| improve this answer | |
  • Thank you, I got it, just a clarification for my understanding. Consider a bank fund transaction, once the money reached from A to B the transaction is success, if it is not updated on slave's relay log, the transaction will be rolled backup and amount will be returned to A. Now whether the transaction is available on tablespace(disk). Since as per my understanding innodb logs will be flushed to tablespace before writing to binary log. Please correct me if I am wrong. – Rathish May 25 '16 at 14:31
  • Rolando -- you left out the binlog_cache and the exact timing of the ACK back to the client. – Rick James Jun 1 '16 at 19:36
  • sync_binlog is recommended. – Rick James Jun 1 '16 at 19:37

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.