MySQL jamana wɛrɛw ka kilisi kaskade baarakɛcogo labanna ka binary log gosi

| Nin kɛcogo jiginni in — min donna dɔɔnindɔɔnin MySQL sɛrɛkiliw kɔnɔ — o bɛ danfaraba dɔ datugu min tun bɛ kɛ sababu ye ka kunnafonidilanw mankan tɛ bɛn ɲɔgɔn ma fɔlɔ sigidaw la minnu bɛ segin-ka-bɔ.

dunanw ka kilisi baara ye mun ye ani mun na u tora ka bɔ binary log kɔnɔ ?

Dana wɛrɛw ka kilisi dantigɛliw bɛ dantigɛli dafalen wajibiya tabali ni ɲɔgɔn cɛ. Ni i ye ON DELETE CASCADE walima ON UPDATE CASCADE ɲɛfɔ, MySQL bɛ fɛn caman jɛnsɛn a yɛrɛma ka bɔ bangebagaw ka tabali la ka taa denmisɛnninw ka jirisunw na minnu bɛ tali kɛ ɲɔgɔn na. San caman kɔnɔ, o fɛn caman b’a la ka fɛn caman wuli ka bɔ ɲɔgɔn na, o yeli kɛra i n’a fɔ kɔlɔlɔ yebaliw — fɛn min bɛ mɔgɔ bila ka wuli, o tun bɛ sɛbɛn, nka jiginɛ minnu kɛra kasɛti ye, olu ma sɛbɛn i n’a fɔ ko kɛlenw yɛrɛmahɔrɔnyalenw sɛbɛn fila kɔnɔ.

Nin tun tɛ kɔlɔsili ye . MySQL jɛkulu y’a Jira fɔlɔ ko replikaw, minnu bɛ schema kelen ni jamana wɛrɛw ka key ɲɛfɔliw Jɛ, olu bɛna cascades (kasɛtiw) Lasegin u yɛrɛma ni kumasen fɔlɔ in fɔra kokura. Logic tun bɛ minɛ — fo ka se a ma. Schema drift, partial replication filters, storage engine configurations différents, ani point-in-time recovery scenarios, olu bɛɛ ye nin hakilina in ka nɔgɔya jira. Kaskade kelen min ma sɔrɔ, o bɛ se ka kɛ sababu ye ka ɲɔgɔndan danfaralenw bɔ minnu bɛ i n’a fɔ u kɛnɛyara fo ka se ɲininkaliw ma minnu bɛ baara kɛ kosɛbɛ, olu ka jaabi juguw segin.

Kasɛti jirisunw tigɛcogo kura bɛ baara kɛ cogo di tiɲɛ na ?

Ni kɛcogo kura ye, MySQL bɛ jirisunw sɛbɛn minnu bɛ nɔ bila kasɛti baarakɛcogo la i n’a fɔ jirisunw ko kɛlen jɛlenw binary log kɔnɔ, o yɔrɔnin bɛɛ ka tugu DML fɔcogo daminɛni kɔ. N’i ye bangebaga ka sɛbɛn dɔ bɔ yen ani den ka sɛbɛn saba bɛ kaska ka bɔ yen, jirisun naani b’a jira sisan i n’a fɔ jatebɔsɛbɛnw sɛbɛnni danfaralenw, minnu bɛ se ka jateminɛ.

Jiginni cogoya nafa ka bon yan . Row-based binary logging (RBL) ye cogoya ye min b’a to o bɛ se ka kɛ ni kantigiya dafalen ye. Jatebla minnu sinsinnen bɛ kumasenw kan, olu tɛ se ka kasɛti fangama jaabiw minɛ ni dannaya ye bawo a kɔlɔlɔ bɛ bɔ kunnafonidilanw cogoya de la waleyali waati la, a tɛ bɔ SQL sɛbɛnni yɛrɛ la. Mixed-mode logging bɛ row format waleya cogo sugandilen na nin ko ninnu na.

ye

Key Insight : Ka se ka jatebɔ fila kɛ minnu sinsinnen bɛ jirisunw kan, o tɛ ɲɔgɔndan ɲɛnatɔmɔli dɔrɔn ye — sisan a kɛra wajibi ye walasa ka kunnafonidilan caman sɛgɛsɛgɛli sira dafalen minɛ, min bɛ se ka sɛgɛsɛgɛ, sitɛmuw kɔnɔ minnu bɛ baara kɛ ni jamana wɛrɛw ka kilisi dantigɛliw ye. Ni o tɛ, kaskade nɔw bɛ to yɔrɔ dɔ la i ka baarakɛminɛnw na.

