Smart MySQL Backup

A lot of web sites are hosted on shared servers via web hosting services like Hostgator where a single web server may have a hundred or more customer accounts hosted on it.  While services like this do backup your files and databases they do not guarantee them.  This means if something goes wrong and their backups couldn't be restored for any reason, you'd simply lose your data and they wouldn't be at fault.  For this reason, I do not use cPanel backups on my shared hosting account.

I personally use and recommend Smart MySQL Backup to create backup databases.  There are a lot of great features...

• Backup individual or all databases in a separate file for each database
• UTF-8 support
• Daily, weekly and monthly backup rotation
• Send backups to email
• Upload backups to FTP
• Handles foreign keys and stored procedures

Once I have that setup and running, I have a simple PHP script to upload the backups to Amazon S3.  These backups can be restored through cPanel/PHPMyAdmin, or via command line.

// Take db backups and copy them to s3
// https://github.com/tpyo/amazon-s3-php-class
require_once 's3.php';

$bucket = 'my-bucket-name';

$backupPath = '/path/to/backups/archive/daily';

$s3 = new S3("[awsAccessKey]", "[awsSecretKey]");

$today = date('Y-m-d');
$expired = date('Y-m-d', strtotime('-5 days'));
foreach(glob($backupPath.'/'.$today.'/*.bz2') as $file) {
    $fileInfo = pathinfo($file);
    
    //move backup file to s3
    $s3->putObject(S3::inputFile($file), $bucket, $fileInfo['basename'], S3::ACL_PRIVATE);
    
    //remove expired files
    $s3->deleteObject($bucket, str_replace($today, $expired, $fileInfo['basename']));
}