Introduction

If you want a real MySQL dump from PHP, the practical solution is to let PHP call mysqldump rather than trying to reimplement the dump format manually. PHP then becomes the orchestrator that decides when the backup runs and where the file goes, while mysqldump handles the actual SQL export.

Why Calling mysqldump Is Better

mysqldump already knows how to export schema, data, and MySQL-specific options correctly. Rebuilding that logic by hand in PHP is possible for a toy export, but it quickly becomes fragile once you need complete schema handling, triggers, routines, or consistent dump behavior.

So the normal architecture is:

• PHP reads the backup settings
• PHP builds a safe shell command
• 'mysqldump generates the SQL file'
• PHP checks the exit code and records the result

That keeps each tool doing what it is best at.

Build the Command Safely

Escape every shell argument and check the exit code.

1<?php
2$host = '127.0.0.1';
3$user = 'backup_user';
4$password = 'secret';
5$database = 'app_db';
6$backupFile = __DIR__ . '/backup.sql';
7
8$command = sprintf(
9    'mysqldump -h %s -u %s -p%s %s > %s',
10    escapeshellarg($host),
11    escapeshellarg($user),
12    escapeshellarg($password),
13    escapeshellarg($database),
14    escapeshellarg($backupFile)
15);
16
17exec($command, $output, $exitCode);
18
19if ($exitCode !== 0) {
20    throw new RuntimeException('mysqldump failed');
21}
22
23echo "Backup created: {$backupFile}\n";

The critical function here is escapeshellarg(). Without it, shell injection becomes a real risk.

Prefer CLI, Cron, or a Controlled Job

A backup script like this is much safer when it runs from the command line, a cron job, or an internal task runner. Exposing it directly as a public web endpoint is risky for two reasons:

• database dumps are sensitive
• backup generation can be slow and resource-heavy

Even with authentication, a web-triggered dump route requires much stricter access control and operational thought than a private scheduled job.

Keep Credentials Out of Source Files

Hardcoded credentials are fine for a tiny example, but production code should read them from environment variables or protected configuration.

1<?php
2$host = getenv('DB_HOST');
3$user = getenv('DB_USER');
4$password = getenv('DB_PASSWORD');
5$database = getenv('DB_NAME');

This keeps secrets out of version control and makes deployment across environments much easier.

Add Compression for Large Dumps

If the dump may be large, compress it as part of the command:

1<?php
2$backupFile = __DIR__ . '/backup.sql.gz';
3
4$command = sprintf(
5    'mysqldump -h %s -u %s -p%s %s | gzip > %s',
6    escapeshellarg($host),
7    escapeshellarg($user),
8    escapeshellarg($password),
9    escapeshellarg($database),
10    escapeshellarg($backupFile)
11);

Compression helps with archival storage and transfer, especially for large databases.

Log Failures and Protect the Output File

A backup is only useful if failure is visible. Capture the exit code, log stderr if possible, and store the dump in a directory with tight filesystem permissions. A dump file may contain the entire database in plain text, so treating it like an ordinary temporary file is a security mistake.

That also means cleanup matters. If the dump is only an intermediate artifact before upload, delete it after a successful transfer.

Common Pitfalls

Building the shell command without escaping arguments exposes the system to command-injection problems.

Running the dump through a public web route without strong access control is dangerous because the backup itself is sensitive.

Hardcoding credentials in source control creates avoidable secret-management problems.

Expecting PHP alone to reproduce everything mysqldump already handles correctly usually leads to incomplete or brittle exports.

Ignoring filesystem permissions on the generated dump file can expose the entire database to the wrong users on the host.

Summary

• The usual way to generate a MySQL dump from PHP is to execute mysqldump.
• Escape every shell argument and check the command exit code.
• Prefer CLI, cron, or another controlled server-side job over a public web endpoint.
• Read credentials from environment or protected config instead of hardcoding them.
• Treat the dump file itself as sensitive data and secure it accordingly.