Using a .php file to generate a MySQL dump

Question

You can use the exec() function to execute an external command.

Note: between shell_exec() and exec(), I would choose the second one, which doesn't return the output to the PHP script -- no need for the PHP script to get the whole SQL dump as a string : you only need it written to a file, and this can be done by the command itself.

That external command will :

• be a call to mysqldump, with the right parameters,
• and redirect the output to a file.

For example :

mysqldump --user=... --password=... --host=... DB_NAME > /path/to/output/file.sql

Which means your PHP code would look like this :

exec('mysqldump --user=... --password=... --host=... DB_NAME > /path/to/output/file.sql');

Of course, up to you to use the right connection information, replacing the ... with those.

If you want to create a backup to download it via the browser, you also can do this without using a file.

The php function passthru() will directly redirect the output of mysqldump to the browser. In this example it also will be zipped.

Pro: You don't have to deal with temp files.

Con: Won't work on Windows. May have limits with huge datasets.

<?php

$DBUSER="user";
$DBPASSWD="password";
$DATABASE="user_db";

$filename = "backup-" . date("d-m-Y") . ".sql.gz";
$mime = "application/x-gzip";

header( "Content-Type: " . $mime );
header( 'Content-Disposition: attachment; filename="' . $filename . '"' );

$cmd = "mysqldump -u $DBUSER --password=$DBPASSWD $DATABASE | gzip --best";   

passthru( $cmd );

exit(0);
?>

Take a look here: https://github.com/ifsnop/mysqldump-php ! It is a native solution written in php.

You can install it using composer, and it is as easy as doing:

<?php

use Ifsnop\Mysqldump as IMysqldump;

try {
    $dump = new IMysqldump\Mysqldump('database', 'username', 'password');
    $dump->start('storage/work/dump.sql');
} catch (\Exception $e) {
    echo 'mysqldump-php error: ' . $e->getMessage();
}

?>

It supports advanced users, with lots of options copied from the original mysqldump.

All the options are explained at the github page, but more or less are auto-explicative:

$dumpSettingsDefault = array(
    'include-tables' => array(),
    'exclude-tables' => array(),
    'compress' => 'None',
    'no-data' => false,
    'add-drop-database' => false,
    'add-drop-table' => false,
    'single-transaction' => true,
    'lock-tables' => false,
    'add-locks' => true,
    'extended-insert' => true,
    'disable-foreign-keys-check' => false,
    'where' => '',
    'no-create-info' => false
);

Please reffer to the following link which contains a scriptlet that will help you: http://davidwalsh.name/backup-mysql-database-php

Note: This script may contain bugs with NULL data types