Of course, the appearance of the 5.6 GUID feature also brings the infinite possibility of multi-master, but this is the digression.
This paper mainly introduces a kind of multi-master method which is applicable to every version of MySQL in real time.
Content Introduction:
The original idea came from the blog:http://thenoyes.com/littlenoise/?p=117 of a foreign DBA.
The rationale is to record the current master-log name and POS of the SP record in the table, and then read the next master record to perform the stop Slave/change Master/start slave. Repeat with this loop.
The individual has improved his approach by adding the following features:
1. Master can set the weight value according to business traffic
2. Monitoring the operation of each master-slave
3. Each master can exit a multiple-master schema in real time
Specific operation process:
1. Create a table that holds individual master information
Copy Code code as follows:
Use MySQL;
CREATE TABLE ' Rotate_master ' (
' id ' int (one) not NULL auto_increment,
' Master_host ' varchar (255) DEFAULT NULL comment ' master address ',
' Master_port ' int (ten) unsigned DEFAULT NULL comment ' master Port ',
' Master_log_file ' varchar (255) DEFAULT NULL ' Master-log file at last stop ',
' Master_log_pos ' int (a) unsigned DEFAULT NULL comment ' Last Stop Master-log-pos ',
' is_slave_running ' varchar DEFAULT NULL comment ' master or subordinate exception ' at last stop
' In_use ' tinyint (1) DEFAULT ' 0 ' comment ' is currently synchronizing the data row ',
' Weight ' int (one) not NULL DEFAULT ' 1 ' comment ' The weight of the master, that is, how many time slices are repeated ',
' Repeated_times ' int (one) not NULL DEFAULT ' 0 ' comment ' The number of time slices that are currently being repeated ',
' Lastexecutetime ' timestamp not NULL DEFAULT current_timestamp on UPDATE current_timestamp comment ' Last execution time ',
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8
Add a new master:
Copy Code code as follows:
Insert INTO ' rotate_master '
(' Master_host ', ' master_port ', ' master_log_file ', ' master_log_pos ', ' in_use ', ' weight ')
Values
--Number 1th master, Weight = 1, and set to current master
(' 192.168.0.1 ', 3307, ' mysqlbinlog.000542 ', 4,1,1),
--No. 2nd Master, weight =2
(' 192.168.0.2 ', 3306, ' mysqlbinlog.000702 ', 64762429,0,2),
--No. 3rd Master, weight =5
(' 192.168.0.3 ', 3306, ' mysqlbinlog.000646 ', 22157422,0,5)
To manually adjust master to the current configuration item:
Copy Code code as follows:
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;
To create the Rotate master SP:
Note: The username and password used to connect to master in your code are: REPL/REPL, please modify it according to your circumstances.
Copy Code code 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 (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;
To create an event:
Run every 2 minutes, rotate_master (), the time slice size is 2 minutes
Copy Code code as follows:
DELIMITER $$
CREATE EVENT ' Rotate_master ' on SCHEDULE EVERY SECOND starts ' 2011-10-13 14:09:40 ' on completion not PRESERVE ENABLE D O call Mysql.rotate_master () $$
DELIMITER;
At this point, the multiple master replication has been completed.
Because the time slice length is 2 minutes.
Master1 after executing 1 * 2 minutes, stop slave, then change Master to Master2;
Master2 after executing 2 * 2 minutes, stop slave, then change Master to Master3;
Master3 after executing 5 * 2 minutes, stop slave, then change Master to Master2;
And in this cycle.
If you want to remove one master from a multiple master copy, you can set the weight of his configuration item to 0;
That is: Update rotate_master set weigh=0 where id = #ID #;
