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

10
  • How was the back up done? Did you stop the mysqld service during the reload?
    – Rick James
    Oct 2 at 23:38
  • I used dd from an Ubuntu live CD to make a backup of the whole partition, and yes I did stop mysqld
    – valepu
    Oct 3 at 7:52
  • Was only the data on that disk partition? Did you make any changes to my.cnf?
    – Rick James
    Oct 3 at 15:11
  • yes that's the only copy I have (it's a development environment so nothing too important except for an application that is still in development and we did some data entry on it) and no I haven't changed the my.cnf file
    – valepu
    Oct 3 at 15:54
  • If you mean if the partition contained only the mysql instance in that case no, it was the whole system
    – valepu
    Oct 3 at 16:08

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.