MySQL database replication

Question

This is the scenario:

1. I have a MySQL server with a database, let's call it consolidateddb. This database a consolidation of several tables from various databases
2. I have another MySQL server, with the original databases, these databases are production databases and are updates daily.
3. The company wants to copy each update/insert/delete on each table in the production databases to the corresponding tables in consolidateddb.

Would replication accomplish that? I know that replication is done on a databas to database, but not on tables that belong to different databases to one target database.

I hope my explanation was clear. Thanks.

Edit: Would a recursive copy of all tables inn each database to the single slave work? Or is it an ugly solution?

Answer 1

To clear up some things, let's name things accordingly to current mysql practice. A database is a database server. A schema is a database instance. A database server can have multiple schemas. Tables live within a schema.

Replication will help you if you want to duplicate schemas or tables as they are defined on the master/production server. The replication works by shipping a binary log of all the sql statements that are run on the master to the slave which dutifully runs them as if they run sequentially on itself.

You can choose to replicate all data, or you can choose some of the schemas or even just some of the tables.

You can not choose tables from different schemas and have them replicated into one schema, a table belongs to a specific schema.

By the way, important notice. A replication server can not be a slave to multiple masters. You could mimic this using federated tables, but that would never copy the data to the consolidation server, just show them as if the data from different servers were on one server.

The bonus of replication is that your consolidation server will more or less have updated data all the time.