NYCPHP Meetup

[Matt Juszczak]

> Probably worth noting.  Turns out if you connect PHP to one database and insert a record in another database then the
> binlogs filter this together with the database you are connected to.  In other words, I wasn't seeing any inserts with
> the --database=  option in mysqlbinlog.  Ended up dumping all binlog data and parsing it before reinserting my missing
> data.

This is correct behavior.

It's actually why I don't recommend people use database-specific 
replication.  When you skip a database or only replicate a specific 
database, it will only work if queries are actually selecting that 
database as the working database (in PHP, mysql_select_db(), and on the 
command line, use <database>;).

I've seen what you describe above break replication setups massively, when 
people think an entire specific database is being replicated, and then run 
queries like:

INSERT INTO DB.tableName VALUES (...,...,...);

And that doesn't get replicated.

A better thing to do is blackhole tables on a slave if you really don't 
want to store that data, and just setup replication normally - then you're 
at least guaranteed to have consistent data (though, you can't happily 
promote that slave to a master, but you wouldn't have been able to do that 
anyway with the original db-specific replication).

Just my .02

-Matt