Supernifty – nifty stuff

Backing up is important.

If you host a website which has a database, and you’re not backing it up, you should.

If your database was wiped, and you lost your entire blog, wiki, customer details, and the rest, if you’re like me, you’d be deeply unhappy.

Most webhosting companies do backups, but are somewhat vague on guarantees and process. They often charge to do restores as well.

Best bet is to do it yourself.

This set of instructions applies to MySQL, and can be applied to one database or many.

Step 1. Backup your databases on the server
Below is a python script that will backup multiple MySQL databases. Put this on your web server, preferably in a directory called backup, with the name backup.py.

import os
 
databases = dict()
databases['*** db1 ***'] = '*** password1 ***'
databases['*** db2 ***'] = '*** password2 ***'
databases['*** db3 ***'] = '*** password3 ***'
 
def set_pass( name ):
 file = open( '/*** your home directory ***/.my.cnf', 'w' )
 file.writelines( ( '[client]\n', 'password=%s\n' % databases[name] ) )
 file.flush()
 file.close
 
for key in databases.keys():
 set_pass( key )
 os.system( "mysqldump --user %s --opt %s > %s.sql" % ( key, key, key ) )
 
os.system( "zip backup.zip *.sql" )
os.system( "rm *.sql" )
os.system( "chmod 600 backup.zip" )

About this script:

• A problem with backing up multiple databases without intervention is the entering of passwords in a secure way. This script uses .my.cnf to set the password securely.
• Fill in the database usernames and passwords. This script assumes the database has the same name as the username.
• Fill in your home directory e.g. /home/john
• The result of this script is backup.zip, in the current directory.

Step 2. Copy backup to your machine
This script connects to your web server, runs the above backup script, then copies the result back to your local machine. Put this on your local machine.

ssh -i "your_key" you@yourhost.com "cd backup; python backup.py"
rsync -avz --rsh="ssh -i your_key" you@yourhost.com:~/backup/ backup

About this script:

• This script assumes the backup script is in the “backup” directory on your web server.
• You need ssh and rsync to use this script. On Windows, PuTTY provides a great ssh client, but I don’t know of a good rsync implementation, other than cygwin. Instead of rsync, you could use scp, which comes with PuTTY.
• If you want to automate this script (see later), you’ll want passwordless ssh, which means setting up “your_key”. Google on how to set this up.

Step 3. Repeat periodically
You want to automate this process so you can forget all about it.

• Unix: crontab. If you’re unfamiliar with crontab, try “man crontab”. The main commands to use are “crontab -e” and “crontab -l”.
• Windows: scheduler. Check out Control Panel->Scheduled Tasks, and add your client script.
• As mentioned above, you need passwordless ssh so your scheduled task can run unattended.

There’s your nutshell guide to backing up multiple MySQL databases with Python.