bag.dump_mysql module

Utility module for the poor souls who have to deal with MySQL.

How to set this up on your server:

sudo apt-get install python-setuptools virtualenvwrapper
# Create a directory for this project
mkdir ~/mysql-dumps; cd ~/mysql-dumps
# Create a virtualenv and install the bag library in it
mkvirtualenv -p `which python3` dumper
pip install bag

Then edit a file called dump_db.py with more or less the following content. The example assumes you are using WordPress; read it carefully:

#!/usr/bin/env python3

""\"Backs up MySQL database once.""\"

from datetime import datetime
from bag.dump_mysql import DumpMySQL

dumper = DumpMySQL.from_wordpress_config(
    config_path='/home/deployer/my_wordpress_site/wp-config.php',
)
destination_path = str(datetime.utcnow())[:19] + '.sql.gz'
dumper.dump(destination_path)

# It is also possible to send the backup to an S3 bucket,
# but please remember to secure access to the bucket:
result = dumper.copy_to_s3(
    destination_path, aws_id='REDACTED', aws_secret='REDACTED',
    aws_region='REDACTED', bucket_name='production-db-backups')

Give permission for the above script to be executed:

chmod +x dump_db.py

Run the script once to test it:

./dump_db.py

If it is working, create a shell script, called dump_db.sh, for cron to call:

cd /home/deployer/mysql-dumps
# Run dump_db.py under the appropriate virtualenv:
/home/deployer/.virtualenvs/dumper/bin/python dump_db.py

Give permission for the above shell script to be executed:

chmod +x dump_db.sh

Then add a cronjob for it with the command crontab -e:

# Minute Hour Day Month Weekday Command
07       */8  *   *     *       /home/deployer/mysql-dumps/dump_db.sh > /tmp/crondump 2>&1
# The above creates a database backup every 8 hours.

class bag.dump_mysql.DumpMySQL(database, user, password, host='localhost', dump_command_path='/usr/bin/mysqldump', log_directory='.')

Bases: object

Dumps MySQL database to SQL file. Can send the file to an S3 bucket.

copy_to_s3(path, aws_id, aws_secret, aws_region, bucket_name, namespace='')

Send the file path to an S3 bucket.

dump(destination_path, gzip='gzip')

Actually perform the database dump.

When you create a gzipped dump, you can read it like this:

zcat path/to/my-dump.sql.gz | less

classmethod from_wordpress_config(config_path='./wp-config.php', encoding='utf-8', **kw)

Read WordPress configuration file to get database credentials.

get_dump_cmd(destination_path, gzip='gzip')

Return list/command to dump this environment’s database to SQL.