My ‘master’ and the ‘replication’ server both restarted yesterday because of power outage.
Now after the things coming back to normal, My ‘replication’ server is not able to connect to the master and when i check the status on it, I see this error :-
I also tried to change the values to that of master in these 2 files ( relay-log.info & master.info ) on the replication server, as i could see some discrepancy in it…but still no luck
I also copied the bin file (newcrmdb1-bin.000027) from the master server to the replication server , But still the same.
After sudden reboot mysql rolled back last transactions in binary logs however slave already incremented its binary position so after master is up slave is not able to get correct binary position. To resolve this issue you need to point slave to new binary file created after the server reboot and mysql restart. The same issue happened to be just 2 days back. After setting new binary position I skipped few entries on slave which were updated on slave and rolled back in binary position.
add sync_binlog in your master my.cnf file to updated binary files during crashes.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.10.100.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000462
Read_Master_Log_Pos: 384546492
Relay_Log_File: mysql-relay-bin.001388
Relay_Log_Pos: 384546638
Relay_Master_Log_File: mysql-bin.000462
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB: db_live
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 384546492
Relay_Log_Space: 384546837
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Client requested master to start replication from impossible position; the first event ‘mysql-bin.000462’ at 384546492, the last event read from ‘./mysql-bin.000462’ at 4, the last byte read from ‘./mysql-bin.000462’ at 4.’
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
Change the binary file position on slave to new binary file mysql-bin.000463 in above case
change master to MASTER_LOG_FILE=‘mysql-bin.000463’, Master_Log_Pos=4;
The next binary file would be newcrmdb1-bin.000028 after mysql restart, so change master to master_log_file=‘newcrmdb1-bin.000028’, master_log_pos=4.
I am sure this will work if you havent done much R&D on slave.
According to your show slave status output your slave was on binary file newcrmdb1-bin.000027 before the reboot, after the reboot master database incremented binary file to newcrmdb1-bin.000028 and a new starting position 4, so you need to start replication from this new position, so execute change master on slave server so that slave start replicating from new position
On slave server
slave> change master to MASTER_LOG_FILE=‘newcrmdb1-bin.000028’, Master_Log_Pos=4;
slave> start slave;
4 is first binary position in mysql binary log file, if you process log file using mysqlbinlog utility you can see the output.
$ mysqlbinlog mysql-bin.000004
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/; [COLOR=#FF0000]# at 4 #130325 16:20:55 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.30-log created 130325 16:20:55 at startup
Warning: this binlog is either in use or was not closed properly.
and that is what lead to the confusion