The error
Slave_IO_Running: No
Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'binlog.000202' at 307396531, the last event read from 'binlog.000202' at 4, the last byte read from 'binlog.000202' at 4.', Internal MariaDB error code: 1236It appear there was some issue on the server hosting the MariaDB and the server administrator opted to reboot. When the server restarted, replication stopped working.
Solution
On the Master
Firstly, on master, confirm that it is the end of the binary log. Browse to the directory
cd /var/lib/mysql (location will vary depending on your installation)
mysqlbinlog --base64-output=decode-rows --verbose --verbose --start-position=307396531 binlog.000202I also peaked on the next log binlog.000203
mysqlbinlog --base64-output=decode-rows --verbose --verbose --start-position=0 binlog.000203On the Slave
Stop the slaveAll seems well, so I proceeded change the master log and master log position
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000203', MASTER_LOG_POS=4;
Start the slaveOnce done, check the Slave Status and all should be well.
show slave status\G;Please note that your binlog is e.g. mysql-bin.001 advance to the next log file mysql-bin-002
Why did this happen? According to Muhammad
I foresee master server crashed or rebooted and hence binary log events not synchronized on disk. This usually happens when sync_binlog != 1 on the master. You can investigate it as inspecting binary log contents as below:user yogesh77 on the Percona Forum expanded this
"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."How to prevent
The suggestion is this little command.
On the master
SET GLOBAL sync_binlog=1;Add to my.cnf or server.cnf for MariaDB to make permanent.
sync_binlog=1;Also, try shutting down the server gracefully.
However there is a issue with sync_binlog. It appears enabling on certain file systems results in a significant performance hit. That discussion will have to be for another blog. Some discussions seem to indicate that it is getting better
n.b. If you have MYSQL replication set up, monitor it and have alerts emailed to you. Replication is a nice feature but must be monitored.
References
