A Little Noise

August 25, 2010

Multi-master replication

Filed under: MySQL — snoyes @ 9:35 am

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();

7 Comments »

  1. Very cool! Even though not strictly necessary, you could put the body of rotate_master directly into the event body.

    Comment by Mats Kindahl — August 26, 2010 @ 8:08 am

  2. — 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;

    should it be
    SELECT xxxx INTO yyyy
    FROM rotate_master
    WHERE id<LAST_INSERT_ID() ORDER BY id LIMIT 1;

    Comment by huarong — September 10, 2010 @ 1:08 am

  3. ok. I am wrong.
    But em, could you explain how ” order by id <=LAST_INSERT_ID(), id LIMIT 1" works?

    Comment by huarong — September 10, 2010 @ 1:26 am

  4. @huarong: I want to list all the rows in the table, ordered by id, but rotate the result set so that we start with the one that comes after the current entry.

    So, if my entries are (1), (2), (3), (4), and the current master is (2), I want the result (3), (4), (1), (2), and then I’ll take the first one from that list.

    It’s not enough to say “WHERE id < LAST_INSERT_ID()", because then what do I do when my current master is (4)? Somehow I have to start over at 1 again.

    Comment by snoyes — September 10, 2010 @ 8:09 am

  5. It’s a good idea,

    did you mean each master have different application and no intersections between them.

    So what will did if one record insert in M1 then update in M2, and the Slave get update statement before insert?

    Comment by mohammad Lahlouh — September 21, 2010 @ 2:26 am

  6. @mohammad: You risk wildly inconsistent data if the different masters edit the same tables. You could probably get away with it if you set up auto_increment_increment and auto_increment_offset as if you were doing master-master replication, and were careful that each master only modified its own rows (add a WHERE id % numServers = @@server_id or some such to every DML statement).

    Comment by snoyes — September 21, 2010 @ 9:30 am

  7. has anyone really used this approach in real world setup? i’m actually planning on implementing this type of setup for one of our clients…

    Comment by harryperales — October 8, 2012 @ 12:33 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress