Case study of MySQL startup failure data recovery

Case study of MySQL startup failure data recovery

Forcing InnoDB Recovery provides six levels of repair mode. Note that when the value is greater than 3, the data file will be permanently damaged and cannot be recovered. The six levels are excerpted as follows:

Forcing InnoDB Recovery

1 (srv_force_ignore_0000upt)
Lets the server run even if it detects a temporary upt page. Tries to make SELECT * FROM tbl_name jump over duplicate upt 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 a crash wowould 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 wocould cause a crash, does not do them. does not calculate table statistics. this value can permanently encrypt upt data files. after using this value, be prepared to drop and recreate all secondary indexes.

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 encrypt upt data files.

6 (SRV_FORCE_NO_LOG_REDO)
Does not do the redo log roll-forward in connection with recovery. this value can permanently encrypt upt data files. leaves database pages in an obsolete state, which in turn may introduce more than uption into B-trees and other database structures.

The usage is as follows. In the mysql configuration file, add or modify the following configuration values:

My. cnf
[Mysqld]
Innodb_force_recovery = 1

According to the method found, my repair steps are as follows:

Because I cannot start mysql, I need to start mysql first and then dump the data. Try innodb_force_recovery from the value of 1 to check whether mysql can be started in this repair mode. The value cannot be greater than 4.
Here, mysql can be started when the value is 2. This is to stop the database and then back up the data
Sudo service mysql stop
Mysqldump-u root-p -- all-databases> all-databases. SQL

Delete an error data file
Mv ib_logfile0 ib_logfile0.bak
Mv ib_logfile1 ib_logfile1.bak
Mv ibdata1 ibdata1.bak

Start mysql and restore data from the backup file
Sudo service mysql start
Mysql-u root-p <all-databases. SQL

In the repair mode, an error is returned when data is inserted. That is to say, data cannot be written at this time. So the repair mode is disabled.
[Mysqld]
Innodb_force_recovery = 0

Restore data after restart mysql

Sudo service mysql restart
Mysql-u root-p <all-databases. SQL

Restart mysql again. Now mysql can be started normally and data is restored successfully.

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/151049.htm