Search code examples
mysqldatabasephpmyadminappserver

Not being able to reach MySQL data

I've been using AppServ which includes MySQL, PHP, Apache24 and phpMyAdmin features.

One day, I accidentally uninstalled my AppServ service from my computer and I had to install it yet again. Everything was OK until I saw my database is not working correctly as it used to be.

I'm somehow not able to reach my data so are the websites I made before the accident happened. When I go to my phpMyAdmin, I can clearly see the structures I made but not their content which is the issue. In fact, they're all standing there when I see them in the directory.

Here you see it says the data doesn't exist but it actually does

This is a view from the directory and it all seems good I suppose

Thanks in advance.

NOTE: The AppServ uninstalling process does not remove your own data.

Solution

  • To recover database from .frm and contents from .ibd You have to do following:

    1) create temporary database to avoid suddenly working with other databases.

    CREATE DATABASE soru_sor_recover;

    2) generate CREATE TABLE dumps from .frm files (You'll need to install mysql-utilities from here);

    mysqlfrm –diagnostic answers.frm >> create_table.txt
mysqlfrm –diagnostic categories.frm >> create_table.txt
mysqlfrm –diagnostic inbox.frm >> create_table.txt
mysqlfrm –diagnostic questions.frm >> create_table.txt
mysqlfrm –diagnostic users.frm >> create_table.txt

    3) import create_table.txt using some gui or just by terminal:

    mysql -u root -p soru_sor_recover < create_table.txx

    4) now we have tables but they are empty. so we have to replace .ibd file of empty tables with ibd files of recovery. so we need to discard tablespace:

    ALTER TABLE answers DISCARD TABLESPACE; 
ALTER TABLE categories DISCARD TABLESPACE; 
ALTER TABLE inbox DISCARD TABLESPACE; 
ALTER TABLE questions DISCARD TABLESPACE; 
ALTER TABLE users DISCARD TABLESPACE;

    5) replace .ibd files in data folder and define user and group for that files:

    cp *.ibd /var/lib/mysql/soru_sor_recover
chown -R mysql:mysql /var/lib/mysql/soru_sor_recover/*.ibd

    6) restart mysql:

    /etc/init.d/mysql restart

    7) enable table spaces, go to mysql console:

    ALTER TABLE answers IMPORT TABLESPACE; 
ALTER TABLE categories IMPORT TABLESPACE; 
ALTER TABLE inbox IMPORT TABLESPACE; 
ALTER TABLE questions IMPORT TABLESPACE; 
ALTER TABLE users IMPORT TABLESPACE;

    original source of documenatation is here