How to send MySQL backup to email with cronjob

Question

I am trying to automatically backup my database from cPanel using a cronjob. I want to send the database to my email address when the cronjob is run and I have written code (below) but it is still not working.

mysqldump -e --user=username --password='password' dbname | gzip | uuencode sql_backup.gz | mail example@example.com

In my email when cronjob is run I am getting this message:

/usr/local/cpanel/bin/jailshell: mail: command not found
mysqldump: Got errno 32 on write

I have been referring to this article: Automatic MySQL Backup.

I hope you understand my question and help me.

I have also tried with curl but still not working. You can check my steps that I had followed.

First Step: Created mail_alert.sh file and added bellow code.

#!/bin/bash
curl --url "smtps://smtp.gmail.com:465" --ssl-reqd \
  --mail-from "example@gmail.com" --mail-rcpt "example@gmail.com" \
  --upload-file mail.txt --user "example@gmail.com:mypassword" --insecure

Second Step: Created mail.txt and added below code.

From: "Name" example@gmail.com
To: "Name" example@gmail.com
Subject: Backup completed

The backup has been completed.

Third Step: Added code in command line.

mysqldump -e --user=username --password='password' dbname | gzip | uuencode sql_backup.gz | sh public_html/sql/mail_alert.sh

After this I am getting this message in my mail.

curl: option --ssl-reqd: is unknown
curl: try 'curl --help' or 'curl --manual' for more information
mysqldump: Got errno 32 on write

Answer 1

Looks like mail is not available for you to use or is not installed.

Another option to consider is to using curl to send emails as described here: https://stackoverflow.com/a/16069786/280842

Here's how you could implement this, using code from the link above:

mail_alert.sh file contents

#!/bin/bash
curl --url "smtps://smtp.gmail.com:465" --ssl-reqd \
  --mail-from "username@gmail.com" --mail-rcpt "john@example.com" \
  --upload-file mail.txt --user "username@gmail.com:password" --insecure

mail.txt file contents

From: "User Name" <username@gmail.com>
To: "John Smith" <john@example.com>
Subject: Backup completed

The backup has been completed.

It's considered a bad security practice to pass account credentials thru command line arguments. The above example is for demo purpose only.

Then add your newly created script to your existing cron job

mysqldump -e --user=username --password='password' dbname | gzip | uuencode sql_backup.gz | sh /home/myuser/mail_alert.sh

Answer 2

OK, I will show you how to create a php script that backs up a MySQL database WITHOUT phpMyAdmin and then attaches the .sql file to an email.

Well today I needed to create a little script that backed up a database and then sent it in an email. I found the best way to do this was using the mysqldump program. Usually you have permission to run this program even on a Reseller hosting package.

In linux the program is usually located at CODE:

/usr/bin/mysqldump

Ok so lets get started. First of all we need to setup our variables containing MySQL credentials, email addresses to send to, path to store sql file, absolute path to mysqldump program.

CODE:

ini_set("memory_limit","250M"); // We don't want any nasty memory error messages

$SendTo[] = 'myemailaddress@thephpanswers.com'; // This is your email address, you can copy this line and add another recipient 

$path = '/home/website/public_html/backupSQL/sql/' // This is the absolute path to where we are going to save the .sql files - please note you should place a .htaccess to deny any users browsing the directory

$tmpFilename = time() .'_mysql.sql'; // This is the tmp filename for the sql, needs to be different everytime

define('mysqlUser','mysqlusername'); // This is the username for the MySQL database
define('mysqlPass','mysqlpassword'); // Password for the username
define('mysqlDatabase','mysqldatabase'); // The database you wish to backup
define('mysqldump','/usr/bin/mysqldump'); // The absolute path to the mysqldump program

Using mysqldump to backup MySQL database: mysqldump is very easy to use, for more information visit here: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html Now we just add the shell_exec to tell mysqldump to backup the database.

CODE:

shell_exec(mysqldump . ' -u ' . mysqlUser .' -p' . mysqlPass .' ' . mysqlDatabase .' > ' . $path .$tmpFilename); // See the > $path . $tmpFilename these are populated from the variables you set above

You can now run this script and see if it actually creates the .sql file in folder you specified.

Sending an attachment in PHP Ok so we know our file is located at $path . $tmpFilename so lets get on with the complicated emailing of the attachment.

CODE:

$from = "Backup <backup@domain.co.uk>"; // Who the email is coming from
$subject = 'MySQL Database backup'; // The subject of the email
$absoluteFile = $path . $tmpFilename; // Keep it simple, creates a variable of where the file is
$fileType = 'text/plain'; // Content type
$mailBodyText = '<h1>MySQL Database attached</h1>'; // Our HTML body of the email

