RT Cunningham

Backup MySQL Databases and Send Them Elsewhere

backup mysql databases

The first script below is a PHP script I wrote a long time ago. Until recently, I used it to backup 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.

The second script is based on the first, changing the destination from e-mail to Dropbox.

First, 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

Backup MySQL Databases and Send Them to Your E-Mail Account

This is the script I used 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
date_default_timezone_set('Pacific/Honolulu'); // 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 -uroot $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.' . "\n\n";
$message .= '--' . $boundary . "\n";
$message .= 'Content-Type: text/plain; charset="utf-8"' . "\n";
$message .= 'Content-Transfer-Encoding: 7bit' . "\n\n" . 'Backup file attached. ' . "\n\n";
$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' . "\n\n";
$message .= $file_data. "\n\n" . '--' . $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);
}

Backup MySQL Databases and Send Them to Your Dropbox Account

This is the script I now use to back up and send my databases to my Dropbox account. You need a Dropbox account, of course, and the Dropbox Uploader shell script.

I have the shell script placed at “/home/scripts” but you can put it anywhere:

<?php
date_default_timezone_set('Pacific/Honolulu'); // your timezone instead of my timezone
$date = date('Y-m-d'); // matches the date to your timezone
$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 -uroot $db | gzip -c -9 > " . $database_file);
shell_exec("/home/scripts/dropbox_uploader.sh upload $database_file $database_file");
unlink($database_file);
}

Using the Scripts

When either PHP script runs, it creates gzipped archives for all the databases in the array one at a time. It then sends them one at a time. Finally, it deletes the archive files created on the server one at a time.

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 for Dropbox or Google Drive. It didn’t always work. I found it simpler to just leave the files where they were, keeping like the last two or three of each database file (deleting the rest manually).

This is an Update

I wrote this article, without the second script, years ago. The last time I republished it was in 2016. Hopefully, I won’t have to do anything with it again.

Share:    

RT Cunningham
April 10, 2019
Web Development