1

Ok here is my scenario

Server running master DB with other DB's for each site also on server. an update is written to master DB, and a trigger inserts this record into the relevant DB for a specific site which is intended to be replicated down to site DB level.

Trigger works fine, but the inserted record doesnt make it to the slave DB. I am using statement based replication as row based isnt an option. I have also tried calling a stored procedure and doing this as an event but nothing comes through.

Is there any way around this - by the way i am using MySQL 5.1

Thanks

Syntax for trigger below

dbmaster=server master db dbsite= server site DB (this is the replication master)

delimiter # 
create trigger rstreplicate after insert on dbmaster.exchange 
for each row begin insert into dbsite.exchange 
select distinct new.TYPE, new.FILENAME, new.STATUS, new.USER_ID, new.INP_DATE, new.CHG_DATE from dbmaster.exchange where new.IP='127.0.0.1'; 
end# 
delimiter ;
JazziJeff
  • 721
  • 4
  • 17
  • 35

1 Answers1

0

From MySQL documentation:

B.5.12: Do triggers work with replication?

Yes. However, the way in which they work depends whether you are using MySQL's “classic” statement-based replication available in all versions of MySQL, or the row-based replication format introduced in MySQL 5.1.

When using statement-based replication, triggers on the slave are executed by statements that are executed on the master (and replicated to the slave).

When using row-based replication, triggers are not executed on the slave due to statements that were run on the master and then replicated to the slave. Instead, when using row-based replication, the changes caused by executing the trigger on the master are applied on the slave.

So, you'll need the statements that are triggering the trigger to replicate to the slave, and you'll need to create duplicate triggers on the slave that will apply the same updates in response to the statements.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • but will this work if the dbmaster doesnt exist on site level?? they only use dbsite – JazziJeff Mar 21 '12 at 17:09
  • Trigger doesn't have to match exactly. Modify it as necessary to accomplish required update, based on replicated statement. – Marcus Adams Mar 21 '12 at 17:18
  • im sorry i dont quite understand - if we take the above trigger as an example, at site level i have my database dbsite and my exchange table - what would my trigger syntax be here, the same as above? thanks again for your help with this by the way – JazziJeff Mar 21 '12 at 18:31
  • this didnt really get me anywhere :( – JazziJeff Apr 03 '12 at 10:52