Backup and Transfer MySQL Databases
The first script below is a PHP script I wrote a long time ago, before 2016, until I switched to the second script in 2016. I no longer use either one, but I’m re-publishing them for posterity. Who knows? I may use MySQL as my database server again someday.
They’re “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 Configuration Item
It’s considered unsafe to pass usernames 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 and E-Mail MySQL Databases
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 and Transfer MySQL Databases
This is the script I used 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).
Photo Attribution: OpenClipart-Vectors from Pixabay
Edited and updated. Originally published at one of my other websites in 2016.
These Posts May Also Be Interesting:
- An Nginx Firewall I Designed and Still Use Today
- Linux, Nginx and WebDAV
- Prevent Direct Access to a PHP File
- Prevent Session Hijacking When Using PHP