Skip to main content

3.备份和恢复策略

常规奔溃场景​

  • 操作系统崩溃
  • 电源（检测）失败
  • 文件系统崩溃
  • 硬件问题（硬盘、主板等）

假设数据存储在 InnoDB 存储引擎中，存储引擎支持事务和自动崩溃恢复。还假设崩溃时 MySQL 服务器处于负载状态。如果不是，就不需要恢复。

操作系统崩溃或断电​

对于操作系统崩溃或断电的情况，我们可以假设重启后MySQL的磁盘数据可用。

InnoDB由于崩溃，数据文件可能不包含一致的数据，但会 InnoDB读取其日志并在其中找到尚未刷新到数据文件的挂起的已提交和未提交事务的列表。

InnoDB自动回滚那些未提交的事务，并将已提交的事务刷新到其数据文件。有关此恢复过程的信息通过 MySQL 错误日志传达给用户。

以下是日志摘录示例：

InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

文件系统崩溃或硬件问题​

对于文件系统崩溃或硬件问题的情况，假设 重启后MySQL磁盘数据不可用。这意味着MySQL无法成功启动，因为某些磁盘数据块不再可读。

在这种情况下，有必要重新格式化磁盘、安装新磁盘或以其他方式纠正根本问题。然后需要从备份中恢复我们的MySQL数据，这意味着备份必须已经做好。为了确保这种情况，请设计并实施备份策略。