This error occurs on any operating system (windows, linux). The problem is that instead of getting a sql file with the database data you get a empty (0 kb.) file.
So we have the following code:
$command = "mysqldump --opt --skip-extended-insert --complete-insert -h ".$DB_HOST." -u ".$DB_USER." -p ".$DB_PASS." ".$DB_NAME." > backup.sql";
exec($command, $ret_arr, $ret_code);
echo "ret_arr: <br />";
print_r($ret_arr);
and we get an empty file and no output.
So we will fix this error in a few steps:
1. First we need to make sure that we have access to mysqldump command. For Linux machines this command is accessible from anywhere if not you will have to find the place where mysqldump file is (usually the bin folder of mysql).
In order to do this we have to get some output from our command so we will strip all the options from the command and we will remain with this:
$command = "mysqldump"; // mysqldump.exe on Windows
So execute the php script. It’s ok if you get output like this:
Array ( [0] => Usage: mysqldump [OPTIONS] database [tables] [1] => OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] [2] => OR mysqldump [OPTIONS] --all-databases [OPTIONS] [3] => For more options, use mysqldump --help )
If you don’t see something like that then you must check to see the path to the mysqldump command.
If you are in windows make sure you append the full path to the command. If you have a folder like I have C:\\Program Files\\MySQL\\MySQL Server 4.1\\bin\\mysqldump.exe with spaces in it you must make sure that you enclose the command between quotes like this:
$command = "\"C:\\Program Files\\MySQL\\MySQL Server 4.1\\bin\\mysqldump.exe\" --opt --skip-extended-insert --complete-insert -h ".$DB_HOST." -u ".$DB_USER." -p ".$DB_PASS." ".$DB_NAME." > backup.sql";
If append the right path to the command and you still cannot get the output then this article can’t help.
2. Make sure you have the rights to create the sql file. This step is mostly for Linux machines where it is very possible that you may try to create a file from php in a folder where you don’t have writing rights.
So to test this after the previous step is done you can do the following: append to the previous command extra options so that the output is not returned but instead written in a file. So we have the previous command:
In Windows:
$command = "\"C:\\Program Files\\MySQL\\MySQL Server 4.1\\bin\\mysqldump.exe\" > backup.sql";
In Linux:
$command = "mysqldump > backup.sql";
After running the file “backup.sql” should be created.
3. You must now correct your statement. This means that we must use the long versions of the options like this:
Windows:
$command = "\"C:\\Program Files\\MySQL\\MySQL Server 4.1\\bin\\mysqldump.exe\" --opt --skip-extended-insert --complete-insert --host=".$DB_HOST." --user=".$DB_USER." --password=".$DB_PASS." ".$DB_NAME." > backup.sql";
Linux:
$command = "mysqldump --opt --skip-extended-insert --complete-insert --host=".$DB_HOST." --user=".$DB_USER." --password=".$DB_PASS." ".$DB_NAME." > backup.sql";
The fix: The options for mysqldump when called from php must be in the longer version. Instead of –u use –user, instead of –p use –password and so on.
February 20th, 2009 at 2:12 pm
No, this doesn’t work…in Windows
February 20th, 2009 at 2:12 pm
Here is the code I wrote that proves this…
isAuthorized())
{
$this->redirect($this->getConfig(‘login_page’));
}
// Get requirements and verify they loaded correctly
if (!stristr($_SERVER['HTTP_REFERER'], ‘pages/adminManager.php’))
{
$this->append($this->error(‘Access denied to this location’));
return;
}
// Get the database credentials
$db = new db();
$host = $db->getHost();
$dbname = $db->getDbName();
$user = $db->getUserName();
$pass = $db->getPass();
// Create the dump filename
$date = date(‘h:i:s–m-d-Y’);
$backup_path = “../db_dumps/{$host}-{$dbname}-{$date}.sql”;
// Attempt the backup for a Linux environment
$command = “mysqldump -f -C -K –host={$host} –user={$user} –password={$pass} $dbname >{$backup_path}”;
exec($command, $output, $error_code);
// Check for errors
if ($error_code != 0)
{
// Get the path to the mysqldump program
$command = ‘dir /b/s \mysqldump.exe’;
exec($command, $output, $error_code);
// Check for errors
if ($error_code != 0)
{
// Something is wrong but we don’t know what
$this->append($this->error(“$command failed with an error code of $error_code”));
return;
}
// Windows refuses to work with dashes and or colons in the file name
$date = date(‘hismdY’);
$base_path = $this->getConfig(‘base_dir’);
$backup_path = “{$base_path}db_dumps/{$host}{$dbname}{$date}.sql”;
// Windows needs back slash paths instead of forward slash paths
$path_array = explode(‘/’, $backup_path);
$backup_path = implode(‘\\’, $path_array);
// Attempt the backup for a Windows environment
$command = “\”{$output[0]}\” -f -C -K –host={$host} –user={$user} –password={$pass} $dbname >\”{$backup_path}\”";
exec($command, $output, $error_code);
// Check for errrors
if ($error_code != 0)
{
// Something is wrong but we don’t know what
$this->append($this->error(“$command failed with an error code of $error_code”));
return;
}
if (!file_exists($backup_path))
{
// The file that needs to be created cannot be
$this->append($this->error(“The path $backup_path, does not exist”));
return;
}
if (sizeof($backup_path) == 0)
{
// The file was created but no data was written to it
$this->append($this->error(“The backup file was created but not filled. Try running the command $command from cmd.”));
return;
}
}
header(“Location: {$_SERVER['HTTP_REFERER']}”);
}
}
$db_dump = new dbDump();
?>
February 20th, 2009 at 3:26 pm
Maybe you should try with the long commands for “-f -C -K” like this:
"--force --compress --disable-keys --host={$host} --user={$user} --password={$pass} "October 27th, 2009 at 10:22 am
do you have a solution for linux too ?
October 27th, 2009 at 10:33 am
Did you check that the paths are correctly entered?
$command = “/path/to/mysqldump –opt –skip-extended-insert –complete-insert –host=”.$DB_HOST.” –user=”.$DB_USER.” –password=”.$DB_PASS.” “.$DB_NAME.” > /path/to/backup.sql”;
April 17th, 2010 at 4:17 pm
I’m unsure if I agree with everything written but this was definitely informative and well-written.