Using a .php file to generate a MySQL dump

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:

<?phpuse 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);