mysqldump via PHP

Question

I have a PHP script that gets passed the MySQL connection details of a remote server and I want it to execute a mysqldump command. To do this I'm using the php exec() function:

<?php
exec("/usr/bin/mysqldump -u mysql-user -h 123.145.167.189 -pmysql-pass database_name > /path-to-export/file.sql", $output);
?>

When the right login details are passed to it, it'll work absolutely fine. However, I'm having trouble checking if it executes as expected and if it doesn't finding out why not. The $output array returns as empty, whereas if I run the command directly on the command line a message is printed out telling me the login failed. I want to capture such error messages and display them. Any ideas on how to do that?

Answer 1

I was looking for the exact same solution, and I remembered I'd already solved this a couple of years ago, but forgotten about it.

As this page is high in Google for the question, here's how I did it:

<?php
define("BACKUP_PATH", "/full/path/to/backup/folder/with/trailing/slash/");

$server_name   = "your.server.here";
$username      = "your_username";
$password      = "your_password";
$database_name = "your_database_name";
$date_string   = date("Ymd");

$cmd = "mysqldump --hex-blob --routines --skip-lock-tables --log-error=mysqldump_error.log -h {$server_name} -u {$username} -p{$password} {$database_name} > " . BACKUP_PATH . "{$date_string}_{$database_name}.sql";

$arr_out = array();
unset($return);

exec($cmd, $arr_out, $return);

if($return !== 0) {
    echo "mysqldump for {$server_name} : {$database_name} failed with a return code of {$return}\n\n";
    echo "Error message was:\n";
    $file = escapeshellarg("mysqldump_error.log");
    $message = `tail -n 1 $file`;
    echo "- $message\n\n";
}
?>

It's the --log-error=[/path/to/error/log/file] part of mysqldump that I always forget about!

Answer 2

The solution I found is to run the command in a sub-shell and then output the stderr to stdout (2>&1). This way, the $output variable is populated with the error message (if any).

i.e. :

exec("(mysqldump -uroot -p123456 my_database table_name > /path/to/dump.sql) 2>&1", $output, $exit_status);

var_dump($exit_status); // (int) The exit status of the command (0 for success, > 0 for errors)
echo "<br />";
var_dump($output); // (array) If exit status != 0 this will handle the error message. 

Results :

int(6)

array(1) { [0]=> string(46) "mysqldump: Couldn't find table: "table_name"" }

Hope it helps !