We have lots of blogs to manage and keep backed up so we employ Python scripts that are scheduled to run every day and backup the full database sets. Python is a great language because you can use it for web development (WSGI/Django/etc), game development, automation, systems administration, desktop apps and many other things. We will have more python scripts here on how we use it to run things. Keep in mind these are cross platform because we manage applications on all types of environments from Windows, to UNIX/Linux to OSX (same as *nix really).
How to Use
Use this script by scheduling it to run nightly or when you choose. Then make a folder under it called target or change the location in the script.
The file that will export will be a complete sql dump and stamped like the following filename, it contains the zipped up database.sql file that is your backup:
export-2009-02-03-Tuesday.zip
If you need to change settings to get flat files or other read on mysqldump utility to pass the options you need, currently it backs up in SQL format.
The Script
import os, time, sys, zipfile, datetime,calendar from os import sep # Script Parameters # Target Directory for final Zip files target_dir = r'target' copy_dir = r'copy' # The name of the sql file (this will be the same for all backups) file_name = 'databases.sql' # Authentication user = '[YOUR USER]' password = '[YOUR PASSWORD]' # The command for the mysql dump. This could include the entire path to the executable if mysqldump is not on the path environment variable dumpcmd = r'mysqldump' # The host you want to connect to host = 'localhost' args = [dumpcmd, '-C', '-f', '--host=%s' %host, '--user=%s' %user, '--password=%s' %password, '--result-file=%s' %file_name, '--all-databases'] os.chdir(target_dir) cmd = ' '.join(args) print 'Running Command: %s' %cmd.replace(password, '*'*len(password)) os.system(cmd) # Below is for zipping the files and creating a redundant backup def getZipFileName(): # Set time variables ( # Note: you don't have to use some of them, # but they make the script easier to understand t = time.time(); g = time.gmtime(t) year = g[0]; month = g[1]; day = g[2] try: datetime.date(year, month, day+1) except: # Last day of the month, return month name root = time.strftime('%Y', g) if not os.access(root, os.F_OK): os.mkdir(root) return os.path.join(root, '%s.zip' %time.strftime('%b')) if int(time.strftime('%w', g)) == 0: # Last day of week, return week number cal_month = calendar.monthcalendar(year, month) for week in cal_month: if day in week: return 'Week%i.zip' %month.index(week) # Just a regular weekday... return '%s.zip' %time.strftime('%A') # Write the zip file zip_name = 'export-' + str(datetime.date.today()) + '-' + getZipFileName() zip_file = os.path.abspath(zip_name) zip = zipfile.ZipFile(zip_file, 'w', zipfile.ZIP_DEFLATED) zip.write(file_name) zip.close() # Remove uncompressed sql file os.remove(file_name) to_path = os.path.join(copy_dir, zip_name) to_dir = sep.join(to_path.split(sep)[:-1]) if not os.access(to_dir, os.F_OK): os.makedirs(to_dir) if os.access(to_path, os.F_OK): os.remove(to_path) shutil.copy(zip_file, to_path)

