Documentation Home
MySQL Enterprise Backup 9.3 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb


8.3 Restoring a Source Database

To fix a corruption problem in a replication source database, you can restore the backup, taking care not to propagate unnecessary SQL operations to the replica servers:

  1. Shut down the source database and then use, for example, thecopy-back-and-apply-log command, to restore a backup of it and prepare the data.

  2. Edit the source'smy.cnf file and comment outlog-bin, so that the replicas do not receive twice the binary log needed to recover the source.

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

    mysql> STOP REPLICA;

  4. Start the sourcemysqld on the restored backup:

    $ mysqld…InnoDB: Doing recovery: scanned up to log sequence number 0 64300044InnoDB: Last MySQL binlog file position 0 5585832, file name./omnibook-bin.000002…

    InnoDB prints the binary log file (./omnibook-bin.000002 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 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 themeta/backup_variables.txt file in the backup you just restored in step 1 above (accessbackup_variables.txt by, for example, going to the temporary backup directory you specified with--backup-dir during the restore, and find the file under themeta folder): look for the entrybinlog_position=value inmeta/backup_variables.txt, and supplyvalue tomysqlbinlog with the--start-position option.

    Note

    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 formysqlbinlog 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.000003 andomnibook-bin.000004 that come afteromnibook-bin.000002 and the recovery in step 4 above has ended by5585834 according to thebackup_variables.txt file, 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

    SeePoint-in-Time (Incremental) Recovery for more instructions on usingmysqlbinlog.

  6. The source database is now recovered. Shut down the source and editmy.cnf to uncommentlog-bin.

  7. Start the source again.

  8. Start replication in the replicas again:

    mysql> START REPLICA;