I have 1 MySQL server to support PHP application. The logic in the application requires multiple queries. For example, to add the transaction, I need to create multiple query objects and run those queries separately without using commit. Meaning adding transaction data first in the table with one object. And to add a payment method, I am creating the different object.
Right now it's good because I have only 1 MySQL server. What will happen if I have multiple servers (MySQL Master-Slave).
Creating different objects in one operation will call different servers. And because of latency data might not available on another server.
Please advise me to do this correctly. If the question is not clear ask for clarification. Thanks!
This really isn't an issue with replication. Let's say you have Query A, then Query B (which relies on A) and Query C (which relies on B).
So you do the straight forward thing and run them in the order A, B and C. Replication puts them into a log in that same order. So when your slave executes them, it will do them in the same way and the same order.
In the even they go out of sync, the slave knows where it last left off in the log and will pick back up there. If you use RDS to set the slave up, this is handled for you automatically.