0

OK, I have had a catastrophic crash of mysql on my VPS and I have been trying for 2 days to work out a solution withut losing my data (this is only a mysql issue - website data is all fine.

Basically, I tried to login using phpmyadmin to my databases and I got an error (can't remember exactly) so I SSH'd into the server to check things. I tried to restart the mysql server and I got this error

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

I simply cannot locate an exact error of why this is happening. The journal simply states:

mysql.service: Control process exited, code=exited, status=1/FAILURE systemd[1]: mysql.service: Failed with result 'exit-code'. systemd[1]: Failed to start MySQL Community Server.

So I gave up trying to get mysql running and started focusing on trying to recover the database data. I found all the 'data' in /var/lib/mysql which contains all the database folders as well as the ibdata file. THERE ARE NO .FRM FILES. (I believe that mysql 8 does not create frm files)

So my question mainly relates to how I can extract the raw data from these database files (.ibd files) with no .frm files? Everywhere I have read involves recreating the tables with the CREATE TABLE command but I don't know the structure of the table in the first instance to create the table? Can I extract the structure of the tables from any of the data I have?

Any help would be appreciated. Thanks.

Improve this question
6
  • It will probably be much easier to understand why MySQL won't start, and resolve that. To locate the error, you should probably look in the MySQL log file instead of the syslog.
    – mustaccio
    Commented Sep 8 at 12:40
  • Thanks. I tried looking for a logfile in /var/log/mysql but there is nothing in there - no log file at all.
    – Donald
    Commented Sep 8 at 12:51
  • find the mysql.ini/cnf and look where error.log file is created you could laso look with find for it
    – nbk
    Commented Sep 8 at 15:41
  • OK, I've managed to solve this myself through a lot of researching online (as you do!). I stumbled across a python script that will extract the data ONLY from an .ibd file and create the CREATE TABLE script and the INSERT script to populate the data. github.com/ddcw/ibd2sql - superb!
    – Donald
    Commented Sep 8 at 15:42
  • if you have the exact version as the soource you can copy the files, else you can try to repaor the dartabase
    – nbk
    Commented Sep 8 at 20:10

1 Answer 1

Reset to default
0

I have had a catastrophic crash of mysql on my VPS and I have been trying for 2 days to work out a solution without losing my data

Short Answer: Restore from the Backups that you take for exactly this situation.

If you had proper backups of your database, you could have been up and running again in a matter of hours (perhaps even minutes, depending on the size of your database) and with a lot less heartache and soul-searching (and web-searching).

Trying to get your database running again after an unknown, "catastrophic", failure is akin to driving you car, at full speed, into a brick wall and then hoping that you can just get back into it and drive it away. You have no idea what went wrong or what dangers are lurking under the hood.

This is why a planned Recovery Strategy, supported by the Backups you take, is absolutely essential.

Improve this answer
1
  • Thanks Phill, and yes, I can only agree with you, but your comments are not very helpful in the current situation where I desparately need to get several databases put back together with no backup. I am usually very good with backups so I cannot explain how I missed this one so badly!! Rest assured this has taught me a big lesson!
    – Donald
    Commented Sep 9 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.