Is it possible to run triggers on a MySQL read-only slave?

Meaning, the Master receives all the insert/update statements. Then passes them to the slave via binary log. On the slave, is it possible to have triggers installed that will react to the binary logs, so that I could enable some reporting/BI capabilities?


It's possible, since MySQL replication is nothing more than query replay. You're almost certainly going to end up blowing your foot off at the knee sooner or later, but hey, it's your data.

The trick is that you can't rely on existing data in the master being processed by the triggers. So when you finish the initial data copy, you need to run whatever queries are required to process your existing data, then create the triggers, and then let the floodgates open for the new data from the master.

  • +1 for correctly stating that your leg is not for long in this world. But then that goes without saying with anything to do with MySQL :) – David Pashley Jun 10 '09 at 11:10
  • What about mysql 5.1 that has row-based binary logs as well as statement-based? Plus.. why is my leg in danger? O.o – Jonathan Jun 10 '09 at 11:41
  • @Scuzzbopper: MySQL replication has a long and vigorous history of sucking like a black hole. I haven't used 5.1's row-based binlogs, and if I get my way I never, ever, ever will. – womble Jun 10 '09 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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