Here’s one ‘feature’ that I bumped into and scratched my head over for a while, until it occurred to me what was happening. It appears that, if you are using replication, when you enable replication only on select databases (instead of all databases and excluding what you don’t want to replicate), you are running the risk that some of your queries will not replicate.
This happens when you have selected a database, which is not being replicated, (or you have no databases selected) and you perform a query specifying another database in the query, which does replicate. Normally, if you had selected the replicating database, then executing the query would allow it to replicate, but not if another is selected. This caught me by surprise, because you would expect the parser to be clever enough to know that you have specified a database in your query that does replicate, but it isn’t. I assume this is for performance reasons.
To give you an example in code, assume 2 databases: FRUITS, NUTS and VEGETABLES. NUTS and FRUITS are being replicated (ie. in the configuration: ‘replicate-do-db=FRUITS’), while VEGETABLES is not being replicated.
Secondly, assume we are going to run the query, which should replicate to our second database:
INSERT INTO FRUITS.list VALUES ( ‘apple’, ‘canada’, ‘red’ );
If we ran the query in this order, it would work fine:
USE FRUITS
INSERT INTO FRUITS.list VALUES ( ‘apple’, ‘canada’, ‘red’ );
However, if we ran the query as such, it would NOT replicate:
USE VEGETABLES
INSERT INTO FRUITS.list VALUES ( ‘apple’, ‘canada’, ‘red’ );
Likewise, because NUTS is being replicated, if you ran the following it would work again:
USE NUTS
INSERT INTO FRUITS.list VALUES ( ‘apple’, ‘canada’, ‘red’ );
So, so long as you have a selected database that is being replicated, then it replicates fine. But select a database which is being ignored or if you have no database selected at all, then it doesn’t.
Obviously, this is kind of annoying, because it’s something that seems so trivial, you would expect MySQL to be able to handle it, but apparently not.
So, the obvious solution is to specify in your database configuration that all databases replicate and exclude the ones you don’t want to replicate. Of course, bear in mind, the opposite is true. You can break replication if you select a replicating database and run a query specifying a non-replicating database, because it will still add that query to the replication binary log. Oh brother.
So the best thing to do, really, is to make sure that all of your replicating database servers are identical, turn on full replication and do not have any tables that do not replicate. I wish this sort of thing is more clearly documented, so you don’t run around with the belief that MySQL is doing something that would otherwise seem so trivial and logical to do and then suffer the consequences with a terrifying potential.