Backtrack:  
 
by lunarg on July 6th 2012, at 14:06

Two possible occurences may happen related to this error 1236: either you get the error message like so:

Got fatal error 1236: 'Client requested master to start
 replication from impossible position' from master when 
 reading data from binary log

Or, no error is given and the Slave_IO_Running is returning no while the Slave_SQL_Running returns yes.

If this occurs, it may be because the master went down unexpectedly (and has since rebooted). Because of this, the slave has not received the command to switch to the next binlog file and is stuck at the previous one.

In other cases, it's possible that no error is displayed, but that the Slave_IO_Running still says no. In this case, the master provided the slave with a new master_log_pos, but went down before it could write it to disk (usually because journaling filesystems do a rollback of uncommitted transactions). In this case, the master_log_pos at the slave is higher than the master_log_pos of that binlog file. With the master back up, it will have started a new binlog, but the slave is still waiting on the previous binlog at that particular master_log_pos (which will never exist because the master is working on the next master_log_pos).

Resolving the issue

In both cases, you can fix this by manually moving the master_log_file on the slave to the next binlog, and resetting the master_log_pos to the start of that file.

On the master, look up the name of the next binlog. This is usually the same file as used on the slave, but with the current number +1. You can find the name of the binlog:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000712 |   815929 | database     |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On the slave you can now stop the current slave, reset the position and file, and restart the slave.

Caution
Note that the binlog file active on the master is not necessarily the file to use. If the slave is down for longer periods of time, the master can be several files ahead of the slave. You need to choose the next binlog (the one right after the slave is currently on), and not the binlog the master is currently on!

For example, consider this:
  • binlog on the master: mysql-bin.000712
  • binlog on the slave: mysql-bin.000708
Then the file to use is mysql-bin.000709 (and not mysql-bin.000712).

First, stop the slave (as you can't change the position and binlog when the slave (Slave_SQL_Running) is running.

mysql> stop slave;

Next, change the position and binlog to the next file

mysql> change master to master_log_pos = 0, master_log_file = 'mysql-bin.XXXXXX';

Substitute XXXXXX with the next log available, usually the one currently in use on the master. But read the notice above and double-check to make sure it's the right one!

Then start the slave:

mysql> start slave;

If done correctly, the slave will continue to sync and the remaining changes of the master will gradually be updated on the slave. Check to see the slave is running properly:

mysql> show slave status \G
             Slave_IO_State: Waiting for master to send event
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
             Until_Log_File:
              Until_Log_Pos: 0
      Seconds_Behind_Master: 0

Note that you will get a lot more info, but I've limited the output to the essential. See that Slave_IO_Running and Slave_SQL_Running are both set to yes.

 
 
« August 2020»
SunMonTueWedThuFriSat
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
 
Links
 
Quote
« Most people tend to avoid true conflict. Ironically this breeds more conflict. »