We help IT Professionals succeed at work.
Get Started

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

jakac
jakac asked
on
890 Views
Last Modified: 2012-05-09
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
Database Consultant
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE