Python MySQLDump Scripts

[Skip Montanaro]

    Gavin> I've done a bit of search on the web for python scripts to
    Gavin> automate the backup of MySQL databases but haven't had much
    Gavin> success. I've come across a decent PERL module and are thinking
    Gavin> about porting that, however I just wished to see if anyone has
    Gavin> any snippets available prior to taking this path. I do have
    Gavin> specific criteria however I'm just wishing to see in general what
    Gavin> people have done and what solutions may be available.

Gavin,

What sort of automation are you interested in?  Knowing only a single
database on the server, it seems to me you could do something like this:

    conn = MySQLdb.connect(...to 'mysql' database...)
    c = conn.cursor()
    c.execute("show databases")
    # iterate over all databases
    for (db,) in c.fetchall():
        # probably want an os.path.join(BASE, db) here, where BASE is
        # perhaps date-related or cycles through a fixed set of backup
        # directories
        os.mkdir(db)
        # second connection to specific database
        conn2 = MySQLdb.connect(...to db...)
        c2 = conn2.cursor()
        c2.execute("show tables")
        # iterate over all tables in db, dumping as we go
        for (tbl,) in c2.fetchall():
            c3 = conn2.cursor()
            fn = os.path.join(BASE, db, tbl) + ".dump"
            try:
                os.unlink(fn)
            except OSError:
                pass
            c3.execute("select * from %s into outfile '%s'" % fn)

Skip