2

Say I have MySQL master DB(used for updates) and corresponding Slave DB(used for reads). Now user inserts the data into MASTER through application(java), just after insertion he tries to retrieve the data which will go slave. I am sure there be some time lag in replication from master to slave(may be of few Millis) and it won't be instantaneous.

My question is how to deal this scenario in master slave model where user will definitely expect the latest data inserted by him down the line.

Does it need to be handled at application level i.e. wherever transaction reads/fetches the data with any CUD, it should be done from Master so that latest data inserted by same transaction is retrieved or are there some other better ways too?

For example:- User first created the account . Insert goes to Master but when he fires the query to retrieve the user details like user_id, name call will go to slave and he may not be getting any data ?

3
  • why do you use the Oracle tag? Because mysql is an Oracle product or because you want an answer or Oracle database, too? In the latter case, for which kind of replication do you use?
    – miracle173
    Jun 11, 2017 at 9:08
  • Another solution is to avoid the problem -- design the UI so that the "critical read" rarely, if ever, happens.
    – Rick James
    Jun 27, 2017 at 0:07
  • But if critical reads are required, We can't avoid them.
    – emilly
    Jun 30, 2017 at 13:08

1 Answer 1

1

This is often handled by the application actively choosing to read from the master based on context.

A common example I've seen mentioned is a database of blog posts. *Viewing, a blog post would obviously be done from a replica, but previewing an edit to a blog post -- which may be the same code, since it's rendering the post -- might be something you do by reading from the master.

Depending on the application, you might also read from the replica and then fall back to reading from the master if you don't find what you expect.

There are also solutions, like Maxscale -- designed by MariaDB but works with MySQL too -- that are essentially a proxy between your app and both MySQL servers. Whenever you are in a transaction, you're talking to the master, whenever you're not in a transaction and issue a SELECT, the queries go to the replica... so you can easily force a read from the master just by doing it in a transaction. There are other rules in play as well that send certain statements to both servers, such as USE and SET statements. If the replica lag goes beyond a certain threshold, Maxscale sends everything to the master.

1
  • 1
    for galera replication users they can make use of wsrep_sync_wait variable, aside from MaxScale there is ProxySQL where you can create routing rules etc. Jun 11, 2017 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.