使用Rotate Master实现MySQL 多主复制的实现方法
程序员文章站
2023-12-22 15:50:04
当然，5.6的guid功能的出现也带来了multi-master的无限可能，不过这个已经是题外话了。 本文主要介绍一种非实时的适用于各版本mysql的multi-maste...
当然，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`@`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# ;
本文主要介绍一种非实时的适用于各版本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`@`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# ;