-from elixir import Entity, Field, String, Integer, DateTime, options, metadata, setup_all, session
+from elixir import Entity, Field, String, Text, Integer, DateTime, options, metadata, setup_all, session
from sqlalchemy import create_engine, func
options.options_defaults['shortnames'] = True
+class Backup(Entity):
+ db = Field(String(256), primary_key=True)
+ skip_reason = Field(String(256), nullable=True)
+ skip_date = Field(DateTime(), nullable=True)
+ dump_path = Field(String(512), nullable=True)
+ dump_errnum = Field(Integer(4), nullable=True)
+ dump_errstr = Field(Text(), nullable=True)
+ dump_date = Field(DateTime(), nullable=True)
+
metadata.bind = create_engine('mysql://sql.mit.edu/mitsql?read_default_group=mysql', pool_recycle=True)
if __name__ == '__main__':
#!/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 sys, os, stat, time
+import subprocess
+
+PATH_MYSQL_DATA = '/srv/mysql'
+PATH_AFS_BACKUP = '/afs/athena.mit.edu/contrib/sql/backup'
+PATH_AFS_BACKUP_VOL = '/afs/athena.mit.edu/contrib/sql/.backup'
+BACKUP_MAX_SIZE = 200*1024*1024
import MySQLdb, MySQLdb.cursors
+def new_cursor(group='client'):
+ return MySQLdb.connect('localhost', read_default_group=group, cursorclass=MySQLdb.cursors.DictCursor).cursor()
+
def use_db(cursor, db):
cursor.execute('USE `%s`' % db)
return 0
def get_db_size(cursor, db):
- return sum([status['Data_length'] for table, status in get_db_tables_status(cursor, db).items()])
+ return sum([status['Data_length']
+ for table, status in get_db_tables_status(cursor, db).items()
+ if status['Data_length']])
def get_db_tables_engines(cursor, db):
return [(table, status['Engine']) for table, status in get_db_tables_status(cursor, db).items()]
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)))
+def db_backup_pre(cursor, db):
+ db_size = get_db_size(cursor, db)
+ if db_size < BACKUP_MAX_SIZE:
+ if len(db.split('+')) <= 1:
+ owner = 'root'
else:
- print db, 'SKIP (size: %.2fM > %dM)' % (db_size/1024/1024, BACKUP_MAX_SIZE/1024/1024)
+ owner = db.split('+')[0]
+ db_mtime = get_db_mtime(cursor, db)
+ db_path = '%s/%s/%s.sql.gz' % (PATH_AFS_BACKUP, owner.lower(), db)
+ try:
+ bk_mtime = os.stat(db_path)[stat.ST_MTIME]
+ except:
+ bk_mtime = 0
+ if not db_mtime:
+ r = (False, 'empty database')
+ elif int(time.mktime(db_mtime.timetuple())) > bk_mtime:
+ r = (True, db_path)
+ else:
+ r = (False, 'mtime: %s < %s' % (str(db_mtime),
+ time.strftime('%Y-%m-%d %H:%M:%S',
+ time.localtime(bk_mtime))))
+ else:
+ r = (False, 'size: %.2fM > %dM' % (db_size/1024/1024, BACKUP_MAX_SIZE/1024/1024))
+ return r
-if __name__ == '__main__':
- import sys, os, stat, time
- BACKUP_ROOT = '/afs/athena.mit.edu/contrib/sql/backup'
- BACKUP_MAX_SIZE = 200*1024*1024
+def db_backup_pstrip(path):
+ r = path
+ if r.startswith(PATH_AFS_BACKUP):
+ r = r[len(PATH_AFS_BACKUP):]
+ if r and r[0] == '/':
+ r = r[1:]
+ return r
- cursor = MySQLdb.connect('localhost', read_default_group='client', cursorclass=MySQLdb.cursors.DictCursor).cursor()
+def db_backup_mkdir(path):
+ p = db_backup_pstrip(path)
+ owner = p.split('/')[0]
+ p_owner = '%s/%s' % (PATH_AFS_BACKUP, owner)
+ p_owner_vol = '%s/%s/%s' % (PATH_AFS_BACKUP_VOL, owner[0], owner)
+ if not os.path.islink(p_owner):
+ os.symlink(p_owner_vol, p_owner)
+ if not os.path.isdir(p_owner_vol):
+ os.mkdir(p_owner_vol)
+ subprocess.call(['fs', 'sa', p_owner_vol, 'system:anyuser', 'none'])
+ subprocess.call(['fs', 'sa', p_owner_vol, 'system:authuser', 'none'])
+ if owner == 'root':
+ subprocess.call(['fs', 'sa', p_owner_vol, 'system:sql-backup', 'none'])
+ #print owner
+ #print ' ', p_owner
+ #print ' ', p_owner_vol
+
+def tables_in_ibdata(cursor):
+ for db in get_dbs(cursor):
+ table_engines = get_db_tables_engines(cursor, db)
+ innodb_tables = [x[0] for x in table_engines \
+ if x[1] and x[1].lower() == 'innodb']
+ for table in innodb_tables:
+ path = '%s/%s/%s.ibd' % (PATH_MYSQL_DATA, table[0], table[1])
+ if not os.path.isfile(path):
+ yield '`%s`.`%s`' % table
+if __name__ == '__main__':
+ pass
--- /dev/null
+#!/usr/bin/python
+
+import os, sys
+import sql.db
+from sql.util import new_cursor, get_dbs, db_backup_pre, db_backup_mkdir
+from Queue import Queue, Empty
+import threading
+import subprocess
+
+MYSQLDUMP_ARGS = ['--compact', '--add-drop-table', '--add-locks',
+ '--create-options', '--disable-keys', '--dump-date',
+ '--extended-insert', '--quick',
+ '--no-autocommit', '--quote-names', '--routines',
+ '--single-transaction', '--triggers', '--force']
+
+finished = False
+queue = Queue()
+
+def consumer():
+ while True:
+ try:
+ next = queue.get(timeout=3)
+ print next[0] + ':',
+ log = sql.db.Backup.get_by(db=next[0])
+ if not log:
+ log = sql.db.Backup(db=next[0])
+ log.dump_path = next[1]
+ log.dump_date = sql.db.func.now()
+ db_backup_mkdir(next[1])
+ args = ['mysqldump', next[0]]
+ args.extend(MYSQLDUMP_ARGS)
+ p0 = subprocess.Popen(args, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
+ p1 = subprocess.Popen(['gzip'], stdin=p0.stdout, stdout=file(next[1], 'w+'))
+ p1.wait()
+ err = p0.stderr.read()
+ if len(err):
+ log.dump_errnum = p0.returncode
+ log.dump_errstr = err
+ else:
+ log.dump_errnum = None
+ log.dump_errstr = None
+ log.save_or_update()
+ sql.db.session.flush()
+ print 'Done'
+ except (KeyboardInterrupt, SystemExit):
+ break
+ except Empty:
+ if finished:
+ break
+
+t_consumer = threading.Thread(target=consumer)
+t_consumer.start()
+
+def producer():
+ c = new_cursor('mysqldump')
+ for db in get_dbs(c):
+ log = sql.db.Backup.get_by(db=db)
+ if not log:
+ log = sql.db.Backup(db=db)
+ d = db_backup_pre(c, db)
+ if d[0]:
+ queue.put((db, d[1]))
+ log.skip_reason = None
+ log.skip_date = None
+ else:
+ log.skip_reason = d[1]
+ log.skip_date = sql.db.func.now()
+ log.save_or_update()
+ sql.db.session.flush()
+
+try:
+ producer()
+except KeyboardInterrupt:
+ sys.exit(1)
+finally:
+ finished = True
andersk / sql.git / commitdiff
