Implement multi-master to single-slave replication by periodically saving state and changing masters, and do it all in SQL.*<\/p>\n
Just a proof of concept. I suggest you add some error checking (like, don’t make the switch if the slave isn’t running).<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n 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.<\/p>\n CREATE PROCEDURE rotate_master() STOP SLAVE;<\/p>\n -- fetch and store current position -- fetch next host 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.<\/p>\n 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[4],"tags":[],"class_list":["post-117","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-1T","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/117","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=117"}],"version-history":[{"count":4,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/117\/revisions"}],"predecessor-version":[{"id":121,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/117\/revisions\/121"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=117"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=117"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=117"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}USE mysql;
\nDROP TABLE IF EXISTS rotate_master;
\nCREATE TABLE rotate_master (
\n id int auto_increment primary key,
\n master_host varchar(255),
\n master_port int unsigned,
\n master_log_file varchar(255),
\n master_log_pos int unsigned,
\n in_use boolean DEFAULT 0
\n);<\/code><\/p>\nINSERT INTO rotate_master VALUES (NULL, 'localhost', 3306, 'bin.000001', 0, 1);
\nINSERT INTO rotate_master VALUES (NULL, 'localhost', 3308, 'bin.000001', 0, 0);<\/code><\/p>\nDROP PROCEDURE IF EXISTS rotate_master;
\nDELIMITER \/\/<\/p>\n
\nBEGIN
\n DECLARE _info text;
\n DECLARE _master_file varchar(255);
\n DECLARE _master_pos int unsigned;
\n DECLARE _master_host varchar(255);
\n DECLARE _master_port int unsigned;
\n DECLARE _id int;<\/p>\n
\n SELECT LOAD_FILE(@@relay_log_info_file) INTO _info;
\n SELECT
\n SUBSTRING_INDEX(SUBSTRING_INDEX(_info, '\\n', 3), '\\n', -1),
\n SUBSTRING_INDEX(SUBSTRING_INDEX(_info, '\\n', 4), '\\n', -1)
\n INTO _master_file, _master_pos;
\n UPDATE mysql.rotate_master SET master_log_file = _master_file, master_log_pos = _master_pos, id = LAST_INSERT_ID(id) WHERE in_use = 1;<\/p>\n
\n SELECT
\n id,
\n master_host,
\n master_port,
\n master_log_file,
\n master_log_pos
\n INTO _id, _master_host, _master_port, _master_file, _master_pos
\n FROM rotate_master
\n ORDER BY id <= LAST_INSERT_ID(), id LIMIT 1;\n\n -- advance to next host\n SET @sql := CONCAT(\n 'CHANGE MASTER TO master_host=', QUOTE(_master_host), \n ', master_port=', _master_port, \n ', master_log_file=', QUOTE(_master_file), \n ', master_log_pos=', _master_pos);\n\n PREPARE myStmt FROM @sql;\n EXECUTE myStmt;\n\n -- mark host as changed\n UPDATE mysql.rotate_master SET in_use = 0 WHERE in_use = 1;\n UPDATE mysql.rotate_master SET in_use = 1 WHERE id = _id;\n\n START SLAVE;\nEND\/\/\n\nDELIMITER ;<\/code><\/p>\nDROP EVENT IF EXISTS rotate_master;
\nCREATE EVENT rotate_master
\n ON SCHEDULE EVERY 10 SECOND
\n DO CALL mysql.rotate_master();<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"