Help retrieving a mySQL DB from a troubled server...

jakob · 2014-10-23 21:19:04

I’m trying to help a client retrieve a backup of their mySQL database from a server where the database connection has been lost, and have run out of ideas…

At first I thought it would simply be a matter of correcting config details or passwords but it seems that the web host has an internal problem as the database is not reachable via the proprietary admin area and returns a phpMyAdmin Error #2002 – The server is not responding (or the local MySQL server’s socket is not correctly configured). mysqldump and various manual php dump scripts likewise produce no result.

Unfortunately the web hosting company is entirely unresponsive: support tickets go unanswered, the telephone and fax numbers are not working/unresponsive (not even the tech partner contact details stored with the domain registrar) and the web hosts internal backup system fails to retrieve past system backups (which are supposed to include a database). In all my years of web design, I’ve never come across such a shoddy setup. Maybe they’ve gone bust and the servers are still running…

Do any of you experts out there have an idea how I might be able to trigger / get at a backup of the database. It’s a simple shared host, i.e. I don’t have SSH access and can’t access any php.ini settings, but I do still have FTP access and can run phpinfo. If I can’t get at it, it will be a matter of reconstructing the site from scratch from the google cache pages…

I’d be most grateful for any ideas… ~J

Bloke · 2014-10-23 23:47:54

A tricky one. Short of being able to fire up another instance of mysqld from inside a PHP file using your own my.ini, could you perhaps use the power of PHP’s shell commands to roam the server looking for the database files? Assuming they’re MyISAM tables, the MySQL manual has this to say about them:

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

So if you can write a short script that reads the URL and just does an ls -la inside the given folder, you might get lucky and find where those files are stored. There must be some standard locations where MySQL stashes stuff so if Uncle Google gives you any hints where these are, you can start there. Permissions may well be an issue. I don’t know if the MySQL engine runs as a web-compatible user; guess it depends on the host.

If you can find the files and they’re readable, you may be able to use a similar script to cp them from their location to somewhere from which you can FTP them down. It’s a long shot though.

If the DB isn’t responding it can be that it simply doesn’t have the disk space to write to the temporary files in the default location. A likely scenario if the server’s been left to rot. Can you run df or du to ascertain the status of the disk usage? If /var/lib/mysql is full or unwritable, for example, then MySQL won’t start up. Perhaps there’s a way to fire up a client and pass a command line switch to inform it of where to stash the lock file? If you have enough space (or can clear some) in a location that you can get to, you might be able to start it up that way and at least get to the data for a short period.

Past that I’m all out of ideas at present. A toughie for sure.

kuopassa · 2014-10-24 03:45:06

Although you did mention trying several PHP dump scripts, but have you tried Adminer or other similar tools?

bici · 2014-10-24 05:43:27

The website is still available. Therefore the content is being output from the SQL DB.
Running phpinfo provides what information? Is there a CMS to control the site? does it have any ouput possibilities?
are there any system db backups further up the directory chain via FTP?

adminer is mentioned here

also found this dumper

Last edited by bici (2014-10-24 06:26:23)

jakob · 2014-10-24 07:13:51

Thanks for the speedy replies. I’d tried various dumpers but not adminer. Thanks for the tip. There too, though, I get Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2) so it looks adminer can’t cross that bridge either.

It’s running on Wordpress (currently!) but as it can’t connect to the DB it only shows a ‘can’t connect’ message. There are backup directories further up the FTP tree but they are empty (yes, hidden files are set to show). There are also log directories (probably just http access logs) but the last logs are from 2012…

Stef, your ideas sound good too, but they’re all martian to me. I’ll investigate further and perhaps email you… phpinfo shows the document root as /var/www/web858/html/blog/ and ftp access starts from the slash after web858/ so no root access, I’m afraid. Would php’s shell commands get around that?

gaekwad · 2014-10-24 07:33:59

Hey Jakob – any use to you?

Bloke · 2014-10-24 08:44:51

jakob wrote #285111:

Stef, your ideas sound good too, but they’re all martian to me

I get that a lot :-)

How about trying this script I cobbled together. It’s sort of a file browser, but keep it away from prying eyes as there’s no security built into it:

<?php

if (isset($_GET['directory'])) {
   $directory = $_GET['directory'];
} else {
   return;
}

echo '<pre>';

$path_parts = pathinfo($directory);

if (is_dir($directory)) {
    echo "Entries:\n<ul>";
    $files = @scandir($directory);
    if ($files === false) {
        echo 'Unreadable directory :-(';
    } else {
       foreach ($files as $entry) {
          if (is_dir($path_parts['dirname'] . '/' . $path_parts['basename'] . '/' . $entry)) {
             if ($entry === '.') {
                 continue;
             }

             if ($entry === '..') {
                 echo '<li><a href="?directory=' . $path_parts['dirname'] . '">' . $entry . '</a></li>' ;
             } else {
                 echo '<li>Dir: <a href="?directory=' . ($path_parts['dirname'] === '/' ? '' : $path_parts['dirname']) . '/' . $path_parts['basename'] . '/' . $entry .'">' . $entry . '</a></li>';
             }
          } else {
              echo '<li><a href="?directory=' . ($path_parts['dirname'] === '/' ? '' : $path_parts['dirname']) . '/' . $path_parts['basename'] . '/' . $entry .'">' . $entry . '</a></li>';
          }
       }
    }
    echo '</ul>';
} else {
    $content = @file_get_contents($directory);
    if ($content === false) {
       echo 'Unreadable file :-(';
    } else {
        echo $content;
   }
}

