Although the InnoDB storage engine has mechanisms to preserve data, problems such as power failure or sudden restart can still cause errors in MySQL/MariaDB data. These errors can make the system unable to boot, unable to restore the normal data state. Some of the error messages we often encounter are listed below.
[Note] InnoDB: Starting shutdown...
[ERROR] InnoDB: Database was not shut down normally!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[Note] Plugin 'FEEDBACK' is disabled.
[ERROR] InnoDB: Starting crash recovery from checkpoint LSN=1637690
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
Note: Some crashes are caused by other causes but also return the error message "Unknown/unsupported storage engine: InnoDB". In that case, there will be more detailed information in the MySQL log to help you find the cause and handle it. As for the cases where the service can't tell why InnoDB is unsupported or unrecoverable, most of the time, data errors are caused. You can handle that case according to the instructions in this article.
To use this mode is very simple. You just need to specify the value innodb_force_recovery in the installation file of MySQL or MariaDB. Then restart the service. MySQL will restart and try to use recovery mode to recover the corrupted data.
[mysqld]
innodb_force_recovery = 1
MySQL has a total of 6 recovery modes in ascending severity. You often choose to get the most suitable mode for your system. Please try again with a higher level if the error is not resolved.
1 (SRV_FORCE_IGNORE_CORRUPT)
Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
2 (SRV_FORCE_NO_BACKGROUND)
Prevents the master thread and any purge threads from running. If an unexpected exit would occur during the purge operation, this recovery value prevents it.
3 (SRV_FORCE_NO_TRX_UNDO)
Does not run transaction rollbacks after crash recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes. Sets InnoDB to read-only.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files. Sets InnoDB to read-only.
6 (SRV_FORCE_NO_LOG_REDO)
Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures. Sets InnoDB to read-only.
Finally, after troubleshooting, restore the "innodb_force_recovery" configuration to level 0 and then restart the database service to keep the system working properly.
Also note that this is only a workaround, it may also fail if the data is badly corrupted. Our advice remains that every database system should always have a mechanism to automatically back up data.
0 nhận xét:
Post a Comment