up vote 1 down vote favorite
share [g+] share [fb]

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?

link|improve this question
feedback

1 Answer

up vote 1 down vote accepted

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.

link|improve this answer
+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
feedback

Your Answer

 
or
required, but never shown
discard

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