WEBLEIT MAIN FORUMS

Synology Backup MySQL/MariaDB using PHP and Task Scheduler

If you're running a website or application on your Synology NAS that relies on a MySQL or MariaDB database, regular backups are essential. While Synology offers built-in tools for backups, sometimes you need a more customized solution. In this guide, I'll show you how to create a PHP script to back up your MySQL/MariaDB database and automate it using Synology's Task Scheduler.

Why Use PHP for Database Backups?

Using PHP for database backups offers several advantages:

Flexibility: PHP allows you to add custom logic, such as email notifications, logging, or even uploading backups to cloud storage.
Ease of Use: If you're already familiar with PHP, writing and maintaining a backup script is straightforward.
Cross-Platform: PHP scripts can be easily adapted to run on other systems, not just Synology.

Prerequisites

Before we begin, ensure the following:

PHP is installed on your Synology NAS. You can install it via the Synology Package Center.
MySQL/MariaDB is set up and running on your NAS.
You have access to the Task Scheduler in Synology DSM.

Step 1: Create the PHP Backup Script

Here’s a PHP script that backs up your MySQL/MariaDB database and retains backups for a specified number of days.


<?php

// Configuration

$DB_BACKUP_PATH = '/volume3/Storage/SYNOLOGY/BACKUPS/DATABASES'; // Backup directory

$MYSQL_HOST = 'localhost'; // MySQL host

$MYSQL_PORT = '3306'; // MySQL port

$MYSQL_USER = 'root'; // MySQL username

$MYSQL_PASSWORD = 'password'; // MySQL password

$DATABASE_NAME = 'database'; // Database name

$BACKUP_RETAIN_DAYS = 90; // Number of days to retain backups

$TODAY = date("Y-m-d"); // Current date

$TIME = date("H-i-s"); // Current time

// Create backup directory for today

$backupDir = $DB_BACKUP_PATH . '/' . $TODAY;

if (!file_exists($backupDir)) {
    if (!mkdir($backupDir, 0777, true)) {
        die("Error: Failed to create directory $backupDir");
    }
    echo "Directory $backupDir created successfully.\n";

}

// Backup file name

$backupFile = $backupDir . '/' . $DATABASE_NAME . '_' . $TODAY . '_' . $TIME . '.sql.gz';

// mysqldump command

$command = "mysqldump -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE_NAME | gzip &gt; $backupFile";

// Execute the command

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

if ($returnVar === 0) {
    echo "Database backup created successfully: $backupFile\n";

} else {
    echo "Error: Failed to create database backup.\n";
    print_r($output); // Display errors if any
    exit(1);

}

// Remove old backups

$oldBackups = glob($DB_BACKUP_PATH . '/*', GLOB_ONLYDIR);

foreach ($oldBackups as $oldBackup) {
    $backupDate = basename($oldBackup);
    $backupTime = strtotime($backupDate);
    $currentTime = time();
    $diffDays = ($currentTime - $backupTime) / (60 * 60 * 24);
    if ($diffDays &gt; $BACKUP_RETAIN_DAYS) {
        echo "Removing old backup: $oldBackup\n";
        system("rm -rf $oldBackup");
    }

}

echo "Backup and cleanup completed successfully.\n";

?>

How the Script Works:

Configuration

Backup Directory: Creates a directory for today’s backup.
mysqldump: Uses the ```

mysqldump

command to export the database and compresses it using

gzip


- <strong>Cleanup</strong>: Removes backups older than the specified retention period.

<hr class="wp-block-separator has-alpha-channel-opacity"/>

Step 2: Save the Script

Save the script as ```

backup.php

``` in a directory on your Synology NAS, for example:

/volume3/scripts/backup.php


Make sure the script has execute permissions. You can set this via SSH:

chmod +x /volume3/scripts/backup.php


<hr class="wp-block-separator has-alpha-channel-opacity"/>

Step 3: Automate the Backup with Task Scheduler

To automate the backup process, use Synology's <strong>Task Scheduler</strong>:

<ol class="wp-block-list">- Open <strong>Control Panel &gt; Task Scheduler</strong>.

- Click <strong>Create &gt; Scheduled Task &gt; User-defined script</strong>.

- Set the following:

<ul class="wp-block-list">- <strong>Task Name</strong>: ```

Database Backup

User: ```

root


- <strong>Schedule</strong>: Set the frequency (e.g., daily at 2 AM).

<ol class="wp-block-list">- In the <strong>Task Settings</strong> tab, enter the command to run the PHP script:

php /volume3/scripts/backup.php


- Click <strong>OK</strong> to save the task.


Step 4: Test the Backup

- Manually run the task from the Task Scheduler to ensure it works.

- Check the backup directory to confirm the backup file was created.

- Verify that old backups are being deleted as expected.



Optional Enhancements

Here are some ideas to improve the script:

mail()


function to send an email after the backup completes.

- <strong>Cloud Upload</strong>: Integrate with cloud storage (e.g., Google Drive, Dropbox) to upload backups automatically.

- <strong>Logging</strong>: Write logs to a file for easier troubleshooting.


Conclusion

With this PHP script and Synology's Task Scheduler, you can easily automate MySQL/MariaDB backups on your NAS. This solution is flexible, easy to maintain, and can be customized to fit your specific needs. Whether you're running a personal blog or a business application, regular backups are a must, and this setup ensures your data is always safe.

If you want to comment: Login or Register