Export MySQL database using PHP

To export a MySQL database using PHP, you can use the mysqldump command-line utility or the mysqli extension. Here's an example of how to use the mysqli extension to create a backup of a single table:

Example of exporting a MySQL table using PHP

<?php
// Connect to the database
$conn = mysqli_connect('host', 'username', 'password', 'database');

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// Export a single table
// Note: INTO OUTFILE writes to the MySQL server's filesystem and requires the FILE privilege.
// It generates tab-separated values, not SQL statements.
$table = 'users'; // Replace with your table name
$sql = "SELECT * INTO OUTFILE '/path/to/mysql-writable-dir/backup.tsv' FROM $table";

if (mysqli_query($conn, $sql)) {
  echo "Table backup successfully created!";
} else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Close the connection
mysqli_close($conn);

This example exports the $table to a TSV file. Note that SELECT INTO OUTFILE only handles one table at a time, and the path must be writable by the MySQL server process. The database user also requires the FILE privilege.

Alternatively, you can use the mysqldump utility to create a full database backup like this:

Example of using "mysqldump" utility to create a backup of the MySQL database using PHP

<?php
$username = 'username';
$password = 'password';
$dbname = 'database';
$outputFile = '/path/to/backup.sql';

// Use --password= to pass credentials securely (avoids deprecated MYSQL_PWD)
$command = sprintf('mysqldump -u %s --password=%s -B %s > %s',
    escapeshellarg($username),
    escapeshellarg($password),
    escapeshellarg($dbname),
    escapeshellarg($outputFile)
);
$output = [];
$return_var = 0;
exec($command, $output, $return_var);

if ($return_var === 0) {
  echo "Database backup successfully created!";
} else {
  echo "Error: mysqldump failed.";
}

This will create a backup of the database in the file /path/to/backup.sql. The -B (or --databases) flag is included to ensure the generated SQL contains CREATE DATABASE statements. You can adjust the path and filename to suit your needs.

Note that both of these examples assume that you have the necessary permissions to create files on the server and that the mysqldump utility is available on the server.

Triggering a browser download If you want to trigger a browser download of the generated backup directly without saving it to the server first, you can stream the mysqldump output directly to the browser:

<?php
$username = 'username';
$password = 'password';
$dbname = 'database';

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="backup.sql"');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');

$command = sprintf('mysqldump -u %s --password=%s -B %s',
    escapeshellarg($username),
    escapeshellarg($password),
    escapeshellarg($dbname)
);
passthru($command);
exit;

This approach streams the SQL dump directly to the user's browser, eliminating the need for intermediate server storage.