How to Backup MySQL Database With a PHP Script? 2026

Learning how to backup mysql database with a php script puts a one-click safety net inside your application. The core idea is simple: PHP opens a connection to MySQL, exports every table as CREATE TABLE and INSERT statements, and saves the result to a .sql file you can store, download, or schedule.

In this guide I walk you through three working methods – calling mysqldump with exec(), looping through tables in pure PHP, and using the mysqldump-php library. You will also see how to trigger a browser download, automate the script with cron, restore the database, and lock the file down so nobody else can grab it.

Quick Answer: The Shortest Working Script

Create a file called backup.php, drop the snippet below in it, and open it in your browser. The script shells out to the mysqldump binary and writes the entire database to a timestamped .sql file on disk.

<?php
$db   = 'mydb';
$user = 'dbuser';
$pass = 'secret';
$host = 'localhost';

$file = 'backup-' . $db . '-' . date('Y-m-d-H-i-s') . '.sql';
system("mysqldump --user={$user} --password={$pass} --host={$host} {$db} > {$file}");
echo "Backup saved to {$file}";

If mysqldump runs on your server, this is the entire backup. Most production stacks have it, and it is what the official MySQL team recommends. If the command is blocked or missing – common on shared hosting – jump to the Pure PHP method or the mysqldump-php library sections below.

Which Method Should You Use?

Three approaches cover every hosting situation. Use the table below to pick the right one before you write a single line of code.

Method	Needs shell access	Works on shared hosting	Speed on large DBs	Complexity
mysqldump via exec()/system()	Yes	Rarely	Fast	Low
Pure PHP loop (SHOW TABLES)	No	Yes	Slow above ~500MB	Medium
mysqldump-php library	No	Yes	Fast	Low

If you have a VPS or dedicated server, Method 1 is the clear winner. If you are on cPanel, Bluehost, SiteGround, or any shared plan where exec() is disabled, go straight to Method 3 – it is the only one that gives you real mysqldump-quality output without shell access.

Prerequisites Before You Write the Script

Before you run any PHP backup script, confirm the following on the server. Skipping this step is the number one reason backups silently produce empty files.

• PHP 7.4 or newer with the mysqli extension enabled. PHP 8.x is recommended.
• The mysqldump binary if you plan to use Method 1. Test it from the command line: mysqldump --version.
• A MySQL or MariaDB user with at least SELECT and LOCK TABLES privileges on the target database.
• A writable folder outside the webroot for storing .sql files (more on this in the security section).
• PHP memory_limit and max_execution_time raised if your database is over 200MB. Add ini_set('memory_limit', '512M'); and set_time_limit(0); at the top of the script.

Method 1: Backup MySQL With mysqldump and exec()

This is the most common way to backup a MySQL database with a PHP script. PHP calls the server-side mysqldump utility, captures its output, and either streams it to the browser or saves it to a .sql file.

Step 1: Create the backup.php file

Save this as backup.php in a folder outside the public webroot when possible. Replace the four credential variables with your own.

<?php
// --- Configuration ---
$dbhost = 'localhost';
$dbuser = 'dbuser';
$dbpass = 'secret';
$dbname = 'mydb';

// Switch between exec(), system(), passthru(), shell_exec()
// based on what your host allows.
$binary = 'mysqldump';
$cmd    = sprintf(
    '%s --user=%s --password=%s --host=%s --single-transaction --quick %s > %s.sql',
    $binary,
    escapeshellarg($dbuser),
    escapeshellarg($dbpass),
    escapeshellarg($dbhost),
    escapeshellarg($dbname),
    escapeshellarg($dbname . '-' . date('Y-m-d-H-i-s'))
);

exec($cmd, $output, $result);
if ($result !== 0) {
    die('Backup failed - check mysqldump path and credentials');
}
echo 'Backup complete.';

The --single-transaction flag keeps your data consistent by wrapping the whole dump in a single transaction – critical for InnoDB tables. The --quick flag prevents mysqldump from buffering entire tables in memory, which protects you from out-of-memory errors on large databases.

Step 2: Test it from the command line first

Before you trust any PHP backup script, run mysqldump directly from SSH. This confirms the credentials work and the binary is on the path.

mysqldump --user=dbuser --password=secret --host=localhost mydb > test.sql
ls -lh test.sql
head -n 20 test.sql

If the file size is non-zero and you see CREATE TABLE lines at the top, you are good. PHP can now wrap the same command.

Step 3: Add gzip compression for large databases

Most production backups compress well – a 2GB SQL file often shrinks to 300MB after gzip. Pipe the output through gzip directly.

$file = $dbname . '-' . date('Y-m-d-H-i-s') . '.sql.gz';
$cmd  = sprintf(
    "%s --user=%s --password=%s --host=%s %s | gzip > %s",
    $binary,
    escapeshellarg($dbuser),
    escapeshellarg($dbpass),
    escapeshellarg($dbhost),
    escapeshellarg($dbname),
    escapeshellarg($file)
);
exec($cmd);

Method 2: Pure PHP Backup (No Shell Access Required)

When exec() is disabled – the default on most shared hosts – you can still build a working backup script in pure PHP. The approach is to enumerate every table with SHOW TABLES, generate the CREATE TABLE statement for each one, then walk every row and build INSERT INTO statements.

<?php
$conn = new mysqli('localhost', 'dbuser', 'secret', 'mydb');
$conn->set_charset('utf8mb4');

$sql  = "-- Backup generated " . date('Y-m-d H:i:s') . "n";
$sql .= "SET NAMES utf8mb4;nn";

