Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I suspect the answer to this is "you're boned", but I'd like to see if there are any good options that I haven't explored.

We're building a system that will integrate with a 3rd-party POS system and display tickets on boards at various stations in a kitchen. We don't have direct access to the POS system's database; rather, the database is replicated to another server that we can read data from.

In order to send new tickets to the clients, or change/remove modified tickets, we need to watch this database for changes. If we were running it all on a single database server, I'd add a few change logging triggers to the tables in question (which total a couple GB), then poll that log table every 5-10 seconds to see which tickets need to be rebroadcast.

Unfortunately, we're dealing with this replicated system. I know that I can create triggers on the slave, but they'll only run for operations that are logged in statement format rather than row format. The master server is configured for mixed binlog format, and looking through the list of operations that are unsafe to log in statement format, I'd be surprised if none of them are being used on the POS tables. We absolutely can't have tickets get missed due to the trigger not being run on the slave.

These POS tables also don't appear to have any update timestamp columns on them, so I can't simply query rows that have been updated since the last polling time (not to mention the possible race conditions we'd have to address there). Further, these tables are much too big to snapshot and diff them (or a relevant subset of their columns) at every polling.

Oh, and I'm guessing it would be way too much work to connect to the master directly pretending to be a slave and reading the binlog content directly. So, anybody have any clever tricks I haven't thought of before we tell the client we can't do it with what we have? (They would probably be comfortable letting us access the master server, but it's the application vendor that's cagey about it. The client owns the server and data, and we could certainly force our way in, but I don't want to void any contracts and leave them really screwed.)

share|improve this question
add comment

1 Answer

If you can modify the configuration of the slave, then the solution would be to fairly straightforward: turn on log_slave_updates on the slave, which will result in the writing everything changed on the slave via replication events into the slave's own binlog, which you could then "sniff" and decode every event as it occurs, by connecting to the slave as "your" master and reading from the replication stream that it generates.

Further, changing the slave's binlog_format to ROW will cause the slave to translate any statement-based events from the master so that they will be written to the slave's binlog (and replicated to you) as row-based events, which are dramatically simpler to process from the replication stream.

Once you have that, there are numerous projects in various languages that can spoof a slave connection and decode a replication stream, although in my case, I ended up writing my own parser because I wanted to genuinely understand the low-level nuts and bolts of the implementation well enough that I could thoroughly troubleshoot it as well as extend it... because with each major version iteration from MySQL Server 5.1 → 5.5 → 5.6 (I don't have anything older in my networks), new capabilities in replication have been introduced that will break older code that fails to understand the new structures. This, incidentally, is why you always upgrade the slave first, when doing version upgrades, since new slaves understand everything an old master can send, but not the other way around. Some of the open source projects that accomplish this will not work with newer versions of MySQL or will require non-default configurations, because of these changes.

I also wanted support for the compressed client/server protocol, which can significantly reduce the number of bytes sent over the wire in replication events, but which didn't often seem to be as much of a priority to some of the other projects.

Sniffing the replication stream has been very useful in my operation, since I really hate polling. The one caveat I would point out that you might not expect is the fact that foreign key cascade events are not explicitly replicated, ever. MySQL itself is apparently completely unaware when InnoDB performs foreign key operations on the child tables as a result of updates or deletes on the parent table. I never thought about precisely which "layer" was doing this before, but it makes sense... if foreign keys weren't happening strictly internal to InnoDB, then there's no reason why MyISAM wouldn't have them by now... and there would also be no reason why foreign keys cascades couldn't activate triggers, but that too makes sense in this light, since triggers happen at the MySQL layer, not the storage engine layer... and this also explains why cascade actions are not reflected in the "rows affected" count. The rows changing on the parent table are replicated, and on a normal slave, the foreign key cascades are handled internally by InnoDB magic... but that's the only unexpected thing I've encountered in the last couple of months of using this in live (back-end, not end-user-facing) systems.

share|improve this answer
    
Interesting. I'll look into it and see if it seems viable. –  db2 Dec 13 '13 at 21:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.