$mineBoundaryStr=md5(time()); // Needs to be random for the mime
// Advanced headers from http://xahlee.org/php/send_mail_attachment.html
$headers= <<<EEEEEEEEEEEEEE
From: $from
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="$mineBoundaryStr"

EEEEEEEEEEEEEE;
$mailBodyEncodedText = <<<TTTTTTTTTTTTTTTTT
This is a multi-part message in MIME format.

--{$mineBoundaryStr}
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

$mailBodyText

TTTTTTTTTTTTTTTTT;

$file = fopen($absoluteFile,'rb'); 
$data = fread($file,filesize($absoluteFile)); 
fclose($file);
$data = chunk_split(base64_encode($data));

$mailBodyEncodedText .= <<<FFFFFFFFFFFFFFFFFFFFF
--$mineBoundaryStr
Content-Type: $fileType;
name=$tmpFilename
Content-Disposition: attachment;
filename="$tmpFilename"
Content-Transfer-Encoding: base64

$data

--$mineBoundaryStr--

FFFFFFFFFFFFFFFFFFFFF;

foreach($SendTo as $k => $v) { // Loop through all our recipients
      mail( $v , date("H:i - jS \of F Y") . 'MySQL Database backup' , $mailBodyEncodedText, $headers ); // Send the emails 
}

So lets put all the script together and you should have this: CODE:

<?php ini_set("memory_limit","250M"); // We don't want any nasty memory error messages

$SendTo[] = 'myemailaddress@thephpanswers.com'; // This is your email address, you can copy this line and add another recipient 

$path = '/home/website/public_html/backupSQL/sql/' // This is the absolute path to where we are going to save the .sql files - please note you should place a .htaccess to deny any users browsing the directory

$tmpFilename = time() .'_mysql.sql'; // This is the tmp filename for the sql, needs to be different everytime

define('mysqlUser','mysqlusername'); // This is the username for the MySQL database
define('mysqlPass','mysqlpassword'); // Password for the username
define('mysqlDatabase','mysqldatabase'); // The database you wish to backup
define('mysqldump','/usr/bin/mysqldump'); // The absolute path to the mysqldump program

shell_exec(mysqldump . ' -u ' . mysqlUser .' -p' . mysqlPass .' ' . mysqlDatabase .' > ' . $path .$tmpFilename); // See the > $path . $tmpFilename these are populated from the variables you set above

$from = "Backup <backup@domain.co.uk>"; // Who the email is coming from
$subject = 'MySQL Database backup'; // The subject of the email
$absoluteFile = $path . $tmpFilename; // Keep it simple, creates a variable of where the file is
$fileType = 'text/plain'; // Content type
$mailBodyText = '<h1>MySQL Database attached</h1>'; // Our HTML body of the email

$mineBoundaryStr=md5(time()); // Needs to be random for the mime
// Advanced headers from http://xahlee.org/php/send_mail_attachment.html
$headers= <<<EEEEEEEEEEEEEE
From: $from
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="$mineBoundaryStr"

EEEEEEEEEEEEEE;
$mailBodyEncodedText = <<<TTTTTTTTTTTTTTTTT
This is a multi-part message in MIME format.

--{$mineBoundaryStr}
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

$mailBodyText

TTTTTTTTTTTTTTTTT;

$file = fopen($absoluteFile,'rb'); 
$data = fread($file,filesize($absoluteFile)); 
fclose($file);
$data = chunk_split(base64_encode($data));

$mailBodyEncodedText .= <<<FFFFFFFFFFFFFFFFFFFFF
--$mineBoundaryStr
Content-Type: $fileType;
name=$tmpFilename
Content-Disposition: attachment;
filename="$tmpFilename"
Content-Transfer-Encoding: base64

$data

--$mineBoundaryStr--

FFFFFFFFFFFFFFFFFFFFF;

foreach($SendTo as $k => $v) { // Loop through all our recipients
      mail( $v , date("H:i - jS \of F Y") . 'MySQL Database backup' , $mailBodyEncodedText, $headers ); // Send the emails 
}
?>

You really should protect the directory you choose for the .SQL files, this can be done by creating a file named .htaccess and save it in the directory. The contents of the .htaccess are as follows: CODE:

order allow,deny

deny from all

You can now automate this with a cron job, set the cron job to run the script every day at midnight

I hope this helps some people out there!

PS: After using this script I realised there is no real security on the .sql dumps, I found using openssl or something similiar on .sql files before emailing them it is 100% secure!