0

Here is the setup, I have multiple online stores that I would like to use the same product database. Currently they are all separate, so updating anything requires going through and copying products over, it is a giant pain. What I would like to do is create a master product database that every night, each site will compare its database with, and make updates accordingly.

The idea is one master database of products that will be updated a few times a day, and then say at 2:00 AM, a cron job will run pulling the updates to the individual websites.

Just a few more details on the database, there is one table 'products' that needs to be compared, but it also needs to look at table 'prodcuts_site_status' to determine the value for the products status for each given site, so I can't simply dump the master table and re-important it into the site databases.

Creating a php script to go row by row and compare and update would be easy enough, but I was hoping there existed a more elegant/efficient solution in mysql. Any suggestions?

Thanks!

Nathan
  • 409
  • 4
  • 15
  • 1
    When you are talking about multiple databases are you talking about physically separated servers or different databases in the same MySQL server? – Károly Nagy Aug 09 '13 at 15:46
  • 4
    And what about using [MySQL replication capabilities](http://dev.mysql.com/doc/refman/5.0/en/replication.html)? – Sylvain Leroux Aug 09 '13 at 15:49
  • See http://stackoverflow.com/questions/18036299/how-do-i-keep-two-article-tables-synced-but-keep-stock-separate for a similar question. – Sylvain Leroux Aug 09 '13 at 15:52
  • You can save the query results of your cron job from your master database to the individual one. See http://answers.oreilly.com/topic/158-how-to-save-query-results-in-a-mysql-table/ – RafaSashi Aug 09 '13 at 15:55
  • Yes, the servers are physically separated. – Nathan Aug 09 '13 at 15:55
  • If your servers are physically separated you might be interested in either "replication" or "ETL". I suggest DVK's answer in http://stackoverflow.com/questions/4570877/perl-how-to-copy-mirror-remote-mysql-tables-to-another-database-possibly-dif – RafaSashi Aug 09 '13 at 16:01
  • You can also try FEDERATED storage engine to join tables from different servers, http://stackoverflow.com/questions/11114197/join-tables-from-two-different-server – RafaSashi Aug 09 '13 at 16:06
  • Thanks for your help everyone, I think replication is going to work just fine! I will have to alter the platform code slightly to pull the product_status from a different location, but that is a small enough change! – Nathan Aug 09 '13 at 16:14

1 Answers1

0

To sum up you could try 3 different methods:

  1. use SELECT ... INTO OUTFILE and then LOAD DATA INFILE from MySQL Cross Server Select Query

  2. use the replication approach described here Perl: How to copy/mirror remote MYSQL table(s) to another database? Possibly different structure too?

  3. use a FEDERATED storage engine to join tables from different servers http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html

Community
  • 1
  • 1
RafaSashi
  • 16,483
  • 8
  • 84
  • 94