使用Rotate Master实现MySQL多主复制(二)
-1)
INTO _master_file, _master_pos;
UPDATE mysql.rotate_master SET `master_log_file` = _master_file, `master_log_pos` =
_master_pos, id = LAST_INSERT_ID(id), `IS_Slave_Running` = _is_slave_running WHERE
in_use = 1;
_master_pos, id = LAST_INSERT_ID(id), `IS_Slave_Running` = _is_slave_running WHERE
select weight,repeated_times into _weight,_repeated_times from mysql.rotate_master
where in_use =1;
where in_use =1;
if(_weight <= _repeated_times)
THEN
SELECT
id,
master_host,
master_port,
master_log_file,
master_log_pos
INTO _id, _master_host, _master_port, _master_file, _master_pos
FROM rotate_master
ORDER BY id <= LAST_INSERT_ID(), id LIMIT 1;
SET @sql := CONCAT(
'CHANGE MASTER TO master_host=', QUOTE(_master_host),
', master_port=', _master_port,
', master_log_file=', QUOTE(_master_file),
', master_log_pos=', _master_pos
', master_user="repl"',
', master_password="repl"'
); www.2cto.com
PREPARE myStmt FROM @sql;
EXECUTE myStmt;
UPDATE mysql.rotate_master SET in_use = 0,repeated_times=0 WHERE in_use = 1;
UPDATE mysql.rotate_master SET in_use = 1,repeated_times=1 WHERE id = _id;
ELSE
UPDATE mysql.rotate_master SET `repeated_times`=`repeated_times`+1 WHERE in_use = 1;
END IF;
START SLAVE;
END$$
DELIMITER ;
创建Event：
每2分钟运行一次，rotate_master() ，即时间片大小是2分钟
DELIMITER $$
CREATE EVENT `rotate_master` ON SCHEDULE EVERY 120 SECOND STARTS '2011-10-13 14:09:40' ON COMPLETION NOT PRESERVE ENABLE DO CALL mysql.rotate_master()$$
DELIMITER ; www.2cto.com
至此，多主复制已经搭建完成。
由于时间片长度是2分钟。
Master1 在执行 1*2 分钟后，stop slave，然后change master to Master2；
Master2 在执行 2*2 分钟后，stop slave，然后change master to Master3；
Master3 在执行 5*2 分钟后，stop slave，然后change master to Master2；
并以此循环往复。
如果希望把其中一个master移除多主复制，可以将他的配置项权重设置为0；
即： update rotate_master set weigh=0 where id = #ID# ;
作者 cenalulu