how to recover database from MariaDB data folder

andrea_ca_gt's profile image andrea_ca_gt posted 2 years ago in General Permalink

Hi all, I needed to reinstall my computer and could only save the data folder within the MariaDB software to keep my database. I would like to reinstall my database from that. I tried to just copy the folders with the .frm and .ibd files but it is not working. I get errors such as the table doesn't exist in engine in which the files are just set as view.

Anyone has any tips of how to reestablish the databases from that folder? I am using heidi only on my local windows

Thanks in advance Andrea

ansgar's profile image ansgar posted 2 years ago Permalink

In case if .ibd files (InnoDB tables) I suppose you need the tablespace file as well from the data directory. Here I found a blog article for recovering an InnoDB tables from an .ibd file. But that reads really crazy.

andrea_ca_gt's profile image andrea_ca_gt posted 2 years ago Permalink

Yeah, that blog was a bit too much for my limited sql knowledge. But for each data base I have the .frm and .ibd files from each table as well as a db.opt file. Are the .frm what you called tablespace file?

ansgar's profile image ansgar posted 2 years ago Permalink

No, as far as I know the .frm files just contain the structure of a table or view. In the data directory, there should be some ibdata* file(s). Perhaps you have these files, not only the subdirectories?

andrea_ca_gt's profile image andrea_ca_gt posted 2 years ago Permalink

Yes, I also have a file called ibdata1, outside those subfolders. I send a picture of the files I have.

1 attachment(s):
  • ibdata1
ansgar's profile image ansgar posted 2 years ago Permalink

Did you try to use the whole old data folder in your new MariaDB installation?

andrea_ca_gt's profile image andrea_ca_gt posted 2 years ago Permalink

I just tried to do everything from scratch and place the whole data folder and it worked now!! I also installed Heidi in a different Program File folder before and now I put it in exactly the same folder as it was before. And now everything works fine!! Not sure if it was because of the data folder or the location of heidi but it worked. Thanks a lot :)

ansgar's profile image ansgar posted 2 years ago Permalink

Glad you fixed it.

The data directory was the fix. Having HeidiSQL in a different directory does not change anything in your data.

By the way, it's highly recommended to use the same MariaDB version as the previous one, when doing such a migration, so the server is safe to read the data files in the correct way.

Also, a better way to copy/backup your data is to create SQL dumps. This can be done in HeidiSQL: Tools > Export database as SQL. Even better: use the mysqldump command line from your MariaDB bin folder to generate such dumps.

Please login to leave a reply, or register at first.