Mysql – filter records within MySQL replication

Since "native" MySQL uses asynchronous replication, there is no system-wise way to guarantee nor force a slave to apply a master's statement at any given time.

Semi-sync replication shortens this lag time by at least ensuring your statement is recognized by the slave -- but does not go as far as making sure it has been applied on your slave.

This is a common issue. Ultimately, your application needs to be replication-aware, such that your query "knows" it is being executed on master or on slaves. In your case, the common solution is to make sure a SELECT query which immediately follows a write operation is executed on master.

You may choose to use synchronous solutions: MySQL NDB Cluster or Galera Replication. Even with Galera you would need a special configuration to make sure a consistent write-and-read is successful.

If you're not a DBA - i.e. you're not experienced with the complexities of replication, failover, subtleties of transaction isolation, etc - then if possible do not use multi-master.

There's no multi-master clustering for PostgreSQL, only multi-master replication via 3rd party add-ons. These add-ons have a significant performance impact, but more importantly they don't deal with inter-node locking and transaction isolation. Concurrent transactions on different nodes can see different data, can both modify the same rows, etc.

Frankly, multi-master clustering is pretty underwhelming even in products that do have it available. Performance tends to be pretty poor and scale badly. This isn't a deficiency in the products, it's just that MM clustering is hard and you pretty much get to pick either correct or fast.

Personally, I recommend using two big nodes. Have one node running a streaming replica and the other running as master. If one goes down, fail over to the other. Tune your app and queries properly before adding more capacity.