MySQL duta safui cascade dɔwɔwɔwo va ƒo binary log la mlɔeba

MySQL duta safui cascade dɔwɔwɔwo dzena le binary log me azɔ, si dea dzesi tɔtrɔ si wole mɔ kpɔm na ɣeyiɣi didi aɖe si kpɔa ŋusẽ ɖe egbugbɔgawɔ, agbalẽdzikpɔkpɔ, kple nyatakakawo gbugbɔgaxɔ dzi tẽ na nyatakakadzraɖoƒe-ʋuʋu ƒe dɔwɔwɔwo. Nuwɔna ƒe tɔtrɔ sia — si woto vɛ vivivi le MySQL ƒe tɔtrɔwo me — xea dometsotso vevi aɖe si hea nyatakaka siwo me ɖoɖoezizi le ƒe masɔmasɔwo vɛ tsã le nɔnɔme siwo wogbugbɔ wɔ me.

Nukae Nye Duta Key Cascade Dɔwɔwɔwo Kple Nukatae Wonɔ Binary Log la Me?

Duta safui ƒe mɔxenuwo zia nufiame ƒe blibonyenye dzi le kplɔ̃wo dome. Ne èɖe ON DELETE CASCADE alo ON UPDATE CASCADE gɔme la, MySQL kaka tɔtrɔwo tso dzila kplɔ̃ dzi yi ɖevi fli siwo do ƒome kplii gbɔ le eɖokui si. Ƒe geɖe la, tɔtrɔ siawo siwo le tsia dzi wɔa dɔ abe nugbegblẽ siwo womekpɔna o ene — woŋlɔa nya si ʋãa ame la ɖi, gake womeŋlɔa fli siwo le anyime siwo dzi ʋuʋudedia kpɔ ŋusẽ ɖo la ɖi abe nudzɔdzɔ siwo le wo ɖokui si ene le nuŋlɔɖi binary me o.

Esia menye ŋkumaʋumaʋu o. MySQL ƒe ƒuƒoƒoa susui gbã be nɔnɔmetata siwo wogbugbɔ wɔ, siwo ma schema ɖeka kple duta safui gɔmeɖeɖewo, agbugbɔ cascades awɔ le wo ɖokui si ne nya gbãtɔa gbugbɔ ƒo. Susu la lé — vaseɖe esime mewɔe o. Schema drift, partial replication filters, nudzraɖoƒe ƒe mɔ̃ ƒe ɖoɖo vovovowo, kple point-in-time recovery scenarios katã ɖe alesi susu sia ƒe gbagbã le la ɖe go. Cascade ɖeka si to ateŋu awɔ diverged replicas siwo dze abe ɖe wole lãmesẽ me ene vaseɖe esime production-critical questions natrɔ emetsonu siwo mesɔ o.

Aleke Cascade Logging ƒe Nuwɔna Yeyea Wɔa Dɔ ŋutɔŋutɔe?

Kple nuwɔna yeyea la, MySQL ŋlɔa fli siwo dzi cascade dɔwɔwɔwo kpɔ ŋusẽ ɖo abe fli ƒe nudzɔdzɔ siwo dze ƒã ene le binary log me, le DML nyagbɔgblɔ si ʋãa ame la yome enumake. Ne ètutu dzila ƒe nuŋlɔɖi eye vi nuŋlɔɖi etɔ̃ le tsia dzi la, fli ƒe tɔtrɔ eneawo katã adze azɔ abe nuŋlɔɖi ƒe nuŋɔŋlɔ siwo to vovo, siwo woate ŋu adzro ene.

Nuŋɔŋlɔ ƒe ɖoɖo le vevie le afisia. Row-based binary logging (RBL) nye ɖoɖo si na esia te ŋu dzɔna kple nuteƒewɔwɔ blibo. Nyagbɔgblɔ si wotu ɖe nuŋlɔɖi dzi mateŋu alé dynamic cascade ƒe emetsonuwo kakaɖedzitɔe o elabena emetsonua nɔ te ɖe nyatakaka ƒe nɔnɔme dzi le ewɔwɔɣi, ke menye SQL nuŋɔŋlɔa ŋutɔ dzi o. Mixed-mode logging wɔa fli ƒe nɔnɔme ŋudɔ tiatiatɔe le nɔnɔme siawo me.

ƒe nyawo

Key Insight: Fli-si wotu ɖe binary logging ƒe dɔwɔwɔ menye gbugbɔgawɔ ƒe tiatia ko o — fifia enye nusi hiã hafi woalé nyatakaka ƒe tɔtrɔ ɖesiaɖe ƒe agbalẽdzikpɔkpɔ ƒe mɔzɔzɔ blibo, si ŋu woateŋu aɖo kpee le ɖoɖo siwo zãa duta safui ƒe mɔxenuwo me. Ema manɔmee la, cascade ƒe ŋusẽkpɔɖeamedziwo nɔa anyi le akpa aɖe si womekpɔna na wò dɔwɔnuwo o.

