I’m publishing a simple script I wrote a year or so ago to do automatic mysql backups with 7zip on a linux server. It is inspired by the original mysqlbackup script, in that it can rotate the databases on a daily/weekly etc. basis, but it also provides stuff like exporting to an ftp server, or simpler backups. It isn’t completely polished, so any forks/tips are welcome!
I’ve made a permanent page here with a download link and some more about it.
#!/usr/bin/python
#################################
# created by Alex Knaust 06/2011
#################################
# mysql login credentials
MYSQL_USERNAME = ''
MYSQL_PASSWORD = ''
MYSQL_HOST = 'localhost'
MYSQL_PORT = 3306
# directory that directories (daily, weekly, monthly, etc.) will be created in
BACKUP_DIR = '/backup/mysql'
# filename pattern to archive files as, will be formatted with datetime.strftime
FILENAME_PATTERN='%d-%m-%Y_database-dump.7z'
# arguments for the p7zip program
P7ZIP_ARGS = '-t7z -bd -m0=LZMA2 -mx=9'
#file to write the intermediate dump to
TMPFILE = '/var/tmp/dbdump.sql'
# arguments for the mysqldump command (specify which databases here)
MYSQLDUMP_ARGS = '--all-databases --force'
# octal permissions for the database dump
PERMS = 0o640
###############################################################################
# DO NOT EDIT BELOW HERE
###############################################################################
from datetime import datetime, timedelta
import shutil, os, subprocess
from ftplib import FTP
from syslog import syslog
def dumpmysqldb(user, password, port, host, args='--all-databases --force',
filename=TMPFILE):
'''Runs mysqldump to backup all databases to filename, args are passed to mysqldump
raises an Exception if the filename does not exist afterwards
'''
# set permissions of PERMS file to be written to
with open(filename, 'w') as f:
pass
os.chmod(filename, PERMS)
mysqldumpcommand = '''mysqldump --user="{user}" --password="{passwd}" --host={host} --port={port} {args} > {output}'''.format(
user=user, passwd=password, output=filename, args= args, host=host, port=port)
pipe = subprocess.Popen(mysqldumpcommand, shell=True, stdout=subprocess.PIPE)
pipe.communicate()
if not os.path.isfile(filename):
syslog("{0} doesnt seem to exist although it should".format(filename))
raise Exception("{0} doesnt seem to exist, although it should".format(filename))
def compress_7z(path, args='-bd', outfile=None):
'''compresses a file with p7zip, outputting as .7z file, returns the name of the
compressed file
'''
if not outfile:
outfile = os.path.join(os.path.split(path)[0], 'dump.7z')
p7zipcommand = '''7z a {args} "{outfile}" "{filename}"'''.format(
args=args, filename=path, outfile=outfile)
pipe2 = subprocess.Popen(p7zipcommand, shell=True, stdout=subprocess.PIPE)
pipe2.communicate()
if not os.path.isfile(outfile):
syslog("{0} was not created correctly".format(outfile))
raise Exception("{0} was not created correctly".format(outfile))
else:
os.chmod(outfile, PERMS)
return outfile
class BackupUpdater:
'''Class that handles deleting old files'''
def __init__(self):
raise NotImplementedError
def update(self, time, file):
'''This will be called when a new dump is created'''
raise NotImplementedError
class BasicBackupUpdater(BackupUpdater):
'''Just saves the files in the directory given'''
def __init__(self, directory, fileformat='%d-%m-%Y_database-dump'):
self.directory = directory
self.fileformat = fileformat
if not os.path.isdir(self.directory):
os.makedirs(self.directory)
def update(self, time, file):
newfilename = time.strftime(self.fileformat)
shutil.move(file, os.path.join(self.directory, newfilename))
class FTPBasicBackupUpdater(BackupUpdater):
'''Saves the file to a directory on an FTP Server'''
def __init__(self, directory, user, passwd, host, fileformat='%d-%m-%Y_database-dump'):
self.fileformat = fileformat
self.FTP = FTP(host=host, user=user, passwd=passwd)
self.ftp.cwd(directory)
def update(self, time, file):
newfilename = time.strftime(self.fileformat)
with open(newfilename, 'rb') as tfile:
self.FTP.storbinary("STOR " + newfilename, tfile)
ftp.quit()
class AdvancedBackupUpdater(BasicBackupUpdater):
'''Works by rotating the backups into a folder hierarchy based on their
creation time, i.e. after 1 week the daily folder will be emptied and the
oldest file will be moved to weekly, and the newest file will now be in daily
, does the same for weekly, monthly, yearly.
'''
folders = (
('daily', timedelta(weeks=1)),
('weekly', timedelta(weeks=5)),
('monthly', timedelta(weeks=52)),
('yearly', timedelta.max), #if this program works for more than 2 million years, we're in trouble
)
def __init__(self, directory, fileformat):
BasicBackupUpdater.__init__(self, directory, fileformat)
def _getAge(self, directory):
'''Returns a datetime object representing the oldest creation date in a directory'''
filelist = [os.path.join(directory, f) for f in os.listdir(directory) if os.path.isfile(os.path.join(directory, f))]
if filelist:
filelist.sort(key= lambda f: os.path.getmtime(f))
return datetime.fromtimestamp(os.path.getmtime((filelist[0]))), filelist[0]
else: return datetime.now(), None
def _updatePushing(self, time, filename, index=0):
folder = os.path.join(self.directory, self.folders[index][0])
delta = self.folders[index][1]
if not os.path.isdir(folder):
os.makedirs(folder)
oldesttime, oldestfile = self._getAge(folder)
#if its time to rotate, push them up with a recursive call
if time - oldesttime > delta:
self._updatePushing(oldesttime, os.path.join(folder, oldestfile), index+1)
for file in [os.path.join(folder, f) for f in os.listdir(folder) if os.path.isfile(os.path.join(folder, f))]:
os.remove(file)
try:
shutil.move(filename, folder)
except shutil.Error:
syslog("Error : file {0} already exists".format(os.path.join(folder, filename)))
print "Error : file {0} already exists".format(os.path.join(folder, filename))
pass #probably already existed
def update(self, time, filename):
nf = os.path.join(os.path.split(filename)[0],
time.strftime(self.fileformat))
shutil.move(filename, nf)
self._updatePushing(time, nf, index=0)
if __name__=='__main__':
print 'Dumping databases...'
syslog('Dumping databases...')
dumpmysqldb(user=MYSQL_USERNAME, password=MYSQL_PASSWORD,
host=MYSQL_HOST, args=MYSQLDUMP_ARGS, port=MYSQL_PORT, filename=TMPFILE)
print 'Compressing {0} with 7z...'.format(TMPFILE)
syslog('Compressing {0} with 7z...'.format(TMPFILE))
cmpfile = compress_7z(TMPFILE, args=P7ZIP_ARGS)
print 'Updating backups with {0} in {1}'.format(cmpfile, BACKUP_DIR)
syslog('Updating backups with {0} in {1}'.format(cmpfile, BACKUP_DIR))
b = AdvancedBackupUpdater(BACKUP_DIR, FILENAME_PATTERN)
b.update(datetime.now(), cmpfile)
print'Success'