Sources:

  1. If replication stops due to some error you will see it in SHOW SLAVE STATUS\G output in “Last_Errno” and “Last_Error” columns.
  2. If error message contains bin log name and position (like for example `…at master log mysql-bin.002697, end_log_pos 90585871…` you can use SHOW BINLOG EVENTS command:
    • show binlog events in 'mysql-bin.002697';
    • you will see output in table format with columns – log_name, pos, event_type, server_id, end_log_pos, info
    • only problem is error message shows “end_log_pos” but command “show binlog events xxxx from yyyy” works with “pos” and if you specify “pos” value randomly it will most likely not work
    • so in such case you will have to list whole binlog to find problematic command – WARNING! this can be very heavy query !!!
    • WARINING – according to some sources (http://code.openark.org/blog/mysql/on-show-binlogrelaylog-events) this command locks binary logs on master so by using it you can cause problems on master
    • if you use ROW format, output will be significantly less informative because you will not see statements
    • OS tool `mysqlbinlog` gives slightly better output but even this tool does not show statement if you use ROW format
    • sudo mysqlbinlog -u root -p /var/log/mysql/mysql-bin.xxxxxx > mysql-bin.xxxxxx.txt
  3. Restart of replication usually does not help. So you can recreate replica
    • by cloning it from some other replica which does not have this error
    • by making new base backup from master
  4. But sometimes you cannot stop replica and recreate it. If this is a case and statement which fails in not critical you can skip it;
    • stop slave;
    • set global sql_slave_skip_counter=1;
    • start slave;
    • show slave status\G
      • if error was skipped and replication runs you can set sql_slave_skip_counter back to zero
      • if you get another errors you should really think about recreating the replica