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