Our database MySQL8 crashed due to no space left on the data directory. After we arranged more space the database kept crashing with the error
I know that there a lot of threads with this kind of issue. Believe me, I tried everything I could find, otherwise, I would not have opened this one.
I was only able to start it with recovery mode 6, but after that, it still does not start without recover mode. After an internet search, I tried many solutions but nothing worked.
At this point, I gave up trying to recover the DB and created a new DB with the same my.cnf hoping to import data from IBD files. I only changed the data directory from my.cnf file. Data directory from crashed DB was "/var/lib/mysql/sas/" the new DB has "/var/lib/mysql/sas/data/data/", with this I can move IBD files immediately. I cannot dump tables and import them to the new DB because the tables are too big and too many. It would take one month at least.
What I did was;
create table on the new DB discard tablespace and move the IBD file to the new DB data directory import tablespace I am getting this error
Page offset does not match file offset: page offset: 262144, file offset: 262144
I can select the same table in the crashed DB opened with recovery mode 6. So I don't think that the IBD file is corrupt. Here I went out of ideas.
I am quite desperate at this point and any help would be appreciated. Thank you