Link to home
Start Free TrialLog in
Avatar of arghosrho
arghosrho

asked on

Mysql Database corruption

Dear Experts.
i have a simple WINDOWS  webserver with an installation of MYSQL (5.6) that hosts some wordpress Databases
one of my dabases gives the following error

, , , 2018-09-11 08:39:27 183c InnoDB: cannot calculate statistics for table "wordpress941"."wp_posts" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

i looked inside the Database folder and the file wp_posts.ibd is there but it looks apparently corrupted i have no idea how can i fix this on a windows server.
can someone please help.

restore from backup didnt help apparently the last 14 days have all been backed up with the corrupted files.
Avatar of theGhost_k8
theGhost_k8
Flag of India image

Hi,

As the message say ".ibd file is missing" if that is the case we really can't do much. You need to bring the ibd file back and then try with innodb_force_recovery options.
Avatar of arghosrho
arghosrho

ASKER

the IBD file is in the directory. i can confirm its there
Hi,

I got similar issue once
this occurred because I use the Windows rollback function to a previous day and then the file date was not marching so MySQL give that kind of error...

So what I do now is a backup of the MySQL... / data  directory prior any rollback.

To fixit If you have a backup of the DB, create a new DB name upload the data and change the name of DB in your script.
i do not have a SQL dump or file. i just have the files from the data directory
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
what i mean is at the current state the MYSQL server can see the database just not this particular table when i even try to access it from the Woribench it says table is missing. wordpress941.wp_posts      analyze      Warning      InnoDB: Tablespace is missing for table 'wordpress941/wp_posts'

so if i backed it up. it also gives an error
mysqldump: Couldn't execute 'show create table `wp_posts`': Table 'wordpress941.wp_posts' doesn't exist (1146)

i get this error when im trying to create a backup of the database
you need to run a repair on the table

within mysql workbench administrator,,...

check the table to see what the issue is then you can try repair (note data loss is possible)
https://dev.mysql.com/doc/refman/5.7/en/check-table.html

https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html
well looks like i made things worse. im left with an empty database in MYSQL server (i can see it in workbench) and the directory structure i have in my backups.
is there anyway i can use the directory structure on a windows server to restore the database?
Not sure what you did, or what you think you made worse.
One thing to make sure in a situation as you report is to make sure the OWNERSHIP top be accessible/writeable by the account under which mysql server is running on your system.....

Much depends on how you are backing them up.

DATABASEs should be backed up by tools in mysql ,it is using mysqldump. or when you backup the filesystem, mysql server MUST be stopped.



going back, what did you do that ended up having an empty database?
its a long story i tried to follow some of the advice i found in diffrent forums to restore this particular table but i ended up dropping the entire database.
now i have the directory structure of the database which is in the program data directory so all database files are here.
is there anyway to use these files to resotre the database
you can try,restore the files from the filesystem backup to a different location. stop mysql, move these files into the data folder.
start mysql and hope for the best.

filesystem backup is a snapshot in time, the data would be in an unknown state.

was this a VM? VM backups might be aware of the ....

did you run mysqldump at any time before that?

you may have to run repair on the tables ... potentially trimming data from the original, but in this case the end result if works .....
In the future, anytime you end up with database corruption, before any other operation, make a tarball of the entire /var/lib/mysql directory hierarchy.

Before going any further, place an update about exactly what types of backups you have.

So, raw /var/lib/mysql tarballs, mysqldump files, exact type of backups you have available to work with.
thanks alot for ur help