MySQL Recovery

Discussion in 'ISPConfig 3 Priority Support' started by BobGeorge, May 6, 2018.

  1. BobGeorge

    BobGeorge Member HowtoForge Supporter

    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.
     
  2. ztk.me

    ztk.me ISPConfig Developer ISPConfig Developer

    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 ;) )
     
  3. BobGeorge

    BobGeorge Member HowtoForge Supporter

    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?
     
  4. ztk.me

    ztk.me ISPConfig Developer ISPConfig Developer

    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?
     
  5. BobGeorge

    BobGeorge Member HowtoForge Supporter

    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>
     
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    If you have a complete copy from /var/lib/mysql/ and a copy of the mysql config from /etc, then you should be able to restore a MariaDB or MySQL InnoDB database. I moved databases by simply copying them to another server like this several times in the past What @ztk.me mentioned are that the settings must match, so having a backup from /etc might be necessary for a restore, and the target MariaDB version should be the same.
     
  7. BobGeorge

    BobGeorge Member HowtoForge Supporter

    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.
     
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    But do you remember if you changed anything there? If not, then the settings of a new 'stock' install should wrk.

    Then you should consider to get small vm from any hoster which has the old version of the os, install mariadb there and use that to restore the data. then dump the data with mysqldump and import it into your current server.
     
  9. ztk.me

    ztk.me ISPConfig Developer ISPConfig Developer

    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.
     
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    Good to know that and especially good that they solved the problem. So my knowledge was a bit outdated :)
     
  11. ztk.me

    ztk.me ISPConfig Developer ISPConfig Developer

    till likes this.
  12. BobGeorge

    BobGeorge Member HowtoForge Supporter

    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.
     

Share This Page