PHP Script to Automatically Backup MySQL Database

Looking to backup a MySQL database using PHP without headaches? This guide shows how to create a dependable PHP-MySQL database backup script, automate MySQL backup with PHP on a schedule, and keep your data safe.

We will be installing a small package, writing clean PHP code to backup the MySQL database, and setting up cron so that your backups run on autopilot.

Why regular database backups are important

It’s your safety net; mistakes do happen, often due to bad deploys, dropped tables, or corrupted disks. With recent backups, you bounce back quickly and keep business moving.

Recover from accidental deletes and bad migrations
Roll back risky deployments with confidence
Transfer data securely between servers
Meet compliance needs and reduce downtime

Install the package with Composer

For easy and direct integration, we will use battle‑tested spatie/db-dumper package. It will provide backup feature for most of the database providers like MySQL, PostgreSQL, SQlite and MongoDB.

You need to install this package to start using database backup functionality in PHP. Open your terminal and enter below composer command into root directory of your project:

composer require spatie/db-dumper

Make sure PHP, Composer, and the MySQL client tools (mysqldump) are installed on your server.

Example of Backup MySQL Database Using PHP Script

Let’s take an example for backup using script. In this example, we will create an endpoint which will perform database backup to file and print it to browser. Create a file like scripts/backup-db.php and paste the code below. It writes timestamped .sql files and prunes older backups.

<?php

require __DIR__ . '/vendor/autoload.php';

use Spatie\DbDumper\Databases\MySql;

$dbHost = '127.0.0.1';
$dbName = 'your_database';
$dbUser = 'your_user';
$dbPass = 'your_password';

$backupDir = __DIR__ . '/storage/backups';
if (!is_dir($backupDir)) {
    mkdir($backupDir, 0755, true);
}

$date = date('Y-m-d_H-i-s');
$file = $backupDir . "/{$dbName}_{$date}.sql";

MySql::create()
    ->setDbName($dbName)
    ->setUserName($dbUser)
    ->setPassword($dbPass)
    ->setHost($dbHost)
    ->dumpToFile($file);

$daysToKeep = 7;
foreach (glob($backupDir . '/*.sql') as $f) {
    if (is_file($f) && time() - filemtime($f) >= $daysToKeep * 86400) {
        unlink($f);
    }
}

echo $file . PHP_EOL;

When user visit this URL it will take put database backup to the file. you can restore it anytime using MySQL command like below. Here, you need to change working credentials for your database connection.

mysql -h 127.0.0.1 -u your_user -p your_database < /path/to/your_database_2025-01-01_02-00-00.sql

The above command will be used to restore file whenever your database need to revert to back point.

Schedule MySQL backup in PHP with cron

You can also use Cron to perform automatic database backup on specific time. This script it also configured to delete older backup files automatically. To set cron job on server, you can use either Cpanel to setup cron entry or for manual set up checkout our post on “How to Install Crontab in Ubuntu“.

The guide include installation and setup process into Ubuntu operating system. If you already know setup process, below command is used for that:

0 2 * * * /usr/bin/php /var/www/project/scripts/backup-db.php >> /var/log/db-backup.log 2>&1

The cron will scheduled to run backup every 6 hours. With this package, can also take backup of different databases like shown into below example.

//PostgreSql
PostgreSql::create()
    ->setDbName('your_database')
    ->setUserName('your_user')
    ->setPassword('your_password')
    ->setHost('127.0.0.1')
    ->setPort(5432)
    ->dumpToFile($file);

// SQLServer
SqlServer::create()
    ->setDbName('your_database')
    ->setUserName('your_user')
    ->setPassword('your_password')
    ->setHost('127.0.0.1')
    ->setPort(1433)
    ->dumpToFile($file);

// SQLite
Sqlite::create()
    ->setDbName(__DIR__ . '/database/database.sqlite')
    ->dumpToFile($file);

It shows code for PostgreSQL, SQLServer, and SQlite.

Conclusion

With the given PHP code to backup the MySQL database and a simple cron job, you get reliable, automated protection. You can schedule MySQL backup in PHP, keep versions for safety, and restore fast when something goes wrong. If there is a need to run the job on a remote host, lean on Connect and Run Commands Over SSH Using PHP to keep it secure and hands-free.

Codewolfy