Helper command to export data for an arbitrary mysql query into a CSV file.

mysql2csv

#!/usr/bin/env php
<?php
$intro = <<<TEXT
Helper command to export data for an arbitrary mysql query into a CSV file.
Especially helpful if the use of "SELECT ... INTO OUTFILE" is not an option, e.g. 
because the mysql server is running on a remote host.

Usage example:
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"

cat /tmp/result.csv
TEXT;

$opts = [
    "query"     => ["q", "query", ":", null, "The query string to extract data from mysql.", true],
    "host"      => ["h", "host", ":", "127.0.0.1", "The hostname of the mysql server.", false],
    "database"  => ["D", "database", ":", null, "The default database.", false],
    "port"      => ["P", "port", ":", "3306", "The port of the mysql server.", false],
    "user"      => ["u", "user", ":", null, "The username to connect to the mysql server.", false],
    "password"  => ["p", "password", ":", null, "The password to connect to the mysql server.", false],
    "file"      => ["F", "file", ":", "php://stdout", "The filename to export the query result to ('php://stdout' prints to console).", false],
    "delimiter" => ["L", "delimiter", ":", ",", "The CSV delimiter.", false],
    "enclosure" => ["C", "enclosure", ":", "\"", "The CSV enclosure (that is used to enclose values that contain special characters).", false],
    "escape"    => ["E", "escape", ":", "\\", "The CSV escape character.", false],
    "null"      => ["N", "null", ":", "\\N", "The value that is used to replace NULL values in the CSV file.", false],
    "header"    => ["H", "header", ":", 1, "If '0', the resulting CSV file does not contain headers.", false],
    "help"    => ["", "help", "", null, "Prints the help for this command.", false],
];

$options  = "";
$longopts = [];

foreach ($opts as list($option, $longopt, $modifier)) {
    $options    .= $option . $modifier;
    $longopts[] = $longopt . $modifier;
}

// resolve command line options
$actualOptions = getopt($options, $longopts);

$printHelp = function($intro, $opts){
    $str = [];
    foreach ($opts as list($option, $longopt, $modifier, $default, $description,$required)){
        $requiredStr = "";
        if($required){
            $requiredStr = "[required] ";
        }
        $s = [];
        if(trim($option) !== ""){
            $s[] = "-$option";
        }
        if(trim($longopt) !== ""){
            $longoptStr = "--{$longopt}";
            if($modifier == ":"){
                $longoptStr .= "=name";
            }
            elseif($modifier == "::"){
                $longoptStr .= "[=name]";
            }
            $s[] = $longoptStr;
        }
        $s = implode(",",$s);
        $defaultStr = "";
        if(trim($default) !== ""){
            $defaultStr = "(Default: $default) ";
        }
        $str[] = "\t$s\t{$requiredStr}\n\t\t{$defaultStr}{$description}";
    }
    $helpString = $intro."\n\nOptions:\n".implode("\n",$str);
    return $helpString;
};

/**
 * Get the actual value of the given $optionKey based on the defined
 * short option / long option. Short is preferred over long.
 * @param $optionKey
 * @return string|null
 */
$resolveOption = function ($optionKey) use ($actualOptions, $opts) {
    list($option, $longopt, $_, $default) = $opts[$optionKey];
    $actualOption = $actualOptions[$option] ?? $actualOptions[$longopt] ?? $default;
    return $actualOption;
};

/**
 * Resolve the DSN string for the mysql PDO connection
 * @see http://php.net/manual/de/ref.pdo-mysql.connection.php
 * @return string
 */
$resolveDsn = function () use ($resolveOption) {
    $availableDsnArgs = [
        "host"   => "host",
        "port"   => "port",
        "dbname" => "database",
    ];
    $actualArgs       = [];
    foreach ($availableDsnArgs as $arg => $optionKey) {
        $actualOption = $resolveOption($optionKey);
        if ($actualOption !== null) {
            $actualArgs[] = $arg . "=" . $actualOption;
        }
    }

    $dsn = "mysql:" . implode(";", $actualArgs);

    return $dsn;
};

// resolve the given arguments / options
$help        = $resolveOption("help") !== null;
if($help){
    die($printHelp($intro, $opts)."\n");
}

$dsn         = $resolveDsn();
$user        = $resolveOption("user");
$passwd      = $resolveOption("password");
$query       = $resolveOption("query");
$file        = $resolveOption("file");
$delimiter   = $resolveOption("delimiter");
$enclosure   = $resolveOption("enclosure");
$escape_char = $resolveOption("escape");
$null        = $resolveOption("null");
$hasHeader   = $resolveOption("header") == 1;

// validate input
if (trim($query) === "") {
    list($option, $longopt) = $opts["query"];
    die("Query (-$option, --$longopt) must not be empty!\n");
}
if (trim($file) === "") {
    list($option, $longopt) = $opts["file"];
    die("File (-$option, --$longopt) must not be empty!\n");
}

try{

// get data from mysql
$db   = new PDO ($dsn, $user, $passwd,[
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]
);
$stmt = $db->prepare($query);
$stmt->execute();
}catch(\Exception $e){
    die("Error: ".$e->getMessage()."\n");
}

// write to CSV file
// @see http://php.net/manual/de/splfileobject.fputcsv.php
$headers = null;
$file    = new SplFileObject($file, 'w');

/**
 * @var array|bool $row
 */
while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
    if ($hasHeader && $headers === null) {
        $headers = array_keys($row);
        $file->fputcsv($headers, $delimiter, $enclosure, $escape_char);
    }
    if ($null !== null) {
        // replace NULL values
        foreach ($row as $k => $v) {
            if ($v === null) {
                $v = $null;
            }
            $row[$k] = $v;
        }
    }
    $file->fputcsv($row, $delimiter, $enclosure, $escape_char);
}

Install via

wget https://gist.githubusercontent.com/paslandau/37bf787eab1b84fc7ae679d1823cf401/raw/29a48bb0a43f6750858e1ddec054d3552f3cbc45/mysql2csv -O mysql2csv -q && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && chmod +x mysql2csv

(download content of this gist, check checksum and make it executable)

Usage example
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"

generates file /tmp/result.csv with content

foo,bar
1,2

Print help via
./mysql2csv --help

Any specific PHP settings/requirements? Our of the box it fails on PHP 5.3.29 (cli) with PHP Parse error: syntax error, unexpected '[' in /home/xxxxx/mysql2csv on line 14

@AjaxOdessa, the short array syntax is supported on php-5.4+. The php-5.3 will not support this syntax.

You can see more details about this comment on official PHP website.

PHP 7 is required because of line 81:

    $actualOption = $actualOptions[$option] ?? $actualOptions[$longopt] ?? $default;

PHP 7 is required because of line 81:

    $actualOption = $actualOptions[$option] ?? $actualOptions[$longopt] ?? $default;

You can recode this statement old-school for PHP <7

$actualOption = ( isset($actualOptions[$option]) ? $actualOptions[$option] : ( isset($actualOptions[$longopt]) ? $actualOptions[$longopt] : $default ) );

In the case of an exception, returning a non-zero error code would be very helpful. For example, using echo() with exit(1) instead of die() when catching PDO exceptions, like

echo("Error: ".$e->getMessage()."\n");
exit(1);

would allow error checking in a script context like,

if [[ $(mysql2csv ...) ]]
then
    <ring the bell>
fi

Thanks @paslandau for sharing.
Watch out when pulling large datasets. If the script is running out of memory, set the following flag in the PDO constructor, after line 140:

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false