echo '</pre>';

Just upload that to your site root with a suitably vague file name and run it, giving it ?directory=/some/path and you can browse your file system from the browser. Even directory=/ should work. Requires PHP 5+, btw.

Some places and files won’t have permissions so it’ll tell you that they’re unreadable. But bear in mind that some directories might say that, while lower directories inside may be readable. For example, /home is reported as unreadable because on a shared host they don’t want you seeing other user’s folder names. But /home/your-account (and everything below) is readable because that’s the home folder for the account.

It might not help, but you may be able to use it to find some useful files. If you locate anything of interest, shout and we’ll get to the next stage which is trying to copy them out.

Bloke · 2014-10-24 09:27:10

You can swap the code above for various system commands, like this:

<?php
echo '<pre>';
system('some-linux-command-here');
echo '</pre>';

Examples that may yield information are:

cat /proc/version: version info of the OS. You can cat loads of stuff in the /proc directory to reveal various bits of info about the system (e.g. proc/partitions or /proc/cpuinfo, etc).
netstat: (might take a while to run) to show network/socket usage. If there are trillions of sockets open, it might have simply saturated the socket limit or not be closing them properly. The ipcs command can give further info on shared memory usage, but it’s probably not much use.
dmesg: some systems leak boot up info in here, along with possible script or connectivity problems.
df -H: File system usage info.
free -m: memory usage.
lscpu: CPU info.
cp /path/to/source/filename /path/and/filename/where/i/want/the/file/to/go: copy the first file to the second location and filename.

One other thing I touched on earlier which might help is being able to run mysql in a different manner. There are myriad options you can try (again, testing them using system() as above), some of which might be able to bypass the connection issues. If you find one that works we can build a script to exploit that feature to get at the data.

You can put it in --debug mode which writes to a debug log file that may yield info about the specifics of the issue at hand. If you’re lucky, you might even be able to run it using --protocol=PIPE which switches to named pipes instead of sockets. That could sidestep socket saturation issues. More info here.

Hope some of that helps.

jakob · 2014-10-24 10:20:18

Thanks immensely for your various tips. Pete and bici: I’ve tried those two, and various variants of those.

Thanks stef for the script ideas. I can walk the account’s www directories with the file list script but get “unreadable file” for any other directory I put in (tried many variants from the phpinfo file).

Using

<?php
echo '<pre>';
system('some-linux-command-here');
echo '</pre>';
?>

and inserting a shell command returns nothing consistently, only the two pre tags. I tried everything you suggested and mysql in various ways as described on the page in the hope of getting debug infos or verbose output. Nothing, I’m afraid. I’ll send you a note separately…

Thanks again!

colak · 2014-10-24 11:03:10

I take it that you have no SSL access either. Do you?

jakob · 2014-10-24 14:33:13

Yiannis, unfortunately no SSL access. It’s a bargain-basement host, and that has proven to be a problem in the end.

Stef has taken a good look around too and neither of us have found a way round the errors, so I am stumped. I did at least retrieve a much older zip file which contains an sql file from a few years ago. I’ll need to investigate if it’s worth going back to that stage or simply to start over…

Morals of the story:

It’s worth paying more for a reliable host
Keep backups that are not on the same server
Clients should stay on good terms with your web-guy or girl and ensure they do a proper data handover if changing partners. Likewise, web-guys-and-girls should do the same. All I received were some account details and a string of “password reset request” emails with the info that “one of them should work”.

Thank you all for your ideas and feedback. It’s good to know you’re here!

bici · 2014-10-24 17:43:19

were you able to login into the WP backend?

+1 Keep backups that are not on the same server

Textpattern CMS

Textpattern CMS support forum

#1 2014-10-23 21:19:04

Help retrieving a mySQL DB from a troubled server...

#2 2014-10-23 23:47:54

Re: Help retrieving a mySQL DB from a troubled server...

#3 2014-10-24 03:45:06

Re: Help retrieving a mySQL DB from a troubled server...

#4 2014-10-24 05:43:27

Re: Help retrieving a mySQL DB from a troubled server...

#5 2014-10-24 07:13:51

Re: Help retrieving a mySQL DB from a troubled server...

#6 2014-10-24 07:33:59

Re: Help retrieving a mySQL DB from a troubled server...

#7 2014-10-24 08:44:51

Re: Help retrieving a mySQL DB from a troubled server...

jakob wrote #285111:

#8 2014-10-24 09:27:10

Re: Help retrieving a mySQL DB from a troubled server...

#9 2014-10-24 10:20:18

Re: Help retrieving a mySQL DB from a troubled server...

#10 2014-10-24 11:03:10

Re: Help retrieving a mySQL DB from a troubled server...

#11 2014-10-24 14:33:13

Re: Help retrieving a mySQL DB from a troubled server...

#12 2014-10-24 17:43:19

Re: Help retrieving a mySQL DB from a troubled server...

Board footer