Recovery for Data, Transaction, and Binary Logs on Separate Volumes

MySQL and MariaDB
Audience
Public
Source Type
Documentation

To recover a full database after a catastrophic failure, perform the following procedure:

  1. Restore data volume from the latest snapshot.
  2. Restore binary log volume from the latest snapshot.
  3. Start MySQL database server.
  4. Recover to a point-in-time using binary logs.

    The incremental point-in-time recovery is dependent on the binary log and data volume snapshots. If the data volume snapshot was taken at time T0 with subsequent log volume snapshots at times T1, T2 with failure occurring at time T3, the database recovery is possible up to the last successfully committed and recorded transaction in the binary log at time T3.

    When the database server is started after the failure, the new binary log is created. The binary logs recovered from snapshots will incrementally restore data to the most recent time available. The binary log recovery is initiated with mysqlibinlog command. For example, the log volume recovered from the snapshot created at T2 included binlog.00001 closed at time T1, binlog.00002 closed time T2. If binlog.00003 is available when a catastrophic failure occurred at T3, it should be also included in the database recovery; to begin the incremental recovery, execute the following:

    
shell> mysqlbinlog binlog.00001 binlog.00002 binlog.00003 | mysql -u <user> -p

    For additional details in incremental binary log recovery, refer to MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html.