$tables = $conn->query("SHOW TABLES");
while ($row = $tables->fetch_array()) {
    $table = $row[0];

    $create = $conn->query("SHOW CREATE TABLE `{$table}`")->fetch_array();
    $sql   .= "DROP TABLE IF EXISTS `{$table}`;n";
    $sql   .= $create[1] . ";nn";

    $rows = $conn->query("SELECT * FROM `{$table}`");
    if ($rows->num_rows) {
        $cols = array_keys((array) $rows->fetch_assoc());
        $rows->data_seek(0);
        while ($r = $rows->fetch_assoc()) {
            $vals = array_map(function ($v) use ($conn) {
                return $v === null ? 'NULL' : "'" . $conn->real_escape_string($v) . "'";
            }, array_values($r));
            $sql .= "INSERT INTO `{$table}` (`" . implode('`,`', $cols) . "`) VALUES (" . implode(',', $vals) . ");n";
        }
        $sql .= "n";
    }
}

file_put_contents('backup-' . date('Y-m-d-H-i-s') . '.sql', $sql);
echo "Pure-PHP backup complete.";

This works on any shared host. The trade-off, as SitePoint commenters have pointed out, is that the pure-PHP loop becomes painfully slow once the database passes 500MB to 1GB. For anything bigger, switch to Method 3 or run mysqldump from cron.

Method 3: mysqldump-php Library (Best for Shared Hosting)

The mysqldump-php library by David Stoline re-implements the entire mysqldump CLI tool in pure PHP. It needs zero shell access, yet produces the same .sql output as the real binary. Install it with Composer.

composer require ifsnop/mysqldump-php

Then a backup takes three lines of code:

<?php
require 'vendor/autoload.php';

$dump = new IfsnopMysqldumpMysqldump('mysql:host=localhost;dbname=mydb', 'dbuser', 'secret');
$dump->start('backup-' . date('Y-m-d-H-i-s') . '.sql');

The library handles charset conversion, large table streaming, and gzip compression via the compress option. It is the safest bet when you are on shared hosting and need a real mysqldump-quality backup.

Triggering a Browser Download of the .sql File

If you want a one-click “Download Backup” button in your admin panel, send the SQL content to the browser with the right headers.

<?php
$file = 'backup.sql';
if (ob_get_level()) { ob_end_clean(); }
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="' . basename($file) . '"');
header('Content-Length: ' . filesize($file));
readfile($file);
exit;

The ob_end_clean() call is important – without it, whitespace or warnings from earlier in the script corrupt the downloaded file.

How to Restore a MySQL Database From the .sql File

A backup is only useful if you can restore from it. The fastest way is from the command line.

mysql --user=dbuser --password=secret mydb < backup-2026-09-11-02-00-00.sql

If you do not have SSH, you can restore from PHP by splitting the SQL file on semicolons and running each statement through mysqli::query(). For very large files (over 1GB), use the command line – PHP will time out before the restore finishes.

<?php
$conn = new mysqli('localhost', 'dbuser', 'secret', 'mydb');
$sql  = file_get_contents('backup.sql');
$conn->multi_query($sql);
do { $conn->store_result(); $conn->next_result(); } while ($conn->more_results());
echo "Restore complete.";

Automating the Backup With Cron (Linux)

Manually running a PHP backup script is fine for one-off exports, but production databases need daily snapshots. Cron is the standard scheduler on Linux.

Open your crontab with crontab -e and add a line like this one – it runs the backup every night at 2:00 AM:

0 2 * * * /usr/bin/php /home/youruser/backups/backup.php >> /home/youruser/backups/backup.log 2>&1

For Windows servers, open Task Scheduler, create a basic task, set the trigger to “Daily at 2:00 AM”, and set the action to “Start a program” with C:phpphp.exe as the program and C:inetpubbackupsbackup.php as the argument.

Security Checklist Before You Ship This

A backup script sitting in your webroot is an open door to your entire database. Every PHP backup script should follow these rules.

• Move backup.php outside the public webroot (e.g. /home/youruser/backups/) and have cron call it directly.
• If you must keep it in the webroot, protect it with .htaccess: <Files "backup.php"> Require all denied </Files> for Apache, or the equivalent Nginx location block.
• Require a secret token in the URL: backup.php?token=aB7xK9pL3mN2qR. Check it with $_GET['token'] before running anything.
• Never commit credentials to git. Read them from environment variables or a config file outside the repo.
• Delete old .sql files on a rotation schedule. A 30-day rolling window keeps storage predictable.

Community feedback on r/PHP and DBA Stack Exchange is consistent on one point: production users prefer cron-driven filesystem backups to remote storage rather than on-demand PHP downloads. The PHP script remains useful for one-off exports, admin panels, and shared-hosting users who have no shell access.

Verifying Your Backup and Handling Large Databases

A backup you never tested is not a backup – it is a hope. After your script runs, restore the .sql file into a temporary database and confirm the row counts match.

mysql --user=dbuser --password=secret -e "CREATE DATABASE mydb_test;"
mysql --user=dbuser --password=secret mydb_test < backup.sql
mysql --user=dbuser --password=secret mydb_test -e "SELECT COUNT(*) FROM users;"

For databases above 1GB, raise the PHP limits and use --single-transaction --quick with mysqldump. If the dump still takes too long, run it directly from the server’s command line rather than through a web request – PHP’s max_execution_time will eventually kill long-running scripts no matter what.

Frequently Asked Questions

Wrapping Up

You now have three working approaches for how to backup mysql database with a php script, plus the restore, automation, and security steps that turn a one-off export into a production-ready backup routine.

Start with Method 1 if you have shell access – it is the fastest and most reliable. If exec() is blocked, install mysqldump-php via Composer and run Method 3. Always move the script outside the webroot, schedule it with cron, and test-restore to a temporary database before you trust it with production data.