From: Joe Presbrey Date: Fri, 26 Sep 2008 07:04:05 +0000 (+0000) Subject: daily_afs_backups only dumps databases with changes since last dump; moved to /srv X-Git-Url: http://andersk.mit.edu/gitweb/sql.git/commitdiff_plain/829818554b00f9cafceb17bde84f14d0b87eac65 daily_afs_backups only dumps databases with changes since last dump; moved to /srv git-svn-id: svn://presbrey.mit.edu/sql@167 a142d4bd-2cfb-0310-9673-cb33a7e74f58 --- diff --git a/etc/lighttpd/lighttpd.conf b/etc/lighttpd/lighttpd.conf index 8b20f3b..82b4d89 100644 --- a/etc/lighttpd/lighttpd.conf +++ b/etc/lighttpd/lighttpd.conf @@ -28,12 +28,12 @@ fastcgi.server = ( ".php" => )) ) -server.document-root = "/srv/sql/web/home/" -alias.url = ( "/~sql" => "/srv/sql/web" ) -alias.url += ( "/main" => "/srv/sql/web/main" ) -alias.url += ( "/dev" => "/srv/sql/web/dev" ) -alias.url += ( "/tools" => "/srv/sql/web/tools" ) -#alias.url += ( "/" => "/srv/sql/web/home" ) +server.document-root = "/srv/web/home/" +alias.url = ( "/~sql" => "/srv/web" ) +alias.url += ( "/main" => "/srv/web/main" ) +alias.url += ( "/dev" => "/srv/web/dev" ) +alias.url += ( "/tools" => "/srv/web/tools" ) +#alias.url += ( "/" => "/srv/web/home" ) url.rewrite-once = ( "^/tools/([^\?]+)(\??.*)" => "/tools/$1.php$2" ) url.rewrite-once += ( "^/~sql/main/do/([^\?]+)(\??.*)" => "/~sql/main/$1.php$2" ) url.rewrite-once += ( "^/~sql/dev/do/([^\?]+)(\??.*)" => "/~sql/dev/$1.php$2" ) diff --git a/lib/python/sql/db.py b/lib/python/sql/db.py index 70e1ad4..ba4adfd 100755 --- a/lib/python/sql/db.py +++ b/lib/python/sql/db.py @@ -1,7 +1,16 @@ -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__': diff --git a/lib/python/sql/util.py b/lib/python/sql/util.py index 3da1756..e4dcc91 100755 --- a/lib/python/sql/util.py +++ b/lib/python/sql/util.py @@ -1,10 +1,18 @@ #!/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) @@ -35,7 +43,9 @@ def get_db_mtime(cursor, 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()] @@ -55,35 +65,66 @@ def repair_tables_from_stdin(cursor): 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 diff --git a/libexec/daily_afs_backups b/libexec/daily_afs_backups new file mode 100755 index 0000000..08f72be --- /dev/null +++ b/libexec/daily_afs_backups @@ -0,0 +1,76 @@ +#!/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