Mysql backup using Python

It's been a long while since I posted so I am back with a relatively simple post today and will try to keep the posts coming.

This post will show a simple python script that will go through the databases available to a MySQL user. It will check each database for a configuration table which tells the script if it is to be backed up and who to notify when the backup is complete.

This has been built and tested using a Linux system but shouldn't take much to convert to other platforms as well.

An idea to extend this script could be to add in the ability to transfer the backup to an ftp location. This is a very simple solution that is really only useful if you have a number of MySQL databases that need to be backed up separately and even have different people notified when a backup is complete. It could easily be modified to just backup all the databases and then when done send an email to yourself to let you know it's been done. You could even add in checking to see if any of the backups failed and notify of failure.

It should be pretty easy to extend upon this script to suit your needs.

Here it is :

#!/usr/bin/env python
import os
import time
import MySQLdb
import sys

SENDMAIL = "/usr/sbin/sendmail" # sendmail location

username = "backupuser"
password = "backuppass"
hostname = "localhost"

filestamp = time.strftime('%d-%m-%Y')

# Get a list of databases on the mysql server
database_list_command="mysql -u %s -p%s -h %s --silent -N -e 'show databases'" % (username, password, hostname)
for database in os.popen(database_list_command).readlines():
    database = database.strip()
   
    # Check if the auto backup module is enabled and get the notification data
    notify_db = MySQLdb.connect( user=username,passwd=password,db=database)
    c = notify_db.cursor()
   
    try:
        c.execute("SELECT `notify_email`,`enabled` FROM `backup_config` WHERE 1 LIMIT 1")
        result = c.fetchone()
        notify_to = result[0]
        enabled = result[1]
    except MySQLdb.Error, e:
        continue
   
    if enabled == 1:
        filename = "/backups/mysql/%s-%s.sql" % (database, filestamp)
        os.popen("mysqldump -u %s -p%s -h %s -e --opt -c %s | gzip -c > %s.gz" % (username, password, hostname, database, filename))
       
        FROM = "backup@mydomainhere.com.au"
       
        if notify_to == "":
            notify_to = "backup@mydomainhere.com.au"
           
        TO = [notify_to]    # using a list to make it easier to support multiple addresses
        SUBJECT = "Backup Complete Notification"
        TEXT = "The latest database backup has been complete."

        # Prepare actual message
        message = """From: %s\nTo: %s\nSubject: %s\n\n%s""" % (FROM, ", ".join(TO), SUBJECT, TEXT)

        # Send the mail
        p = os.popen("%s -t -i" % SENDMAIL, "w")
        p.write(message)
        status = p.close()
        if status:
            print "Sendmail exit status", status