MySQL triggers + replication with multiple databases.

Question

I am running a couple of databases on MySQL 5.0.45 and am trying to get my legacy database to sync with a revised schema, so I can run both side by side. I am doing this by adding triggers to the new database but I am running into problems with replication. My set up is as follows.

Server "master"

• Database "legacydb", replicates to server "slave".
• Database "newdb", has triggers which update "legacydb" and no replication.

Server "slave"

• Database "legacydb"

My updates to "newdb" run fine, and set off my triggers. They update "legacydb" on "master" server. However, the changes are not replicated down to the slaves. The MySQL docs say that for simplicity replication looks at the current database context (e.g. "SELECT DATABASE();" ) when deciding which queries to replicate rather than looking at the product of the query. My trigger is run from the context of database "newdb", so replication ignores the updates.

I have tried moving the update statement to a stored procedure in "legacydb". This works fine (i.e. data replicates to slave) when I connect to "master" and manually run "USE newdb; CALL legacydb.do_update('Foobar', 1, 2, 3, 4);". However, when this procedure is called from a trigger it does not replicate.

So far my thinking on how to fix this has been one of the following.

• Force the trigger to set a new current database. This would be easiest, but I don't think this is possible. This is what I hoped to achieve with the stored procedure.

• Replicate both databases, and have triggers in both master and slave. This would be possible, but a pain to set up.

• Force the replication to pick up all changes to "legacydb", regardless of the current database context.

• If replication runs at too high a level, it will never even see any updates run by my trigger, in which case no amount of hacking is going to achieve what I want.

Any help on how to achieve this would be greatly appreciated.

Answer 1

have a look at how these Tungsten Replicatorguys do it

Answer 2

This may have something to do with it:

A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log. Statements that invoke a function are recorded rather than the statements executed within the function. Consequently, stored functions that update the same underlying tables do not execute in parallel.

In contrast, stored procedures do not acquire table-level locks. All statements executed within stored procedures are written to the binary log.

Additionally, there are a whole list of issues with Triggers: http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html