MySQL用备份与恢复策略

·         操作系统崩溃

·         电源故障

·         文件系统崩溃

·         硬件问题(硬盘、母板等等)

该命令不包括mysqldump和mysql程序的--user和—password等选项。应包括必要的选项让MySQL服务器允许你连接它。

我们假定数据保存在MySQL的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未能成功启动，因为一些硬盘数据块不再可读。在这种情况下，需要重新格式化硬盘，安装一个新的，或纠正问题。