0

We have around 14 eCommerce sites which are working in 2 LAMP server. Two sites in server one and second server consist of rest of the sites. There is one central DB in which we are syncing all the data from different site using CRON on every 10 minutes. This CRON and along with other CRON jobs which are used for syncing and its giving huge load and servers are going down due to this.

Diagram of current server architecture: enter image description here

As we have different structure of central DB, I could not use replication. Or am I wrong on this. Which is the best method I can look into for syncing more live.

jack
  • 101
  • 1
  • 3

1 Answers1

1

Create slave MySQL server with same version (include build number) and use master-slave replication. After this you can make backup from slave server via standard mysqldump without stopping worked master server.

Mikhail Khirgiy
  • 2,003
  • 9
  • 7
  • As we have different structure of central DB, I could not use replication. Or am I wrong on this. Which is the best method I can look into for syncing more live. You have not covered this in this proposed solution. – jack Apr 11 '18 at 16:51
  • Please, write more about `we are syncing all the data from different site using CRON`. – Mikhail Khirgiy Apr 11 '18 at 19:42
  • We have different db for each of the 14 sites and we are running a single cron which will check all the db one by one and if any data is modified after the previews cron we will take that records and copy it to the central db. In this cron we are not taking all the tables data, but we are copying details like orders, cusomers, products...etc so that we can compare our results and have a place where we can see all data together. In this process we are not updating any data in the central db instead, we are will delete all the modified entries and insert the new contents to appropriate table. – jack Apr 12 '18 at 07:08
  • We are trying to cut down the sync speed and make it more live. Replication along with cron will make the system slow. – jack Apr 16 '18 at 15:17
  • As I understand you don't make synchronization or replication. You collect data from different databases in one another database. Use triggers on databases to do it in live regime. Also I advice you to do it via another table which will collect all changes with ID's and links to databases where changes would have been. – Mikhail Khirgiy Apr 16 '18 at 16:21
  • Thats a great suggestion! Triggers how can I do that. Can i get some docs on that one? – jack Apr 16 '18 at 16:35
  • Look at https://www.percona.com/blog/2017/06/14/triggers-and-updatable-views/ – Mikhail Khirgiy Apr 17 '18 at 05:06