ye

Dankunnafoni ɲɛmɔgɔw ka kan k’u ka binlog_format sigicogo sɛgɛsɛgɛ ani k’u ka ɲɔgɔndan topidiya miiriliw lajɛ n’u ye tariku kɔnɔ u jigi da ɲɔgɔndan fan fɛ kasɛti waleyali kan walasa ka bɛnkan garantiw sɔrɔ.

O bɛ gɛlɛya jumɛnw ɲɛnabɔ diɲɛ kɔnɔ , minnu bɛ se ka kɛ baarakɛminɛnw ye ?

A nɔfɛkow bɛ se ka tɛmɛ kosɛbɛ teori lasegincogo ɲuman kan . Ekipu minnu bɛ jagokɛyɔrɔ gɛlɛnw baara ni jɛɲɔgɔnya kunnafonidilanw ye — SaaS fɛn dilannenw, ɛntɛrinɛti jago motɛriw, kɛnɛyako sɛbɛnni siraw — olu ye dɛsɛcogo jɔnjɔnw sɔrɔ, nin fɛn caman Changement in bɛ minnu ɲɛnabɔ:

• Point-in-time seginni tiɲɛni : Ka kunnafonidilan dɔ segin waati dɔɔnin sanni jama bɔli ka kɛ, o bɛ denmisɛnninw ka bɔli kasɛti bɛɛ minɛ sisan, a tɛ kɛ ɲininkali daminɛni dɔrɔn ye, ka phantom row bali ka bɔ kokura segin kɔfɛ.
• Yɛlɛma donanw minɛcogo (CDC) pibilikiw : Baarakɛminɛn minnu bɛ i n’a fɔ Debezium ani Maxwell minnu bɛ binary log ko kɛlenw lase Kafka walima sinkɔrɔ wɛrɛw ma, olu bɛ kunnafoni caman cili bɛɛ ja dafalen sɔrɔ sisan, o bɛ kɛ sababu ye ka jiginɛ jateminɛ tigitigiw kɛ.
• Sɛgɛsɛgɛli ni sariya labatoli sɛbɛnni : Sɛnɛko minnu bɛ sariya kɔnɔ, n’olu bɛ kunnafonidilanw bɔnsɔn dafalen wajibiya, olu bɛ se sisan ka denmisɛnninw ka sɛbɛn minnu bɔ yen, olu bɛ se ka tugu tigitigi bangebagaw ka baara min nɔfɛ, ka jatebɔ wajibiyalenw wasa k’a sɔrɔ u ma kɛ ni baarakɛminɛnw ye .
• Sɛbɛnni danfara dɔnni : Kɔlɔsili baarakɛminɛn minnu bɛ ɲɔgɔndan sinsinni jatew walima sɛgɛsɛgɛliw suma ni fɔlɔ ye sisan, olu bɛ ni jatebɔsɛbɛnw kunnafoni tigitigiw ye walasa ka danfara o danfara dɔn ani k’u sɛgɛsɛgɛ joona.
• Sɔrɔyɔrɔ caman ni sɛrɛkiliw ɲɔgɔnnabɔli : Topidiya gɛlɛn minnu tun bɛ jamana wɛrɛw ka kilisiw bali ka kɛ ɲɔgɔndanw kan fɔlɔ walasa ka u yɛrɛ tanga kasɛtiw kɛli ma siɲɛ fila, olu bɛ ko kɛlenw sɛrɛkili sɔrɔ min labɛnna ka ɲɛ, min bɛ se ka fɔ ka ɲɛ, walasa ka hakilinata di o kan.

daɲɛgafe dilannikɛlaw ka kan k' u ka ɲɔgɔndan ni u seginni fɛɛrɛw ladilan cogo di ?

Ka cascade-aware binary logging ta, o tɛ sɛgɛsɛgɛli pasif ye — a bɛ ɲini ka laɲini kɛ ka fɛnw lajɛ minnu bɛ yen. Log ko kɛlen wɛrɛw minnu bɛ sɔrɔ cascades fɛ, olu bɛ dɔ fara binary log volume kan, tuma dɔw la kosɛbɛ, ka da i ka schema bɛ baara kɛ ni cascade constraints ye cogo min na ni fanga ye. Architectes ka kan ka jate kɛ disiki I/O cayalenba la, réseau replication bandwidth, ani log rotation frequency.

