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?

Solution 1

You should check the third parameter of exec function: &$return_var.

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

By convention in Unix a process returns anything other than 0 when something goes wrong.

And so you can:

if($return_var) { /* there was an error code: $return_var, see the $output */ }

Solution 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 !

Solution 3

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!

Solution 4

Because this line redirect the stdout output > /path-to-export/file.sql try this,

<?php 
exec("/usr/bin/mysqldump -u mysql-user -h 123.145.167.189 -pmysql-pass database_name", $output);
/* $output will have sql backup, then save file with these codes */
$h=fopen("/path-to-export/file.sql", "w+");
fputs($h, $output);
fclose($h);
?> 

Solution 5

As exec() is fetching just stdout which is redirected to the file, we have partial or missing result in the file and we don't know why. We have to get message from stderr and exec() can't do that. There are several solutions, all has been already found so this is just a summary.

1. Solution from Jon: log errors from mysqldump and handle them separately (can't apply for every command).
2. Redirect outputs to separate files, i.e. mysqldump ... 2> error.log 1> dump.sql and read the error log separately as in previous solution.
3. Solution from JazZ: write the dump as a subshell and redirect stderr of the subshell to stdout which can php exec() put in the $output variable.
4. Solution from Pascal: better be using proc_open() instead of exec() because we can get stdout and stderr separately (directly from pipes).

View more solutions

Comments

[Dany Khalife]

here's the link: php.net/manual/en/function.exec.php and i think its better to say it this way: "By convention in Unix a process returns anything other than 0 when something goes wrong" :)

[Dany Khalife]

a bit far fetched if you ask me

[Burak Tamtürk]

My native language is Turkish which is far away from English, that's what prevent me from writing powerful answers. As he says the $output is empty, then i said the reason that why its empty. $output is stdout and if he use ">" (which means redirect stdout to file) he will not have output, and that's totally logical. However there is stderr too, if there is an error $output will error in this case, even he redirected stdout to file. p.s. he asked $output

[Dany Khalife]

yes i completely agree, i just meant there is a simpler way to do this :)

[Ross McLellan]

Is there any way to know what each value of $return_var means so I can translate it to a meaningful error message? Would guess it's different for each command.

[Ross McLellan]

Thanks. I like the idea. Unfortunately just tested it and even without the > /path-to-export/file.sql redirecting the output it doesn't return the error message in the $output variable.

[Burak Tamtürk]

$output only will have the output that mysqldump gives, not a special message. If you want to use my solution, you must check the $output for a special error. If there is a error, mysqldump would return a line indicating there is a error. You need to parse this string. In other solution, if $return_var is not equal 0, the output would only have stderr which means error description.

[Burak Tamtürk]

if $return_var is not equal to zero, the $output must have error code, because programs write error to stderr which different than stdout that you redirected to a file. 'if($return_var) { die($output); }'

[Ross McLellan]

Unfortunately not. Yes, $return_var returns as a positive integer and I'm now using that to determine if the command executed successfully or if something went wrong which at least gives us some idea. However, with MySQLdump (have not yet noticed it with other commands we're running in the same way) $output is an empty array. When running the exact same command directly on the command line it gives us a relevant error telling us what permission is wrong or missing.

[Fabio Mora]

@RossMcLellan: Check this question

[Ross McLellan]

@Fabio looks great. Thank you for all your help.

[Andrew Fox]

I tried this answer but couldn't get it to work. I'm running a local WAMP server and the directory I'm running in is www/test/index.php. I've set BACKUP_PATH to "/test/" and I created a file called "mysqlydump_error.log" and placed it within the "test" directory. I'm getting the error message, but it does not display an error and the error log is also empty. I've made sure that my user has permissions too. Help?

[Andrew Fox]

Now I can get a backup file to appear, but it's blank.

[Nathan Tuggy]

@AndrewFox, Rick says 'Shouldn't the path be /www/test/ or possibly /var/www/test/ depending on your configuration? Do a pwd from the directory you want the log file placed and put that in with a trailing slash.'

[Andrew Fox]

I've got the paths correct now as the file is appearing as expected, but the file is empty.

[dmnc]

Yes, stdout has been redirected to the file but OP is expecting stderr from the command which exec can't fetch.

[hrishi]

I used above solution in codeigniter but getting error as mysqldump: Got error: 1045: Access denied for user 'username'@'localhost' (using password: YES) when trying to connect

[globalSchmidt]

Not sure why this isn't the correct answer. Simple, direct code that yields the information needed!