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

当然,5.6的GUID功能的出现也带来了multi-master的无限可能,不过这个已经是题外话了。
本文主要介绍一种非实时的适用于各版本MySQL的multi-master方法。 内容简介:
最初的思路来源于一位国外DBA的blog : 基本原理就是通过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`澳门金沙vip,@`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# ;

使用Rotate Master实现MySQL多主复制

 

背景:

众所周知,MySQL只支持一对多的主从复制,而不支持多主(multi-master)复制。

当然,5.6的GUID功能的出现也带来了multi-master的无限可能,不过这个已经是题外话了。

本文主要介绍一种非实时的适用于各版本MySQL的multi-master方法。

 

内容简介:

最初的思路来源于一位国外DBA的blog :

基本原理就是通过SP记录当前
master-log的name和pos记录到表中,然后读取下一个master记录,
执行stop slave / change master / start slave。以此循环反复。

个人对他的方法进行了改进,增加了以下功能:

  1. master可以根据业务流量设置权重值

  2. 各个master-slave运行情况的监控

  3. 各个master可以实时退出多主的架构

 

具体操作过程:

  1. 创建保存各个master信息的表

 

use mysql;  www.2cto.com  

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;  www.2cto.com  

DECLARE _weight int;

DECLARE _repeated_times int;

 

select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where
VARIABLE_NAME =
‘Slave_running’ into _is_slave_running;