Replicaw kan, ekipuw ka kan ka jateminɛ kɛ ni jamana wɛrɛw ka kilisi dantigɛliw bɛ se ka kɛ walima ni u ma baara kɛ. Tariku kɛcogo min tun bɛ kɛ tuma bɛɛ, o tun ye ka jamana wɛrɛw ka kilisiw bali replicaw kan (foreign_key_checks=OFF) walasa ka fɔlɔ bila ka sariya labatoli ɲɛnabɔ. Ni kaskada minnu sɛbɛnna sisan k’a jɛya, n’i y’o kaskade ko kɛlenw sɛbɛnnenw waleya replica kan min fana b’a yɛrɛ ka jamana kɔkan kilisi dantigɛliw baara, o bɛ se ka kɛ sababu ye ka bɔli kɛ siɲɛ fila walima ka bɛnbaliya don. Labɛnni sinsinni ɲɔgɔndan cakɛda bɛɛ kɔnɔ, o nafa ka bon sisan ka tɛmɛn fɔlɔ kan.

Ekipu minnu bɛ baara kɛ ni hakililata baarakɛminɛnw ye i n’a fɔ mysqldump walima farikolo kɔkannafɛnw i n’a fɔ Percona XtraBackup, ka seginkanni taabolo lajɛ ka kɛɲɛ ni jatebɔsɛbɛn kura ye, o b’a to seginkanni boli gafew bɛ to tiɲɛ na. Segin-ka-bɔnyew kɔrɔbɔli sigida laminiw na minnu bɛ kɛ ka kɛɲɛ ni sɛnɛfɛnw bɔli-cidenw ka kunnafonidilanw hakɛ ye, o ka kan ka kɛ baara ye min bɛ kɛ kalo saba o kalo saba kɔnɔ.

O bɛ tali kɛ cogo di ni PostgreSQL ni kunnafonidilan wɛrɛw bɛ Cascade Logging ɲɛnabɔ cogo min na ?

PostgreSQL ye jamana wɛrɛw ka kilisi kaskada nɔw sɛbɛn kabini tuma jan i n’a fɔ WAL (Write-Ahead Log) sɛbɛnni fɔlɔw. Jiri kelen-kelen bɛɛ min magara kaskada-minɛn fɛ, o bɛ sɛbɛn WAL kɔnɔ kelen-kelen, o bɛ PostgreSQL ɲɔgɔnnabɔli ni logical decoding kɛ cascade-aware ye u yɛrɛ la. Nin dilancogo hakilila in — log nɔ bɛɛ, a tɛ kɛ sababu dɔrɔn ye — o ye MySQL ka row-based binary log bɛ min gɛrɛgɛrɛ kosɛbɛ sisan.

Microsoft SQL Server bɛ o cogo kelen na ka fɛn caman Changements cascades minɛ a ka jagokɛta sɛbɛn fɛ row nivo la, o bɛ CDC ɲɛnabɔcogo kɛ SQL Server kan ka ɲɛ hali ni jɛɲɔgɔnya sɛgɛsɛgɛli gɛlɛnw ye. MySQL kurayali b’a lase fɛn dilanni bɛrɛbɛrɛ ma ni nin sigida ninnu ye, ka dɔ bɔ sɔsɔliba dɔ la, kunnafonidilan injiniyɛriw ye min lawuli n’u bɛ MySQL jateminɛ jɛɲɔgɔnya baarakɛta caman na.

Jɛkulu minnu bɛ kunnafonidilan caman sigidaw baara walima minnu bɛ jiginni siraw jateminɛ, o ɲɔgɔndan in nafa ka bon. A bɛ dɔ bɔ kɛwalew sanfɛla la plateformew ni ɲɔgɔn cɛ, baarakɛjɛkuluw ka kan ka minnu faamuya ani ka jatebɔ kɛ baarakɛminɛnw ni taabolo la.

Ɲininkali minnu bɛ kɛ tuma caman na

Yala nin fɛn caman Changement in bɛ nɔ bila MySQL 5.7 la walima MySQL 8.x dɔrɔn de la wa ?

