Sev1: MySQL won't start after a crash. InnoDB corruption and innodb_force_recovery

#Symptom

systemctl start mysqld fails, and the error log (/var/log/mysqld.log or SHOW VARIABLES LIKE 'log_error' when it was last running) shows lines such as:

[ERROR] [MY-011906] [InnoDB] Database page corruption on disk or a failed file read of page [page id: space=45, page number=3]
[ERROR] [MY-013183] [InnoDB] Assertion failure: ...
InnoDB: We intentionally generate a memory trap.

Typical triggers: power loss with write caching on, storage failure, disk full during writes, a killed process during an upgrade.

#Step 0: decide restore vs repair

#Step 1: copy the data directory

systemctl stop mysqld
cp -a /var/lib/mysql /backup/mysql-datadir-$(date +%F-%H%M)

Every step below can make things worse. Keep an untouched copy.

#Step 2: check the basics

• Disk space: df -h /var/lib/mysql (InnoDB fails strangely on a full disk).
• Permissions: files owned by mysql.
• Memory: an innodb_buffer_pool_size larger than RAM after a VM resize causes failures that look like crashes.
• Did an upgrade or downgrade happen? MySQL can't start an 8.4 data directory with 8.0 binaries.

If one of these is the cause, fix it and start normally. InnoDB crash recovery runs automatically.

#Step 3: raise innodb_force_recovery step by step

Add to my.cnf under [mysqld], start, and check whether it stays up:

[mysqld]
innodb_force_recovery = 1

Start at 1 and go up only if it still won't start. With levels ≥ 4, treat the data files as disposable: you're only reading data out.

The server runs read-only for user data at level 4 and above (and you shouldn't write at any level).

#Step 4: dump the data

mysqldump --all-databases --routines --events --triggers --single-transaction \
  --set-gtid-purged=OFF > /backup/full-dump.sql

If one table makes the dump crash, dump database by database, then table by table, to find it:

for t in $(mysql -N -e "SELECT table_name FROM information_schema.tables WHERE table_schema='app'"); do
  mysqldump app "$t" > /backup/app.$t.sql || echo "FAILED: $t"
done

For a broken table, try SELECT ... INTO OUTFILE with a WHERE on primary key ranges to save the readable rows.

#Step 5: rebuild

1. Stop MySQL, move the old data directory aside (keep it).
2. Initialise a fresh one: mysqld --initialize --user=mysql (note the temporary root password in the log).
3. Remove innodb_force_recovery from my.cnf; start.
4. Load the dump: mysql < /backup/full-dump.sql.
5. Check row counts against what the application expects; run CHECK TABLE on key tables.
6. Recreate users if you didn't dump the mysql system schema (8.0+: use mysqlpump / MySQL Shell dump utilities, or script SHOW CREATE USER / SHOW GRANTS).

Caution

Never leave innodb_force_recovery set in production config. Remove it as soon as the data is dumped. A server running with level 4+ can silently damage data further.

#Prevent it next time

• Storage with battery/flash-backed write cache or no volatile write caching; innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 for durable workloads.
• Regular physical backups with restore tests; binlogs kept for point-in-time recovery.
• A replica (or InnoDB Cluster) so recovery is a failover, not a forensic exercise.
• Disk-space alerts well before full.