A simple script to automate MySQLdump backups

Mar 5, 2014 · 180 words · 1 minute read data_eng

I just moved my MySQL database to some OpenVZ VPS, which doesn’t support snapshot backups. Therefore I had to set up some backup mechanism myself.

The solution I came up with is to use BitTorrent Sync to sync my backups to the other server. It turns out to be much faster than transfering backups using scp and much easier (and perhaps more secure) than using FTP. I highly recommend BitTorrent Sync.

However, dumping mysql databases, archiving the results, and then moving into the syncing directory can still be tedious. Since the databases are big, sitting in front of the computer and waiting for the dump to complete is not an option. As a result, I wrote a simple script to automate the whole process using this amazing library sh. Here I provide a bootstrap code for anyone who is interested to start with:

Prerequisites

  • sh (python)
  • p7zip

Bootstrap Code

import sh
from datetime import date

filename = "database_name." + date.today().isoformat() + ".sql"
#perform full dump on the selected database
p = sh.mysqldump("database_name", "-p'password'", u="username", r=filename, _bg=True)
p.wait()

sh.p7zip(filename)
sh.mv(filename+".7z", "backup/")
tweet Share