Last updated on August 9th, 2022 at 09:55 am
This script will show you how to take complete MySQL database backup by running the PHP script. Then Email the backup file as an attachment.
Steps involved
- Take complete MySQL database backup
- Gzip the file
- Send the file as an Email attachment.
Modify these variables according to your configuration
$dbuser = ""; // Database username $dbpwd = ""; // Database password $dbname = ""; // Database name. Use --all-databases if you have more than one $filename= "backup-$datestamp.sql.gz"; // The name (and optionally path) of the dump file $to = "[email protected]"; // Email address to send dump file to $from = "[email protected]"; // Email address message will show as coming from. $subject = "MySQL backup file"; // Subject of email
Complete script is shown below. Save it as (email.php)
<?php
$datestamp = date("Y-m-d"); // Current date to append to filename of backup file in format of YYYY-MM-DD
/* CONFIGURE THE FOLLOWING SEVEN VARIABLES TO MATCH YOUR SETUP */
$dbuser = ""; // Database username
$dbpwd = ""; // Database password
$dbname = ""; // Database name. Use --all-databases if you have more than one
$filename= "backup-$datestamp.sql.gz"; // The name (and optionally path) of the dump file
$to = "[email protected]"; // Email address to send dump file to
$from = "[email protected]"; // Email address message will show as coming from.
$subject = "MySQL backup file"; // Subject of email
$command = "mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename";
$result = passthru($command);
$attachmentname = array_pop(explode("/", $filename)); // If a path was included, strip it out for the attachment name
$message = "Compressed database backup file $attachmentname attached.";
$mime_boundary = "< <<:" . md5(time());
$data = chunk_split(base64_encode(implode("", file($filename))));
$headers = "From: $from\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-type: multipart/mixed;\r\n";
$headers .= " boundary=\"".$mime_boundary."\"\r\n";
$content = "This is a multi-part message in MIME format.\r\n\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";
$content.= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$content.= $message."\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Disposition: attachment;\r\n";
$content.= "Content-Type: Application/Octet-Stream; name=\"$attachmentname\"\r\n";
$content.= "Content-Transfer-Encoding: base64\r\n\r\n";
$content.= $data."\r\n";
$content.= "--" . $mime_boundary . "\r\n";
mail($to, $subject, $content, $headers);
unlink($filename); //delete the backup file from the server
?>
NOTE: Make sure that your email system can process huge attachments since MySQL backup files can be very big at times.
How to execute the script?
- You can either execute the script via command line by just running
$ php /var/www/html/email.php
- Another method is to run the script via browser https://<yourwebsite>/email.php
Once the script is executed successfully you will receive an mail as shown. Check the logs for any errors. I recommend testing the script first on a small database.
Check our interesting tutorial on sending Email with attachment using PHP. This tutorial has detailed explanation on how attachments can be added to an email using PHP
TakeAways
- If you database is huge you might hit this error. If that is the case you have to increase the memory allocated for PHP.
[09-Jan-2021 16:33:58 UTC] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 46555128 bytes) in email.php
- Use this script to email small database backups. Its not a great way to send huge database backups via emails as its going to fill your email space.
- max_execution_time is another attribute you should watch / tune if the database file is huge.
admin