change master 'master1' to master_port=MYPORT_1, master_host='127.0.0.1', master_user='root'; start slave 'master1'; set default_master_connection = 'master1'; include/wait_for_slave_to_start.inc show slave 'master1' status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000001 286 mysqld-relay-bin-master1.000002 572 master-bin.000001 Yes Yes 0 0 286 876 None 0 No 0 No 0 0 1 show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000001 286 mysqld-relay-bin-master1.000002 572 master-bin.000001 Yes Yes 0 0 286 876 None 0 No 0 No 0 0 1 show all slaves status; Connection_name Slave_SQL_State Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath Retried_transactions Max_relay_log_size Executed_log_entries Slave_received_heartbeats Slave_heartbeat_period master1 Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000001 286 mysqld-relay-bin-master1.000002 572 master-bin.000001 Yes Yes 0 0 286 876 None 0 No 0 No 0 0 1 0 1073741824 6 0 60.000 drop database if exists db1; create database db1; use db1; create table t1 (i int auto_increment, f1 varchar(16), primary key pk (i,f1)) engine=MyISAM; insert into t1 (f1) values ('one'),('two'); select * from db1.t1; i f1 1 one 2 two # List of relay log files in the datadir mysqld-relay-bin-master1.000001 mysqld-relay-bin-master1.000002 mysqld-relay-bin-master1.index show relaylog events; Log_name Pos Event_type Server_id End_log_pos Info mysqld-relay-bin-master1.000001 4 Format_desc 3 246 Server version mysqld-relay-bin-master1.000001 246 Rotate 3 304 mysqld-relay-bin-master1.000002;pos=4 show relaylog events in 'mysqld-relay-bin-master1.000002'; Log_name Pos Event_type Server_id End_log_pos Info mysqld-relay-bin-master1.000002 4 Format_desc 3 246 Server version mysqld-relay-bin-master1.000002 246 Rotate 1 0 master-bin.000001;pos=4 mysqld-relay-bin-master1.000002 290 Format_desc 1 246 Server version mysqld-relay-bin-master1.000002 532 Binlog_checkpoint 1 286 master-bin.000001 mysqld-relay-bin-master1.000002 572 Query 1 375 drop database if exists db1 mysqld-relay-bin-master1.000002 661 Query 1 456 create database db1 mysqld-relay-bin-master1.000002 742 Query 1 609 use `db1`; create table t1 (i int auto_increment, f1 varchar(16), primary key pk (i,f1)) engine=MyISAM mysqld-relay-bin-master1.000002 895 Query 1 676 BEGIN mysqld-relay-bin-master1.000002 962 Intvar 1 704 INSERT_ID=1 mysqld-relay-bin-master1.000002 990 Query 1 808 use `db1`; insert into t1 (f1) values ('one'),('two') mysqld-relay-bin-master1.000002 1094 Query 1 876 COMMIT change master 'master1' to master_port=MYPORT_2, master_host='127.0.0.1', master_user='root'; ERROR HY000: This operation cannot be performed as you have a running slave 'master1'; run STOP SLAVE 'master1' first change master to master_port=MYPORT_2, master_host='127.0.0.1', master_user='root'; ERROR HY000: This operation cannot be performed as you have a running slave 'master1'; run STOP SLAVE 'master1' first change master 'master2' to master_port=MYPORT_1, master_host='127.0.0.1', master_user='root'; ERROR HY000: Connection 'master2' conflicts with existing connection 'master1' set default_master_connection = ''; change master to master_port=MYPORT_2, master_host='127.0.0.1', master_user='root'; start slave; include/wait_for_slave_to_start.inc show all slaves status; Connection_name Slave_SQL_State Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath Retried_transactions Max_relay_log_size Executed_log_entries Slave_received_heartbeats Slave_heartbeat_period Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_2 60 master-bin.000001 286 mysqld-relay-bin.000002 572 master-bin.000001 Yes Yes 0 0 286 868 None 0 No 0 No 0 0 2 0 1073741824 6 0 60.000 master1 Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000001 876 mysqld-relay-bin-master1.000002 1162 master-bin.000001 Yes Yes 0 0 876 1466 None 0 No 0 No 0 0 1 0 1073741824 13 0 60.000 insert into t1 (f1) values ('three'); drop database if exists db2; create database db2; use db2; create table t1 (pk int auto_increment primary key, f1 int) engine=InnoDB; begin; insert into t1 (f1) values (1),(2); select * from db1.t1; i f1 1 one 2 two 3 three select * from db2.t1; pk f1 commit; select * from db2.t1; pk f1 1 1 2 2 flush logs; purge binary logs to 'master-bin.000002'; show binary logs; Log_name File_size master-bin.000002 326 insert into t1 (f1) values ('four'); create table db1.t3 (f1 int) engine=InnoDB; show all slaves status; Connection_name Slave_SQL_State Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath Retried_transactions Max_relay_log_size Executed_log_entries Slave_received_heartbeats Slave_heartbeat_period Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_2 60 master-bin.000001 809 mysqld-relay-bin.000002 1095 master-bin.000001 Yes Yes 0 0 809 1391 None 0 No 0 No 0 0 2 0 1073741824 13 0 60.000 master1 Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000002 690 mysqld-relay-bin-master1.000004 976 master-bin.000002 Yes Yes 0 0 690 1324 None 0 No 0 No 0 0 1 0 1073741824 31 0 60.000 select * from db1.t1; i f1 1 one 2 two 3 three 4 four show relaylog events; Log_name Pos Event_type Server_id End_log_pos Info mysqld-relay-bin.000001 4 Format_desc 3 246 Server version mysqld-relay-bin.000001 246 Rotate 3 296 mysqld-relay-bin.000002;pos=4 show relaylog events in 'mysqld-relay-bin.000002'; Log_name Pos Event_type Server_id End_log_pos Info mysqld-relay-bin.000002 4 Format_desc 3 246 Server version mysqld-relay-bin.000002 246 Rotate 2 0 master-bin.000001;pos=4 mysqld-relay-bin.000002 290 Format_desc 2 246 Server version mysqld-relay-bin.000002 532 Binlog_checkpoint 2 286 master-bin.000001 mysqld-relay-bin.000002 572 Query 2 375 drop database if exists db2 mysqld-relay-bin.000002 661 Query 2 456 create database db2 mysqld-relay-bin.000002 742 Query 2 591 use `db2`; create table t1 (pk int auto_increment primary key, f1 int) engine=InnoDB mysqld-relay-bin.000002 877 Query 2 658 BEGIN mysqld-relay-bin.000002 944 Intvar 2 686 INSERT_ID=1 mysqld-relay-bin.000002 972 Query 2 782 use `db2`; insert into t1 (f1) values (1),(2) mysqld-relay-bin.000002 1068 Xid 2 809 COMMIT /* xid= */ stop slave io_thread; show status like 'Slave_running'; Variable_name Value Slave_running OFF set default_master_connection = 'master1'; show status like 'Slave_running'; Variable_name Value Slave_running ON drop database db1; drop database db2; include/reset_master_slave.inc drop database db1; include/reset_master_slave.inc drop database db2; include/reset_master_slave.inc