6.2 Restoring a Master Database in Replication

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:

  1. Using the backup of the master database, do the apply-log operation, shut down the database, and do the copy-back operation.

  2. Edit the master my.cnf file and comment out log-bin, so that the slaves do not receive twice the binary log needed to recover the master.

  3. Replication in the slaves must be stopped temporarily while you pipe the binary log to the master. In the slaves, do:

    mysql> STOP SLAVE;
  4. 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.002

    InnoDB prints the binary log file and the position it was able to recover to.

  5. Pipe the remaining binary log files to the restored backup. For example, if there are two more binary log files, omnibook-bin.003 and omnibook-bin,004, pipe them with a single connection to the server:

              
    $ mysqlbinlog --start-position=5585832 mysqldatadir/omnibook-bin.002 | mysql
    $ mysqlbinlog /mysqldatadir/omnibook-bin.003 /mysqldatadir/omnibook-bin.004 | mysql
    

    The number of remaining binary log files varies depending on when the last backup was taken: the older the backup, the more remaining binary log files there may be.

  6. The master database is now recovered. Shut down the master and edit my.cnf to uncomment log-bin.

  7. Start the master again.

  8. Start replication in the slaves again:

    mysql> START SLAVE;