Using a .php file to generate a MySQL dump

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.

 Answers

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.

---

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

---

As long as you are allowed to use exec(), you can execute shell commands through your PHP code.

So assuming you know how to write the mysqldump in the command line, i.e.

mysqldump -u [username] -p [database] > [database].sql

then you can use this as the parameter to exec() function.

exec("mysqldump -u mysqluser -p my_database > my_database_dump.sql");

---

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

---

None of the above codes worked for me. I am using windows. Below Code worked for me...

$sql = "SELECT * FROM  $tableName WHERE yourclause";
$result = $conn->query($sql);


if($result){

        if ($result->num_rows > 0) {

            $myfile = fopen("daily_events_$district.sql", "w") or die("Unable to open file!");

            while($row = $result->fetch_assoc()) {  

                $rowToString = implode("','",$row);
                $writeToFile = "INSERT INTO $tableName VALUES('$rowToString');". PHP_EOL;
                fwrite($myfile,$writeToFile);
            }
            echo "File saved successfully";
        }
    } else {
        echo "No result found";
    }

This will save file in your project folder according to your query whatever data you want. If you want to dump whole database.

---

<?php exec('mysqldump --all-databases > /your/path/to/test.sql'); ?>

You can extend the command with any options mysqldump takes ofcourse. Use man mysqldump for more options (but I guess you knew that ;))

---

To dump database using shell_exec(), below is the method :

shell_exec('mysqldump -h localhost -u username -ppassword databasename  | gzip > dbname.sql.gz');

---

<?php
    $toDay = date('d-m-Y');

    $dbhost =   "localhost";
    $dbuser =   "YOUR DB USER";
    $dbpass =   "USER PASSWORD";
    $dbname =   "DB NAME";

    exec("mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost $dbname > /home/....../public_html/".$toDay."_DB.sql");


?>