MySQL multi master replication storage process

Implement Multi master Replication Using Stored Procedures
The method is simple. The principle is: slave uses a table to save the masterfile and position of each master, and then uses the scheduler to traverse and change to different masters at intervals.
Note the following:

* The MySQL scheduler is disabled by default and needs to be manually enabled for dynamic modification.
* Stored procedures read relay-log.info files, so you first need to grant permissions on relay-log.info files.
* Before calling the stored procedure, you must set up the replication relationship between slave and one of the master nodes.

 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 0  10   ); 11   12   13 Insert into rotate_master values (null, '  X. x  ' , 7777 , '  Mysql-bin.000003  ' , 351 , 1  );  14 Insert into rotate_master values (null, ' X. x  ' , 8888 , '  Mysql-bin.000003  ' , 618 , 0  );  15   16 Change master to master_host = '  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   Begin  24   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 Position 34   Select load_file (@ relay_log_info_file) into _ INFO;  35   Select  36 Substring_index (_ info, '  \ N  ' , 3 ), '  \ N  ' ,- 1  ),  37 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 host  42   Select  43   ID,  44   Master_host,  45   Master_port,  46   Master_log_file,  47   Master_log_pos  48  Into _ id, _ master_host, _ master_port, _ master_file, _ master_pos  49   From rotate_master  50 Order by ID <= last_insert_id (), Id limit 1  ;  51   52 -- Advance to next host  53 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  Changed  66 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_master  76 On schedule every 10  Second  77 Do call mysql. rotate_master ();

 

 

Reference: http://thenoyes.com/littlenoise? P = 117

Another method is to implement multi-master replication:
Master1 has a database of db1 and a database of blackhole that maintains a replication relationship with DB2 on master2. the database of blackhole only records BINLOG.
Master2 has a DB2 database and a blackhole database that maintains a replication relationship with db1 on master1. the blackhole database only records BINLOG.
Such a Server Load balancer can change the master to master1 or master2 and have two master nodes at the same time.
Reference: http://stackoverflow.com/questions/1576603/is-it-possible-to-do-n-master-1-slave-replication-with-mysql