Search code examples
mysqldatabasereplicationdatabase-replicationsystem-design

Master Slave Replication in Databases

As per How To Set Up Replication in MySQL,

Once the replica instance has been initialized, it creates two threaded processes. The first, called the IO thread, connects to the source MySQL instance and reads the binary log events line by line, and then copies them over to a local file on the replica’s server called the relay log. The second thread, called the SQL thread, reads events from the relay log and then applies them to the replica instance as fast as possible.

Isn't it contradictory to the theory of master-slave database replication in which the master copies data to the slaves?

Solution

  • Reliability. (A mini-history of MySQL's efforts.)

    When a write occurs on the Primary, N+1 extra actions occur:

    • One write to the binlog -- this is to allow for any Replicas that happen to be offline (for any reason); they can come back later and request data from this file. (Also see sync_binlog)
    • N network writes, one per Replica. These are to get the data to the Replicas ASAP.

    Normally, if you want more than a few Replicas, you can "fan out" through several levels, thereby allowing for an unlimited number of Replicas. (10 per level would give you 1000 Replicas in 3 layers.)

    The product called Orchestrator carries this to an extra level -- the binlog is replicated to an extra server and the network traffic occurs from there. This offloads the Primary. (Booking.com uses it to handle literally hundreds of replicas.)

    On the Replica's side the two threads were added 20 years ago because of the following scenario:

    • The Replica is busy doing one query at a time.
    • It gets busy with some long query (say an ALTER)
    • Lots of activity backs up on the Primary
    • The Primary dies.

    Now the Replica finishes the Alter, but does not have anything else to work on, so it is very "behind" and will take extra time to "catch up" once the Primary comes back online.

    Hence, the 2-thread Replica "helps" keep things in sync, but it is still not fully synchronous.

    Later there was "semi-synchronous" replication and multiple SQL threads in the Replica (still a single I/O thread).

    Finally, InnoDB Cluster and Galera became available to provide [effectively] synchronous replication. But they come with other costs.