MySQL 复制 - 触发器不会复制到从属

Question

好的，这是我的场景

服务器在服务器上为每个站点运行主数据库和其他数据库。将更新写入主数据库，触发器将此记录插入到特定站点的相关数据库中，该站点旨在复制到站点数据库级别。

触发器工作正常，但插入的记录没有进入从数据库。我正在使用基于语句的复制，因为基于行不是一个选项。我也试过调用存储过程并将其作为一个事件来执行，但没有成功。

有没有办法解决这个问题 - 顺便说一句，我正在使用 MySQL 5.1

谢谢

触发器语法如下

dbmaster=服务器主数据库dbsite= 服务器站点 DB(这是复制主机)

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 ;

Answer 1

来自 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.

因此，您需要将触发触发器的语句复制到从属设备，并且您需要在从属设备上创建重复的触发器，这些触发器将应用相同的更新来响应这些语句。