Somehow moving email text to MySQL

Forum Moderators: open

Message Too Old, No Replies

Somehow moving email text to MySQL

csdude55

6:19 am on May 16, 2021 (gmt 0)

Guys, I'm having a super bad day. My server's hard drive crashed, and as far as I can tell about 20G of MySQL data is just... gone. When I log in to PMA it shows 40 tables (alphabetically a-e), but everything f-z is gone. It's not in /var/lib/mysql, and WHM shows that my server's storage is about 20G less than it was.

That's 20 years of data... private messages, usernames and passwords, personal ads, you name it. I'm having a hard time not crying.

But anyway.

I at least have most of the usernames emailed to me over the years, so I have 400,000 emails that look like:

Subject: New User
Body:
Username: csdude55
Password: foobar

These are in Outlook 365, and they all have the same format; first line is "Username: " followed by the username, next line is "Password: " followed by the encrypted password that should be in the database.

Any suggestions on how to get them from the emails to a file that I can upload to MySQL?

Kendo

6:52 am on May 16, 2021 (gmt 0)

Why no backup?

csdude55

7:06 am on May 16, 2021 (gmt 0)

The whole database was about 30G.

I have no real way of backing up 30G of data other than copying it to the same drive (which is pointless in a crash) or downloading it to my computer via FTP. I tried that in December, but with my 3MB internet service it was going to take about a week to complete.

Either way, it's too late now! The best I can do is work with the email copies that I have.

lammert

10:15 am on May 16, 2021 (gmt 0)

It is not too late.

Is the same hard drive still in the server? Good chance that just the directory entries of /var/lib/mysql have an error, but all the data is still in place. Errors in sectors of directories are quite common, because these are the parts of a disk most often changed.

First step is to not do anything that may overwrite sectors with existing data. Stop your services. Don't add any data. Best is to switch the server off completely. It is currently useless anyway.
Second step is to find someone with in-depth knowledge of the OS and with access to the necessary tools to do a sector-by-sector copy of the disk and recover data.
Third step is to remove the disk and add it as a secondary disk to another server.
Fourth step is to copy the disk track by track to a brand new disk. There are some free utilities around which will try to recover as much as possible, even when part of the sectors of a track are unreadable.
Fifth step is to run data recovery tools on the new disk (not on the damaged one!)

I have done this before in the past. You may lose some bytes here and there, but chances are pretty high that you will be able to recover almost everything with a few days of work. Except when the electronics is damaged (SSD or spinning rust) or your arm in the disk is mechanically damaged, most crashes are caused by local failures of the magnetic media. All the data which is not near that affected location is still safe.

The main thing you have to do is not mess with the original disk. It is partly damaged and excessive runs of recovery tools will likely break it entirely. Instead, do a gentle copy (track by track copies are not a real strain on a disk) and do all your recovery work on a disk which can handle the stress.

csdude55

6:01 pm on May 16, 2021 (gmt 0)

Unfortunately, the server isn't here locally, it's from a provider. I sent this to them, and they said that they do not plan to replace the hard drive as they think it was a "virtual disk image issue. No plans to replace the drive because it doesn't show as failing or in danger of failing".

They didn't comment on doing a sector-by-sector copy of the disk and recovering data.

I found a MySQL backup from December, so right now I'm working to import that and recover what I can, then work from there.

I found in Outlook that I can go to File > Open & Export > Import/Export and then export the emails to a .CSV (which is just text). So after I get the database restored, I'll write a PHP script to read through the .csv file and rip it in to arrays that I can insert to MySQL.