本节讨论了一种执行备份的程序，该程序使您能够在几种类型的故障后恢复数据：This section discusses a procedure for performing backups that enables you to recover data after several types of crashes:

示例命令未包含 --user 和 --password 等选项，用于 mysqldump 和 mysql 客户端程序。您应根据需要包含这些选项，以便客户端程序能够连接到 MySQL 服务器。The example commands do not include options such as --user and --password for the mysqldump and mysql client programs. You should include such options as necessary to enable client programs to connect to the MySQL server.

假设数据存储在支持事务和自动崩溃恢复的InnoDB存储引擎中。还假设在崩溃时 MySQL 服务器正处于负载状态。如果不是这样，就永远不需要恢复。Assume that data is stored in the InnoDB storage engine, which has support for transactions and automatic crash recovery. Assume also that the MySQL server is under load at the time of the crash. If it were not, no recovery would ever be needed.

对于操作系统崩溃或停电的情况，我们可以假设 MySQL 的磁盘数据在重启后是可用的。InnoDB 数据文件可能由于崩溃而不包含一致的数据，但是 InnoDB 会读取其日志，并在其中找到尚未刷新到数据文件的已提交和未提交事务的列表。InnoDB 会自动回滚那些未提交的事务，并将已提交的事务刷新到其数据文件中。有关此恢复过程的信息会通过 MySQL 错误日志传达给用户。以下是日志摘录示例：For cases of operating system crashes or power failures, we can assume that MySQL's disk data is available after a restart. The InnoDB data files might not contain consistent data due to the crash, but InnoDB reads its logs and finds in them the list of pending committed and noncommitted transactions that have not been flushed to the data files. InnoDB automatically rolls back those transactions that were not committed, and flushes to its data files those that were committed. Information about this recovery process is conveyed to the user through the MySQL error log. The following is an example log excerpt:

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 数据，这意味着必须已经进行了备份。为了确保这一点，需要设计并实施备份策略。For the cases of file system crashes or hardware problems, we can assume that the MySQL disk data is not available after a restart. This means that MySQL fails to start successfully because some blocks of disk data are no longer readable. In this case, it is necessary to reformat the disk, install a new one, or otherwise correct the underlying problem. Then it is necessary to recover our MySQL data from backups, which means that backups must already have been made. To make sure that is the case, design and implement a backup policy.