软件开发培训班 >> 编程开发 >> MySQL


使用Rotate Master实现MySQL 多主复制的实现方法

　　本文标签：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# ;