Menu

RTCXpression

Close

Backup your MySQL Databases and Send them to your E-Mail Account

- August 13, 2016

PHP - databases This is a PHP script I wrote a long time ago. I use it to back up my MySQL and MariaDB databases and send them to my e-mail account every day.

It’s one of my “quick and dirty” scripts. These types of scripts can be dangerous because they don’t include any form of error checking. For the same reason, they’re usually very fast.

A MySQL Configuration Item

It’s considered unsafe to pass user names and passwords through the command line, even if no one else “should” be able to see them. The way around this is to create a file in the root directory:

nano /root/.my.conf

Then add this information to it:

[mysqldump]
user=root
password=mysqlrootpassword

And finally:

chmod 600 /root/.my.cnf

The PHP Script for Backing up and Mailing the MySQL Databases

This is the script I use to back up and e-mail all my databases to my Gmail account. The only things that need to be changed are in the lines before the loop. It will still work if you only have a single database to contend with:

<?php
//ini_set('max_execution_time', '120');   // increase the number of seconds, if necessary
date_default_timezone_set('Asia/Manila'); // your timezone instead of my timezone
$date = date('Y-m-d');                    // matches the date to your timezone
$from = 'email_address@yourdomain.com';   // email from address
$to = $from;                              // email to address
$dbf = array('database_1', 'database_2'); // an array of all the database names
foreach ($dbf as $db) {
  $database_file = $db . '_' . $date . '.sql.gz';
  shell_exec( 'mysqldump -u mysqluser $db | gzip -c -9 > " . $database_file' );
  $file_data = file_get_contents( $database_file );
  $file_data = chunk_split( base64_encode($file_data) );
  $subject  = "[$db] Database Backup " . $date;
  $boundary = "$db " . md5( time() );
  $message  = 'This is a multi-part message in MIME format.' . "nn";
  $message .= '--' . $boundary . "n";
  $message .= 'Content-Type: text/plain; charset="utf-8"' . "n";
  $message .= 'Content-Transfer-Encoding: 7bit' . "nn" . 'Backup file attached. ' . "nn";
  $message .= '--' . $boundary . "n";
  $message .= 'Content-Type: application/octet-stream;' . "n";
  $message .= ' name="' . $database_file . '"' . "n";
  $message .= 'Content-Disposition: attachment;' . "n";
  $message .= ' filename="' . $database_file . '"' . "n";
  $message .= 'Content-Transfer-Encoding: base64' . "nn";
  $message .=  $file_data. "nn" . '--' . $boundary . '--' . "n";
  $headers  = 'From: ' . $from . "n";
  $headers .= 'MIME-Version: 1.0' . "n" . 'Content-Type: multipart/mixed;' . "n" . ' boundary="' . $boundary . '"';
  mail($to, $subject, $message, $headers);
  unlink($database_file);
}
?>

Using the Script

When the PHP script runs, it creates gzipped archives for all databases in the array one at a time, e-mails them as attachments to my Gmail account one at a time and then deletes the archive files one at a time. It could obviously be turned into something more elaborate where you could save something like the last two backup files on the server.

I once had a convoluted way of sending the databases to my Gmail account, assigning a specific label to the messages when they arrived, and then downloading them from Gmail to a hard drive folder stored on Dropbox or Google Drive.

Nowadays, I just leave the databases in Gmail with the proper label. Every so often, I’ll remove the messages more than a few days old. The last time I checked, I was using less than two gigabytes of the 15 gigabytes Google gives me for storage.

If you just want to store the backup files elsewhere, you can use any of the big e-mail services (Gmail, Yahoo Mail, whatever Microsoft calls theirs, etc.) and just leave them there until you don’t need them anymore. If you prefer a standard e-mail client, that’s okay too and that’s why they’re gzipped — a gzipped database can be imported using something like phpMyAdmin or Adminer pretty fast.

Of course, your web server has to be capable of sending e-mail. If it isn’t, you’ll have to alter this script to simply save the gzipped archives of your databases to some storage place on your server.

Share this:

Categories: Technology

Tags: , , , , ,

Previous and Next Articles (if any):

« »

More

You've made it this far down the page. Please read some of my more important pages if you have the time:

Comments Policy           Privacy Policy

RTCXpression established Feb 28, 2011
Copyright © 2013-2017 RT Cunningham
Hosted at Digital Ocean