Python Backup Script For MySQL Automatically

Do you like this?

Summary:
This Python snippet can help you do backup for MySQL database remotely and then download it to your local hard drive.


Content:

To use this script, you have to install the following dependencies:

  • Python 2.7 or above
  • Paramiko
  • ftputil
On Windows machine, you might encounter errors setting up Paramiko since it cannot find vcvarsall.bat file. What you can do is downloading a setup file from Voidspace and then install Paramiko using the file.

This script requires command line inputs. Following is a sample of how to use the script:
mysql_backup.py --host your-server.com --host_username ftp_username --host_password ftp_password --db_name dbname --db_username dbusername --db_password dbpassword --file_out file.sql --local_dir "\path\to\local\backup\folder"
All the arguments are self explanatory. You just have to change them according to your setup. What it does is as follows:
  1. SSH to your server
  2. Remove any folder with the name "python_backup_mysql"
  3. Create a folder with the name "python_backup_mysql"
  4. Run MySQL backup command: mysqldump -u dbusername dbname -p<dbpassword> > python_backup_mysql/file.sql
  5. FTP to your server
  6. Download the file.sql to your local hard drive "\path\to\local\backup\folder"
  7. Name the file with the following format: <Year><Month><Day><Hour><Minute>_file.sql
At first I used ftplib to download the backup file from the server but then I discovered the library didn't actually manage disconnected connections. In other words, the script downloaded the backup files some of which were incomplete. Hence, I switched to ftputil which is a high-level interface to ftplib and it can handle the issue which was due to timeout. Bear in mind that, I have increased the ftp timeout to be 120 seconds or even more for the ftplib as follows but still it couldn't make sure all the files were downloaded completely. Therefore, you can download the ftputil backup script and the ftplib backup script is only for reference.
settings.ftp_timeout= 120
You can download the file here:
# this is the script which I used ftputil
import ftputil
import argparse, sys, os
import traceback
import paramiko
import datetime
import logging

class AttributeDict(dict):
    def __init__(self):
        super(AttributeDict,self).__init__()
        __getattr__ = super(AttributeDict,self).__getitem__
        __setattr__ = super(AttributeDict,self).__setitem__

settings= AttributeDict()
settings.remote_backup_folder= "python_backup_mysql"

def get_remote_file_path(args):
    return '%s/%s' % (settings.remote_backup_folder,args.file_output)

def get_arguments():
    parser = argparse.ArgumentParser()
    parser.add_argument("-hu", "--host_username", action="store", type=str)
    parser.add_argument("-hp", "--host_password", action="store", type=str)
    parser.add_argument("-ho", "--host", action="store", type=str)
    parser.add_argument("-d", "--db_name", action="store", type=str)
    parser.add_argument("-du", "--db_username", action="store", type=str)
    parser.add_argument("-dp", "--db_password", action="store", type=str)
    parser.add_argument("-fo", "--file_output", action="store", type=str)
    parser.add_argument("-ld", "--local_dir", action="store", type=str)

    return parser.parse_args(sys.argv[1:])

def server_backup_step(args,client):
    logging.debug('Removing remote folder [%s]' % settings.remote_backup_folder)
    client.exec_command("rm -r %s" % settings.remote_backup_folder)
    logging.debug('Recreating remote folder [%s]' % settings.remote_backup_folder)
    client.exec_command("mkdir %s" % settings.remote_backup_folder)

    backup_command= "mysqldump -u %s %s -p%s > %s" % \
                        (args.db_username,args.db_name, \
                         args.db_password,get_remote_file_path(args))
    logging.debug('Running remote command: %s' % backup_command)
    client.exec_command(backup_command)

def download_backup(args,host):
    if not os.path.exists (args.local_dir):
        os.mkdir (args.local_dir)
    d = datetime.datetime.now()
    download_file_path= os.path.join(args.local_dir,('%s-%s'%(d.strftime('%Y%m%d%H%M'),args.file_output)))
    host.download(get_remote_file_path(args), download_file_path, 'b')

def backup_ssh(args):

    username = args.host_username
    port = 22
    hostname = args.host
    password= args.host_password

    try:
        logging.debug('Connecting SSH to %s' % hostname)
        client = paramiko.SSHClient()
        client.load_system_host_keys()
        client.set_missing_host_key_policy(paramiko.WarningPolicy())
        client.connect(hostname, port, username, password)
        logging.debug('Starting to do backup')
        server_backup_step(args,client)

        logging.debug('Connecting FTP to %s' % hostname)
        host = ftputil.FTPHost(args.host, args.host_username, args.host_password)
        logging.debug('Downloading backup file')
        download_backup(args,host)
        logging.debug('Closing down SSH and FTP')
        client.close()
        host.close()
        logging.debug ('Closed SSH and FTP')
    except Exception, e:
        logging.error('Exception when doing backup remotely or downloading the file')
        logging.error('Exception: %s: %s' % (e.__class__,e))
        print '*** Caught exception: %s: %s' % (e.__class__, e)
        traceback.print_exc()
        try:
            client.close()
            ftp.quit()
        except:
            pass
        sys.exit(1)

