Comment 2 for bug 1092593

Revision history for this message
Nickolay Ihalainen (ihanick) wrote : Re: crash resistant replication doesn't work correctly after change master or reset slave

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)