Redundant MySQL backup with Python

The Story:
Ok so we lost our entire company wiki the day of launch... luckily I was able to recover over 60% of it from the cache of MANY people's computers (I just wrote a script that filtered their cache and spit out the wiki content pages, then a bunch of us went through the pages)... but that is a whole different subject. (Little Note: I was not responsible for backing up or transferring the database)

The Solution:
Since then I have been pretty paranoid about running backups and have volunteered for the task (because I have done a LOT of work on the wiki). I understand MySQL Replication, which is very powerful, but sometimes you just want the sql... so I created a redundant mysqldump script that will create zipped backups in the following structure:

<root>
<year>
January.zip
February.zip
...
Week1.zip
Week2.zip
Monday.zip
Tuesday.zip
...

The Script:
The script is pretty short (under 50 lines long). I put the parameters right into the script, but you could easily add them as options. I also have it backing up all databases. This could be run as a cron job/Scheduled Task and should run on Linux, Mac and Windows.
sql_backup.py<br />import os, time, sys, zipfile, datetime,calendar<br />from os import sep<br /><br /># Script Parameters<br /># Target Directory for final Zip files<br />target_dir = r'target' <br /># The name of the sql file (this will be the same for all backups)<br />file_name = 'databases.sql' <br /># Authentication<br />user = 'username' <br />password = 'password'<br /># The command for the mysql dump. This could include the entire path to the executable if mysqldump is not on the path environment variable<br />dumpcmd = r'mysqldump' <br /># The host you want to connect to<br />host = 'example.com' <br /><br />args = [dumpcmd, '-C', '-f', '--host=%s' %host, '--user=%s' %user, '--password=%s' %password, '--result-file=%s' %file_name, '--all-databases']<br /><br />os.chdir(target_dir)<br />cmd = ' '.join(args)<br />print 'Running Command: %s' %cmd.replace(password, '*'*len(password))<br />os.system(cmd)<br /><br /># Below is for zipping the files and creating a redundant backup<br />def getZipFileName():<br /> # Set time variables (<br /> # Note: you don't have to use some of them, <br /> # but they make the script easier to understand<br /> t = time.time(); g = time.gmtime(t)<br /> year = g[0]; month = g[1]; day = g[2]<br /> <br /> try:<br /> datetime.date(year, month, day+1)<br /> except:<br /> # Last day of the month, return month name<br /> root = time.strftime('%Y', g)<br /> if not os.access(root, os.F_OK):<br /> os.mkdir(root)<br /> return os.path.join(root, '%s.zip' %time.strftime('%b'))<br /> <br /> if int(time.strftime('%w', g)) == 0:<br /> # Last day of week, return week number<br /> cal_month = calendar.monthcalendar(year, month)<br /> for week in cal_month:<br /> if day in week:<br /> return 'Week%i.zip' %month.index(week)<br /> # Just a regular weekday...<br /> return '%s.zip' %time.strftime('%A')<br /><br /># Write the zip file<br />zip_name = getZipFileName()<br />zip_file = os.path.abspath(zip_name)<br />zip = zipfile.ZipFile(zip_file, 'w', zipfile.ZIP_DEFLATED)<br />zip.write(file_name)<br />zip.close()<br /><br /># Remove uncompressed sql file<br />os.remove(file_name)<br />to_path = os.path.join(copy_dir, zip_name)<br />to_dir = sep.join(to_path.split(sep)[:-1])<br />if not os.access(to_dir, os.F_OK):<br /> os.makedirs(to_dir)<br />if os.access(to_path, os.F_OK):<br /> os.remove(to_path)<br />shutil.copy(zip_file, to_path)<br />Further Explanation:
The script is five parts:

1. Parameters: This is really all you need to alter for the script to work for you. You will need to specify:
• Target- The directory for all redundant backups
• File Name- The name for all .sql files
• Username/password- The authentication data used for signing into mysql
• Dump command- ok this is just the command used to call mysqldump. You may need to include the full path to the executable if the mysql bin directory is not on your environment path variable
• Host- this is your host- example.com
2. The command for the dump
   
3. Name Resolver: This just resolves the name of the backup (Monday, Tuesday, Week 1, Week 2, etc.)
4. Writing the Zip file
5. Cleanup- remove uncompressed sql file and move the zip file to the specified target