Export MySQL database using PHP

Export MySQL database using PHP

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Export MySQL Database Using PHP: From Script to User-Controlled Backup

The provided script demonstrates a basic approach to exporting a MySQL database by iterating through tables, fetching their structure via SHOW CREATE TABLE, and then dumping row data using SELECT *. However, the current implementation hardcodes the file destination to 'backup.sql' and writes directly to the filesystem. This creates two immediate problems: it forces a specific directory that may not exist or be writable by the web server, and it prevents the user from initiating an immediate download without configuring client-side headers. Furthermore, restoring requires uploading a file, which introduces security risks if not handled correctly.

To address these issues, we need to decouple the backup generation logic from the file system write operation and implement a controlled interface for both export and import.

Generating SQL with Specific Destination Control

The original code uses fopen() to write directly to a file handle. While functional for server-side backups, this approach is insufficient when the user needs to control the location or trigger an immediate download. A more robust solution involves building the entire SQL dump into a memory buffer (using ob_start()) and then outputting it with appropriate HTTP headers.

When the user clicks an "Export" button, your PHP script should generate the SQL string exactly as the original code does, but instead of writing to disk immediately, you send the response headers first:

<?php
// Ensure no other output is sent before these headers
header('Content-Type: application/sql; charset=utf-8');
header('Content-Disposition: attachment; filename="database_backup.sql"');

$DB_HOST = "localhost";
$DB_USER = "root";
$DB_PASS = "admin"; // In production, use environment variables or .env files
$DB_NAME = "dbname";

$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

$tables = [];
$result = mysqli_query($con, "SHOW TABLES");
while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}

$return = '';

foreach ($tables as $table) {
    // Drop existing table to ensure a clean state in the backup
    $return .= 'DROP TABLE IF EXISTS `' . $table . '`;';
    
    // Get structure
    $row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE `' . $table . '`'));
    $return .= "\n\n" . $row2[1] . ";\n\n";

    // Fetch data
    $result = mysqli_query($con, "SELECT * FROM `" . $table . "`");
    $num_fields = mysqli_num_fields($result);

    if ($num_fields > 0) {
        while ($row = mysqli_fetch_row($result)) {
            $return .= 'INSERT INTO `' . $table . '` VALUES(';
            for ($j = 0; $j < $num_fields; $j++) { 
                // Escape data properly for SQL injection prevention
                if (isset($row[$j])) {
                    $val = addslashes($row[$j]);
                    $return .= '"' . $val . '"';
                } else { 
                    $return .= '""';} 
                
                if($j < $num_fields-1){ $return .= ','; }
            }
            $return .= ");\n";
        }
    }
}

echo $return;
$con->close();
?>

By using Content-Disposition: attachment, the browser will prompt the user to save the file, allowing them to choose a specific folder on their local machine. This eliminates the need for write permissions in the web root or server configuration files.

Handling Restoration and File Uploads

For the restoration process, you cannot rely on direct SQL execution against an uploaded file due to potential security vulnerabilities (e.g., DROP DATABASE statements hidden in the file). Instead, use a library like doctrine/dbal or manually sanitize the input, but the safest method for a custom script is to read the file content and verify its integrity before executing.

If you wish to keep the complexity low as mentioned in the source material, ensure the upload form uses enctype="multipart/form-data". Upon receiving the file via $_FILES['backup'], save it to a secure temporary directory that the web server can read but not execute scripts from, then read the content into a string variable.

// Pseudocode for restoration logic
$uploadFile = $_FILES['backup']['tmp_name'];
$content = file_get_contents($uploadFile);

// Basic security check: Ensure no dangerous commands are present if not using a library
if (stripos($content, 'DROP DATABASE') !== false || stripos($content, 'DROP SCHEMA') !== false) {
    die("Invalid backup file detected.");
}

// Execute the SQL string against your target database
$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if ($con->query($content)) {
    echo "Restoration successful.";
} else {
    echo "Error during restoration: " . $con->error;
}

Character Set Considerations

The source material specifies a utf8_general_ci collation with English, French, and Italian content. The provided code uses addslashes() for escaping. While this works for simple ASCII data, it does not handle UTF-8 multi-byte characters correctly in all contexts and relies on the client's connection charset matching the database.

To ensure proper handling of accented characters (e.g., é, à, ì) without complex code, you must explicitly set the character set in the connection:

$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
$con->set_charset("utf8_general_ci"); 

This guarantees that the SELECT * queries return the correct byte sequences for your multilingual data. Without this, special characters might appear as garbled text or cause syntax errors during the restore process if the target database has a different collation.

Conclusion on Implementation Strategy

By moving from direct file writes to HTTP-based output and handling uploads with strict validation, you empower users to manage their backups securely regardless of their local filesystem permissions. The key trade-off here is simplicity versus robustness; while the original script was simple, it lacked control over data flow and security boundaries. Implementing headers for downloads and validating SQL content during restoration bridges the gap between a quick prototype and a production-ready tool.

Our network