Mysql RELICATION對存過的處理

當時我在想，MASTER用近兩個小時，是不是SLAVE也用這麼長時間麼；

到SLAVE一看，根本沒有SQL在跑；
而且表裡資料已經被清空；

透過測試，我們發現MASTER在應用存過時，在BINLOG裡記錄的是真正最後執行的DML操作；
比如：
#101202 13:58:43 server id 2 end_log_pos 15842 Query thread_id=4058 exec_time=0 error_code=0
SET TIMESTAMP=1291269523/*!*/;
delete from test where id = NAME_CONST('v_entry_id',238)
/*!*/;
# at 15842
#101202 13:58:43 server id 2 end_log_pos 15974 Query thread_id=4058 exec_time=0 error_code=0
SET TIMESTAMP=1291269523/*!*/;
delete from test where id = NAME_CONST('v_entry_id',240)
/*!*/;
# at 15974
#101202 13:58:43 server id 2 end_log_pos 16106 Query thread_id=4058 exec_time=0 error_code=0
SET TIMESTAMP=1291269523/*!*/;
delete from test where id = NAME_CONST('v_entry_id',242)
/*!*/;

...

所以只要MASTER執行完後，就會馬上應用到SLAVE；

下面是一個刪除資料的儲存過程範例：
================================================
use test;
drop procedure if exists delete_expired;
delimiter //
CREATE PROCEDURE delete_expired(in in_date date)
BEGIN
declare done int default 0;
declare rowcnt int default 0 ;
declare v_entry_id int;
declare cur_del cursor For select id from test ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
open cur_del;
start transaction;
cursor_loop:loop
fetch cur_del into v_entry_id;
if done=1 then leave cursor_loop ;
end if ;
delete from test where id =v_entry_id;
set rowcnt=rowcnt+1;
if rowcnt=1000 then
set rowcnt =0 ;
commit;
start transaction;
end if ;
end loop cursor_loop ;
commit ;
close cur_del;
END ;//
DELIMITER ;

call delete_expired ('2010-01-10');
drop procedure if exists delete_expired ;
===========================================================