Triggers on Slave database (in the context of replication) for auditing purpose in MySQL

Question

I have a requirement for auditing in MySQL database. It means I am interested in what data is changed in my database and when. For fulfilling this requirement I am thinking about the triggers. I am also using row based replication which ensures that data from master database is copied on slave machine. Since triggers might reduce the performance and it can introduce additional issues like row locking, I would like to avoid it on master database. Instead I would like to define triggers only on slave machine. Since data on slave will be identical with master, my requirement of auditing will be fulfilled also I can avoid performance and locking issues on master. Following are my environment details

Environment details

1. MySQL version 5.6 on windows
2. Master and Slave database with row based replication

So my questions are

1. Is it possible in MySQL to define the triggers only on slave?
2. Is this a correct approach?
3. Will it create any serious issue?

Answer 1

@parag Yes you can create triggers on the slave which won't be replicated. The only issue I see is if you're Master fails and you promote your slave to master you will need to keep track of what changes need to be applied to the new slave and what changes need to be applied to the newly promoted master. If I'm not mistaken you can create the triggers on the master so that they can be replicated and then disable them and just enable them on your slave. That way all your environments will have the triggers but only enabled on one slave.

Or, so you can avoid managing details like that, which can become overwhelming, you can make the slave, the master of another slave which will do the auditing and only have the triggers on that subsystem.

Thanks John