DB-유

Recovering InnoDB with innodb_force_recovery

· mysql, innodb, recovery

Power failure, full disk, a dying RAID controller: MySQL won't start, the error log is full of checksum failures or an InnoDB assertion, and it restarts in a loop. If you have a good backup, restore it and stop reading. If you don't, or the backup is too old, innodb_force_recovery gets the server up long enough to dump what's left.

It's a last resort, not a repair. Start read-only, dump everything, rebuild the data directory from scratch, reload. Never run production traffic with this option set.

First, and I mean before anything else, make a file-level copy: cp -a /var/lib/mysql /var/lib/mysql.crashed. I skipped this once out of impatience and regretted it, because every step below can make things worse. You also need free disk for the dump and the fresh data directory, and the error log handy (/var/log/mysqld.log on CentOS, /var/log/mysql/error.log on Ubuntu).

Read the error log

Find the last successful start and the first error after it:

Look at the last successful start and the first error after it:

sudo grep -n -E 'InnoDB: (Database page corruption|Assertion failure|Unable to|Page .* log sequence number)' /var/log/mysqld.log | tail -20

Typical messages that lead here:

InnoDB: Database page corruption on disk or a failed file read of page [page id: space=47, page number=1182].
InnoDB: Assertion failure in thread 140334572369664 in file btr0cur.cc line 4372
InnoDB: Page [page id: space=0, page number=5] log sequence number 1234567890 is in the future! Current system log sequence number 1234000000.

The space number maps to a tablespace; SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE space = 47 once the server is up tells you which table. The "in the future" message means the redo log and data files are from different points in time, usually because someone copied files around or restored ib_logfile* from a different backup.

Start with the lowest level that works

Add the option to /etc/my.cnf and try each level in order. Don't skip ahead; higher levels disable more of InnoDB and can corrupt data that was still fine.

[mysqld]
innodb_force_recovery = 1

sudo systemctl start mysqld; sudo tail -30 /var/log/mysqld.log

What the levels do:

  1. SRV_FORCE_IGNORE_CORRUPT – skip corrupt pages when scanning; lets SELECT continue past bad pages.
  2. SRV_FORCE_NO_BACKGROUND – do not start the master thread and purge threads (helps when a crash happens during purge).
  3. SRV_FORCE_NO_TRX_UNDO – do not roll back transactions after recovery.
  4. SRV_FORCE_NO_IBUF_MERGE – do not merge the change buffer; can corrupt secondary indexes, which are rebuildable.
  5. SRV_FORCE_NO_UNDO_LOG_SCAN – ignore undo logs; treats uncommitted transactions as committed.
  6. SRV_FORCE_NO_LOG_REDO – do not apply the redo log at all; leaves pages in whatever state they were on disk.

From level 4 upward, on 5.7 and 8.0 the server automatically becomes read-only for InnoDB tables (innodb_read_only behavior), so you cannot accidentally write. Values above 3 can permanently damage data files, which is why that copy is not optional.

Dump everything the moment it starts

The moment the server accepts connections:

mysqldump --single-transaction=0 --skip-lock-tables --routines --triggers --events --all-databases > /backup/forced-dump.sql

Drop --single-transaction because the transaction system may be disabled; --skip-lock-tables because LOCK TABLES may fail. If the full dump aborts on one table, dump the rest with --ignore-table=schema.table and then attempt that table alone:

mysqldump --skip-lock-tables shop orders > /backup/orders.sql

If a single table is too damaged, read what you can in chunks by primary key from a client and write the rows out with SELECT ... INTO OUTFILE; a SELECT on a corrupt page may still crash the server, so bisect on the key range.

Rebuild the data directory

With the dumps saved, stop the server and initialize a clean instance:

sudo systemctl stop mysqld
sudo mv /var/lib/mysql /var/lib/mysql.forced
sudo mkdir /var/lib/mysql && sudo chown mysql:mysql /var/lib/mysql
sudo sed -i '/innodb_force_recovery/d' /etc/my.cnf
sudo mysqld --initialize-insecure --user=mysql
sudo systemctl start mysqld
mysql -u root < /backup/forced-dump.sql
mysql -u root shop < /backup/orders.sql

--initialize-insecure creates root without a password; set one immediately after the reload with ALTER USER. On 8.0 the dump of the mysql schema from a forced start is best skipped (--ignore-table=mysql.* variants) in favour of recreating users from your provisioning scripts.

After the reload

CHECK TABLE shop.orders EXTENDED;
SELECT table_schema, SUM(table_rows) FROM information_schema.tables GROUP BY table_schema;
SHOW ENGINE INNODB STATUS\G

Compare row counts with the last known good numbers (monitoring graphs are useful here). Confirm innodb_force_recovery is 0 with SHOW VARIABLES LIKE 'innodb_force_recovery'. Then take a fresh backup before letting the application back in.

If it still won't cooperate

  • The server still crashes at level 1–3 with InnoDB: Assertion failure ... in file fsp0fsp.cc – The corruption is in the system tablespace (ibdata1). Go to level 4, then 5, then 6; at 6 InnoDB opens whatever it can without redo.
  • ERROR 1030 (HY000): Got error 1 from storage engine on INSERT or CREATE while recovering – Expected; levels 4 and above are read-only. Only dump.
  • [ERROR] InnoDB: Cannot open datafile for read-only: './shop/orders.ibd' OS error: 71 – The .ibd file is missing or unreadable at the OS level; check dmesg for disk errors before blaming MySQL, and restore the file from the copy if it is present there.
  • ERROR 1146 (42S02): Table 'shop.orders' doesn't exist although the .ibd file exists – Dictionary and file out of sync. On 5.7 try ALTER TABLE shop.orders DISCARD TABLESPACE followed by IMPORT TABLESPACE with the .cfg; on 8.0 the data dictionary is transactional and this usually means the table was dropped mid-DDL and rolled back.
  • The dump has fewer rows than expected – Pages skipped at level 1 or lost at level 6 are gone. Cross-check against binary logs: mysqlbinlog from the last backup's position can replay the missing writes into the rebuilt instance.

The lesson every time was about the backup schedule, not the option.