3 import sys, os, stat, time
6 PATH_MYSQL_DATA = '/srv/mysql'
7 PATH_AFS_BACKUP = '/afs/athena.mit.edu/contrib/sql/backup'
8 PATH_AFS_BACKUP_VOL = '/afs/athena.mit.edu/contrib/sql/.backup'
9 BACKUP_MAX_SIZE = 200*1024*1024
11 import MySQLdb, MySQLdb.cursors
13 def new_cursor(group='client'):
14 return MySQLdb.connect('localhost', read_default_group=group, cursorclass=MySQLdb.cursors.DictCursor).cursor()
16 def use_db(cursor, db):
17 cursor.execute('USE `%s`' % db)
19 def get_dbs(cursor, prefix=None):
20 cursor.execute('SHOW DATABASES' + (prefix and (' LIKE \'%s%%\'' % prefix) or ''))
21 return [x.values()[0] for x in cursor.fetchall() if x.values()[0] != 'information_schema']
23 def get_db_tables(cursor, db):
24 cursor.execute('SHOW TABLES FROM `%s`' % db)
25 return [(db, x.values()[0]) for x in cursor.fetchall()]
27 def get_db_tables_ft(cursor, db):
29 for table in get_db_tables(cursor, db):
30 cursor.execute('SHOW INDEXES FROM `%s`.`%s`' % table)
31 r.extend(set([(db, x['Table']) for x in cursor.fetchall() if x['Index_type'] == 'FULLTEXT']))
34 def get_db_tables_status(cursor, db):
35 cursor.execute('SHOW TABLE STATUS FROM `%s`' % db)
36 return dict([((db, x['Name']), x) for x in cursor.fetchall()])
38 def get_db_mtime(cursor, db):
39 db_mtimes = filter(lambda x: not x is None, [status['Update_time'] for table, status in get_db_tables_status(cursor, db).items()])
41 db_mtimes.sort(reverse=True)
45 def get_db_size(cursor, db):
46 return sum([status['Data_length']
47 for table, status in get_db_tables_status(cursor, db).items()
48 if status['Data_length']])
50 def get_db_tables_engines(cursor, db):
51 return [(table, status['Engine']) for table, status in get_db_tables_status(cursor, db).items()]
53 def repair_table_quick(cursor, table):
54 # REPAIR TABLE tbl QUICK
55 # works when changing ft_min_word_len
56 cursor.execute('REPAIR TABLE `%s`.`%s` QUICK' % table)
58 def repair_tables_from_stdin(cursor):
60 data = sys.stdin.readline()
66 repair_table_quick(cursor, table)
68 def db_backup_pre(cursor, db):
69 db_size = get_db_size(cursor, db)
70 if db_size < BACKUP_MAX_SIZE:
71 if len(db.split('+')) <= 1:
74 owner = db.split('+')[0]
75 db_mtime = get_db_mtime(cursor, db)
76 db_path = '%s/%s/%s.sql.gz' % (PATH_AFS_BACKUP, owner.lower(), db)
78 bk_mtime = os.stat(db_path)[stat.ST_MTIME]
82 r = (False, 'empty database')
83 elif int(time.mktime(db_mtime.timetuple())) > bk_mtime:
86 r = (False, 'mtime: %s < %s' % (str(db_mtime),
87 time.strftime('%Y-%m-%d %H:%M:%S',
88 time.localtime(bk_mtime))))
90 r = (False, 'size: %.2fM > %dM' % (db_size/1024/1024, BACKUP_MAX_SIZE/1024/1024))
93 def db_backup_pstrip(path):
95 if r.startswith(PATH_AFS_BACKUP):
96 r = r[len(PATH_AFS_BACKUP):]
101 def db_backup_mkdir(path):
102 p = db_backup_pstrip(path)
103 owner = p.split('/')[0]
104 p_owner = '%s/%s' % (PATH_AFS_BACKUP, owner)
105 p_owner_vol = '%s/%s/%s' % (PATH_AFS_BACKUP_VOL, owner[0], owner)
106 if not os.path.islink(p_owner):
107 os.symlink(p_owner_vol, p_owner)
108 if not os.path.isdir(p_owner_vol):
109 os.mkdir(p_owner_vol)
110 subprocess.call(['fs', 'sa', p_owner_vol, 'system:anyuser', 'none'])
111 subprocess.call(['fs', 'sa', p_owner_vol, 'system:authuser', 'none'])
113 subprocess.call(['fs', 'sa', p_owner_vol, 'system:sql-backup', 'none'])
116 #print ' ', p_owner_vol
118 def tables_in_ibdata(cursor):
119 for db in get_dbs(cursor):
120 table_engines = get_db_tables_engines(cursor, db)
121 innodb_tables = [x[0] for x in table_engines \
122 if x[1] and x[1].lower() == 'innodb']
123 for table in innodb_tables:
124 path = '%s/%s/%s.ibd' % (PATH_MYSQL_DATA, table[0], table[1])
125 if not os.path.isfile(path):
126 yield '`%s`.`%s`' % table
128 if __name__ == '__main__':