]>
Commit | Line | Data |
---|---|---|
562fda3a JP |
1 | #!/usr/bin/python |
2 | ||
82981855 JP |
3 | import sys, os, stat, time |
4 | import subprocess | |
5 | ||
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 | |
562fda3a JP |
10 | |
11 | import MySQLdb, MySQLdb.cursors | |
12 | ||
82981855 JP |
13 | def new_cursor(group='client'): |
14 | return MySQLdb.connect('localhost', read_default_group=group, cursorclass=MySQLdb.cursors.DictCursor).cursor() | |
15 | ||
562fda3a JP |
16 | def use_db(cursor, db): |
17 | cursor.execute('USE `%s`' % db) | |
18 | ||
0f682eea JP |
19 | def get_dbs(cursor, prefix=None): |
20 | cursor.execute('SHOW DATABASES' + (prefix and (' LIKE \'%s%%\'' % prefix) or '')) | |
562fda3a JP |
21 | return [x.values()[0] for x in cursor.fetchall() if x.values()[0] != 'information_schema'] |
22 | ||
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()] | |
26 | ||
27 | def get_db_tables_ft(cursor, db): | |
28 | r = [] | |
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'])) | |
32 | return r | |
33 | ||
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()]) | |
37 | ||
0f682eea JP |
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()]) | |
40 | if len(db_mtimes): | |
41 | db_mtimes.sort(reverse=True) | |
42 | return db_mtimes[0] | |
43 | return 0 | |
44 | ||
45 | def get_db_size(cursor, db): | |
82981855 JP |
46 | return sum([status['Data_length'] |
47 | for table, status in get_db_tables_status(cursor, db).items() | |
48 | if status['Data_length']]) | |
0f682eea | 49 | |
562fda3a JP |
50 | def get_db_tables_engines(cursor, db): |
51 | return [(table, status['Engine']) for table, status in get_db_tables_status(cursor, db).items()] | |
52 | ||
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) | |
57 | ||
0f682eea | 58 | def repair_tables_from_stdin(cursor): |
562fda3a JP |
59 | while True: |
60 | data = sys.stdin.readline() | |
61 | if not data: | |
62 | break | |
63 | data = data.strip() | |
64 | table = eval(data) | |
65 | print table | |
66 | repair_table_quick(cursor, table) | |
0f682eea | 67 | |
82981855 JP |
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: | |
72 | owner = 'root' | |
0f682eea | 73 | else: |
82981855 JP |
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) | |
77 | try: | |
78 | bk_mtime = os.stat(db_path)[stat.ST_MTIME] | |
79 | except: | |
80 | bk_mtime = 0 | |
81 | if not db_mtime: | |
82 | r = (False, 'empty database') | |
83 | elif int(time.mktime(db_mtime.timetuple())) > bk_mtime: | |
84 | r = (True, db_path) | |
85 | else: | |
86 | r = (False, 'mtime: %s < %s' % (str(db_mtime), | |
87 | time.strftime('%Y-%m-%d %H:%M:%S', | |
88 | time.localtime(bk_mtime)))) | |
89 | else: | |
90 | r = (False, 'size: %.2fM > %dM' % (db_size/1024/1024, BACKUP_MAX_SIZE/1024/1024)) | |
91 | return r | |
0f682eea | 92 | |
82981855 JP |
93 | def db_backup_pstrip(path): |
94 | r = path | |
95 | if r.startswith(PATH_AFS_BACKUP): | |
96 | r = r[len(PATH_AFS_BACKUP):] | |
97 | if r and r[0] == '/': | |
98 | r = r[1:] | |
99 | return r | |
0f682eea | 100 | |
82981855 JP |
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']) | |
112 | if owner == 'root': | |
113 | subprocess.call(['fs', 'sa', p_owner_vol, 'system:sql-backup', 'none']) | |
114 | #print owner | |
115 | #print ' ', p_owner | |
116 | #print ' ', p_owner_vol | |
117 | ||
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 | |
0f682eea | 127 | |
82981855 JP |
128 | if __name__ == '__main__': |
129 | pass | |
0f682eea | 130 |