Automatic Mysql Backup

PS (15/05/2008) : my boss has written a new script in shell : mysql-backup.sh.txt

You just change your information at the top of the file (user, password…) and you put it in /etc/cron.daily

You should choose a remote folder for you backup.

Warning : do not keep the .sh at the end of the file once in /etc/cron.daily , on debian etch it’s not allowed

——

My boss has written a very nice code (in Python) to have a backup of every mysql databases.

To use it I had to install mysql library of python on my debian server:

apt-get install python-mysqldb

Then after I can use this script in your crontab:

/usr/bin/python mysqlbackup.py -r 7 -a -u MYUSER -p MYPASSWORD -H localhost -f /home/bakcup/mysql

You can get all options like that:

/usr/bin/python mysqlbackup.py --help

The most important option is “-r”, you can choose how many days you want to keep, to I always have 7 folders :

root@server:/home/backup/mysql/offroad# ll
total 5492
-rw-r--r-- 1 root root 822902 2008-04-29 17:56 offroad_20080429.sql
-rw-r--r-- 1 root root 827306 2008-04-30 03:00 offroad_20080430.sql
-rw-r--r-- 1 root root 823815 2008-05-01 03:00 offroad_20080501.sql
-rw-r--r-- 1 root root 792233 2008-05-02 03:00 offroad_20080502.sql
-rw-r--r-- 1 root root 794411 2008-05-03 03:00 offroad_20080503.sql
-rw-r--r-- 1 root root 761013 2008-05-04 03:00 offroad_20080504.sql
-rw-r--r-- 1 root root 764510 2008-05-05 03:00 offroad_20080505.sql

Here is the code of mysqlbackup.py

import traceback
import datetime
import os
import sys
import re

def backup_folder(options,database):
if options.subdir:
result = os.path.join(options.folder,database)
try:
os.makedirs(result)
except:
pass
return result
else:
return options.folder

def find_previous_backups(options,database):
file_re = re.compile(database+r'_(\d{4})(\d{2})(\d{2})\.sql(\.bz2)?',re.I)

result = []
now = datetime.datetime.now()
folder = backup_folder(options,database)
for filename in os.listdir(folder):
m = file_re.match(filename)
if m is not None:
age = now - datetime.datetime(int(m.group(1)),int(m.group(2)),int(m.group(3)))
fullname = os.path.join(folder,filename)
result.append((age,fullname))

result.sort()

return result

def main(options):
options.excludedatabases=set(db.lower() for db in options.excludedatabases)

if options.alldatabases:
try:
import MySQLdb
except:
print "Could not import MySQLdb ; you should probably install it"
print "Fetch it from the 'MySQL for Python' Sourceforge project :"
print "\thttp://sourceforge.net/project/showfiles.php?group_id=22307"
return

connection = MySQLdb.connect(options.host,options.user,options.password)
try:
cursor = connection.cursor()
try:

cursor.execute('show databases')
for row in iter(cursor.fetchone,None):
db=row[0].lower()
if db not in options.excludedatabases:
options.databases.append(row[0])

finally:
cursor.close()
finally:
connection.close();

for database in options.databases:
try:
now = datetime.datetime.now()
filename = database+'_%04i%02i%02i.sql'%(now.year,now.month,now.day)
directory = backup_folder(options,database)
fullname = os.path.join(directory,filename)

if options.mysqlpath:
mysqldump = os.path.join(options.mysqlpath,'mysqldump')
else:
mysqldump = 'mysqldump'

if options.compress:
command = """%s --host="%s" --user="%s" --password="%s" "%s" | %s -tbzip2 -mx=5 -si a "%s.bz2" """%(
mysqldump,
options.host,
options.user,
options.password,
database,
os.path.join(os.path.dirname(sys.argv[0]),"7za.exe"),
fullname
)
command = "%s " % (
command,
)
else:
command = """%s --host="%s" --user="%s" --password="%s" -r "%s" "%s" """%(
mysqldump, options.host, options.user, options.password, fullname, database
)

print "! %s"%command
os.system(command)

if options.max_age is not None:
for age, fullname in find_previous_backups(options,database):
if age.days > options.max_age:
print "- %s"%fullname
os.remove(fullname)

if options.command :
command = options.command%dict(backup=fullname)
print '! %s'%command
os.system(command)
except:
traceback.print_exc()

if __name__=='__main__':
import optparse
parser = optparse.OptionParser()

parser.add_option('-m','--mysql-path',dest='mysqlpath',help='MySQL binaries path')

parser.add_option('-H','--host',dest='host',help='MySQL host')
parser.add_option('-u','--user',dest='user',help='MySQL user')
parser.add_option('-p','--password',dest='password',help='MySQL password')

parser.add_option('-a','--all-databases',action='store_true',dest='alldatabases',default=False,help="Sauvegarde toutes les bases de donnees")
parser.add_option('-d','--database',action='append',dest='databases',default=[],help="Nom d'une base de donnees a include")
parser.add_option('-D','--exclude-database',action='append',dest='excludedatabases',default=[],help="Nom d'une base de donnees a exclure")

parser.add_option('-f','--folder',dest='folder',default='.',help='Backup folder')
parser.add_option('-S','--no-subdirs',action='store_false',dest='subdir',default=True,help='Don\'t create a separate folder per database')
parser.add_option('-r','--remove',type='int',dest='max_age',help='Remove backup files older than MAX_AGE days')
parser.add_option('-z','--zip',action='store_true',dest='compress',default=False,help='Compress the file')

parser.add_option('-c','--command',dest='command',default=False,help='Execute this command when a database backup is over. Use "%(backup)s" to substitute the file name')

options, args = parser.parse_args()
main(options)

Leave a Reply

Your email address will not be published. Required fields are marked *