使用Rotate Master實現MySQL 多主複製的實現方法

當然，5.6的GUID功能的出現也帶來了multi-master的無限可能，不過這個已經是題外話了。
本文主要介紹一種非即時的適用於各版本MySQL的multi-master方法。
內容簡介：
最初的思路來源於一位國外DBA的blog ： http://thenoyes.com/littlenoise/?p=117
基本原理就是通過SP記錄當前 master-log的name和pos記錄到表中，然後讀取下一個master記錄，執行stop slave / change master / start slave。以此迴圈反覆。
個人對他的方法進行了改進，增加了以下功能：
1. master可以根據業務流量設定權重值
2. 各個master-slave運行情況的監控
3. 各個master可以即時退出多主的架構
具體操作過程：
1. 建立儲存各個master資訊的表 複製代碼 代碼如下:use mysql;
CREATE TABLE `rotate_master` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`master_host` varchar(255) DEFAULT NULL comment 'master地址',
`master_port` int(10) unsigned DEFAULT NULL comment 'master連接埠' ,
`master_log_file` varchar(255) DEFAULT NULL ‘上次停止時的master-log檔案',
`master_log_pos` int(10) unsigned DEFAULT NULL comment '上次停止時的master-log-pos',
`IS_Slave_Running` varchar(10) DEFAULT NULL comment '上次停止時主從是否有異常',
`in_use` tinyint(1) DEFAULT '0' comment '是否是當前正在同步的資料行',
`weight` int(11) NOT NULL DEFAULT '1' comment '該master的權重，即重複執行多少個時間片',
`repeated_times` int(11) NOT NULL DEFAULT '0' comment '當前已經重複執行的時間片數',
`LastExecuteTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '上次執行的時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

新增加一個master ： 複製代碼 代碼如下:insert into `rotate_master`
(`master_host`,`master_port`,`master_log_file`,`master_log_pos`,`in_use`,`weight`)
values
-- 1號master，權重=1，並設定為當前master
('192.168.0.1',3307,'mysqlbinlog.000542',4,1,1),
-- 2號master，權重=2
('192.168.0.2',3306,'mysqlbinlog.000702',64762429,0,2),
-- 3號master，權重=5
('192.168.0.3',3306,'mysqlbinlog.000646',22157422,0,5)

手工把master 調整到當前的配置項： 複製代碼 代碼如下:change master to master_host='192.168.0.1', master_port=3306,master_log_file='mysqlbinlog.000542',master_log_pos=4,master_user='repl',master_password='repl';
start slave;

建立rotate master SP：
注意：代碼中用於串連master的使用者名稱和密碼是 ： repl / repl ，請根據自己的情況修改。 複製代碼 代碼如下:DELIMITER $$
DROP PROCEDURE IF EXISTS `mysql`.`rotate_master`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `rotate_master`()
BEGIN
DECLARE _info text;
DECLARE _master_file varchar(255);
DECLARE _master_pos int unsigned;
DECLARE _master_host varchar(255);
DECLARE _master_port int unsigned;
DECLARE _is_slave_running varchar(10);
DECLARE _id int;
DECLARE _weight int;
DECLARE _repeated_times int;
select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Slave_running' into _is_slave_running;
STOP SLAVE;
SELECT LOAD_FILE(@@relay_log_info_file) INTO _info;
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(_info, '\n', 3), '\n', -1),
SUBSTRING_INDEX(SUBSTRING_INDEX(_info, '\n', 4), '\n', -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;
select weight,repeated_times into _weight,_repeated_times from mysql.rotate_master 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"'
);
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 ;

至此，多主複製已經搭建完成。
由於時間片長度是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# ;