MySQL database replication

Question

Hello;

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

Check out Replicating Different Databases to Different Slaves, see if it helps you in any way.

Answer 2

MySQL statement-based replication (basic replication) works by running the exact same statements that were run on the master on the slave. This includes information about what database the table was in.

I don't think MySQL provides any built-in way to move replication statements between databases (i.e. "insert into db1.table1 ..." -> "insert into db2.table1"). You may be able to trick it by manually altering the replication logs on the fly, but it wouldn't be out-of-the-bod MySQL replication.

Answer 3

You might be able to pull it off with MySQL Proxy

Answer 4

You may want to check out the maatkit toolkit. It's a free download and has a host of tools that specialize in optimizing things like archiving tables. I've used it on past projects to duplicate certain data to another DB, etc. You can do it based on time or any other number of factors.

Answer 5

To the best of my knowledge you can set up replication (MySQL 4+) and in the my.cnf file have the slave either only process certain tables or have the master log only certain tables, either way will solve your problem.

Here is a guide to some techniques:

http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html

I have very few problems with replication set-up, all my problems came trying to sync DBs, especially after a reboot etc.

Answer 6

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.

Answer 7

You could take the binary logs from each of the masters, parse them with mysqlbinlog and then run that into the consolidated machine.

Something very approximately like:

mysqlbinlog [binary log files] | mysql -h consolidated

you'd need some kind of simple application (I suspect it could be done in bash if you needed) to wrap the logic.