Of cause, MySQL does not have materialized view log now as Oracle has. But we can implement the same feature with a log table and a trigger, since MySQL version 5 does support the trigger. In MySQL we do not need to create a sequence, you just need to specify the operation sequence column as auto increment.
CREATE TABLE MLOG$_ASYNCDATA
(
SEQUENCE$$ BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
DMLTYPE$$ CHAR,
COL1 BIGINT
);
Then we need to create three triggers, one for insert, one for update and one for delete (I am not sure whether we can combine them into one trigger as Oracle does). The syntax looks quite like Oracle's PL/SQL.
DELIMITER |
CREATE TRIGGER TLOG$_I_ASYNCDATA BEFORE INSERT ON ASYNCDATA
FOR EACH ROW BEGIN
INSERT INTO MLOG$_ASYNCDATA (DMLTYPE$$,COL1) VALUES ('I',NEW.COL1);
END;
|
CREATE TRIGGER TLOG$_U_ASYNCDATA BEFORE UPDATE ON ASYNCDATA
FOR EACH ROW BEGIN
IF (NEW.COL1 = OLD.COL1) THEN
INSERT INTO MLOG$_ASYNCDATA (DMLTYPE$$,COL1) VALUES ('U',OLD.COL1);
ELSE
INSERT INTO MLOG$_ASYNCDATA (DMLTYPE$$,COL1) VALUES ('D',OLD.COL1);
INSERT INTO MLOG$_ASYNCDATA (DMLTYPE$$,COL1) VALUES ('I',NEW.COL1);
END IF;
END;
|
CREATE TRIGGER TLOG$_D_ASYNCDATA BEFORE DELETE ON ASYNCDATA
FOR EACH ROW BEGIN
INSERT INTO MLOG$_ASYNCDATA (DMLTYPE$$,COL1) VALUES ('D',OLD.COL1);
END;
|
DELIMITER ;
So you can test the replication from MySQL to Oracle now with asyncdata script. As I know, SQL Server, Sybase, DB2 all support triggers, so replication between different types of database is not a very difficult thing now.
Comments (1)
New site, SP
Posted by dushenglin | September 11, 2007 6:03 PM