We help IT Professionals succeed at work.

Mysql replicate / duplicate / sync only certain tables on the same server on different databases

jakac
jakac asked
on
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:
- products
- categories
- customers
- orders

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!
Comment
Watch Question

Hi,
 if ur using sql yog, then it has in built function, which provides u features to synchronize with different database server.
 you can just transfer few tables in other db server or databse.
Srry i don't know abt others even navicat has that features..
Please let me know if any issue.

Author

Commented:
Hello,
As far as I know Sql yog is just a GUI for mysql which can replicate / duplicate database manually by exporting, comparing and importing data.. I need an automated solution which will run 24/7 and replicate all the changes between selected tables.

theGhost_k8Database Consultant

Commented:
theGhost_k8Database Consultant

Commented:
Ahh!! Wait... I didn't read "same Mysql server? "

Author

Commented:
yes it's on the same server... right now I'm testing mk-table-sync from Maatkit.. it works on the small tables but I have to check how it will perform on a large database.
Database Consultant
Commented:
Try to use a cron daily basis to sync:
 replace into db1.table1 select * from db2.table1;

Otherwise:

If you just want a place holder you can use federated tables:

CREATE TABLE DB2.TABL1(

...TABLE DEFINITION...

) ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://USERNAME:PASSWORD@HOSTNAME:PORTNAME/DB1/TABLE1';

Author

Commented:
Thank you! Federated table did the trick without any synchronization!