MySQL Cross Replication
I was looking for an alternative to MySQL Cluster for large tables, less critical tables but still important enough ones ;)
I got MySQL cluster up and running for most of my tables, but we need some tables that are storing log information, it's not really critical, but we don't want to loose it anyway. As we don't want to put that stuf in memory I was looking into replication those tables. Now Linux-HA takes care of which mysqld instance to talk to , but if I fail over the active database IP the applications start writing to the 2nd node which is the replication slave for a couple of tables. That's perfect for the Cluster tables, but it's pain for the replicated InnodB tables as replication breaks and I can't migrate back automagically.
Upon reading the --read-only parameter in the replication documentation I tought I had found the solution, only to realise this actually puts the whole mysql in read-only also the tables being used by MySQL Cluster, not a good solution either therefore.
Fiddling with different users on the different nodes having diffent permissions crossed my mind , but then I started thinking about creating a set of 3 tables. A .master table, a .slave table and on both nodes a .merge table of the previous ones. Read access would be to the .merge table, write access to the .master table, while using --replicate-rewrite-db to replicate to the .slave table. It could have worked (didn't try) but my tables were InnoDB.
I went ahead anyway and setup cross replication for both nodes to another database. Both nodes now have databases with the same tables
blah and blahrepl as this is log information I can always manually merge when I need the data , but at least I don't lose it when a node crashes.
One tricky thing however .. it's documented in the comments of the mysql site
If you're attempting to use both
replicate-do-db=from_name
and
replicate-rewrite-db=from_name->to_name
be aware that you need to actually say
replicate-do-db=to_name
because the rewrite rule apparently happens before
the do-db rule.
And that works.
I should look into upgrading those boxen to MySQL 5.1.11 and continue my tests on Disk Based Cluster , but as they are on an older platform with no prebuild rpm's available that wasn't an option today ..