We are running a webshop using mysql database and now we want to build another webshop on different domain with different design, different target population (customers) but with the same set of products and categories.
Database itself has about 150 tables (e.g. products, products_properties, products_stock, products_attributes, ........) but to make this easy I will concentrate on the main four of them. Let's say this is database called 'shopDB1' with following tables:
We want to make a copy of our webshop which will use another database (let's say shopDB2) which will have its own customers and orders tables but the products and categories tables will remain the same as in shopDB1.
To manage this we need some sort of replication or synchronization only for tables 'products' and 'categories. We don't need a real-time solution (it would be great though) but still we want a fast solution (e.g. data should not be more than 5 minutes old). The replication can also be "master-slave" since only selects will be executed on "shopDB2" database.
Both databases are on the same MySql server so we don't need any remote synchronization.
I know that we could do a cross-database selects in our application but unfortunately this is not an option because we need about 40 tables from the other database (not only 2 as in the example above) and the application is also very complex so it would take just too much time to find and replace all statements for these tables (and it would also make upgrading impossible).
We tried to replicate these tables by periodically executing a simple dump & insert selected tables script but it took too long because these tables are quite large (about 6000 products with attributes, properties, data, descriptions, ....). Also during this dump&insert operation there were errors in the webshop.
Has anyone a better solution how to replicate/synchronize only certain tables from different databases on the same Mysql server? Thank you!