Ele be nyatakakadzraɖoƒe dzikpɔlawo naɖo kpe woƒe binlog_format ɖoɖoa dzi eye woato woƒe nɔnɔmetatawo gbugbɔgawɔ ƒe nɔnɔme ƒe susuwo me nenye be le ŋutinya me la, woɖo ŋu ɖe nɔnɔmetata ƒe akpa dzi cascade ƒe dɔwɔwɔ ŋu hena ɖekawɔwɔ ƒe kakaɖedziwo.

Xexeme Ŋutɔŋutɔ ƒe Kuxi Kawoe Esia ɖɔna ɖo na Dɔwɔhawo?

Nusi wòfia la ɖoa nusi gbɔ nukpɔsusu ƒe gbugbɔgawɔ ƒe dzɔdzɔenyenye ŋu sãsãsã. Ƒuƒoƒo siwo le asitsatsa ƒe mɔnu sesẽwo zãm kple ƒomedodo ŋuti nyatakakawo ƒe kpɔɖeŋuwo — SaaS adzɔnuwo siwo hayala geɖe, e-asitsatsa ƒe mɔ̃wo, lãmesẽnyawo ŋuti nuŋlɔɖi ƒe ɖoɖowo — do go kpododonu ƒe mɔnu ŋutɔŋutɔ siwo tɔtrɔ sia kpɔa egbɔ:

ƒe nyawo
• Point-in-time recovery accuracy: Nyatakakadzraɖoƒe gbugbɔgaɖoanyi ɖe ɣeyiɣi kpui aɖe hafi mass delete la léa ɖeviwo ƒe cascaded deletions katã azɔ, ke menye biabia si le ʋuʋum la ɖeɖeko o, si xea mɔ na phantom rows be woagadze le gbugbɔgaxɔ megbe o.
• Trɔ nyatakakawo léle (CDC) ƒe pɔmpiwo: Dɔwɔnu abe Debezium kple Maxwell siwo tsɔa binary log events yia Kafka alo sinks bubuwo gbɔ xɔa nyatakaka ƒe tɔtrɔ ɖesiaɖe ƒe nɔnɔmetata bliboa fifia, si wɔnɛ be woate ŋu awɔ downstream projections pɛpɛpɛ.
• Akɔntabubu kple sedziwɔwɔ ŋuti nuŋlɔɖi: Dɔwɔƒe siwo ŋu wowɔ ɖoɖo ɖo siwo bia nyatakaka ƒe dzidzime blibo ate ŋu akpɔ ɖeviwo ƒe nuŋlɔɖi siwo tututu woɖe ɖa le dzila ƒe dɔwɔwɔ me tsonuwo ta la azɔ, si ana agbalẽdzikpɔkpɔ ƒe nudidiwo nava eme na dɔwɔwɔ-ƒomevi ƒe dɔwɔwɔ manɔmee.
• Replica divergence detection: Kpɔkpɔdɔwɔnu siwo tsɔa replica row counts alo checksums sɔna kple primary la, log data siwo de pɛpɛpɛ le wo si fifia be woatsɔ ade dzesi vovototo ɖesiaɖe ahade dzesii kaba.
• Dzɔtsoƒe geɖe kple gotagome gbugbɔgawɔ: Topology sesẽ siwo hiã tsã be woawɔ duta safuiwo nuwɔametɔwo le nɔnɔmetatawo dzi be woaƒo asa na cascades zazã zi eve la kpɔa nudzɔdzɔ ƒe tɔsisi si woɖo ɖe ɖoɖo nu wu, si woate ŋu agblɔ ɖi be woade ŋugble le eŋu.

Aleke Wòle Be Nyatakakadzraɖoƒe ƒe Xɔtuɖaŋunyalawo Natrɔ Asi Le Woƒe Gbugbɔgawɔ Kple Wo Gbugbɔgaxɔ ƒe Mɔnuwo Ŋu?

Cascade-aware binary logging xɔxɔ menye passive upgrade o — ebia be woaɖoe koŋ alé ŋku ɖe xɔtuɖoɖo siwo li ŋu. Log nudzɔdzɔ bubu siwo cascades wɔ la dzia binary log volume ɖe edzi, ɣeaɖewoɣi ŋutɔ, le alesi wò schema zãa cascade ƒe mɔxenuwo vevie nu. Ele be xɔtuɖaŋunyalawo nabu akɔnta le disk I/O si dzi ɖe edzi, network replication bandwidth, kple log rotation frequency ŋu.

