Sunday, June 16, 2013

Backup and Recovery Script for MySQL using Percona's innobackup and Xtrabackup

So Percona has the widely used backup tool Xtrabackup and they realize that everyone often uses this tool in a script of some kind. The have a page that talks about this:
http://www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/scripting_backups_xbk.html?id=percona-xtrabackup:xtrabackup:scripting

Since I recently gave an example of how to use the backup in a previous post. I figured I might as well write a script that shows how to script the backup process. Plus it has been years since I wrote in Python so I wanted to get a little practice as well.

So the introduction to the code is below but I have placed the script on github.
It needs more testing but feel free to check the codebase out and update and edit.

Once the code is tested more I can update examples but I wanted to be open with this project from the start.

Since it is in the early stages I would recommend using the --showcommands=1 option so you can see what the code plans to do and maybe try those commands. Obviously it should not be used on a production system yet.


First an introduction to it:

# ./backup_restore.py --help
Usage: backup_restore.py  --process=[fullbackup,incremental,prepare,restore] --help --version --showcommands=1

This program enables you to backup full and incremental backups then prepare
and restore them using Percona's Xtrabackup

Options:
  --version             show program's version number and exit
  -h, --help            show this help message and exit
  --process=PROCESS     What would you like to do --process=
                        [fullbackup,incremental,prepare,restore]
  --debug=DEBUG         TURN DEBUG ON 1 OR OFF 0 OR VERBOSE 3
  --showcommands=SHOWCOMMANDS
                        Shows the commands instead of executing them except
                        for the restore section because we go through that
                        step by step
  --backup_root_directory=BACKUP_ROOT_DIRECTORY
                        THE ROOT DIRECTORY OF ALL YOUR BACKUPS, You can set
                        DEFAULT at start of the script
  --percona_xtrabackup_location=PERCONA_XTRABACKUP_LOCATION
                        THE LOCATION OF YOUR xtrabackup FILE, You can set
                        DEFAULT at start of the script
  --datadir=DATADIR     MYSQL DATA DIR LOCATION, You can set DEFAULT at start
                        of the script
  --username=DB_USERNAME
                        MySQL Username, You can set DEFAULT at start of the
                        script
  --password=DB_PASSWORD
                        MySQL Password, You can set DEFAULT at start of the
                        script
  --default_file=DEFAULT_FILE
                        MySQL my.cnf file location, You can set DEFAULT at
                        start of the script
  --options=PERCONA_OPTIONS
                        Additional Options for innobackupex