dumping databases, sometimes with forced latin1

This script dates back to when I needed to dump MySQL database accessed by whatever legacy app that decided to connect as latin1 but send data as utf8 – meaning, that dumping it with usual phpMyAdmin (that connects as utf8) resulted in file that failed to properly import. Well, it did import, but the contents were double-utf8’ed … so at least part of the characters were interpreted incorrectly and I spent hours if not days trying to understand why all character conversion libraries completely fail on my input.

So now, whenever I need to dump MySQL database produced by unfriendly application, I do it from command line and twice – as utf8 and latin1, deciding later whichever dump works better. And as it happens FTP access is much easier to get than SSH… and you can find config with SQL access pretty easily… I don’t even bother myself with anything else – I just upload humpty-dump.php, configure some access variables and hit it from the web side (well, I do take care to rename it before uploading and delete it afterwards, just in case you wonder about the security implications :-).

AND, as I sometimes need to grab also database dumps from WordPress installs with only FTP access (and unwilling to bother myself with installing a database-management plugin)… here comes humpty-dump.php:

<?php

// dump database - either using WordPress config from same directory or locally configured parameters
// v 1.2 (2012-11-25) Peeter Marvet, http://tehnokratt.net

if ( is_file( dirname( __FILE__ ) . '/wp-config.php' ) ) {

    include( dirname( __FILE__ ) . '/wp-config.php' );

} else {

    define('DB_NAME', 'name');
    define('DB_USER', 'user');
    define('DB_PASSWORD', 'pass');
    define('DB_HOST', 'localhost');
    define('DB_CHARSET', 'utf8'); // NB! use latin1 on legacy systems that tend to produce unreadable dumps from phpmyadmin!

}

$backupFile = DB_NAME . "_" .date("Y-m-d-H-i-s");
$command = "mysqldump --opt ";

if ( defined ('DB_CHARSET') ) {
    $command .= "--default-character-set=" . DB_CHARSET . " ";
    $backupFile .= "_" . DB_CHARSET;
}

$backupFile .= '.gz';

$command .= "--host=" . DB_HOST . " --user=" . DB_USER . " --password=" . DB_PASSWORD . " " . DB_NAME . " | gzip > $backupFile";

echo "Dumping <strong>" . DB_NAME . "</strong> on <strong>" . DB_HOST . "</strong>... ";

echo system($command);

echo 'Done! Grab it before it rots: <a href="http://' . $_SERVER['SERVER_NAME'] . '/' . $backupFile . '">' . $backupFile . '</a>';

?>