Backing Up a MySQL Database from PHP

Database backups are essential for recovery, migrations, and operational safety. Although PHP can query table definitions and manually generate SQL, a production-ready MySQL backup is better delegated to MySQL’s own mysqldump utility when it is available.

This guide shows how a PHP script can invoke mysqldump safely enough for a controlled server environment.

1. Define the Database and Backup Settings

<?php
$host = 'localhost';
$user = 'backup_user';
$password = getenv('MYSQL_BACKUP_PASSWORD');
$database = 'your_database';
$backupFile = __DIR__ . '/backups/backup-' . date('Ymd-His') . '.sql';

Keep credentials out of source code whenever possible. Environment variables, secret stores, or protected configuration files are preferable to hard-coded passwords.

2. Make Sure the Backup Directory Exists

$backupDir = dirname($backupFile);

if (!is_dir($backupDir) && !mkdir($backupDir, 0700, true) && !is_dir($backupDir)) {
    throw new RuntimeException('Could not create the backup directory.');
}

The directory should not be publicly accessible through your web server.

3. Build the mysqldump Command

$command = sprintf(
    'mysqldump --host=%s --user=%s --password=%s --single-transaction --routines --triggers %s > %s',
    escapeshellarg($host),
    escapeshellarg($user),
    escapeshellarg($password),
    escapeshellarg($database),
    escapeshellarg($backupFile)
);

Important options in this example:

• --single-transaction → creates a consistent snapshot for transactional tables such as InnoDB without locking them for the duration of the dump.
• --routines → includes stored procedures and functions.
• --triggers → includes table triggers.
• escapeshellarg() → prevents database names, paths, and other values from being interpreted as arbitrary shell syntax.

Passing a password directly on the command line can expose it to process inspection on some systems. For serious deployments, prefer a protected MySQL option file, login path, or another credential mechanism supported by your environment.

4. Run the Backup and Check the Exit Status

$output = [];
$exitCode = 0;

exec($command, $output, $exitCode);

if ($exitCode !== 0) {
    throw new RuntimeException('mysqldump failed with exit code ' . $exitCode);
}

if (!is_file($backupFile) || filesize($backupFile) === 0) {
    throw new RuntimeException('Backup file was not created correctly.');
}

echo "Backup created: {$backupFile}\n";

Always check the command’s exit status and confirm the resulting file exists. A scheduled job that silently creates empty or incomplete files is not a reliable backup system.

5. Verify That You Can Restore It

A backup strategy should include restore testing. A typical restore command is:

mysql --host=localhost --user=restore_user --password your_database < backup-20260901-090000.sql

Use a disposable or staging database when testing restores. Do not overwrite production data just to validate a backup.

Why Not Generate INSERT Statements Manually in PHP?

A handwritten exporter can easily mishandle NULL, binary data, character sets, generated columns, views, triggers, stored routines, escaping rules, or very large datasets. mysqldump already understands these MySQL-specific details and is usually the safer tool for logical backups.

Conclusion

PHP can orchestrate MySQL backups, but the database-specific work is best handled by mysqldump. Store backups outside the public web root, protect credentials, monitor failures, rotate old files, and regularly prove that your backups can actually be restored.