Big Issues with MySQL, Any Gurus?

[gokujou]

An organization I work with has had a big problem arise. They had a server with a wiki and a lot of important information they had neglected to back up and then the MySQL database died.

I worked on it and found one file that is corrupt or something and was wondering if anyone knew enough about the files to take a peak and see if it is correctable, or point me to a service that can do it for me. Thank you!

[kdd]

:( You should check the actual mysql backup and recovery stuff in the reference manual. Those files are "like encrypted" files. The files have weird ASCII characters, and messing the stuff inside would only make it more worse.

Btw, what storage engine(s) did you use? Also, check the logs of mysql, and determine at what point it crashed. If your disk crashed, it's not good. If only mysql server crashed, then you mostly still have files.

And please elaborate on "database died." What exactly do you mean by "died"? Usually you should be able to recover from any errors. For example, innodb, I believe, has the option of rollback, so you can at least retrieve most of the lost data.

Also, how did it crash? Did it crash on insert statement or something, or did the server crash due to a power failure or what exactly happened?

Man, I hope this helps, and I hope you can recover most of the stuff.
And, backup! :)

[gokujou]

Ah, well what happened is MySQL just stopped working after a reboot one day. Nothing out of the ordinary, they rebooted the machine and it came back up and we could not access or use MySQL in anyway. When you try to access it from the GUI or terminal it gives some socket error, I searched it online and all the fixes are about getting MySQL running and not fixing a crash. All the tables are there and I am just about positive that the file holds either the data for the tables or the config info. Hope that helps, if not I will post later with the actual messages and names once I can check the machine again.

Thank you!

[kdd]

Ah ha! Then I'm very sure everything (all the data) is safe and sound! If I were you, I'd first dump out all the data and back it up!

Then, on "another" mysql, I'd try to open the database, and see what happens. I've never ever tried this before, but I'd just copy the 3 files (if you have the myisam), and move it to working db, paste them in that folder, and try to access the files from that db. Since mysql ain't Oracle, this might just work.

But if you can back it up first, do that first!
:)

Edit: try connecting to mysql from command-line. Maybe the GUI program got screwed up somehow. That happens to mysql query browser sometimes.

[Charleh]

If you do end up doing a reinstall you can just copy the database files out of the 'data' directory across - you also need to copy the old database definintion file across to the new MySQL installation which lets the database know which files to load up when it initialises (otherwise the database will show up in the browser but won't be accessible)

Unfortunately I can't remember the filename but I think it's in the data directory too.

[kdd]

^ Yes, like Charleh said, if you've myisam tables, then you'll have 3 files. Just copy those files is mysql is completely inaccessible.