Score:1

Using MySQL/MariaDB replication, how to know when the Replica is in a consistent state?

in flag

This is a question about MariaDB (mostly) and a replication that I am considering.

For this replication, the Primary database is updated every minute by a process which can take about 15 seconds to complete, each time. During these 15 seconds, the Primary database may not be completely consistent on each tables.

I want to set a Replica for this database on another server. On the Replica server, I expect to do some periodic post-processing on this database, which needs (preferably) the database to be in a consistent state (and maybe writing the results back to the Primary server, see this question).

Is there a way to know when the Replica has fully replicated this consistent state, or to force it to stay in this state, in order I can do the post-processing without errors?

(On the multiple servers I have, MariaDB versions can be 10.3.38 or 10.5.19 or 10.6.12. Servers are Linux Debian 10, Linux Debian 11 or Linux Ubuntu 22.04.3 LTS).

ua flag
`Seconds_behind_master` is not definitive, but it is probably the best available. What the heck takes 15 minutes? Can that be sped up?
GingkoFr avatar
in flag
I said 15 seconds, not 15 minutes.
ua flag
Inject (into an extra table) some indication that the periodic update is finished.
jp flag
SQL Transactions are there to keep the database consistent whenever you need to do complex data modifications.
GingkoFr avatar
in flag
This extra table would also need to be part of the replication. Can I be sure it will be replicated in strict order? And about SQL Transactions … replicating them will certainly, nevertheless, need some time. Can I be sure that they will be atomic in the Replica?
ua flag
@GingkoFr - The general answer to those questions is " all will work correctly". However, since there are ways to bypass things, we would need to seep specifics to confirm it.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.