1

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?

4
  • I'm not sure I understand your use case. Are you basically saying you want to make three exact copies of a table on your slave? If so can you add more "why" to your description? Or, are these other tables aggregate summary tables of sorts?
    – atxdba
    May 3, 2012 at 6:09
  • Yes. I am looking for a way to make three exact copies of a table on slave. Why? All three needs to have different partitioning. hourly, daily and monthly.
    – shantanuo
    May 3, 2012 at 7:30
  • Perhaps 3 slave databases with identical tables (and different partitioning): slave_db_a.tableX, slave_db_b.tableX, slave_db_c.tableX. May 3, 2012 at 7:42
  • Please include SHOW CREATE TABLE table_a\G May 3, 2012 at 16:08

1 Answer 1

1

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
1
  • Does it take care of the updates and deletes taking place on table_a ?
    – shantanuo
    May 7, 2012 at 5:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.