The first version of PyMyDB Backup has been released and is based on Python 2.4.3. This is a Python script that will back up your MySQL databases, calculate the size of the backed up files, tarball and compress the contents, then email the results.

To use:

1. Download the EPEL RPM which can be found at: http://download.fedora.redhat.com/pub/epel/
2. Install pymydb-0.5.1-1.noarch.rpm which can be found at: GitHub
• This script creates the necessary directories and user account
3. After installation, there will be two scripts placed in /usr/local/bin which are pymydb.py and setup.py
4. Run the setup.py script, this will configure the pymydb.py script
• Logs into the MySQL server
• Creates the backup user, sets a password, and gives him select and lock tables privileges
• Configures the email settings
5. Change the permissions on the pymydb.py script
• chown root.pymydb pymydb.py
6. Add the pymydb.py script into cron and make sure to add a password for the system user

I needed a place to host my scripts, so I opened an account on GitHub. If you are unaware of what GitHub is, its a great place to publish code to the public by using Git; they also offer private hosting for a fee. Even if you do not have a need for this I recommend looking through the site as its a great place to view open source projects.

There are a lot of examples and scripts out on the Internet to automate MySQL backups but not a whole lot written in Python. This was developed to use in a MySQL replication environment and should be run on the slave server. This is an optimal solution as backup’s can run without affecting production. The script dumps each database individually, calculates the size of the sql backup, tarball them up, and emails a log when its done.

To run this the MySQLdb module must be installed on the server. You should also create a user specifically designed to run backups. You can effectively do this by running:

grant select, lock tables on *.* to backup@'localhost' identified by 'password';

The password is stored in base64 format. To encrypt the password for use in the script, run the following command within the Python terminal:


>>> from base64 import b64encode as encode
>>> encode("password")
'cGFzc3dvcmQ='

Though this is not the most secure way to encrypt a password to use within a script, it will prevent shoulder surfers.

Here it is, enjoy – https://github.com/jasonbrown17/MySQL-Backups

import os
diskSpace = os.statvfs(‘/’)
(diskSpace.f_bavail * diskSpace.f_frsize) / (1024 * 1024)