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
slave_db_a.tableX
,slave_db_b.tableX
,slave_db_c.tableX
.SHOW CREATE TABLE table_a\G