Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 '12 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 '12 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. –  ypercube May 3 '12 at 7:42
    
Please include SHOW CREATE TABLE table_a\G –  RolandoMySQLDBA May 3 '12 at 16:08

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
    
Does it take care of the updates and deletes taking place on table_a ? –  shantanuo May 7 '12 at 5:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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