This is actually the scenario:
- I've got a MySQL server having a database, let us refer to it as consolidateddb. This database a consolidation of countless tables from various databases
- I've another MySQL server, using the original databases, these databases are production databases and therefore are updates daily.
- The organization really wants to copy each update/place/remove on each table within the production databases towards the corresponding tables in consolidateddb.
Would replication accomplish this? I understand that replication is performed on the databas to database, but this is not on tables owed to various databases to 1 target database.
I really hope my explanation was obvious. Thanks.
Edit: Would a recursive copy of tables motel each database towards the single slave work? Or perhaps is it an ugly solution?
To obvious up several things, let us title things accordingly to current mysql practice. A database is really a database server. A schema is really a database instance. A database server might have multiple schemas. Tables live inside a schema.
Replication can help you if you wish to duplicate schemas or tables because they are defined around the master/production server. The replication functions by shipping a binary log of all of the sql claims which are operate on the actual towards the slave which dutifully runs them as though they run sequentially on itself.
You are able to decide to replicate all data, or choose a few of the schemas as well as are just some of the tables.
You can't choose tables from different schemas and also have them duplicated into one schema, a table goes to some specific schema.
Incidentally, important notice. A replication server can't be present multiple masters. You can mimic this using federated tables, but that will never copy the information towards the consolidation server, just demonstrate to them as though the information from different servers were on a single server.
The power of replication is your consolidation server will pretty much have up-to-date data constantly.
You might take the binary logs from each one of the masters, parse all of them with
mysqlbinlog after which run that in to the consolidated machine.
Something very roughly like:
mysqlbinlog [binary log files] | mysql -h consolidated
you'd take some type of simple application (I suspect it may be completed in party should you needed) to wrap the logic.
Take a look at Replicating Different Databases to Different Slaves, find out if it will help you by any means.
MySQL statement-based replication (fundamental replication) functions by running the identical claims which were operate on the actual around the slave. Including details about what database the table is at.
I do not think MySQL provides any built-in method to move replication claims between databases (i.e. "place into db1.table1 ..." -> "place into db2.table1"). You might have the ability to trick it by by hand changing the replication logs quickly, however it would not be out-of-the-bod MySQL replication.
You may have the ability to accomplish it with MySQL Proxy
You might want to browse the maatkit toolkit. It is a download free and it has a number of tools specializing in optimizing such things as archiving tables. I have tried on the extender on past projects to copy certain data to a different DB, etc. It can be done according to time or other quantity of factors.
To the very best of my understanding you are able to setup replication (MySQL 4+) as well as in the my.cnf file possess the slave either only process certain tables or possess the master log only certain tables, in either case will solve your condition.
This is a help guide to some techniques:
I've very couple of issues with replication set-up, my problems came attempting to sync DBs, especially following a reboot etc.