DB size and mtime utils

git-svn-id: svn://presbrey.mit.edu/sql@166 a142d4bd-2cfb-0310-9673-cb33a7e74f58

diff --git a/lib/python/sql/util.py b/lib/python/sql/util.py
index 7f05b20b04830c59e8d3cc27d810eb65f934aca2..3da175641e8fd0043cc1273c5cfc69fbf7d09969 100755 (executable)
--- a/lib/python/sql/util.py
+++ b/lib/python/sql/util.py
@@ -8,8 +8,8 @@ import MySQLdb, MySQLdb.cursors
 def use_db(cursor, db):
     cursor.execute('USE `%s`' % db)
 
-def get_dbs(cursor):
-    cursor.execute('SHOW DATABASES')
+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):
@@ -27,6 +27,16 @@ 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()]
 
@@ -35,18 +45,7 @@ def repair_table_quick(cursor, table):
     # works when changing ft_min_word_len
     cursor.execute('REPAIR TABLE `%s`.`%s` QUICK' % table)
 
-if __name__ == '__main__':
-    import sys
-
-    cursor = MySQLdb.connect('localhost', read_default_group='client', cursorclass=MySQLdb.cursors.DictCursor).cursor()
-    #for db in get_dbs(cursor):
-    #    for table in get_db_tables_ft(cursor, db):
-    #        print table
-
-    #db = 'freeculture+youtomb'
-    #print get_db_tables_status(cursor, db)
-    #sys.exit(0)
-
+def repair_tables_from_stdin(cursor):
     while True:
         data = sys.stdin.readline()
         if not data:
@@ -55,3 +54,36 @@ if __name__ == '__main__':
         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()
+
+