Use Rotate Master to implement MySQL multi-Master Replication

Multi-Master Replication Using Rotate master: As we all know, MySQL only supports one-to-many Master-to-slave replication, but does not support multi-master replication. Of course, the emergence of the GUID function of 5.6 has also brought infinite possibilities for multi-master, but this is a digress. This article mainly introduces a non-real-time multi-master Method for MySQL versions. Content Introduction: the initial idea comes from a foreign DBA blog: http://thenoyes.com/littlenoise? P = 117 the basic principle is to record the name and pos records of the current master-log to the table through SP, and then read the next master record,
Run stop slave/change master/start slave. This cycle is repeated. I personally improved his methods and added the following features: 1. the master can set the weight value based on the business traffic. monitor the running status of each master-slave. each master can exit the multi-master architecture in real time. Specific operation process: 1. CREATE a TABLE that saves information about each master using mysql; www.2cto.com create table 'rotate _ master' ('id' int (11) not null AUTO_INCREMENT, 'Master _ host' varchar (255) default null comment 'master address', 'Master _ port' int (10) unsigned default null comment 'master port', 'Master _ log_file 'varchar (255) default null 'master-log file when the last stop ', 'Master _ log_pos' int (10) unsigned default null comment' master-log-pos when the last stop ', 'Is _ slave_running' varchar (10) default null comment 'whether the Master/Slave has an exception during the last stop ', 'in _ use' tinyint (1) whether the DEFAULT '0' comment' is the data row currently being synchronized ', 'weight' int (11) not null default '1' comment' indicates the weight of the master, that is, the number of time slices that have been repeatedly executed ', 'repeated _ times' int (11) not null default '0' comment' current number of time slices that have already been repeated ', 'lastexecutetime' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment 'last executed time', primary key ('id') ENGINE = InnoDB default charset = utf8 adds a new master: insert into 'rotate _ master' ('master _ host', 'Master _ port', 'Master _ log_file ', 'Master _ log_pos', 'in _ use ', 'weight') values -- master 1, weight = 1, and set it to the current master ('123. 168.0.1 ', 3307, 'mysqlbinlog. 000542 ', 192, 1), -- master 2, Weight = 2 ('100. 168.0.2 ', 3306, 'mysqlbinlog. 000702 ', 64762429,0, 2), -- master 3, Weight = 5 ('123. 168.0.3 ', 3306, 'mysqlbinlog. 000646 ', 22157422,0, 5) manually adjust the master to the current configuration item: change master to master_host = '2017. 168.0.1 ', master_port = 3306, master_log_file = 'mysqlbinlog. 000542 ', master_log_pos = 4, master_user = 'repl ',
Master_password = 'repl'; start slave; create a rotate master SP: Note: In the code, the username and password used to connect to the master are repl/repl. Modify the password as needed. DELIMITER $ drop procedure if exists 'mysql '. 'rotate _ master' $ create definer = 'root' @ 'localhost' PROCEDURE 'rotate _ master' () BEGINDECLARE _ info text; DECLARE _ master_file varchar (255 ); DECLARE _ master_pos int unsigned; DECLARE _ master_host varchar (255); DECLARE _ master_port int unsigned; DECLARE _ 1_varchar (10); DECLARE _ id int; includeclare _ 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; SELECTSUBSTRING_INDEX (SUBSTRING_INDEX (_ info, '\ n', 3 ), '\ n',-1), 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) THENSELECTid, master_host, master_port, master_log_file, master_log_posINTO _ id, _ master_host, _ master_port, _ master_file, _ master_posFROM rotate_masterORDER 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" '); www.2cto.com 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; ELSEUPDATE mysql. rotate_master SET 'repeated _ times '= 'repeated _ times' + 1 WHERE in_use = 1; end if; start slave; END $ DELIMITER; Create E Vent: runs every 2 minutes, rotate_master (), that is, the time slice size is 2 minutes 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; www.2cto.com now, multi-master replication has been established. Because the length of the time slice is 2 minutes. After Master1 is executed for 1*2 minutes, stop slave and change master to Master2; after Master2 is executed for 2*2 minutes, stop slave and change master to Master3; after Master3 is executed for 5*2 minutes, stop slave and change master to Master2. If you want to remove one of the master nodes from multi-master replication, you can set its configuration item weight to 0, that is, update rotate_master set weigh = 0 where id = # ID #; author cenalulu