Hourly database backup locally and via email/FTPS for WordPress/Gambio with AES encryption

18. April 2022

I took some time again and worked on a script for hourly MySQL/MariaDB database backups. The script is based on the PHP programming language and can therefore be used for almost all web applications (including the JTL store, of course). Additionally, the script has already been optimized for WordPress and Gambio.

Update:

With the PHP script can now also optionally save the files.

Below you will find a shellscript for very large databases (where backups older than 3 days are deleted).

The data of the database are compressed after the backup per zip and encrypted with AES 256 bit. The database password is used as the encryption password – therefore it is useful to note this password separately.

The encryption is done to increase the transmission security and data protection.

This article contains protected areas which can be unlocked with a password for existing customers.

1. Installation

Several small steps are necessary for the installation (the example starts from WordPress):

1. Create a “backups” folder with file permissions “775” on the web server and FTPS backup storage if necessary

2. Creating a “.htaccess” file on the webserver inside the “backups” folder with the content:

Deny from all

3. Create a file “backup_lautenbacherio.php” with the content of the script (see below)

4. Create a cronjob which executes the URL of the PHP script in the desired time interval (e.g. every hour or every 6 hours [Crontab: 1 */6 * *])

5. adjust PHP execution times if necessary

If you have problems with the installation or setup of an hourly database backup you can contact me with my contact form. I will help you at reasonable hourly rates.

2. PHP script

In the upper part of the script you can enter the database access data. If you have a wordpress-installation you have to put the script in the root-directory and you can enter a 1 in the variable wordpress. The access data will then be read from the wp-config.php.

Otherwise it is also possible to set with 1 or 0, whether the backup should be sent by e-mail or uploaded by FTPS. Finally, it is also possible to set that the local backups are deleted after completion of the process.

If the backups are to be sent by e-mail or uploaded via FTPS, the appropriate e-mail sender and recipient should of course also be entered, as well as the FTPS access data if necessary.

Here you can find the lite version of the PHP script which allows (only) database backups to be sent by e-mail:

<?php
//database access data
$dbhost = 'localhost';
$dbuser = 'user';
$dbpassword = 'password'; //also used for AES encryption
$dbname = 'databasename';
//take database credentials from WordPress - 1 yes 0 no?
$wordpress = 0;
//Accept database access data from Gambio - 1 yes 0 no?
$gambio = 0;
//Save files
$filebackup = 0; //NOT INCLUDED IN THE PUBLIC VERSION
//Switch mail or FTP backup on or off with 1 or 0 respectively
$mailbackup = 0; //no filebackup via email
$ftpbackup = 0; //database - FILEBACKUP NOT INCLUDED IN THIS VERSION
//delete local backups after completion?
$deletefiles = 0;
//e-mail data:
$sender = '[email protected]';
$receiver = '[email protected]';
//Remote FTP access data:
$host = 'ftp.example.com';
$port = '21';
$user = 'user';
$pass = 'password';

//read out wordpress database credentials
if ($wordpress==1){
include 'wp-config.php';
$dbname = DB_NAME;
$dbuser = DB_USER;
$dbpassword = DB_PASSWORD;
$dbhost = DB_HOST;
}

//Gambio read database access data
if ($gambio==1){
include './includes/configure.php';
$dbname = DB_DATABASE;
$dbuser = DB_SERVER_USERNAME;
$dbpassword = DB_SERVER_PASSWORD;
$dbhost = DB_SERVER;
}


//Do not edit
$dumpfile = "backups/" . $dbname . "_" . date("Y-m-d_H-i-s") . ".sql" ;
$dumpfilezip = "backups/" . $dbname . "_" . date("Y-m-d_H-i-s") . ".zip";
echo base64_decode('cG93ZXJlZCBieSB3d3cubGF1dGVuYmFjaGVyLmlv') . '</br>';
echo 'Start Backup';
echo '</br>' ;
exec("mysqldump --user=$dbuser --password=$dbpassword --host=$dbhost $dbname > $dumpfile");

$zip = new ZipArchive();
if ($zip->open($dumpfilezip, ZipArchive::CREATE) === TRUE) {
    $zip->setPassword($dbpassword);
    $zip->addFile($dumpfile);
    $zip->setEncryptionName($dumpfile, ZipArchive::EM_AES_256);
    $zip->close();
    echo 'AES encrypted</br>';
} else {
    echo 'Error</br>'
}
unlink($dumpfile);

echo 'Backup completed </br> ';

function mail_att($to, $from, $subject, $message, $file) {
 $mime_boundary = "-----=" . md5(uniqid(rand(), 1));
 
 $header = "From: ".$from."rn";
 $header.= "MIME version: 1.0rn";
 $header.= "Content-Type: multipart/mixed;rn";
 $header.= " boundary="".$mime_boundary.""rn";
 $content = "This is a multi-part message in MIME format.rnrn";
 $content.= "--".$mime_boundary."rn";
 $content.= "Content-Type: text/plain charset="iso-8859-1"rn";
 $content.= "Content-Transfer-Encoding: 7bitrnrn";
 $content.= $message."rn"; 
 $name = basename($file);
 $data = chunk_split(base64_encode(file_get_contents($file)));
 $len = filesize($file);
 $content.= "--".$mime_boundary."rn";
 $content.= "content-disposition: attachment;rn";
 $content.= "tfilename="$name";rn";
 $content.= "Content-Length: .$len;rn";
 $content.= "Content-Type: application/x-gzip; name="".$file.""rn";
 $content.= "Content-Transfer-Encoding: base64rnrn";
 $content.= $data."rn"; 
 return mail($to, $subject, $content, $header);
} 

if ($mailbackup == 1) {
mail_att($receiver, $sender, "Backup ".$dumpfilezip, "Backup was successfully created and is attached", $dumpfilezip);
}


if ($deletefiles==1){
unlink($dumpfilezip);
	 echo 'Deleted DB Backup</br>';
}
?>

3. Shellscript for very large databases

First we need to create a suitable database user for the backups.

Then we prepare the smoothest possible data transfer via scp by disabling StrictHostKeyChecking for the target server.

nano /etc/ssh/ssh_config

Host backup.example.com
StrictHostKeyChecking no

Then we prepare everything for the shell script. In detail we still need the tools 7zip, sshpass and lftp.

apt-get install -y p7zip-full
apt-get -y install sshpass
apt-get -y install lftp
touch /root/backup.sh
chmod 775 /root/backup.sh
mkdir /root/db/

After the following shellscript was deposited we add with

crontab -e

to add a new cronjob

1 * * * /root/backup.sh > /dev/null 2>&1

In the shell script itself we add with

nano /root/backup.sh

to insert the following content:

LAMP (EN)