Category: mysqldump

Categories
MySQL mysqldump PHP

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.