这是设置,我有多个在线商店,我想使用相同的产品数据库。 目前它们都是独立的,因此更新任何东西都需要经过并复制产品,这是一个巨大的痛苦。 我想要做的是创建一个主产品数据库,每晚,每个站点将比较其数据库,并相应地进行更新。
这个想法是一个主产品数据库,每天会更新几次,然后在凌晨2点说,一个cron作业将运行拉动各个网站的更新。
关于数据库的更多细节,有一个表'产品'需要进行比较,但它还需要查看表'prodcuts_site_status'来确定每个给定站点的产品状态值,所以我可以'只需转储主表并将其重新放入站点数据库中。
创建一个php脚本逐行进行比较和更新很容易,但我希望在mysql中存在一个更优雅/更有效的解决方案。 有什么建议么?
谢谢!
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!
最满意答案
总结一下,你可以尝试3种不同的方法:
使用SELECT ... INTO OUTFILE ,然后从MySQL Cross Server Select Query中 LOAD DATA INFILE
使用此处描述的复制方法 Perl:如何将远程MYSQL表复制/镜像到另一个数据库? 可能也有不同的结构?
使用FEDERATED 存储引擎连接来自不同服务器的表http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html
To sum up you could try 3 different methods:
use SELECT ... INTO OUTFILE and then LOAD DATA INFILE from MySQL Cross Server Select Query
use the replication approach described here Perl: How to copy/mirror remote MYSQL table(s) to another database? Possibly different structure too?
use a FEDERATED storage engine to join tables from different servers http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html
更多推荐
发布评论