File: //usr/bin/mycatalog.py
#!/usr/bin/python -W ignore::DeprecationWarning
# -*- coding: utf-8 -*-
import os
import os.path
import sys
import fcntl
import logging
import logging.handlers
import syslog
import mybackup
import warnings
import MySQLdb
from mybackuplib.mysqlhacks import tablename_to_filename
syslog.openlog('mycatalog', syslog.LOG_PID, syslog.LOG_SYSLOG)
class MyCatalog(object):
def __init__(self):
self.mybackup = mybackup.MyBackup()
def loadDatabases(self, databases):
"""Loading databases in administration schema"""
try:
self.mybackup.conn_catalog.ping(1)
for database in databases:
self.mybackup.cursor_catalog.execute("""REPLACE INTO mybackup_catalog (host, db, created_at, updated_at, myisam) VALUES (%s, %s, DATE(NOW()), NOW(), 0)""", (self.mybackup.hostname, database))
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
def preCatalog(self):
"""Create admin schema and tables catalog"""
try:
warnings.filterwarnings(action="ignore", category=MySQLdb.Warning, message="Can't create database '%s'; database exists" % self.mybackup.backup_db_catalog)
self.mybackup.cursor_catalog.execute("CREATE DATABASE IF NOT EXISTS %s" % (self.mybackup.backup_db_catalog))
self.mybackup.conn_catalog.select_db(self.mybackup.backup_db_catalog)
warnings.filterwarnings(action="ignore", category=MySQLdb.Warning, message="Table 'mybackup_catalog' already exists")
self.mybackup.cursor_catalog.execute("""CREATE TABLE IF NOT EXISTS `mybackup_catalog` (\
`host` varchar(80) NOT NULL,
`db` varchar(64) NOT NULL,\
`st_compress` varchar(40) NULL,\
`st_backup` varchar(40) NULL,\
`myisam` tinyint NULL default 0,\
`started_at` datetime NULL,\
`ended_at` datetime NULL,\
`created_at` date NOT NULL,\
`updated_at` datetime NOT NULL,\
`attempts` int NULL default 0,\
`message` text NULL,\
`filename` varchar(200) NULL,\
`file_size` bigint(21) NULL default 0,\
`datadir_size` bigint(21) NULL default 0,\
PRIMARY KEY (`db`, `created_at`, `host`)\
) ENGINE=%s DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;""", (self.mybackup.engine,))
warnings.filterwarnings(action="ignore", category=MySQLdb.Warning, message="Table 'mybackup_execution' already exists")
self.mybackup.cursor_catalog.execute("""CREATE TABLE IF NOT EXISTS `mybackup_execution` (\
`host` varchar(80) NOT NULL,
`datadir_size` bigint(21) NULL default 0,\
`file_size` bigint(21) NULL default 0,\
`started_at` datetime NULL,\
`ended_at` datetime NULL,\
`created_at` datetime NOT NULL,\
`st_backup` varchar(40) NULL,\
PRIMARY KEY (`created_at`, `host`)\
) ENGINE=%s DEFAULT CHARSET=latin1;""", (self.mybackup.engine,))
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
def showDatabases(self):
"""Return all the databases"""
syslog.syslog(syslog.LOG_INFO, "showDatabases")
try:
self.mybackup.cursor.execute("""show databases;""")
result = self.mybackup.cursor.fetchall()
databases = []
for database in result:
databases.append(database['Database'])
return databases
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
def checkCatalog(self):
"""Verify databases catalog"""
try:
self.mybackup.cursor_catalog.execute("""SELECT COUNT(1) as total FROM mybackup_catalog WHERE created_at = DATE(NOW())""")
linhas = self.mybackup.cursor_catalog.fetchone()
if linhas['total'] > 0:
syslog.syslog(syslog.LOG_INFO, "Catalog already exists")
return False
return True
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
def close(self):
self.mybackup.close()
def runCatalog(self):
"""Loading informations for backup catalog"""
try:
self.mybackup.cursor.execute("show global variables like 'datadir'")
datadir = self.mybackup.cursor.fetchone()['Value']
self.mybackup.cursor.execute("show databases")
for db in self.mybackup.cursor:
db = db['Database']
if db == 'information_schema':
continue
path = os.path.join(datadir, tablename_to_filename(db))
files = os.listdir(path)
size = 0
mysiam = 1
for f in files:
# ignore table definitions
if f.endswith('.frm'):
continue
# sum all files
stat = os.stat(os.path.join(path, f))
size += stat.st_size
# and mark not MyISAM
if not (f.endswith('.MYI') or f.endswith('.MYD')):
myisam = 0
# and now, insert the data
self.mybackup.conn_catalog.ping(1)
self.mybackup.cursor_catalog.execute("""REPLACE INTO mybackup_catalog (host, db, datadir_size, myisam, started_at, ended_at, created_at, updated_at, st_backup, st_compress) VALUES (%s, %s, %s, %s, NULL, NULL, CURRENT_DATE, CURRENT_TIMESTAMP, NULL, NULL)""", (self.mybackup.hostname, db, size, myisam))
except Exception, e:
# some databases can reach here due diffs between MySQL database name and
# real directory name. see sql/sql_table.cc
# our panel should not allow those names, so they are likely
# to be bogus anyway
syslog.syslog(syslog.LOG_ERR, str(e))
if __name__ == '__main__':
try:
lock = open('/var/lock/mycatalog', 'w')
fcntl.flock(lock.fileno(), fcntl.LOCK_EX | fcntl.LOCK_NB)
mycatalog = MyCatalog()
syslog.syslog(syslog.LOG_INFO, "=================== Start ===================")
mycatalog.preCatalog()
if mycatalog.checkCatalog():
databases = mycatalog.showDatabases()
mycatalog.loadDatabases(databases)
mycatalog.runCatalog()
syslog.syslog(syslog.LOG_INFO, "=================== End ===================")
mycatalog.close()
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
print str(e)
sys.exit(1)