More and more web development, Saas and platform has now moving to the cloud computing such as Amazon AWS. Compare with the tradition disaster recovery plan for web hosting, things become more easy and simple when it’s on the cloud.

A simple yet effective disaster discovery plan for your MySQL  database

No matter you have a dedicated database server or using the same server for applications and database, you want to have your database backups in a different place. It could be another backup server or your local computer.

You could automate this routine with a little SSH and bash scripting.

1. Get the AWS PHP SDK

Install the AWS SDK for PHP here

2. Create a php file:  auto_backup.php in this example:

Create the php file in the directory where you have installed the AWS SDK.

Can’t find your secret key? learn more.

<?php
require 'vendor/autoload.php';
use Aws\S3\S3Client;


// Instantiate the S3 client with your AWS credentials

$client = S3Client::factory(array(
    'credentials' => array(
        'key'    => 'YOUR_KEY',
        'secret' => 'YOUR_SECRET_KEY',
    ),
    'version' => 'latest',
    'region' => 'us-west-2' // the region where your s3 bucket located
));


$filename = date("Y-m").'.sql';
$filepath =  'YOUR_BACKUP_PATH/backups/database_backup.sql';

// Upload an object to Amazon S3

$result = $client->putObject(array(
    'Bucket'       => 'YOUR_S3_BUCKET_NAME',
    'Key'          => $filename,
    'SourceFile'   => $filepath,
    'ContentType'  => 'text/plain',
    'ACL'          => 'public-read',
    'StorageClass' => 'REDUCED_REDUNDANCY',
    'Metadata'     => array(    
        'Content-Type' => 'text/plain'
    )
));


// Access parts of the result object

echo $result['Expiration'] . "\n";
echo $result['ServerSideEncryption'] . "\n";
echo $result['ETag'] . "\n";
echo $result['VersionId'] . "\n";
echo $result['RequestId'] . "\n";

// Get the URL the object can be downloaded from
echo $result['ObjectURL'] . "\n";
?>

3. Create a shell script

This example is using UBUNTU NGINX, the shell directory can be different depends on your server OS.

mysqldump -p{YOUR_MYSQL_PASSWORD} {YOUR_DATABASE_NAME} > /usr/share/nginx/html/backups/database_backup.sql

4. create a cron job

This cron job basically does two things, run the autoBackup.sh to export the mysql database and run the auto_backup.php to upload the database to the S3 bucket.

SSH to your server and open the cron tab:

crontab -e

In the crontab editor, add these lines:

0 0 1 * * /usr/local/bin/autoBackup.sh
0 0 2 * * /usr/share/nginx/html/auto_backup.php

#backup db on first day of every month
#send database to s3 bucket on the 2nd day of every month.

Now the backup process is automated and uploaded to AWS s3 bucket. Hope it helps.

Learn more: How to Automatic Backup MySQL Database using SSH

Learn more: 12 Useful Commands To Check Your Server Health using PuTTY or similar SSH client