Things Not Replicating Correctly? Part 2

My last post on June 10 was about when MySQL received queries from php scripts that looked like this:

mysql_select_db = ('database_I_DO_want_to_replicate', $link);
$sql = "INSERT INTO db_I_DO_NOT_want_to_replicate.repl_table (a,b,c) VALUES (1,2,3);";

-- and thus could not pass this over to the slave unless I set Replicate_Wild_Ignore_Table value IN ADDITION to Replicate_Ignore_DB as such in my configuration file:

replicate_ignore_DB = db_I_DO_NOT_want_to_replicate
replicate_wild_ignore_table = db_I_DO_NOT_want_to_replicate.%

News Flash

Three things:

1. As it happens, we do not need use replicate_ignore_DB for this to work properly;
2. Replicate_do_DB and Replicate_ignore_DB do not need to be used at all;
3. In fact, Replicate_do_DB is somewhat evil.

Firstly, replicate_wild_ignore_table is all you need to ignore databases, and it will properly replicate all kinds of php riff-raff, where the scripts do not necessarily select the proper database and they are always written in db.table style.

Secondly, replicate_wild_do_table is all you need to include databases, and it will properly replicate all kinds of php fudgerama, blah blah blah.

Thirdly, if you use replicate_do_DB in addition to replicate_wild_do_table to try to cover your bases, it will indeed NOT cover your bases. We have strict replication here, and it WILL break if the php code does this:

mysql_select_db = ('database_I_DO_NOT_want_to_replicate,$link);
$sql = "INSERT INTO database_I_DO_want_to_replicate.repl_table (a,b,c) VALUES (1,2,3);";

So, my advise, once again is:

DO NOT USE replicate_ignore_db and replicate_do_db!

Instead, use replicate_wild_ignore_db and replicate_wild_do_db.

Have fun! Try it out and see for yourself how this can work for and against you! (as well as MySQL loading my.cnf twice, causing your list of tables to appear twice -- proposed to be fixed for MySQL 5.1).

Until next time,
David

An Interesting Replication Tip

We recently moved some databases to different servers, and I altered our mySQL slave configuration files to take into account some of the databases we wanted to replicate (Replicate-do-db) and others that we wanted to ignore (Replicate-ignore-db) -- each of these server cases were mutually exclusive.

All went well, until I found a peculiar error:

Error 'Table 'db101.table101' doesn't exist' on query. Default database: 'db201'. Query: 'UPDATE `db101`.`table101` SET `value101`=`value101`+1 WHERE `gender`='F' AND `site`='177' AND `date`=CURDATE()'

I did not want to replicate anything from database db101, but I did want to replicate every table from db201.

How to Proceed

These are my choices:

1. Get our team of php programmers to properly specify the database that their script is using, even if it requires 1,000 updates. And it needs to be done 10 minutes ago because our slave is stopped;
2. Provide a bug report to mySQL and wait (months or years)
   
3. Use a proxy to sort out the proper database (but there are better purposes for my lua scripts);
4. Use Replicate-Wild-Ignore-Table option in the configuration file (very fast).

The Solution

Replicate-Do-Db=db201 # This line was already here
Replicate-Wild-Ignore-Table=db101.*

In Conclusion

There exists many solutions to a problem, but we try to aim for the simplest, most elegant solution to create the smallest footprint in terms of overhead and maintenance.

To further this last point, we must try our best to keep the overhead of our systems to a minimum, otherwise things can get out of control when we least expect it, as described in this post by John Mark Walker.

Until next time,
David