We are using MySQL replication to synchronise databases in master and slave servers.(If you want to know how to set up mysql mirroring please read
How To Set Up Master Slave Replication in MySQL)
Due to power failure, two servers got rebooted and we identified the replication process has not started. The mysqld.log (/var/log/mysqld.log) showed the following error:
[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
We tried to identify the updates missing in the slave mysql server but due to lack of information it was not possible. The only information we got from the client's log file was the location (binary file and its position) that it tried to read from the master side. It was observed the binary log file in the master server side was somewhat newer to the location indicated in the slave server side.
Ex: Client was tried to read up to log mysqld-bin.00045 and position 234994 but the serve had the newer binary log (say mysqld-bin.00048).
After referring the several web resources, we understood these positions at the slave and master can be observed by following mysql commands;
Client side status:
mysql> show slave status;
It displays the status of the slave and importantly parameters
Master_Log_File | Read_Master_Log_Pos
These are the positions client tries to read from master.
In the master side using the
show master status command give the current location of the master. If the locations (positions) are not the same, the synchronization fails.
What to do next?
If you can synchronise two databases manually, do it (I did it with tar zip command).
Check the position of the master with show master status and set the
same position parameters at the slave server.
Use following commands to set the master server parameters at the slave server;
mysql>slave stop;
mysql>CHANGE MASTER TO MASTER_HOST='master-database.example.com', MASTER_USER='username',
MASTER_PASSWORD='password', MASTER_LOG_FILE='new_binary_log_file', MASTER_LOG_POS=new_position;
mysql>slave start;
Check the status of the replication referring /var/log/mysqld.log and show slave status command. Check Slave_IO_Running | Slave_SQL_Running parameters of the output of the show slave status. Both these parameters should be 'Yes'.
mysql>slave stop;
mysql>CHANGE MASTER TO MASTER_HOST='master-database.example.com', MASTER_USER='username',
MASTER_PASSWORD='password', MASTER_LOG_FILE='new_binary_log_file', MASTER_LOG_POS=new_position;
mysql>slave start;
Check the status of the replication referring
/var/log/mysqld.log and show slave status command. Check
Slave_IO_Running | Slave_SQL_Running parameters of the output of the
show slave status. Both these parameters should be 'Yes'.
I found the following references very useful in the error detection/correction process.
http://www.brianklug.com/w/page/15052161/MySQL%20Error%3A%20Client%20requested%20master%20to%20start%20replication%20from%20impossible%20position
http://dev.kafol.net/2011/09/mysql-error-1236-client-requested.html
