Ta yaya master–replica replication na MySQL ke aiki, kuma ta yaya yake taimakawa read scaling?

Question

IT Interview Practice · Accepted Answer

Replication yana yaɗa canje-canje daga **source (master)** zuwa ɗaya ko fiye da **replicas**. Source yana rikodin kowane canjin da aka commit a **binary log (binlog)** ɗinsa; I/O thread na kowace replica yana kwafa wannan log kuma SQL/applier thread yana sāke aiwatar da shi, don haka replicas suna haɗuwa zuwa bayanai iri ɗaya. ## Yadda yake gudana ``` writes app ---------> [ SOURCE ] --- binlog ---> [ replica 1 ] <-- reads | [ replica 2 ] <-- reads +---------- binlog ---> [ replica 3 ] <-- reads ``` - **Formats** — `ROW` (yana rikodin ainihin row images, tsoho mai aminci), `STATEMENT`, ko `MIXED`. - **Async (tsoho)** — source yana commit ba tare da jiran replicas ba: mai sauri, amma replica na iya jinkiri. - **Semi-sync** — source yana jiran aƙalla replica ɗaya ta amsa, yana canja ɗan jinkiri don durability. ## Read scaling Nuna writes zuwa source kuma yaɗa `SELECT`s a kan replicas. Manhajojin da ke mai da hankali kan karatu (yawancin web apps) suna scale karatu kusan linearly ta ƙara replicas, yayin da source ɗaya har yanzu ke sarrafa dukan writes. ```sql -- On a replica, watch how far behind it is: SHOW REPLICA STATUS\G -- Seconds_Behind_Source: 2 <- replication lag -- Replica_IO_Running: Yes -- Replica_SQL_Running: Yes ``` ## Matsalar: replication lag Saboda async replicas suna bin baya bayan source, mai amfani da ke rubutawa sannan nan da nan ya karanta daga replica na iya rashin ganin canjinsa ("read-your-writes" violation). Tura karatun da ke da hankali ga latency ko wanda aka rubuta kwanan nan zuwa source, ko yi amfani da semi-sync. ## Me ya sa yake da muhimmanci Replication shine matakin farko na yau da kullum don scale karatu da samun warm standby don failover/backups. Iyakarsa ta bayyani — lag, da cikas na rubutu ɗaya — tana tsara ƙirar gaske: tana bayyana dalilin da wani lokaci kake karanta bayanai marasa sabo da dalilin da write scaling a ƙarshe ke buƙatar sharding, ba kawai ƙarin replicas ba.

Yadda yake gudana

Read scaling

Matsalar: replication lag

Me ya sa yake da muhimmanci

Tambayoyi masu alaƙa