Taamacogo ɲɛtaa minnu kɛra kasɛti jatebɔ la, olu bɛ tali kɛ fɔlɔ MySQL 8.0 ni kɔfɛ bɔli la, ni ɲɛtaa kɛrɛnkɛrɛnnenw sirilen bɛ point bɔli kelen-kelenw na 8.0.x seriyali kɔnɔ. MySQL 5.7 sera a ka ɲɛnamaya laban ma san 2023 ɔkutɔburukalo la, wa ekipu minnu bɛ a baara hali bi, olu ka kan ka kaskad logging kɛcogo minɛ yen i n’a fɔ dannaya tɛ min na replication consistency kuntilenna na. Yɛlɛma donna MySQL 8.0 walima MySQL 8.4 LTS bɔli la, o ye sira laadilen ye walasa ka se ka don sisan binary log kɛcogo la.

Ni ka kasɛti jatebɔ dafalen kɛ, o bɛna sɛbɛnni kɛcogo dɔgɔya kosɛbɛ wa?

Sɔrɔlen ye tiɲɛ ye nka a ka c'a la, a ka dɔgɔ schemaw la minnu bɛ ni jamana wɛrɛw ka kilisi jɛɲɔgɔnyaw ye minnu bɛ index ɲuman kɛ. Jatebla sɛbɛnni wɛrɛw bɛ tali kɛ denmisɛnninw ka jirisunw hakɛ la minnu bɛ nɔ bila kasɛti kelen-kelen bɛɛ la. Skema minnu bɛ ni cascade cakɛdaw ye minnu bɛ u kɔnɔ kosɛbɛ walima denmisɛnw ka tabali belebelebaw bɛ se ka sumanikɛlan ye min bɛ se ka kɛ sababu ye ka baara kɛ ni u ye. Profiling ni data volumes lakikaw ye staging sigida la ka sɔrɔ ka labɔ ka taa production la, o ye fɛɛrɛ ɲuman ye walasa ka musaka hakɛ jateminɛ i ka baara kɛrɛnkɛrɛnnen na.

Ne bɛ se ka baara kɛ ni binary log cascade data ye walasa ka waati yɛrɛyɛrɛ jateminɛ pibilikiw balo wa ?

Ɔwɔ , wa nin ye baarakɛcogo dɔ ye min bɛ mɔgɔ bila ka wale kɛ . Ni kaskade ko dafalenw kɛra binary log kɔnɔ, CDC baarakɛminɛnw bɛ se sisan ka yecogo tigitigiw jɔ minnu bɛ kɛ ka fɛnw sɔrɔ, ɲinini indexw, ani ko kɛlenw sɛrɛkiliw k’a sɔrɔ u ma application-level hooks wajibiya walasa ka cascade effects bɔ bolo la. Ekipu minnu bɛ waati yɛrɛyɛrɛ dashboardw, nanbarako dɔnni siraw, walima baarakɛcogo sɛgɛsɛgɛli layɛrɛw jɔ, olu bɛna a ye ko log stream dafalen bɛ dɔ bɔ kosɛbɛ pibiliki gɛlɛya la.

---

Jagokɛyɔrɔ min bɛ ka bonya, o ɲɛnabɔli kɔrɔ ye ko i ka fɛnsɔrɔko latigɛlenw bɛ ka caya waati kɔnɔ — danfara min bɛ i ka kunnafonidilanw lasegincogo la bi, o bɛ kɛ ko ye min bɛ kɛ sini. Mewayz ye jagokɛminɛn 207 ye min jɔra ekipuw kama minnu tɛ sɔn ka dankari kɛ dannaya walima seko la, baarakɛla 138.000 ni kɔ dalen bɛ a la minnu bɛ fɛn bɛɛ kɛ k’a ta CRM ni ɛntɛrinɛti jago la ka se kɔnɔkow ɲɛnabɔli ni jateminɛ ma — o bɛɛ bɛ kɛ kɛnɛ kelen kan min bɛ daminɛ dɔrɔmɛ 19 dɔrɔn na kalo kɔnɔ.

I ka Mewayz baarakɛyɔrɔ daminɛ bi app.mewayz.com ani k' i ka jago jɔ fɛnsɔrɔsiraw kan minnu bɛ bɛn i ka dɔntaw ma .

dafalen sɔrɔ