Backup your MySQL database to Amazon AWS S3 using a PHP script

This is a followup on How to backup your web site or blog to Amazon AWS S3. This time we do a backup of your database and save it to Amazon AWS. This is very similar to the previous post and requires the AWS SDK for PHP, a bucket in AWS S3 and also some keys.

This script will save the ZIP file with the date in the file name to make it easy for you to find just in case you need to restore your database.

<?php
require 'vendor/autoload.php'; // Make sure to include the autoload file from the AWS SDK

use Aws\S3\S3Client;
use Aws\S3\Exception\S3Exception;

$databaseHost = 'your-database-host';
$databaseName = 'your-database-name';
$databaseUser = 'your-database-user';
$databasePassword = 'your-database-password';

$s3BucketName = 'your-s3-bucket-name';
$s3BackupKey = 'db_backups/';

// Create a temporary directory to store the backup
$tempDir = sys_get_temp_dir() . '/db_backup_' . date('Y-m-d_H-i-s');
mkdir($tempDir);

// Create a filename for the backup
$backupFileName = $tempDir . '/backup.sql';

// Use mysqldump to create the backup
$command = "mysqldump -h $databaseHost -u $databaseUser -p'$databasePassword' $databaseName > $backupFileName";
exec($command, $output, $exitCode);

if ($exitCode === 0) {
    // Compress the backup file into a zip archive
    $zipFileName = $tempDir . '/SQLbackup'. date('Y-m-d_H-i-s') .'.zip';
    $zip = new ZipArchive();
    if ($zip->open($zipFileName, ZipArchive::CREATE) === true) {
        $zip->addFile($backupFileName, basename($backupFileName));
        $zip->close();

        // Initialize S3 client
        $s3Client = new S3Client([
            'version' => 'latest',
            'region' => 'your-aws-region',
            'credentials' => [
                'key' => 'your-aws-access-key',
                'secret' => 'your-aws-secret-key',
            ],
        ]);

        try {
            // Upload the zip file to S3
            $result = $s3Client->putObject([
                'Bucket' => $s3BucketName,
                'Key' => $s3BackupKey . basename($zipFileName),
                'SourceFile' => $zipFileName,
            ]);

            echo "Database backup uploaded to S3 successfully.\n";
        } catch (S3Exception $e) {
            echo "Error uploading database backup to S3: " . $e->getMessage() . "\n";
        }

        // Clean up temporary files
        unlink($zipFileName);
    } else {
        echo "Could not create backup zip archive.\n";
    }

    unlink($backupFileName); // Remove the temporary SQL backup file
    rmdir($tempDir); // Remove the temporary directory
} else {
    echo "Error creating database backup.\n";
}
?>

Replace the placeholders (your-database-host, your-database-name, your-database-user, your-database-password, your-s3-bucket-name, etc.) with your actual database and AWS S3 credentials. Also, ensure you have the necessary permissions and access to the MySQL server and AWS S3 bucket.

Run this script in the same way as in How to backup your web site or blog to Amazon AWS S3 to make sure you to backups regularly.