快捷導(dǎo)航

使用Rotate Master實(shí)現(xiàn)MySQL 多主復(fù)制的實(shí)現(xiàn)方法

更新時間：2012年05月28日 00:46:29 作者：

眾所周知，MySQL只支持一對多的主從復(fù)制，而不支持多主（multi-master）復(fù)制

當(dāng)然，5.6的GUID功能的出現(xiàn)也帶來了multi-master的無限可能，不過這個已經(jīng)是題外話了。
本文主要介紹一種非實(shí)時的適用于各版本MySQL的multi-master方法。
內(nèi)容簡介：
最初的思路來源于一位國外DBA的blog ： http://thenoyes.com/littlenoise/?p=117
基本原理就是通過SP記錄當(dāng)前 master-log的name和pos記錄到表中，然后讀取下一個master記錄，執(zhí)行stop slave / change master / start slave。以此循環(huán)反復(fù)。
個人對他的方法進(jìn)行了改進(jìn)，增加了以下功能：
1. master可以根據(jù)業(yè)務(wù)流量設(shè)置權(quán)重值
2. 各個master-slave運(yùn)行情況的監(jiān)控
3. 各個master可以實(shí)時退出多主的架構(gòu)
具體操作過程：
1. 創(chuàng)建保存各個master信息的表

復(fù)制代碼代碼如下:

 
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 '是否是當(dāng)前正在同步的數(shù)據(jù)行', 
`weight` int(11) NOT NULL DEFAULT '1' comment '該master的權(quán)重，即重復(fù)執(zhí)行多少個時間片', 
`repeated_times` int(11) NOT NULL DEFAULT '0' comment '當(dāng)前已經(jīng)重復(fù)執(zhí)行的時間片數(shù)', 
`LastExecuteTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '上次執(zhí)行的時間', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

新增加一個master ：

復(fù)制代碼代碼如下:

 
insert into `rotate_master` 
(`master_host`,`master_port`,`master_log_file`,`master_log_pos`,`in_use`,`weight`) 
values 
-- 1號master，權(quán)重=1，并設(shè)置為當(dāng)前master 
('192.168.0.1',3307,'mysqlbinlog.000542',4,1,1), 
-- 2號master，權(quán)重=2 
('192.168.0.2',3306,'mysqlbinlog.000702',64762429,0,2), 
-- 3號master，權(quán)重=5 
('192.168.0.3',3306,'mysqlbinlog.000646',22157422,0,5) 

手工把master 調(diào)整到當(dāng)前的配置項：

復(fù)制代碼代碼如下:

 
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; 

創(chuàng)建rotate master SP：
注意：代碼中用于連接master的用戶名和密碼是： repl / repl ，請根據(jù)自己的情況修改。

復(fù)制代碼代碼如下:

 
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 ; 

創(chuàng)建Event：
每2分鐘運(yùn)行一次，rotate_master() ，即時間片大小是2分鐘

復(fù)制代碼代碼如下:

 
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 ; 

至此，多主復(fù)制已經(jīng)搭建完成。
由于時間片長度是2分鐘。
Master1 在執(zhí)行 1*2 分鐘后，stop slave，然后change master to Master2；
Master2 在執(zhí)行 2*2 分鐘后，stop slave，然后change master to Master3；
Master3 在執(zhí)行 5*2 分鐘后，stop slave，然后change master to Master2；
并以此循環(huán)往復(fù)。
如果希望把其中一個master移除多主復(fù)制，可以將他的配置項權(quán)重設(shè)置為0；
即： update rotate_master set weigh=0 where id = #ID# ;