Python MySQLDump Scripts

Skip Montanaro skip at
Tue Aug 26 15:10:47 CEST 2003

    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.


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( '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
        # second connection to specific database
        conn2 = MySQLdb.connect( 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"
            except OSError:
            c3.execute("select * from %s into outfile '%s'" % fn)


More information about the Python-list mailing list