I have MasterDB
and ReplicaDB
in Amazon RDS environment. I need to move 1 table (MYTable
) from replica to other external DB (ExternalDB
).
My solution (do not work) was to add a AFTER TRIGGER
listening for UPDATES
and INSERTS
to MYTable (and only add that TRIGGER in ReplicaDB) and copy everything on MyTableLog. Them pooling MyTableLog (and remove already processed records).
Problem: It looks like the RDS replica it is not firing the AFTER INSERT
event (is only firing AFTER UPDATES
). However I tested the solution in 5.7 and it worked.
Any ideas? It is a bug in MySQL
? Any solution?
UPDATES:
1- I'm adding a new trigger on SlaveDB
(it is not a trigger replicated from MasterDB
)
2- It is working in 5.7 -> 5.7 … the issue is in 5.6 -> 5.6 MySQL
DB
3- Im adding TWO triggers (both works in 5.7 but only 1 works in 5.6)
4- Update Trigger (works in 5.6 and 5.7)
CREATE TRIGGER after_mytable_update
AFTER UPDATE ON mytable
FOR EACH ROW
BEGIN
INSERT INTO mytable_log
SET is_new = 0, is_processed = 0;
END
5- Insert Trigger (DO NOT WORK IN MYSQL 5.6)
CREATE TRIGGER after_mytable_insert
AFTER INSERT ON mytable
FOR EACH ROW
BEGIN
INSERT INTO mytable_log
SET is_new = 1, is_processed = 0;
END
As simple as it is. The INSERT
trigger it **IS NOT being executed in MySQL 5.6
**
(From Comment)
CREATE TABLE mytable_log_replica (
replica_id int(11) unsigned NOT NULL AUTO_INCREMENT,
is_new int(11) DEFAULT '0',
is_processed int(11) DEFAULT '0',
id int(11) unsigned NOT NULL,
stamp datetime DEFAULT NULL,
user_id int(11) DEFAULT NULL,
name varchar(64) DEFAULT NULL,
address varchar(64) DEFAULT NULL,
transaction_status varchar(64) DEFAULT NULL,
ip varchar(64) DEFAULT NULL,
cb_code varchar(16) DEFAULT NULL PRIMARY KEY (replica_id)
) ENGINE=InnoDB AUTO_INCREMENT=240878 DEFAULT CHARSET=latin1
Best Answer
According Paragraphs 1-3 of MySQL Documentation
Replication and Triggers
MySQL default for binlog_format in 5.6 is
STATEMENT
andROW
in 5.7. You will have to set that in the DB Parameter Group. You may need to convert the binlog_format toSTATEMENT
on RDS Master, RDS Slave, and the External Slave. Only the can you configure a trigger on the External Slave.Interestingly, MariaDB has in its Documentation
Running Triggers on the Slave for Row-based Events
. You may have to switch to MariaDB.