To fix a corruption problem in a replication master database, you can restore the backup, taking care not to propagate unnecessary SQL operations to the slave servers:
Shut down the master database and then use, for example, the
copy-back-and-apply-logcommand, to restore a backup of it and prepare the data.
Edit the master
my.cnffile and comment out
log-bin, so that the slaves do not receive twice the binary log needed to recover the master.
Replication in the slaves must be stopped temporarily while you pipe the binary log to the master. In the slaves, do:
mysql> STOP SLAVE;
Start the master mysqld on the restored backup:
$ mysqld … InnoDB: Doing recovery: scanned up to log sequence number 0 64300044 InnoDB: Last MySQL binlog file position 0 5585832, file name ./omnibook-bin.000002 …
InnoDB prints the binary log file (
./omnibook-bin.000002in this case) and the position (
5585832in this case) it was able to recover to.
Pipe the remaining of the binary log files to the restored server. The number of remaining binary log files varies depending on the length of the timespan between the last backup and the time to which you want to bring the database up to date. The longer the timespan, the more remaining binary log files there may be. All the binary log files, containing all the continuous binary log positions in that timespan, are required for a successful restore.
You also need to supply the starting position in the binary log by which the piping of the events should start. Deduce that information from the
meta/backup_variables.txtfile in the backup you just restored in step 1 above (access
backup_variables.txtby, for example, going to the temporary backup directory you specified with
--backup-dirduring the restore, and find the file under the
metafolder): look for the entry
meta/backup_variables.txt, and supply
valueto mysqlbinlog with the
While the last binary log position recovered is also displayed by InnoDB after the restore (see step 4 above), that is not a reliable number for deducing the start position for mysqlbinlog to use, as there could be DDL events and non-InnoDB changes that have taken place after the time reflected by the displayed position.
For example, if there are two more binary log files,
omnibook-bin.000004that come after
omnibook-bin.000002and the recovery in step 4 above has ended by
5585834according to the
backup_variables.txtfile, pipe the binary log with a single connection to the server with this command:
$ mysqlbinlog --start-position=5585834 /mysqldatadir/omnibook-bin.000002 \ /mysqldatadir/omnibook-bin.000003 /mysqldatadir/omnibook-bin.000004 | mysql
See Point-in-Time (Incremental) Recovery Using the Binary Log for more instructions on using mysqlbinlog.
The master database is now recovered. Shut down the master and edit
Start the master again.
Start replication in the slaves again:
mysql> START SLAVE;