DB size and mtime utils

[sql.git] / lib / python / sql / util.py

#!/usr/bin/python

def get_backup_exceptions(path='/mit/sql/etc/db_no-daily-backup'):
    return filter(len, map(str.strip, file(path).read().split('\n')))

import MySQLdb, MySQLdb.cursors

def use_db(cursor, db):
    cursor.execute('USE `%s`' % db)

def get_dbs(cursor, prefix=None):
    cursor.execute('SHOW DATABASES' + (prefix and (' LIKE \'%s%%\'' % prefix) or ''))
    return [x.values()[0] for x in cursor.fetchall() if x.values()[0] != 'information_schema']

def get_db_tables(cursor, db):
    cursor.execute('SHOW TABLES FROM `%s`' % db)
    return [(db, x.values()[0]) for x in cursor.fetchall()]

def get_db_tables_ft(cursor, db):
    r = []
    for table in get_db_tables(cursor, db):
        cursor.execute('SHOW INDEXES FROM `%s`.`%s`' % table)
        r.extend(set([(db, x['Table']) for x in cursor.fetchall() if x['Index_type'] == 'FULLTEXT']))
    return r

def get_db_tables_status(cursor, db):
    cursor.execute('SHOW TABLE STATUS FROM `%s`' % db)
    return dict([((db, x['Name']), x) for x in cursor.fetchall()])

def get_db_mtime(cursor, db):
    db_mtimes = filter(lambda x: not x is None, [status['Update_time'] for table, status in get_db_tables_status(cursor, db).items()])
    if len(db_mtimes):
        db_mtimes.sort(reverse=True)
        return db_mtimes[0]
    return 0

def get_db_size(cursor, db):
    return sum([status['Data_length'] for table, status in get_db_tables_status(cursor, db).items()])

def get_db_tables_engines(cursor, db):
    return [(table, status['Engine']) for table, status in get_db_tables_status(cursor, db).items()]

def repair_table_quick(cursor, table):
    # REPAIR TABLE tbl QUICK
    # works when changing ft_min_word_len
    cursor.execute('REPAIR TABLE `%s`.`%s` QUICK' % table)

def repair_tables_from_stdin(cursor):
    while True:
        data = sys.stdin.readline()
        if not data:
            break
        data = data.strip()
        table = eval(data)
        print table
        repair_table_quick(cursor, table)

def backup_dbs(cursor):
    for db in get_dbs(cursor):
        db_size = get_db_size(cursor, db)
        if db_size < BACKUP_MAX_SIZE:
            if len(db.split('+')) <= 1:
                owner = 'root'
            else:
                owner = db.split('+')[0]

            db_mtime = get_db_mtime(cursor, db)
            try:
                bk_mtime = os.stat('%s/%s/%s.sql.gz' % (BACKUP_ROOT, owner, db))[stat.ST_MTIME]
            except:
                bk_mtime = 0
            if not db_mtime or \
                int(time.mktime(db_mtime.timetuple())) > bk_mtime:
                print db, 'DUMP'
            else:
                print db, 'SKIP (mtime: %s < %s)' % (str(db_mtime),
                                                     time.strftime('%Y-%m-%d %H:%M:%S',
                                                                   time.localtime(bk_mtime)))
        else:
            print db, 'SKIP (size: %.2fM > %dM)' % (db_size/1024/1024, BACKUP_MAX_SIZE/1024/1024)

if __name__ == '__main__':
    import sys, os, stat, time
    BACKUP_ROOT = '/afs/athena.mit.edu/contrib/sql/backup'
    BACKUP_MAX_SIZE = 200*1024*1024

    cursor = MySQLdb.connect('localhost', read_default_group='client', cursorclass=MySQLdb.cursors.DictCursor).cursor()