Implement multi-master to single-slave replication by periodically saving state and changing masters, and do it all in SQL.*
Just a proof of concept. I suggest you add some error checking (like, don’t make the switch if the slave isn’t running).
Remember that all this stuff goes on the SLAVE. If you absent-mindedly log in to the master, like I did a few times, you might wonder why your relay_log.info file isn’t read.
Set up a table to keep track of each master file. Mine use the same user name and password for all hosts; you can add those fields and adjust the procedure accordingly if you like.
USE mysql;
DROP TABLE IF EXISTS rotate_master;
CREATE TABLE rotate_master (
id int auto_increment primary key,
master_host varchar(255),
master_port int unsigned,
master_log_file varchar(255),
master_log_pos int unsigned,
in_use boolean DEFAULT 0
);
Insert the list of masters. The one that is currently master should have `in_use` set to 1; all the others should be 0. The order you insert them is the order they will rotate.
INSERT INTO rotate_master VALUES (NULL, 'localhost', 3306, 'bin.000001', 0, 1);
INSERT INTO rotate_master VALUES (NULL, 'localhost', 3308, 'bin.000001', 0, 0);
Then, a procedure to grab the current position (by reading from the relay_log.info file, since the slave position is not otherwise exposed in any accessible manner), record it in the above table, and change masters to the next one in the list.
DROP PROCEDURE IF EXISTS rotate_master;
DELIMITER //
CREATE 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 _id int;
STOP SLAVE;
-- fetch and store current position
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) WHERE in_use = 1;
-- fetch next host
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;
-- advance to next host
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);
PREPARE myStmt FROM @sql;
EXECUTE myStmt;
-- mark host as changed
UPDATE mysql.rotate_master SET in_use = 0 WHERE in_use = 1;
UPDATE mysql.rotate_master SET in_use = 1 WHERE id = _id;
START SLAVE;
END//
DELIMITER ;
Finally, schedule it to rotate. I used 10 seconds just to play with; I imagine a few minutes would be more practical. *If you don't have 5.1 or later, you'll have to schedule this part externally, with cron or task scheduler.
DROP EVENT IF EXISTS rotate_master;
CREATE EVENT rotate_master
ON SCHEDULE EVERY 10 SECOND
DO CALL mysql.rotate_master();