mysql recreate_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 ;

===========================================================