MySQL: Backup Database and Upload to Amazon S3 via PHP

Update (Dec 30/10) The following is how to load the dump into a db:

Code:
mysql -u #username# -p #database# < #dump_file#

I wrote a post a few years ago about a quick way to backup a MySQL database using email. Since then I’ve updated that script to upload the file to Amazon S3 instead of emailing it. I use an Amazon S3 PHP Class you can find here to make it happen. Your also going to need cURL enabled on your server. On Ubuntu/Debian that’s as easy as:

Code:
sudo apt-get install php5-curl 

Here is the script:

Code:
require_once 'S3.php';

if (!defined('awsAccessKey')) define('awsAccessKey', 'xxxxxxxxxxxxxxxxxxx');
if (!defined('awsSecretKey')) define('awsSecretKey', 'xxxxxxxxxxxxxxxxxxx');
//
// Check for CURL
if (!extension_loaded('curl') && !@dl(PHP_SHLIB_SUFFIX == 'so' ? 'curl.so' : 'php_curl.dll')) {
	exit("\nERROR: CURL extension not loaded\n\n");
}

$tmpDir = "/tmp/";
$user = "dbuser";
$password = "dbpassword";
$dbName = "dbname";
$prefix = "db_";

$sqlFile = $tmpDir.$prefix.date('Ymd_hisA').".sql";
$attachment = $tmpDir.$prefix.date('Ymd_hisA').".tgz";

$creatBackup = "mysqldump -u ".$user." --password=".$password." ".$dbName." > ".$sqlFile;
$createZip = "tar cvzf $attachment $sqlFile";
exec($creatBackup);
exec($createZip);

if (!file_exists($attachment) || !is_file($attachment)) {
	die("ERROR: No file");
}

$s3 = new S3(awsAccessKey, awsSecretKey);
$result = false;
if ($s3->putObjectFile($attachment, 'bucket-name', baseName($attachment), S3::ACL_PRIVATE)) {
	$result = true;
}

unlink($sqlFile);
unlink($attachment);

Related Articles:

One Responseto “MySQL: Backup Database and Upload to Amazon S3 via PHP”

  1. Steve says:

    My host won’t open up mysqldump for users. My provide wants me to use this: http://username:[email protected]:2082/getsqlbackup/mydatabase.sql.gz. Is there a way that I can access that backup since I can’t create one?

Leave a Reply

Line and paragraph breaks automatic.
XHTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>