Mysql – Keeping 2 tables in sync

MySQLreplicationtrigger

I need to write a trigger that will keep one table synced with 2 other tables. I have table_a, table_b and table_c on slave. The master has only table_a and whenever this table is updated on master, the two other tables on slave should also get updated using triggers or any other way.

In other words, I need to replicate only one table from this slave. Since there is no budget for one more server, I will use the same slave schema. Do I need to write after update, insert and delete triggers on table_a? What is the best approach?

Best Answer

If you have this scenario

  • table_a has an auto_increment field
  • table_b and table_c have table structure identical to table_a
  • You are running MySQL 5.1+

you can create a Stored Procedure and a MySQL event instead of a Trigger

For the sake of example, let's say table_a looks like this:

CREATE TABLE table_a
(
    id not null auto_increment,
    ....
    primary key (id),
);

Create a Stored Procedure on the Slave to Copy Data to table_b and table_c

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `Load_New_Data` $$ 
CREATE PROCEDURE `Load_New_Data` () 
BEGIN
    DECLARE min_id,min_idb,mid_idc INT;

    SELECT MAX(id) INTO min_idb FROM table_b;
    SELECT MAX(id) INTO min_idc FROM table_c;
    SET min_id = IF(min_idb < min_idc,min_idb,min_idc);

    DROP TABLE IF EXISTS table_a_delta;
    CREATE TABLE table_a_delta SELECT * FROM table_a WHERE 1=2;
    INSERT INTO table_a_delta SELECT * FROM table_a WHERE id >= min_id;
    INSERT IGNORE INTO table_b SELECT * FROM table_a_delta;
    INSERT IGNORE INTO table_c SELECT * FROM table_a_delta;

    DROP TABLE table_a_delta;
END $$ 
DELIMITER ;

Then, schedule a MySQL Event on the Slave for the Stored Procedure to go off every 5 minutes starting 5 minutes after you create the event:

CREATE EVENT ev 
    ON SCHEDULE 
      EVERY 5 MINUTE 
      STARTS (NOW() + INTERVAL 5 MINUTE) 
    DO 
      CALL Load_New_Data();

Don't forget to Add This to the Slave's /etc/my.cnf

[mysqld]
event_scheduler=1
Related Question