Automatically backup mysql databases to google docs using python | Samir Patel - Web Applications Developer, Belmont NC 28012 * 704-345-2040 | Import, Docs_Password, Docs

Programming menu

Links

Home

Articles

Programming

Automatically backup mysql databases to google docs using python

Tag it:

Google docs is a great resource to back up all my mysql databases using mysqldump from this python script that runs the local machine.

Google docs has an excellent API which can be viewed here , at the moment, it is only available for python and Java, no PHP yet :/

Additionally you will need to download the google gdata client, from here

I ran this code on a linux server running Ubuntu 11.2 with Python ver 2.7.1+

#!/usr/bin/env python

#abstract=Script to backup mysql databases and tables to folders in google docs.
#author=Samir Patel
#copyright GPL 2011

import re
import sys
import getopt
import os.path
import getpass

import time
import pprint
import shutil
import MySQLdb

import gdata
import gdata.data
import gdata.docs.data
import gdata.docs.client
import gdata.docs.service
import gdata.spreadsheet.service


#__CONFIG VALUES__#

docs_email = 'MY_GOOGLE_DOCS_EMAIL_ADDRESS_HERE'
docs_password = 'MY_GOOGLE_DOCS_PASSWORD_HERE'

db_username = 'MYSQL_USERNAME_HERE'
db_password = 'MYSQL_PASSWORD_HERE'



#__FUNCTIONS__#

def uploadFile(docs_email, docs_password, file_path, folder_name):

    client = gdata.docs.client.DocsClient(source='com.solutionsamir.gdocs_mysql_backup')
    
    client.ClientLogin(docs_email, docs_password, client.source)
    
    if not file_path:
        print 'file path is invalid'
        exit
    elif not os.path.isfile(file_path):
        print 'Not a valid file path'
        exit
    
    new_folder = client.Create(gdata.docs.data.FOLDER_LABEL, folder_name)
    print 'Folder "%s" created' % new_folder.title.text
    
    
    sub_folder = client.Create(gdata.docs.data.FOLDER_LABEL, 'SubFolder', folder_or_id=new_folder)
    print 'Subfolder "', sub_folder.title.text, '" created'


def getDatabases(db):
    
    cursor = db.cursor()
    
    cursor.execute("show databases")
    
    db_names = cursor.fetchall();
    
    return db_names


def getTablesInDB(db, database_name):
    
    cursor = db.cursor()
    
    cursor.execute("show tables from "+ str(database_name))
    
    table_names = cursor.fetchall();
    
    hc_tables = []
    
    for table in table_names:
        hc_tables.append(table[0])
    
    return hc_tables
    

def buildDatabasesAndTablesArray(db):
    
    databases = getDatabases(db);

    db_holder = {}
    
    for database in databases:
        if database[0] != "information_schema":
            hc_tbls = {}
            hc_tbls = getTablesInDB(db, database[0])
            db_holder[database[0]] = hc_tbls

    return db_holder
 

def dumpEntireDBToFS(db, db_username, db_password, client, docs_email, docs_password, top_folder):
    
    content_type = gdata.docs.service.SUPPORTED_FILETYPES['DOC']
    
    dump_folder = 'sql_dumps'
    
    database_tables = buildDatabasesAndTablesArray(db)

    if os.path.exists(dump_folder): shutil.rmtree(dump_folder)

    if not os.path.exists(dump_folder): os.makedirs(dump_folder)

    for database, tables in database_tables.items():
        
        db_folder = 'sql_dumps/'+database
        
        if not os.path.exists(db_folder): os.makedirs(db_folder)
        
        g_db_folder = client.Create(gdata.docs.data.FOLDER_LABEL, database, folder_or_id=top_folder)


        for table in tables:
            
            file_name = db_folder+"/"+table+".txt"
            
            dump_command = "mysqldump --add-drop-table -c -u "+db_username+" -p"+db_password+" "+database+" '"+table+"' > '"+file_name+"'"
            
            os.system(dump_command)

            
            try:

                ms = gdata.data.MediaSource(file_path=file_name, content_type=gdata.docs.service.SUPPORTED_FILETYPES['TXT'])
                
                entry = client.Upload(ms, table, folder_or_uri=g_db_folder)
                
                print database + "->" + table + ": " + entry.GetAlternateLink().href + "\n"

            except IOError:
                print 'Problems reading file. Check permissions.'
                return


                    

def bacupToCloud(docs_email, docs_password, db_username, db_password):
    
    db = MySQLdb.connect(host='localhost', user=db_username, passwd=db_password)
    
    client = gdata.docs.client.DocsClient(source='com.solutionsamir.gdocs_mysql_backup')
    
    client.ClientLogin(docs_email, docs_password, client.source)
    
    now = time.strftime( "%Y-%m-%d" )
    
    top_folder = str(now)
    
    #__create top level folder in gDocs.__#
    
    g_top_folder = client.Create(gdata.docs.data.FOLDER_LABEL, top_folder)
    
    
    dumpEntireDBToFS(db, db_username, db_password, client, docs_email, docs_password, g_top_folder)
    



#__PROGRAM__#

bacupToCloud(docs_email, docs_password, db_username, db_password)

Comments

Only registered users can write comments!

Last Updated ( Wednesday, 22 June 2011 )

< Prev

[ Back ]