mysql multi master replication 之 預存程序

使用預存程序實現Multi Master replication
方法很簡單，原理為：Slave使用一張表來儲存每個Master的MasterFile和Position，然後使用調度器每隔一段時間遍曆Change 到不同的Master.
有以下需要注意的地方：

* Mysql的調度器預設是關閉的，需要手動開啟，可動態修改。
* 預存程序會讀relay-log.info檔案，所以首先需要在relay-log.info檔案上賦權。
* 調用預存程序之前需要先搭建好Slave到其中一個Master的複製關係。

 1 USE mysql; 2 DROP TABLE IF EXISTS rotate_master; 3 CREATE TABLE rotate_master ( 4 id int auto_increment primary key, 5 master_host varchar(255), 6 master_port int unsigned, 7 master_log_file varchar(255), 8 master_log_pos int unsigned, 9 in_use boolean DEFAULT 010 );11 12 13 INSERT INTO rotate_master VALUES (NULL, 'x.x.x.x', 7777, 'mysql-bin.000003', 351, 1);14 INSERT INTO rotate_master VALUES (NULL, 'x.x.x.x', 8888, 'mysql-bin.000003', 618, 0);15 16 CHANGE MASTER TO MASTER_HOST='x.x.x.x', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_PORT=7777, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=351;17 18 19 DROP PROCEDURE IF EXISTS rotate_master;20 DELIMITER //21 22 CREATE PROCEDURE rotate_master()23 BEGIN24 DECLARE _info text;25 DECLARE _master_file varchar(255);26 DECLARE _master_pos int unsigned;27 DECLARE _master_host varchar(255);28 DECLARE _master_port int unsigned;29 DECLARE _id int;30 31 STOP SLAVE;32 33 -- fetch and store current position34 SELECT LOAD_FILE(@@relay_log_info_file) INTO _info;35 SELECT36 SUBSTRING_INDEX(SUBSTRING_INDEX(_info, '\n', 3), '\n', -1),37 SUBSTRING_INDEX(SUBSTRING_INDEX(_info, '\n', 4), '\n', -1)38 INTO _master_file, _master_pos;39 UPDATE mysql.rotate_master SET master_log_file = _master_file, master_log_pos = _master_pos, id = LAST_INSERT_ID(id) WHERE in_use = 1;40 41 -- fetch next host42 SELECT43 id,44 master_host,45 master_port,46 master_log_file,47 master_log_pos48 INTO _id, _master_host, _master_port, _master_file, _master_pos49 FROM rotate_master50 ORDER BY id <= LAST_INSERT_ID(), id LIMIT 1;51 52 -- advance to next host53 SET @sql := CONCAT(54 'CHANGE MASTER TO master_host=', QUOTE(_master_host),55 ', master_port=', _master_port,56 ', master_log_file=', QUOTE(_master_file),57 ', master_log_pos=', _master_pos)58 ', master_user="repl"',59 ', master_password="repl"'60 ;61 62 PREPARE myStmt FROM @sql;63 EXECUTE myStmt;64 65 -- mark host as changed66 UPDATE mysql.rotate_master SET in_use = 0 WHERE in_use = 1;67 UPDATE mysql.rotate_master SET in_use = 1 WHERE id = _id;68 69 START SLAVE;70 END//71 72 DELIMITER ;73 74 DROP EVENT IF EXISTS rotate_master;75 CREATE EVENT rotate_master76 ON SCHEDULE EVERY 10 SECOND77 DO CALL mysql.rotate_master();

 

 

參考：http://thenoyes.com/littlenoise/?p=117

另外還有一種方法也是實現多主複製：
Master1 有一個DB1的庫，一個與Master2上DB2保持複製關係的Blackhole的庫，Blackhole的庫只記錄Binlog。
Master2 有一個DB2的庫，一個與Master1上DB1保持複製關係的Blackhole的庫, Blackhole的庫只記錄Binlog。
這樣一個Slave，Change Master到Master1或者Master2都能同時擁有兩個Master的資料。
參考： http://stackoverflow.com/questions/1576603/is-it-possible-to-do-n-master-1-slave-replication-with-mysql