1

A few days ago I was forced to format my PC due to an issue with a Windows update, I managed to make a backup first and now I'm trying to restore the files. I have a mysql instance that I'm having troubles restoring. If i try to run the database I have this error

2022-10-02T20:07:56.238696Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2022-10-02T20:07:56.239311Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-10-02T20:07:56.239429Z 0 [ERROR] [MY-010119] [Server] Aborting

If i make a new data directory using --initialize it starts working again (in a clean state of course). Then if I copy all the files except for the mysql directory (when I do that I get the error above) I get a bunch of these errors

2022-10-02T19:59:28.774381Z 1 [ERROR] [MY-011971] [InnoDB] Tablespace 'mysql' Page [page id: space=4294967294, page number=122] log sequence number 2608993377 is in the future! Current system log sequence number 2608601904.
2022-10-02T19:59:28.774773Z 1 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.

Then errors like these

2022-10-02T19:59:28.775093Z 1 [Warning] [MY-013021] [InnoDB] A transaction id in a record of table `mysql`.`collations` is newer than the system-wide maximum.

And finally the server crashes with this error

2022-10-02T19:59:28.775869Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: trx0rec.ic:96:next_undo_offset > undo_offset thread 13140

If I use innodb_force_recovery = 5 or 6 the server works again but I can't see my old tables.

In the past in situations like these I managed to recover my databases without issue (also looking on the internet this is supposed to be working just fine) but this time it seems something is wrong. My suspects are:

  1. There's a corrupted file somewhere
  2. Some permissions got lost in the backup

But i've checked files in the data/mysql folder and they seem fine (they look similar to the ones generated by --initialize), I also tried to change ownership and permissions on the files but that still didn't help

The version of MySQL I'm using is 8.0.20.0

EDIT:

I had to pause trying to recover this mysql instance for a while but today I tried again and I noticed when I use --initialize it generates a file called mysql.ibd which is missing from my backup, when this file is missing I get the error "Data Dictionary initialization failed.", if I copy my data and add that file the instance works but obviously I won't see my databases

I have already tried this .ibd file is missing and alter table discard tablespace crashing the server

And this

restore table from .frm and .ibd file?

But it didn't work

valepu
  • 111
  • 6

1 Answers1

0

So, apparently some data got lost when recovering the disk and nobody noticed it until today, apart from the mysql.ibd file some .ibd files from single databases were missing too, making it impossible to recover some tables. Contrary to what I said in my question, this

.ibd file is missing and alter table discard tablespace crashing the server

actually worked (copying the ibd files and using alter table xxx import tablespace) for most of the remaining tables (some had corrupted data). I had to re-create the tables for this to work but fortunately I had a script for that

I managed to recover most of the database we had no backups of and recovered the others from proper backups.

I guess the lesson learned here is to check more carefully the files from your backup before trying anything else to recover your database

valepu
  • 111
  • 6