How would you set this up

Polyglot Projects LAMP Stack
1

We have a custom Customer Relationship Management (CRM) tool being built based on LAMP infrastructure.

We receive new customer orders via the web and by cron jobs that FTP and import order data from other sources.

Right now, we have a single web server that also runs MySQL - a dedicated server at a large provider.

Customer Service agents are all located in an office not near the datacenter where the current web/db server is.

Agents make changes to customer orders.

Management (located in the same office as the agents) need to run reports. These reports are very comprehensive and involve millions of orders (rows).

Customers have the ability to check order status online.

I am thinking it is necessary to have a robust server brought in to the office that will have the order data replicated for customer service and management to use and manipulate.

How would you set this up to maximize agent performance, management reporting tools, and customer ability to get the latest information about their orders.

How/when/which direction would you replicate - which server would be slave/master?

Thanks!

2

This is a complex question that I would encourage you to get consulting help on, but I’ll try to give a quick answer here. Most of the time reporting queries should be run from a read-only replication slave. If the business really relies on those reports, then you need to invest some money and effort into making the slave reliable – set up a monitoring infrastructure, verify that the slave and master have the same data regularly, etc.