InnoDB 中文参考手册 --- 犬犬(心帆)翻译 6 备份和恢复 InnoDB 数据库
安全的数据库管理就是使用正轨的数据备份。

InnoDB Hot Backup 是一个在线备份工具，你可以在 InnoDB 数据库运转时使用它来实如今线备份。InnoDB Hot Backup 不需求你关闭你的服务器也不需求加任何锁或影响其它普通的数据操作。InnoDB Hot Backup 是一个非免费的附加工具，它的费用为每 MySQL 服务器每年 400 欧元。浏览网页 InnoDB Hot Backup homepage 可获得更多的信息以及程序屏幕截图。

如果你可以关闭你的 MySQL 服务，那么可以通过下面几个步骤进行数据库的“二进制”备份：
关闭 MySQL 数据库服务，并确定在关闭时没有发生任何错误 将你的所无数据文件复制到一个安全的地方 将所有的 InnoDB 日志文件复制到一个安全的地方 将 my.cnf 配置文件复制到一个安全的地方 将所有的 InnoDB 表 .frm 文件复制到一个安全的地方
在需求高功用的数据库服务站点上，可以通过 MySQL 的复制特性来保持数据库的一个副本，MySQL 的复制特性同样适用于 InnoDB 表类型。

除了上面描述的二进制备份方式之外，最好定期地使用 mysqldump 转储数据表。缘由是二进制文件可能会在你未留意时而被破坏，而表转储(dump)文件是以文本文件方式保存的，它与二进制文件相比更简单、有更好的的可读性。由于转储文件更简单所以更容易发现表损坏， 重要数据损环的可能性很小。

一个好的主意就是在对数据库做二进制备份的同时也做一个转储(dump)备份。为了得到分歧的数据快照，必须关闭所有客户端的连接。然后就可以进行二进制备份，这样你就有了数据分歧的两种格式的备份。

如了实现通过上面所述的二进制备份方法将 InnoDB 数据库恢复到当前形状，必须打开 MySQL 的二进制日志(binlogging)开关。这样你就可以二进制日志 与备份数据配合实现分时间点的恢复：
mysqlbinlog yourhostname-bin.123 | mysql

　

为了恢复一个崩溃了的 MySQL 服务进程，你所能做的唯逐一件事就是重新启动。InnoDB 将自动地检查日志并完成数据库的前滚(roll-forward)到当前形状。同时，InnoDB 将自动回滚崩溃前未提交的事务。在恢复过程中，mysqld 将显示如下所示的提示：

heikki@donna:~/mysql-3.23.48/sql> mysqld 020204 23:08:31 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 177573790 InnoDB: Doing recovery: scanned up to log sequence number 0 177638912 InnoDB: Doing recovery: scanned up to log sequence number 0 177704448 InnoDB: Doing recovery: scanned up to log sequence number 0 177769984 InnoDB: Doing recovery: scanned up to log sequence number 0 177835520 InnoDB: Doing recovery: scanned up to log sequence number 0 177901056 InnoDB: Doing recovery: scanned up to log sequence number 0 177966592 InnoDB: Doing recovery: scanned up to log sequence number 0 178032128 InnoDB: Doing recovery: scanned up to log sequence number 0 178097664 InnoDB: Doing recovery: scanned up to log sequence number 0 178163200 InnoDB: Doing recovery: scanned up to log sequence number 0 178228736 InnoDB: After this prints a line for every 10th scan sweep: InnoDB: Doing recovery: scanned up to log sequence number 0 178884096 ... InnoDB: Doing recovery: scanned up to log sequence number 0 193302016 InnoDB: Doing recovery: scanned up to log sequence number 0 193957376 InnoDB: Doing recovery: scanned up to log sequence number 0 194612736 020204 23:08:40 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 7 3 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 0 195268096 InnoDB: Doing recovery: scanned up to log sequence number 0 195923456 ... InnoDB: Doing recovery: scanned up to log sequence number 0 203132416 InnoDB: Doing recovery: scanned up to log sequence number 0 203787776 InnoDB: Doing recovery: scanned up to log sequence number 0 204443136 InnoDB: 5 uncommitted transaction(s) which must be rolled back InnoDB: Trx id counter is 0 129792 InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 129400 InnoDB: Rolling back of trx id 0 129400 completed InnoDB: Rolling back trx with id 0 129217 InnoDB: Rolling back of trx id 0 129217 completed InnoDB: Rolling back trx with id 0 129098 InnoDB: Rolling back of trx id 0 129098 completed InnoDB: Rolling back trx with id 0 128743 InnoDB: Rolling back of trx id 0 128743 completed InnoDB: Rolling back trx with id 0 127939 InnoDB: Rolling back of trx id 0 127939 completed InnoDB: Rollback of uncommitted transactions completed 020204 23:08:51 InnoDB: Starting an a