I have 3 Virtual machines:
c1:
[mysqld]
server_id=1
innodb_file_per_table
log-bin=mysql1_binlog
c2: slave of c1, donor for c3, future master of c3
[mysqld]
server_id=2
innodb_file_per_table
log-bin=mysql2_binlog
relay-log=mysql2_relaylog
innodb_recovery_update_relay_log=1
innodb_flush_log_at_trx_commit=0
log_slave_updates
c3: new server,
[mysqld]
server_id=3
innodb_file_per_table
log-bin=mysql3_binlog
relay-log=mysql3_relaylog
innodb_recovery_update_relay_log=1
innodb_flush_log_at_trx_commit=0
Steps to reproduce:
1) setup c1 with single innodb table
2) setup replication from c1(master) to c2(slave)
3) on c1 insert rows:
start while true ; do mysql test -e 'insert x values(1000);' ; done
4) c2, send kill -9 to mysqld_safe & mysqld
5) copy /var/lib/mysql from c2 to c3
6) close connections via tcp 3306 from c3 to c1
7) start mysql on c2
8) start mysql on c3
9) on c3: stop slave; change master (not working) ; reset slave;change master; start slave;
9) check on c3 if replication is up&running
10) on c3 kill -9 on mysqld pid
11) replication is not working now on c3, because it wont to use relay log names from c2.
SHOW SLAVE STATUS\G shows: Master_Log_File: mysql2_binlog.000015 Read_Master_Log_Pos: 211757 Relay_Log_File: mysql2_relaylog.000008 Relay_Log_Pos: 839409 Relay_Master_Log_File: mysql1_binlog.000002
InnoDB: Warning: innodb_overwrite_relay_log_info is enabled. Updates by other storage engines may not be synchronized.
InnoDB: relay-log.info is detected.
InnoDB: relay log: position 842094, file name ./mysql2_relaylog.000008
InnoDB: master log: position 2341964, file name mysql1_binlog.000002
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 2339279, file name mysql1_binlog.000002
InnoDB: and relay log file
InnoDB: position 839409, file name ./mysql2_relaylog.000008
InnoDB: Last MySQL binlog file position 0 797205, file name ./mysql2_binlog.000014
Master_Log_File: mysql1_binlog.000002 Read_Master_Log_Pos: 2341964 Relay_Log_File: mysql2_relaylog.000008 Relay_Log_Pos: 839409 Relay_Master_Log_File: mysql1_binlog.000002 Slave_IO_Running: No Slave_SQL_Running: No
Can't run change master:
change master to master_host='192.168.2.148', master_user='repl', master_password='pass',master_log_file='mysql2_binlog.000014', master_log_pos=797205;
130412 21:40:26 [ERROR] Failed to open the relay log './mysql2_relaylog.000008' (relay_log_pos 839409)
reset slave;
now change master is working
kill -9 to mysqld on new server
130412 21:42:05 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Warning: innodb_overwrite_relay_log_info is enabled. Updates by other storage engines may not be synchronized.
InnoDB: relay-log.info is detected.
InnoDB: relay log: position 211907, file name ./mysql3_relaylog.000003
InnoDB: master log: position 211757, file name mysql2_binlog.000015
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 2339279, file name mysql1_binlog.000002
InnoDB: and relay log file
InnoDB: position 839409, file name ./mysql2_relaylog.000008
InnoDB: Last MySQL binlog file position 0 797205, file name ./mysql2_binlog.000014
130412 21:42:05 InnoDB: Waiting for the background threads to start
130412 21:42:06 Percona XtraDB (http://www.percona.com) 5.5.30-rel30.2 started; log sequence number 8346274
InnoDB: relay-log.info was overwritten.
130412 21:42:06 [Note] Recovering after a crash using mysql3_binlog
130412 21:42:06 [Note] Starting crash recovery...
130412 21:42:06 [Note] Crash recovery finished.
130412 21:42:06 [ERROR] Failed to open the relay log './mysql2_relaylog.000008' (relay_log_pos 839409)
I have 3 Virtual machines: file_per_ table mysql1_ binlog
c1:
[mysqld]
server_id=1
innodb_
log-bin=
c2: slave of c1, donor for c3, future master of c3 file_per_ table mysql2_ binlog mysql2_ relaylog recovery_ update_ relay_log= 1 flush_log_ at_trx_ commit= 0
[mysqld]
server_id=2
innodb_
log-bin=
relay-log=
innodb_
innodb_
log_slave_updates
c3: new server, file_per_ table mysql3_ binlog mysql3_ relaylog recovery_ update_ relay_log= 1 flush_log_ at_trx_ commit= 0
[mysqld]
server_id=3
innodb_
log-bin=
relay-log=
innodb_
innodb_
Steps to reproduce:
Master_ Log_File: mysql2_ binlog. 000015
Read_ Master_ Log_Pos: 211757
Relay_ Log_File: mysql2_ relaylog. 000008
Relay_ Log_Pos: 839409
Relay_ Master_ Log_File: mysql1_ binlog. 000002
1) setup c1 with single innodb table
2) setup replication from c1(master) to c2(slave)
3) on c1 insert rows:
start while true ; do mysql test -e 'insert x values(1000);' ; done
4) c2, send kill -9 to mysqld_safe & mysqld
5) copy /var/lib/mysql from c2 to c3
6) close connections via tcp 3306 from c3 to c1
7) start mysql on c2
8) start mysql on c3
9) on c3: stop slave; change master (not working) ; reset slave;change master; start slave;
9) check on c3 if replication is up&running
10) on c3 kill -9 on mysqld pid
11) replication is not working now on c3, because it wont to use relay log names from c2.
SHOW SLAVE STATUS\G shows:
InnoDB: Warning: innodb_ overwrite_ relay_log_ info is enabled. Updates by other storage engines may not be synchronized. relaylog. 000008 binlog. 000002
InnoDB: relay-log.info is detected.
InnoDB: relay log: position 842094, file name ./mysql2_
InnoDB: master log: position 2341964, file name mysql1_
InnoDB: Apply batch completed binlog. 000002 relaylog. 000008 binlog. 000014
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 2339279, file name mysql1_
InnoDB: and relay log file
InnoDB: position 839409, file name ./mysql2_
InnoDB: Last MySQL binlog file position 0 797205, file name ./mysql2_
Can't run change master: host='192. 168.2.148' , master_user='repl', master_ password= 'pass', master_ log_file= 'mysql2_ binlog. 000014' , master_ log_pos= 797205; relaylog. 000008' (relay_log_pos 839409)
change master to master_
130412 21:40:26 [ERROR] Failed to open the relay log './mysql2_
reset slave;
now change master is working
kill -9 to mysqld on new server
130412 21:42:05 [Note] Plugin 'FEDERATED' is disabled. overwrite_ relay_log_ info is enabled. Updates by other storage engines may not be synchronized. relaylog. 000003 binlog. 000015
InnoDB: Warning: innodb_
InnoDB: relay-log.info is detected.
InnoDB: relay log: position 211907, file name ./mysql3_
InnoDB: master log: position 211757, file name mysql2_
InnoDB: Apply batch completed binlog. 000002 relaylog. 000008 binlog. 000014 www.percona. com) 5.5.30-rel30.2 started; log sequence number 8346274 relaylog. 000008' (relay_log_pos 839409)
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 2339279, file name mysql1_
InnoDB: and relay log file
InnoDB: position 839409, file name ./mysql2_
InnoDB: Last MySQL binlog file position 0 797205, file name ./mysql2_
130412 21:42:05 InnoDB: Waiting for the background threads to start
130412 21:42:06 Percona XtraDB (http://
InnoDB: relay-log.info was overwritten.
130412 21:42:06 [Note] Recovering after a crash using mysql3_binlog
130412 21:42:06 [Note] Starting crash recovery...
130412 21:42:06 [Note] Crash recovery finished.
130412 21:42:06 [ERROR] Failed to open the relay log './mysql2_