Le nɔnɔmetatawo ƒe nɔnɔmetatawo me la, ele be ƒuƒoƒowo nalé ŋku ɖe eŋu nenye be wowɔ duta safui ƒe mɔxenuwo ŋudɔ alo womewɔ dɔ o. Ŋutinya me nuwɔna si bɔ enye be woatsi duta safuiwo nu le nɔnɔmetata siwo wogbugbɔ wɔ dzi (foreign_key_checks=OFF) be woana gbãtɔa nakpɔ sedziwɔwɔ gbɔ. Esi woŋlɔ cascades ɖi tẽ fifia ta la, cascade nudzɔdzɔ mawo siwo woŋlɔ ɖi la zazã ɖe replica si hã wɔa dɔ tso eya ŋutɔ ƒe duta safui ƒe mɔxenuwo ŋu ate ŋu ahe ʋuʋu zi eve alo masɔmasɔwo vɛ. Ðoɖowɔwɔ ƒe ɖoɖowɔwɔ le kɔsɔkɔsɔa ƒe gbugbɔgawɔ me le vevie fifia wu tsã.

Na ƒuƒoƒo siwo zãa dɔwɔnu siwo me susu le siwo wotsɔ dzraa nu ɖo abe mysqldump alo nuwo gbugbɔgadzraɖo ƒe kuxiwo gbɔkpɔnu ŋutɔŋutɔwo abe Percona XtraBackup ene la, ŋkuléle ɖe gbugbɔgaɖoɖo ƒe ɖoɖowo ŋu ɖe nuŋlɔɖi ƒe ɖoɖo yeyea ŋu kpɔa egbɔ be gbugbɔgaxɔ ƒe runbookwo gakpɔtɔ le eteƒe. Ele be dodokpɔ gbugbɔgawɔ ƒe nɔnɔmewo le staging nɔnɔmewo me ɖe nuwɔwɔ-teƒenɔla ƒe nyatakaka gbogbowo ŋu nanye ƒe etɔ̃ ɖesiaɖe ƒe dɔwɔwɔ si wowɔna ɖaa.

Aleke Esia Sɔ Kple Alesi PostgreSQL Kple Nyatakakadzraɖoƒe Bubuwo Kpɔa Cascade Logging Gbɔe?

PostgreSQL ŋlɔ duta safui cascade ƒe ŋusẽkpɔɖeamedziwo ɖi ɣeyiɣi didi aɖee nye sia abe gbãtɔ-klas WAL (Write-Ahead Log) nuŋlɔɖiwo ene. Woŋlɔa fli ɖesiaɖe si cascade trigger ka asi eŋu la ɖe WAL ɖekaɖeka, si wɔnɛ be PostgreSQL gbugbɔgawɔ kple logical decoding nye cascade-aware le dzɔdzɔme nu. Aɖaŋuwɔwɔ ƒe xexemenunya sia — ŋlɔ ŋusẽkpɔɖeamedziwo katã, menye nusiwo gbɔ wòtso ɖeɖeko o — nye nusi MySQL ƒe fli-dzi binary log te ɖe eŋu wu fifia.

Microsoft SQL Server hã léa tɔtrɔ siwo le cascaded me to eƒe asitsatsa ŋuti nuŋlɔɖi me le fli ƒe ɖoɖo nu, si wɔnɛ be CDC egbɔkpɔnuwo le SQL Server dzi nɔa tẽ na ƒomedodo ƒe ɖoɖo sesẽwo gɔ̃ hã. MySQL ƒe tɔtrɔ yeyea hee va xɔtuɖaŋu ƒe sɔsɔ kple ɖoɖo siawo me kplikplikpli, si ɖe tsitretsiɖeŋu vevi aɖe si nyatakakadzraɖoƒe ƒe mɔ̃ɖaŋudɔwɔlawo fɔ ɖe te ne wole MySQL me tom hena ƒomedodo ƒe dɔwɔwɔ ƒe agba siwo le blibodede gã la dzi kpɔtɔ.

Na habɔbɔ siwo le nyatakakadzraɖoƒe geɖe ƒe nɔnɔmewo zãm alo le ʋuʋumɔwo me tom la, ƒoƒo ɖekae sia le vevie. Eɖea nuwɔna ƒe anyigba ƒe akpa si le nuƒolanɔƒewo dome si gɔme wòle be dɔwɔwɔ ƒe ƒuƒoƒowo nase eye woabu akɔnta le dɔwɔnuwo kple ɖoɖowo me dzi kpɔtɔna.

