MySQL DB Replication

Question

Okay so here's my scenario. I have two applications that share some tables from the same DB. Let's call them App1 and App2. Lets also assume the DB used here is called DB1. App1 and App2.

App1 is on the same machine as DB1. App2 is on a different server but connects to DB1 remotely via PHP. This setup sometimes slows down access to DB1 from App2 and causes timeouts.

I will be creating DB2 which will be a replica of DB1 and hosted on the same machine as App2. This DB2 should be automatically synched with DB1. Changes are only going to made on DB1, so just need to push changes to DB2 regulary.

How can I do this? I'm assuming the solution to do this will be using cron jobs and PHP but I do not know what code can help do this.

Thanks all!

I am hoping someone has already done something like this and can share what they did.

Answer 1

• Setup master-slave replication between DB1 and DB2.
• APP1 reads and writes to DB1.
• APP2 reads from DB2.
• APP2 writes (if it does) to DB1.

Note that APP2 will read eventually consistent data and it will have to handle writes to DB1 itself. (I do not tend to like master-master replication setup and writes to both ends due to past experiences but it is also an option.)

You said no links but below this link includes steps for setting replication, you might want to refer.