if __name__ == "__main__":
    args= get_arguments()

    #configuring logging files
    paramiko.util.log_to_file(os.path.join(args.local_dir,'paramiko-session.log'))
    logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s %(levelname)s %(message)s',
                    filename=os.path.join(args.local_dir,'logging.txt'),
                    filemode='a')
    #setting default values
    if not args.file_output:
        args.file_output= args.db_name
    if not args.db_username:
        args.db_username= args.host_username
        args.db_password= args.host_password

    #backup
    backup_ssh(args)
    

# this is the script which I used ftplib
from ftplib import FTP
import argparse, sys, os
import traceback
import paramiko
import datetime
import logging

class AttributeDict(dict):
    def __init__(self):
        super(AttributeDict,self).__init__()
        __getattr__ = super(AttributeDict,self).__getitem__
        __setattr__ = super(AttributeDict,self).__setitem__

settings= AttributeDict()
settings.remote_backup_folder= "python_backup_mysql"
settings.ftp_timeout= 120

def get_remote_file_path(args):
    return '%s/%s' % (settings.remote_backup_folder,args.file_output)

def get_arguments():
    parser = argparse.ArgumentParser()
    parser.add_argument("-hu", "--host_username", action="store", type=str)
    parser.add_argument("-hp", "--host_password", action="store", type=str)
    parser.add_argument("-ho", "--host", action="store", type=str)
    parser.add_argument("-d", "--db_name", action="store", type=str)
    parser.add_argument("-du", "--db_username", action="store", type=str)
    parser.add_argument("-dp", "--db_password", action="store", type=str)
    parser.add_argument("-fo", "--file_output", action="store", type=str)
    parser.add_argument("-ld", "--local_dir", action="store", type=str)

    return parser.parse_args(sys.argv[1:])

def server_backup_step(args,client):
    logging.debug('Removing remote folder [%s]' % settings.remote_backup_folder)
    client.exec_command("rm -r %s" % settings.remote_backup_folder)
    logging.debug('Recreating remote folder [%s]' % settings.remote_backup_folder)
    client.exec_command("mkdir %s" % settings.remote_backup_folder)

    backup_command= "mysqldump -u %s %s -p%s > %s" % \
                        (args.db_username,args.db_name, \
                         args.db_password,get_remote_file_path(args))
    logging.debug('Running remote command: %s' % backup_command)
    client.exec_command(backup_command)

def download_backup(args,ftp):
    if not os.path.exists (args.local_dir):
        os.mkdir (args.local_dir)
    d = datetime.datetime.now()
    download_file_path= os.path.join(args.local_dir,('%s-%s'%(d.strftime('%Y%m%d%H%M'),args.file_output)))
    f = open(download_file_path,'wb')
    ftp.retrbinary('RETR %s' % get_remote_file_path(args), f.write)

def backup_ssh(args):

    username = args.host_username
    port = 22
    hostname = args.host
    password= args.host_password

    try:
        logging.debug('Connecting SSH to %s' % hostname)
        client = paramiko.SSHClient()
        client.load_system_host_keys()
        client.set_missing_host_key_policy(paramiko.WarningPolicy())
        client.connect(hostname, port, username, password)
        logging.debug('Starting to do backup')
        server_backup_step(args,client)

        logging.debug('Connecting FTP to %s' % hostname)
        ftp = FTP(args.host,args.host_username,args.host_password, timeout= settings.ftp_timeout)
        logging.debug('Downloading backup file')
        download_backup(args,ftp)
        logging.debug('Closing down SSH and FTP')
        client.close()
        ftp.quit()
        logging.debug ('Closed SSH and FTP')
    except Exception, e:
        logging.error('Exception when doing backup remotely or downloading the file')
        logging.error('Exception: %s: %s' % (e.__class__,e))
        print '*** Caught exception: %s: %s' % (e.__class__, e)
        traceback.print_exc()
        try:
            client.close()
            ftp.quit()
        except:
            pass
        sys.exit(1)

if __name__ == "__main__":
    args= get_arguments()
    
    #configuring logging files
    paramiko.util.log_to_file(os.path.join(args.local_dir,'paramiko-session.log'))
    logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s %(levelname)s %(message)s',
                    filename=os.path.join(args.local_dir,'logging.txt'),
                    filemode='a')
    #setting default values
    if not args.file_output:
        args.file_output= args.db_name
    if not args.db_username:
        args.db_username= args.host_username
        args.db_password= args.host_password

    #backup
    backup_ssh(args)
    

 
comments powered by Disqus