走进官方手册系列 --- InnoDB Recovery
来源:互联网 发布:cs手机扫描软件 编辑:程序博客网 时间:2024/04/30 08:45
https://dev.mysql.com/doc/refman/5.5/en/innodb-recovery.html
This section describes InnoDB recovery. Topics include:
Point-in-Time Recovery
Recovery from Data Corruption or Disk Failure
InnoDB Crash Recovery
1、Point-in-Time Recovery
To recover an InnoDB database to the present from the time at which the physical backup was made, you must run MySQL server with binary logging enabled, even before taking the backup. To achieve point-in-time recovery after restoring a backup, you can apply changes from the binary log that occurred after the backup was made. See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
2、Recovery from Data Corruption or Disk Failure
If your database becomes corrupted or disk failure occurs, you must perform the recovery using a backup. In the case of corruption, first find a backup that is not corrupted. After restoring the base backup, do a point-in-time recovery from the binary log files using mysqlbinlog and mysql to restore the changes that occurred after the backup was made.
In some cases of database corruption, it is enough to dump, drop, and re-create one or a few corrupt tables. You can use the CHECK TABLE statement to check whether a table is corrupt, although CHECK TABLE naturally cannot detect every possible kind of corruption. You can use the Tablespace Monitor to check the integrity of the file space management inside the tablespace files.
In some cases, apparent database page corruption is actually due to the operating system corrupting its own file cache, and the data on disk may be okay. It is best to try restarting the computer first. Doing so may eliminate errors that appeared to be database page corruption. If MySQL still has trouble starting because of InnoDB consistency problems, see Section 14.23.2, “Forcing InnoDB Recovery” for steps to start the instance in recovery mode, which permits you to dump the data.
3、InnoDB Crash Recovery
To recover from a MySQL server crash, the only requirement is to restart the MySQL server. InnoDB automatically checks the logs and performs a roll-forward of the database to the present. InnoDB automatically rolls back uncommitted transactions that were present at the time of the crash. During recovery, mysqld displays output similar to this:
要把crash的MySQL实例恢复过来,唯一要做的就是重启MySQL服务。
InnoDB: Log scan progressed past the checkpoint lsn 452854464
InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 457028695
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 990682 row operations to undo
InnoDB: Trx id counter is 500
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 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 73 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: Waiting for the background threads to start
InnoDB: Starting in background the rollback of uncommitted transactions
InnoDB: Rolling back trx with id 3B1, 990682 rows to undo
...
InnoDB: 5.5.55 started; log sequence number 457028695
...
./mysqld: ready for connections.
The InnoDB crash recovery process consists of several steps:
Redo log application
Redo log application is the first step and is performed during initialization, before accepting any connections. If all changes are flushed from the buffer pool to the tablespaces (ibdata* and *.ibd files) at the time of the shutdown or crash, redo log application is skipped. InnoDB also skips redo log application if redo log files are missing at startup.
Removing redo logs to speed up recovery is not recommended, even if some data loss is acceptable. Removing redo logs should only be considered after a clean shutdown, with innodb_fast_shutdown set to 0 or 1.
Roll back of incomplete transactions
Incomplete transactions are any transactions that were active at the time of crash or fast shutdown. The time it takes to roll back an incomplete transaction can be three or four times the amount of time a transaction is active before it is interrupted, depending on server load.
You cannot cancel transactions that are being rolled back. In extreme cases, when rolling back transactions is expected to take an exceptionally long time, it may be faster to start InnoDB with an innodb_force_recovery setting of 3 or greater. See Section 14.23.2, “Forcing InnoDB Recovery”.
Change buffer merge
Applying changes from the change buffer (part of the system tablespace) to leaf pages of secondary indexes, as the index pages are read to the buffer pool.
Purge
Deleting delete-marked records that are no longer visible to active transactions.
The steps that follow redo log application do not depend on the redo log (other than for logging the writes) and are performed in parallel with normal processing. Of these, only rollback of incomplete transactions is special to crash recovery. The insert buffer merge and the purge are performed during normal processing.
After redo log application, InnoDB attempts to accept connections as early as possible, to reduce downtime. As part of crash recovery, InnoDB rolls back transactions that were not committed or in XA PREPARE state when the server crashed. The rollback is performed by a background thread, executed in parallel with transactions from new connections. Until the rollback operation is completed, new connections may encounter locking conflicts with recovered transactions.
In most situations, even if the MySQL server was killed unexpectedly in the middle of heavy activity, the recovery process happens automatically and no action is required of the DBA. If a hardware failure or severe system error corrupted InnoDB data, MySQL might refuse to start. In this case, see Section 14.23.2, “Forcing InnoDB Recovery”.
For information about the binary log and InnoDB crash recovery, see Section 5.4.4, “The Binary Log”.
- 走进官方手册系列 --- InnoDB Recovery
- 走进官方手册系列 --- 详解InnoDB针对不同类型的SQL所采取的锁策略
- innodb recovery
- 走进MySQL5.6:系列版本官方技术文档合集
- 浅析Innodb crash recovery
- Forcing InnoDB Recovery
- MySQL Forcing InnoDB Recovery
- innodb crash recovery流程
- InnoDB recovery详细流程
- 刷回官方recovery
- InnoDB Crash Recovery 流程源码实现分析
- InnoDB Crash Recovery 流程源码实现分析
- MySQL Innodb crash后的force recovery
- innoDB Crash Recovery 流程源码实现分析
- MySQL InnoDB Update和Crash Recovery流程
- 14.15 InnoDB Backup and Recovery Innodb备份和恢复
- 14.18.1 The InnoDB Recovery Process InnoDB 恢复进程:
- mod_python官方手册翻译
- 项目-团队-技术-个人(提拔篇)
- 【hdu1257】最少拦截系统+LIS(最长上升子序列)
- Android实现定时刷新
- 发生内存警告时的讨论
- [转]简析STM32启动过程
- 走进官方手册系列 --- InnoDB Recovery
- RetroScope工具:下载,编译,启动 (MD)
- vue.js学习笔记-2
- RN生命周期
- 软件测试之谷歌测试定律
- Android实现下拉框(Spinner)
- 1.登陆接口
- 学院学习总结
- springmvc(四) springmvc的数据校验的实现