Main database/Slave database failed because of power outage

[dhijrwn]

After booting all servers, We got corrupted tables like the call_log and the vicidial_xfer_log on the main database but I fixed it using the repair table command.

but the problem is the slave, I'm getting this error when I executed this command show slave status\G;

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event; the first event 'mysql-bin.001718' at 302723624, the last event read from 'mysql-bin.001720' at 608178046, the last byte read from 'mysql-bin.001720' at 608178065.'

How can I resync or fix slave server without formatting it and setting up a slave server again.
I tried to reset/stop/start/restart the server and still doesn't fix it.

[williamconley]

With this event we will first attempt to reset the replication binary logs (purge in case of damage, reload from the master) and sometimes this works because the only thing broken is the "in process" binary log was damaged, so reloading the replication log from the master at the last point of transfer works.

Often, however, the master binary is also damaged, but since that would be simply an improperly closed binary log at the moment of power outage, the db will jumpt to the next binary log on startup. So you can restart replication on the next binary log and position "4" (which is where they all start). No transactions lost, just that broken one at the end during the crash, which was probably purged when the tables were repaired anyway.

We build our replication servers with this information directly in the local profile during login, so the technician can just copy/paste those options one at a time until one of them works. Just be sure to do it before the master server's binary logs expire (and get deleted), because that will require re-replicating from a mysqldump.

SHOW SLAVE STATUS\G will give you the present values (except the slave password, of course) to fill these in.

Code: Select all

1. Attempt Simple Restart without changing location (Step one: in case the replication server crashed and corrupted the 'in transit' data set)

mysql -pxxxx -e "SHOW SLAVE STATUS\G"; mysql -pxxxx -e "STop SLAVE"; mysql -pxxxx -e "RESET SLAVE"; mysql -pxxxx -e "CHANGE MASTER TO MASTER_HOST='x.x.x.x',MASTER_USER='xxxx', MASTER_PASSWORD='xxxx', MASTER_LOG_FILE='mysql-bin.00xxxx', MASTER_LOG_POS=xxxx";  mysql -pxxxx -e "STaRT SLAVE"; sleep 1; mysql -pxxxx -e "SHOW SLAVE STATUS\G";

2. If that fails, and it appears that the error can be skipped or the query can be manually executed, you can skip a single error with this method:

mysql -pxxxx -e "SHOW SLAVE STATUS\G"; mysql -pxxxx -e "STop SLAVE"; mysql -pxxxx -e "SET GLOBAL sql_slave_skip_counter = 1;"; mysql -pxxxx -e "STaRT SLAVE"; sleep 1; mysql -pxxxx -e "SHOW SLAVE STATUS\G";


3. If that fails: Go to the next master binary. (Step three: broken master binary log, crashes at the same spot every time, usually due to power outage)
* Change the Master Log File to the next number
* change the Master Log Position to 4