Using a .php file to generate a MySQL dump

Question

Here's the information I have:

I am working with a Linux based system using MySQL and PHP5. I need to be able to generate a mysqldump from within a .php file, and then have that dump be stored in a file on the server in a location I would specify.

As I'm a PHP nooblet, I'd like someone to give me some assistance, guidance, or code, that would do what I require. This would have to be run remotely from the Internet.

Solution 1

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.

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

For security reasons, it's recommended to specify the password in a configuration file and not in the command (a user can execute a ps aux | grep mysqldump and see the password).

//create a temporary file
$file   = tempnam(sys_get_temp_dir(), 'mysqldump');

//store the configuration options
file_put_contents($file, "[mysqldump]
user={$user}
password=\"{$password}\"");

//execute the command and output the result
passthru("mysqldump --defaults-file=$file {$dbname}");

//delete the temporary file
unlink($file);

View more solutions

Comments

[Thomas Ward]

I will take a stab at this shortly. Is there any way to generate output on the PHP page when it has completed saying "Dump complete!" or something?

[Pascal MARTIN]

exec() will not return until the command is finished ; so, just put some echo "dump complete" after calling exec()

[dave]

@The Evil Phoenix: from php.net, exec() function reference: "To get the output of the executed command, be sure to set and use the output parameter.".

[totallyNotLizards]

This helped me today - and a note for users of osx lion: mysqldump shortcut wasn't set up for me when I installed mysql, so I had to find it - here's the path: /usr/local/mysql/bin/mysqldump

[PinoyStackOverflower]

Is this fast to execute 100Mb+ worth of database file size?

[helpse]

Your comment, jammypeach, saved my life. Please note that any1 might need this little addition: '/usr/local/mysql/bin/mysqldump -u...'

[Deebster]

Thanks, this is a handy plan B if "shell_exec() has been disabled for security reasons" (there you go, Google)

[ABA]

It not works for me. I must execute this code exec("C:\\xampp\\mysql\\bin\\mysqldump dr2014 -u root > $patName");

[ledawg]

It does work - but it produces a different output than the PHPMYADMIN mysqldump. Why ? There is data missing, while my PHPMYADMIN backup is complete.

[ANTARA]

@Ledew-de: Data should not be included in dump only if option skip_data=>true is passed as a parameter

[Thomas Ward]

Yours here returns the output to the end user does it not? I.E. upon execution it outputs it in a way you could copy/paste? If so then this wouldn't achieve what the accepted answer did - just creates the file on the server so I could grab it by scp.

[Matías Cánepa]

@ThomasW. This code does two things: it generates a file saved on server and prompt the download dialog. If you just want to store the file, then don't do the header() and passthru() part. And if you need the just the file location, you can always echo the $dir & $filename variables....No matter what you choose, you still can access the file via SCP

[pogosama]

Note for Windows users: In Windows you have to specify the complete path to mysqldump.exe, e.g. exec("C:/pathto/mysql/bin/mysqldump.exe <options as above>");

[Admin]

This works for me - and I have used it before. But on this particular server - I am having lots of trouble with permissions and no help from the admins. Is there a way to just display the output on the page? ... desperate :/

[posixpascal]

Do note that this uses deprecated mysql commands instead of mysqli.

[Accountant م]

thank you sir for the helpful information. I'm new to web developing and hosting. I heard that using exec() is not allowed on most web hosting service providers. and when I check web hosting sites features , they say that they allow cron jobs , does cron jobs mean that the host allows exec() function ? thanks , and sorry for being noob.

[alexis]

"Bugs with NULL data types" sounds like a problem for a back-up solution.

[giovannipds]

Just a note, in some servers exec and shell_exec are disabled due to security issues. Example: Umbler's servers.

[giovannipds]

Even on servers which have exec and/or shell_exec disabled by security, mysqldump-php works great! Just tried it right now on Umbler's servers and got the dump successfully. Thank you very much for the tip!

[giovannipds]

If your server has exec and shell_exec disabled, check diego's answer.

[Andrej]

this is perfect for servers that dont have mysql shell commands available also!

[Your Common Sense]

This solution is essentially flawed, as it creates incorrect SQL strings

[ban-geoengineering]

To get this to work, I had to replace mysqldump with /usr/bin/mysqldump. I also had to change password=mystrongpassword to password="mystrongpassword".

[Firas Abd Alrahman]

This will ignore indexes, foreign keys, events, functions, etc ...

[HelloWorld]

Worked for me. Upvoted! This should be the best answer, as it is the solution with less dependencies ( doesn't require mysqldump , which may not work if the user has not the priviledge to export )