MySQL backups emailed via Python, cron and cPanel

In a previous post, I outlined how I got my MySQL database backup emailed to me regularly from my shared hosting server. About a week later, I ditched that method because I wanted to change it to send my backups to GMail, but the email headers made those emails look like spam so GMail was ditching them. Instead, I rewrote the backup script in Python.

The problem was due to some fairly heavy restrictions on what I could do with the mail command, and what other commands were available. So instead, I knocked up a short Python script to do the job, because the email package in Python allows you to set the header fields I needed to convince GMail that my emails weren’t spam.

It’s quite simple. First, the script runs the mysqldump command with output going to a pipe, and reads the results back into a string called “dump”. Next, it creates an email message and sets the from/to/date/subject fields. It then attaches the dump data as a base64-encoded MIME attachment. Finally, it sends the email via the local SMTP server. Once I tested it and checked that the files were coming through on GMail, I scheduled it in cron to run instead of the one-line shell script I had previously.

#!/usr/bin/python

import smtplib
import os, socket, time
from subprocess import Popen, PIPE
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email.Utils import COMMASPACE, formatdate
from email import Encoders

sender = 'me <me@example.com>'
recipients = ['my-gmail@example.com','another@example.com']

# run the backup, extracting the data as a string blob
cmd = ['mysqldump', '--hex-blob', 'mysql_mantisbt']
p = Popen(cmd, stdout=PIPE)
dump = p.stdout.read()
p.wait()

if p.returncode == 0:
	# create a MIME message for email
	message = MIMEMultipart()
	message['From'] = sender
	message['To'] = COMMASPACE.join(recipients)
	message['Date'] = formatdate(localtime=True)
	message['Subject'] = "mantis backup %s" % formatdate(localtime=True)

	# attach the data as an attachment
	part = MIMEBase('application', 'octet-stream')
	part.set_payload(dump)
	Encoders.encode_base64(part)
	part.add_header('Content-Disposition', 'attachment; filename="mysql_mantisbt.sql"')
	message.attach(part)

	# send it!
	smtp = smtplib.SMTP("127.0.0.1")
	smtp.sendmail(sender, recipients, message.as_string())
	smtp.close()

Job is done, with over 7GB of always-accessible online storage for my backup files.

facebooktwittergoogle_plusredditpinterestlinkedinmailfacebooktwittergoogle_plusredditpinterestlinkedinmail