As we all know, MySQL only supports one-to-many master-to-slave replication, but does not support multi-master replication.
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.
Introduction:
The original thought came from a blog of a foreign DBA:
The basic principle is to record the name and pos records of the current master-log to the table through SP, then read the next master record, and execute 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 weights based on business traffic.
2. Monitor the running status of each master-slave
3. Each master can exit the multi-master architecture in real time.
Procedure:
1. Create a table to save information about each master.
The Code is as follows:
Use mysql;
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 at 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) DEFAULT '0' comment' indicates whether the data row is being synchronized ',
'Weight' int (11) not null default '1' comment' weight of the master, that is, how many time slices are repeated ',
'Repeated _ times 'int (11) not null default '0' comment' number of time slices already executed repeatedly ',
'Lastexecutetime' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment 'last executed time ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8
Add a new master:
The Code is as follows:
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
('1970. 168.0.1 ', 192, 'mysqlbinlog. 100', 3307, 1 ),
-- Master 2, Weight = 2
('192. 168.0.2 ', 192, 'mysqlbinlog. 100', 64762429,0, 2 ),
-- Master 3, Weight = 5
('192. 168.0.3 ', 192, 'mysqlbinlog. 100', 22157422,0, 5)
Manually adjust the master to the current configuration item:
The Code is as follows:
Change master to master_host = '192. 168.0.1 ', master_port = 192, master_log_file = 'mysqlbinlog. 100', 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.
The Code is as follows:
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 (_ 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)
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;
Create Event:
Run once every 2 minutes, rotate_master (), that is, the size of the time slice is 2 minutes
The Code is as follows:
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;
Now, the multi-master replication has been set up.
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;
And this round-robin.
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 #;
