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 <firstname.lastname@example.org>' recipients = ['email@example.com','firstname.lastname@example.org'] # 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.