mysqldump – with exec() function from php outputs empty file

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.

15 thoughts on “mysqldump – with exec() function from php outputs empty file

  1. 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();
    ?>

  2. Maybe you should try with the long commands for “-f -C -K” like this:

    "--force --compress --disable-keys --host={$host} --user={$user} --password={$pass} " 
  3. 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”;

  4. Hi,
    I tried your given window command it runs but linux command is not running from remote server. I have written code as follow:

    $strCommand = “/usr/bin/mysqldump –host=$host –user=$user –password=$password test > $path”;
    echo “————-START————-\n”;
    $strOutput = exec($strCommand,$arrOutput,$intReturnValue);
    echo “COMMAND: $strCommand\n\n”;
    echo “RETURN VALUE: $intReturnValue\n\n”;
    echo “OUTPUT: \n”;
    print_r($arrOutput);
    echo “————-END————-\n”;
    die();

    But it gives following output:

    START————- COMMAND: /usr/bin/mysqldump –host=krislinmsbt.krislinm.com –user=krislinm_msbte09 –password=(———-) test > /home/krislinm/public_html/msbte11/admin/all_control/master_control/ftp_upload/backup/text.sql RETURN VALUE: 2 OUTPUT: Array ( ) ————-END————-

    So, please advice me where i am wrong.

  5. Hi,
    It seams that return value of 2 means that this the command returned an error (look here: http://turbulentsky.com/monitor-linux-exit-codes-on-command.html).
    1. I’m not sure that the paths are correct so please check the output path.
    2. Try this command to check the mysqldump path: echo system(‘which mysqldump’);

    If all these are correct then you should try to find out the error message by using one of these methods:
    http://stackoverflow.com/questions/8352636/getting-full-output-from-php-system-function

  6. Hi,
    Thanks for your valuable suggestions. I run following code:

    1) echo system(‘which mysqldump’); It shows twice /usr/bin/mysqldump /usr/bin/mysqldump

    2) I tried to find error message but it shows blank output.

  7. I will suggest a few more things to do:

    Try this command:
    $strCommand = “echo \”test\” > $path”;
    and check if the file contains the “test” string.

    If so then it means that the error is in the command so please make sure that before each option like host, user… there are two dashes and also double check the username, password and server address.

    Lef me know if that helped.

  8. Guys a got this is quit simple
    <?php

    $i=0;
    $dbuser = "admin";
    $dbpass = "admin";
    $dbname = "pos1";
    $sendto = "Webmaster “;
    $sendfrom = “Automated Backup “;
    $sendsubject = “Daily Mysql Backup”;
    $bodyofemail = “Here is the daily backup.”;
    // don’t need to edit below this section

    $backupfile = $dbname .time().”-“.date(‘Y-M-D h:i:s’).’.sql’;

    $path_to_mysqldump = “C:\wamp\bin\mysql\mysql5.5.24\bin”;
    $query= “$path_to_mysqldump\mysqldump.exe -u$dbuser -p$dbpass $dbname> database/$backupfile”;

    exec($query);
    echo $query;

    ?>

    make sure that the path to your mysqldump sis correct

  9. Hi,
    Thanks for your script. I tried each step but still getting 0 kb files.
    and result:
    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 )

    My code :

    $back_folder = $_SERVER[‘DOCUMENT_ROOT’].’/backups’;
    $backup_file = date( “m-d-Y-H-i-s” ).”_”.$database_members.”.sql”;

    $command = “mysqldump –opt –skip-extended-insert –complete-insert –host=”.$hostname_members.” –user=”.$username_members.” –password=”.$password_members.” “.$database_members.” > “.$back_folder.”/”.$backup_file;
    exec($command, $ret_arr, $ret_code);

    I am not sure what is going wrong.

  10. global $wpdb;
    $export_posts = $wpdb->prefix . ‘export_posts’;
    $backupFile = $_GET[‘targetDir’].’export-gallery.sql’;
    $dbhost=DB_HOST;
    $dbuser=DB_USER;
    $dbpass=DB_PASSWORD;
    $db=DB_NAME;
    $path_to_mysqldump = “D:\xampp_5.6\mysql\bin”;
    $query= “D:\\xampp_5.6\mysql\bin\mysqldump.exe -u$dbuser -p$dbpass $db $export_posts> $backupFile”;
    exec($query);
    echo $query;

  11. $date = Carbon::now()->format(‘y-m-d_h-i’);
    $host = env(‘DB_HOST’,’127.0.0.1′);
    $user = env(‘DB_USERNAME’,’root’);
    $password = env(‘DB_PASSWORD’,”);
    $database = env(‘DB_DATABASE’,’kalai’);

    $command = “\”D:\\wamp\\bin\\mysql\\mysql5.6.17\\bin\\mysqldump.exe\” –user={$user} –password={$password} –host={$host} –flush-logs –single-transaction {$database} > {$date}.sql”;

    $process = new Process($command);
    $process->start();

    while ($process->isSuccessful()) {
    $local=Storage::disk(‘local’);
    $local->put(”.$date.”.sql”, file_get_contents(“{$date}.sql”));
    unlink(“{$date}.sql”);
    }
    }

    refer this link http://www.theerrormessage.com/2008/10/mysqldump-with-exec-function-from-php-outputs-empty-file/

  12. Hello,
    use 2>&1 to have access to stdout and stderr. Also set $output and $return_var.

    Example Database mysqldump with exec():
    exec(“mysqldump –user=”.$master[‘db_user’].” –password='”.$master[‘db_pwd’].”‘ -h “.$master[‘db_host’].” –opt “.$master[‘db_name’].” test_db | mysql –user=”.$cfg[‘db_user’].” –password='”.$cfg[‘db_pwd’].”‘ -h “.$cfg[‘db_host’].” “.$cfg[‘db_name’].”; >> /test/test.log 2>&1″, $output, $return_var);

    $output_exec .= “ps_product_tag | output: “.implode(” “,$output).” | return_var: “.$return_var.”\n”;

Leave a Reply

Your email address will not be published. Required fields are marked *