Using a .PHP File to Generate a MySQL Dump

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.

mysqldump via PHP

You should check the third parameter of exec function: &$return_var.

$return_var = NULL;
$output = NULL;
$command = "/usr/bin/mysqldump -u mysql-user -h 123.145.167.189 -pmysql-pass database_name > /path-to-export/file.sql";
exec($command, $output, $return_var);

By convention in Unix a process returns anything other than 0 when something goes wrong.

And so you can:

if($return_var) { /* there was an error code: $return_var, see the $output */ }

Create MySQL DB export via PHP

$dump = shell_exec('mysqldump --user=user --password=asdasd --host=localhost db_name');

alternatively you can use php implementation of mysqldump, available on https://github.com/clouddueling/mysqldump-php

Using PHP script: why mysqldump does not dump sql file?

Okay, actually I already solve this issues a few days later after I post the question. Unfortunately, I didn't have enough reputation to post an answer.So here we go.

Here are a few things that need to TAKE NOTE:-

1. Compulsory to use absolute path for MySQL dump.
2. Try using --opt (default option for MySQL dump). Read more here.
3. For the option, if use short form, no need to have (--). eg: --p. Use (--) when use full form. eg: --password. So use '-p' instead of '--p' (applied for others option too).
4. try 'shell_exec' or 'system' if mysqldump doesnt work on 'exec'.
5. try avoid have space between option and variable. Eg: -p$dbpass

*Also bear with mind, it involves with permission whether system command can be executed from php or not.

Generating a dump file from PHP

To discard most common issues:

• Use full paths
• Send output to a publicly writable directory

The path to mysqldump in your computer appears to have white spaces. Make sure you quote it properly:

exec('"C:\\Program Files\\MySQL\\MySQL Server 5.5\\bin\\mysqldump" -u root -ppassword maindb > c:\\DB_Dump.sql 2> c:\file.err.txt')

Export data from database to sql file with PHP

You can run a mysqldump command using PHP exec function to only export data. Ex:

exec('mysqldump -u [user] -p[pass] --no-create-info mydb > mydb.sql');

More info:

• mysqldump data only
• Using a .php file to generate a MySQL dump

---

Related Topics