Thursday, August 16, 2007

mysql replication

If mysql replication breaks with:

070816 13:03:17 Slave I/O thread: connected to master 'repl@mydns01.securepod.com:3306', replication started in log 'mydns01-bin.025' at position 20533567
070816 13:03:17 Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236)
070816 13:03:17 Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log'
070816 13:03:17 Slave I/O thread exiting, read up to log 'mydns01-bin.025', position 20533567

you can check the binlog that it last read up to (mydns01-bin.025), using mysqlbinlog, and search for log position it read up to (in this case 20533567). In this case, the last entry in mydns01-bin.025 is 20533147

mysqlbinlog mydns01-bin.025 | tail -10

#070815 16:01:10 server id 1 log_pos 20533147 Query thread_id=20094 exec_time=0 error_code=0
SET TIMESTAMP=1187211670;

INSERT INTO ZoneRecord
( ZoneRecord.zone, ZoneRecord.name, ZoneRecord.type, ZoneRecord.data, ZoneRecord.aux, ZoneRecord.ttl, ZoneRecord.active, ZoneRecord.categoryid )
VALUES ( '167857' , '' , 'NS' , 'ns1.securepod.com.' , 'None' , '14400' , '1' , '1' );

The next bin log mydns01-bin.026 starts from a new position (this position actually corresponds to the file size of the binlog). So: you can run the query that the last entry has in bin.025 and see if it exists, and run the query in bin.026 and see if it exists. If the first does, but the second does not, then you can change the bin log start position from the first position in bin.026, which is as easy as changing the master.info on the slave(s) to the new value and restarting mysql (or you could do it on the mysql command line if you can be bothered)