Nyabiase Siwo Wobiana Enuenu

Ðe tɔtrɔ sia kpɔa ŋusẽ ɖe MySQL 5.7 dzi loo alo MySQL 8.x ɖeɖeko dzia?

Nuwɔna ƒe ŋgɔyiyiwo le cascade logging me koŋ do ƒome kple MySQL 8.0 kple esiwo woɖe ɖe go emegbe, kple ŋgɔyiyi tɔxɛ siwo wobla ɖe point ɖekaɖekawo ƒe dodo le 8.0.x ƒe xexlẽdzesiwo me. MySQL 5.7 ɖo eƒe agbenɔƒe ƒe nuwuwu le October 2023 me, eye ele be ƒuƒoƒo siwo gakpɔtɔ le edzi yim la nabu cascade logging nuwɔna le afima be womate ŋu aka ɖe edzi o hena replication consistency taɖodzinuwo. Dodo ɖe MySQL 8.0 alo MySQL 8.4 LTS ƒe dodo me nye mɔ si wokafu be woatsɔ akpɔ binary log nuwɔna si li fifia.

Ðe cascade logging blibo ƒe dɔwɔwɔ agblẽ nuŋɔŋlɔ ƒe dɔwɔwɔ me veviea?

Ga si woxena ɖe edzi la nyea nu ŋutɔŋutɔ gake zi geɖe la, ebɔbɔna na ɖoɖo siwo me dutanyanyuigblɔlawo ƒe ƒomedodo veviwo le nyuie. Log nuŋɔŋlɔ bubuawo do ƒome tẽ kple ɖeviwo ƒe fli ƒe xexlẽme si dzi cascade ɖesiaɖe kpɔ ŋusẽ ɖo. Schemas kple cascade kɔsɔkɔsɔ siwo me atɔ goglo alo ɖeviwo ƒe kplɔ̃ gã ŋutɔwo ate ŋu akpɔ ŋusẽ si woate ŋu adzidze le throughput. Nyatakaka ƒe agbɔsɔsɔme ŋutɔŋutɔwo ƒe nɔnɔmetata wɔwɔ le ɖoɖowɔɖi ƒe nɔnɔme me hafi woaɖee ɖe go yi ewɔwɔ me nye mɔnu nyuitɔ si dzi nàto adzidze gazazã ɖe wò dɔ ƒe agba tɔxɛa ŋu.

Ðe mateŋu azã binary log cascade data atsɔ ana nuɖuɖu ɣeyiɣi ŋutɔŋutɔ me numekuku ƒe pɔmpiwoa?

Ẽ, eye esia nye zazã ƒe nɔnɔme siwo ƒonɛ ɖe ame nu wu la dometɔ ɖeka. Esi cascade nudzɔdzɔ blibowo le binary log la me la, CDC dɔwɔnuwo ateŋu atu materialized views siwo de pɛpɛpɛ, didi ƒe indexwo, kple event streams azɔ evɔ mahiã be application-level hooks natsɔ asi ata cascade effects o. Ƒuƒoƒo siwo le ɣeyiɣi ŋutɔŋutɔ me dashboardwo, ametafatafa didi ƒe ɖoɖowo, alo dɔwɔwɔ ŋuti numekuku ƒe ƒuƒoƒo tum la akpɔe be log stream ƒe blibonyenye ɖea pɔmpi ƒe sesẽ dzi kpɔtɔna ŋutɔ.

Asitsatsa ƒe mɔnu si le dzidzim ɖe edzi dzi kpɔkpɔ fia be wò xɔtuɖoɖo ŋuti nyametsotso siwo le ete la nu sẽna ɖe edzi le ɣeyiɣi aɖe megbe — dometsotso aɖe le wò nyatakakadzraɖoƒe ƒe nɔnɔmetata gbugbɔgawɔ ƒe aɖaŋu me egbea va zua nudzɔdzɔ le agbɔsɔsɔ me etsɔ. Mewayz nye 207-module asitsadɔwɔɖoɖo si wotu na ƒuƒoƒo siwo gbe be yewomaɖe asi le kakaɖedzi alo ŋutete ŋu o, si dzi ezãla siwo wu 138,000 ka ɖe edzi siwo le nusianu zãm tso CRM kple e-commerce dzi va ɖo nyatakakawo dzikpɔkpɔ kple numekuku dzi — wo katã le mɔ̃ ɖeka dzi si dzea egɔme tso $19/ɣleti ko dzi.

Dze wò Mewayz dɔwɔƒe gɔme egbea le app.mewayz.com eye nàtu wò dɔwɔƒea ɖe xɔtuɖoɖo siwo zɔna ɖe nusi nènya nu dzi.