MySQL Recovery

The hard drive on my laptop died.

Just before it gave up the ghost, I was able to rapidly copy the important data off it onto a USB stick.

Grabbing /var/www, /var/lib/mysql, my home directory, /usr/local/ispconfig (as I've made a few custom changes) and so forth. Whereas most of these are recovered by nothing more than copying the files to the right places, "/var/lib/mysql" is a problem. The files in this directory are mostly, if not entirely, "InnoDB" databases.

You can't just simply copy these into "/var/lib/mysql" and be done with it, as there's some sort of index files in the root of "/var/lib/mysql" that has to correspond with the actual database files. Trust me, I tried it and got nothing but errors in phpMyAdmin for my troubles. Indeed, it basically broke the mariadb-mysql installation rather horribly and I had to purge everything back to square one and reinstall from scratch, just to get mysql functioning again.

Had this not been a mass file copy in a panic - I had to do it from a Live CD, in fact, as the disk failure was already preventing a clean boot - then I'd have done this in the more proper way of using "mysqldump" to write out a neat ".sql" file that I could just replay to restore the databases. But the disk was rapidly failing - no longer booting - so I'm rather lucky I even managed to just barely scrape off what I did by just copying all the crucial directories as quickly as I could.

Is there any sort of tool that can take the raw data files in "/var/lib/mysql" and recreate a nice set of ".sql" files out of them?

I'd like to get these sites / databases back because I was using my laptop for website development, you see. A few "work in progress" things that I'd rather not have to completely recreate from scratch, as it took a while to create them in the first place. Particularly when, logically, all the data is right there in the files. It's just a matter of working out how to get it all back out of there in a useful format.

 

have you copied the db.opt files and stuff off /var/lib/mysql dir?
It is possible to recover, maybe nowdays google helps better than back then.
Has something to do with stuff mentioned on
https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/

so, create a discardable server instance with copied in mysql directory ( don't forget to check on permissions )
check/save logfiles of what kinf od issue it has / try some of the steps.

Maybe this might help you https://pozniak.pl/it/how-to-recover-mysqlmariadb-after-innodb-storage-corruption/
which however handles fixing same server and only works if you manage to start server in recovery mode. (worked for me several times )

 

I copied everything. The whole directory.

I did this. Installed a brand new mariadb instance and then renamed its "/var/lib/mysql" to something else and then copied my backup "/var/lib/mysql" in its place. I went through and made sure that the ownership and permissions matched what I saw in the new instance's "/var/lib/mysql".

I restarted the service and then tried to login as root. Password didn't work. So I used the usual tricks for resetting the root password and got into the mysql prompt.

"show databases;" showed all the databases and "show tables;" - once I'd done a "use" to select a database - showed the tables.

It looked good until I tried any operation on those tables. Apparently, though they're listed by mysql itself and the files are literally copies of what was there before, the tables "don't exist".

"mysqlcheck" also refuses to even attempt repairing them. Even though, umm, shouldn't something like this be entirely what a tool like that should be designed for?

This, quite frankly, is a bit ridiculous. The whole point of databases is to store data. To have it that it's so difficult to restore that data is stupid design.

But then I saw - whilst googling for answers - that there are companies out there who charge hundreds of dollars to recover data from databases and then it all becomes apparent, I feel. It's deliberately complicated to keep certain people in jobs and money?

 

So you have tried the recovery options?
Was reason for the password issue, you changed authentication plugin to password like in ispconfig setup suggested?
Have you changed other settings regarding innodb and made sure you have them in place on your recovery-d?

The storage format is well documented - I don't think theres much conspiracy in there.

What did the verbose mysql logfile say? did it accept the "old" innodb files? did it create new ones?

 

I have tried some of the recovery options. I won't claim I've exhaustively tried everything, but I gave it a good, albeit basic, try.

Probably.

The "perfect server" I'm following (for Ubuntu 16.04 Apache) doesn't actually mention this issue, but I've hit the problem numerous times now that I know to turn the plugin="unix_socket" on for install of phpmyadmin and such, then turn it off afterwards to make things work smoothly.

In order to try this, the only things I did was install a brand new fresh copy of Mariadb and then swap my backup "/var/lib/mysql" for the one it installed (then ensured all the permissions and ownership were correct).

<rant>
The fact that in UNIX - where "everything is a file" - that a complete file swap like this does not work is the stupid design, I feel. Okay, my conspiracy theories are borne out of deep frustration with this and probably are not remotely true. But this just so reminds me of the bloody "Windows registry" and other hidden "blackbox" options that proprietary software uses to lock you in and make you pay money, my suspicions were aroused.

A complete file swap should work in a transparent "everything is a file" operating system. There shouldn't be any magical "hidden" opaque options. Earlier, I uninstalled a Mariadb instance and then reinstalled a new one, but yet there were previously settings still being remembered somewhere - we do not need nor want no "Windows registry" here, thank you very much.

(Anyway, documenting storage formats doesn't totally debunk my theories - Adobe's Flash storage format or Oracle's Java VM format is openly documented - and very well-documented at that, to be fair - but that doesn't stop them being proprietary, lock-in or mean that Google doesn't get sued. Unnecessary over-complexity can be as good a lock-in as DRM. Indeed, if you think about it, that's really what DRM is in a technical sense. But, whatever, this isn't the forum for ranting and I've stopped being so irritated now.)
</rant>

 

I have that.

But I don't have that, unfortunately.

Forgot about that in my panic to just rapidly copy whatever I could before the drive dropped dead (and all that copying itself on a dying drive was itself a "fingers crossed" affair to grab what I got).

And this is another issue.

As mentioned, I also upgraded to 18.04 - seeing as I had to start from scratch anyway, I started with the latest version - which I'm pretty sure comes with a newer version of MariaDB.

 

I think the innodb size differences issue has been gone after some version, however if you used
innodb_file_per_table on the old and not on the new, the new server gets wrong instructions, read out of a file

It is crucial to use the same config, you need to remember wether nd what might have been tuned or maybe ( if you are lucky ) it is just the difference between the stock config from different ubuntu versions.
Have a look at the output of mysql daemon at startup, either by checking the log or by running mysqld from command line.

 

I hope I don't mix this up now but starting with 5.7 ( mysql ) it shouldn't be an issue anymore - I'd need to look up which/if mariadb version has that, too.
https://mysqlserverteam.com/whats-new-in-mysql-5-7-generally-available/
https://dev.mysql.com/worklog/task/?id=6117

 

Not directly, no.

I myself made no changes to "/etc/mysql", I'm pretty sure. So unless some other program made any changes on my behalf, as it were, then it should, indeed, just be a stock Ubuntu 16.04 configuration.

I'll look into doing that and let you know if that proves any more successful.