Using a .php file to generate a MySQL dump

Using a .php file to generate a MySQL dump

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

Stefan Bogdanescu

Founder & Senior Architect

Using a .php file to generate a MySQL dump

Generating a database dump from within PHP requires bridging the gap between a web request and a system-level utility like mysqldump. While you can certainly use the PDO or mysqli extensions to export data via SQL queries, those approaches are inefficient for large datasets. They consume excessive memory as they stream rows into PHP variables before attempting to write them out. For a production environment on Linux, invoking the native mysqldump command-line tool directly from your script is the standard approach.

The core challenge lies in execution context and output handling. When you run mysqldump from the command line, it writes its output to stdout by default. In a PHP script executed via a web server (like Apache or Nginx), this output will attempt to stream directly back to the browser, which is rarely what you want when creating a file on the server. You need to redirect that standard output into a specific file path.

Here is a basic implementation using shell_exec or exec. This method passes the necessary arguments to the MySQL client, specifies the database name, and redirects the stream to a target filename:

<?php
$host = 'localhost';
$db   = 'production_db';
$user = 'backup_user';
$pass = 'secure_password';
$tables = ['users', 'orders']; // Specific tables or omit for full dump
$output_file = '/var/backups/' . date('Y-m-d') . '_dump.sql';

// Build the command string
$sql_dump_cmd = "mysqldump -h {$host} -u {$user} -p{$pass} --no-data=false {$db} {$tables}";

// Execute and redirect output to a file
$result = shell_exec($sql_dump_cmd . ' > ' . escapeshellarg($output_file));

if ($result === null || $result === '') {
    // Check if the file was actually created
    if (file_exists($output_file)) {
        echo "Dump successful: {$output_file}";
    } else {
        echo "Dump failed. Check server logs for mysqldump errors.";
    }
}
?>

This snippet demonstrates the fundamental logic, but it introduces several critical security and operational considerations that must be addressed to make this viable in a real-world scenario.

Security Implications of Hardcoded Credentials

The most glaring issue with the example above is how credentials are handled. Passing the password directly into the command string or hardcoding it in the script is a severe security risk. If your PHP file is ever compromised, an attacker instantly gains access to your database. Furthermore, if this script is executed remotely via a web interface (as hinted in the source requirements), you must ensure that the php.ini directive allow_url_fopen or similar settings are configured correctly for remote execution contexts, and more importantly, that the user running PHP does not have elevated privileges they shouldn't possess.

A better practice is to use a dedicated configuration file stored outside the web root or rely on environment variables. However, if you must pass credentials via command line arguments, ensure the script runs with restricted permissions. Note that mysqldump supports the --password flag; relying on the -p flag requires the user to be prompted for input, which breaks automation unless stdin is piped or a password file is used.

Managing Output and Error Streams

The previous example uses shell redirection (>) within the command string to capture stdout. This works, but it does not capture stderr. If mysqldump encounters an error—such as a syntax issue in your SQL dump configuration or permission denial on the target directory—the output will be discarded into /dev/null, and your script will report success while silently failing.

To debug these issues effectively, you should append 2>&1 to redirect stderr to stdout before the file redirection. This ensures that any errors generated by the utility are written to the same log file or console where you expect them. Additionally, always verify that the web server process has write permissions to the target directory (e.g., /var/backups). If the directory belongs to root and your PHP script runs as www-data, the dump will fail with a permission denied error, which is notoriously difficult to diagnose without proper logging.

Remote Execution Considerations

If this script must be triggered remotely from the Internet, you are likely wrapping this logic behind an API endpoint or a form handler. In such cases, consider using HTTPS and validating that the request originates from trusted sources if possible. Also, be mindful of execution timeouts. Large database dumps can take significant time to generate. If your web server's max_execution_time or PHP's memory_limit (when buffering) is too low, the script might be killed mid-dump. Using exec() instead of shell_exec() allows you to capture the return code explicitly without blocking the script's internal flow, though for this specific use case, shell_exec is often simpler provided the timeout limits are configured appropriately.

For robust production environments where frequent backups are needed, consider moving this logic into a Cron job triggered by an external scheduler rather than relying solely on HTTP requests, which can be rate-limited or blocked by firewalls designed to prevent scraping.

Our network