Documentation Home
MySQL Enterprise Backup 4.1 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
HTML Download (TGZ) - 187.0Kb
HTML Download (Zip) - 213.3Kb

7.3 Restoring a Master Database

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. Shut down the master database and then use, for example, the copy-back-and-apply-log command, to restore a backup of it and prepare the data.

  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

    InnoDB prints the binary log file (./omnibook-bin.002 in this case) and the position (5585832 in this case) it was able to recover to.

  5. Pipe the remaining of the binary log files to the restored backup. For example, if there are two more binary log files, omnibook-bin.003 and omnibook-bin.004 that come after omnibook-bin.002, pipe them all with a single connection to the server (see Point-in-Time (Incremental) Recovery Using the Binary Log for more instructions on using mysqlbinlog):

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

    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.

  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;

User Comments
Sign Up Login You must be logged in to post a comment.