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 表类型。
如了实现通过上面所述的二进制备份方法将 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 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: Last MySQL binlog file offset 0 40418561, file name ./donna-bin.001
020204 23:08:53 InnoDB: Flushing modified pages from the buffer pool...
020204 23:09:03 InnoDB: Started
mysqld: ready for connections
如果数据库遭到损坏或磁盘失败,则不得不从一个备份文件恢复。在损坏的情况下,首先可以恢复一个未损坏的备份文件。然后依照
MySQL 手册提示从一般的日志文件中恢复数据。
从 3.23.53 和 4.0.4 开始,即使强制恢复被使用你也可以使用
DROP 或 CREATE 一个表。 如果你确定表如引起回滚崩溃,你可以移除(drop)它。你也可以通过这个停止一个因导入大量数据或
ALTER TABLE 而引起的失控(runaway)回滚。你可以杀死 mysqld 进程,并使用 my.cnf
设置项 innodb_force_recovery=3 不使用回滚。然后就可以 DROP 那个引起失控(runaway)回滚的表。
下面较大的数意味着包含所有较低数所对就的安全防范。为了能够转储表设置至少为
4 ,这是相对安全的,仅仅只有一些损坏的页面数据掉失。Option 6 is more dramatic, because database pages
are left in an obsolete state, which in turn may introduce more corruption into
B-trees and other database structures.
1 (SRV_FORCE_IGNORE_CORRUPT)
即使发现一个错误也启动服务;试着使用 SELECT * FROM table 跳过损坏的索引记录和页面,这将帮助转储表。
2 (SRV_FORCE_NO_BACKGROUND)
prevent the main thread from running: 如果在清理过程中将发生崩溃,这将预防它。