Server : Apache System : Linux vpshost1491.publiccloud.com.br 4.4.79-grsec-1.lc.x86_64 #1 SMP Wed Aug 2 14:18:21 -03 2017 x86_64 User : aloic1 ( 10003) PHP Version : 8.0.14 Disable Function : apache_child_terminate,dl,escapeshellarg,escapeshellcmd,exec,link,mail,openlog,passthru,pcntl_alarm,pcntl_exec,pcntl_fork,pcntl_get_last_error,pcntl_getpriority,pcntl_setpriority,pcntl_signal,pcntl_signal_dispatch,pcntl_sigprocmask,pcntl_sigtimedwait,pcntl_sigwaitinfo,pcntl_strerror,pcntl_wait,pcntl_waitpid,pcntl_wexitstatus,pcntl_wifexited,pcntl_wifsignaled,pcntl_wifstopped,pcntl_wstopsig,pcntl_wtermsig,php_check_syntax,php_strip_whitespace,popen,proc_close,proc_open,shell_exec,symlink,system Directory : /bin/
Upload File :
Current File : //bin/mybackup.py
#!/usr/bin/python -W ignore::DeprecationWarning
# -*- coding: utf-8 -*-
import os
import sys
import socket
import fcntl
import subprocess
import ConfigParser
import MySQLdb
import syslog
import re
from glob import glob
from time import sleep, strftime
from datetime import date, datetime
from pwd import getpwnam
from MySQLdb import cursors
from contextlib import nested
syslog.openlog('mybackup', syslog.LOG_PID, syslog.LOG_SYSLOG)
class DroppedDatabase(Exception): pass
class MyBackup(object):
def __init__(self):
conffile = "/etc/locaweb/mybackup/mybackup.conf"
confCatalog = "/etc/locaweb/mybackup/mycatalog.conf"
if not os.path.isfile(conffile) or not os.path.isfile(confCatalog):
syslog.syslog(syslog.LOG_ERR, "[ERROR] - Problem loading configuration file")
print "Problem loading configuration file"
sys.exit(1)
self.initConf(conffile)
self.initConfCatalog(confCatalog)
self.connection()
def initConf(self, conffile):
self.hostname = socket.gethostname().split(".")[0]
config = ConfigParser.ConfigParser()
config.readfp(open(conffile))
self.user = config.get('mybackup', 'user')
self.password = config.get('mybackup', 'password')
self.host = config.get('mybackup', 'host')
self.backup_db = config.get('mybackup', 'dbname')
self.backup_root_path = config.get('mybackup', 'backup_root_path')
self.mybackup_path = config.get('mybackup', 'mybackup_path')
self.backup_path = "%s%s" % (config.get('mybackup', 'backup_path',), strftime("%Y%m%d"))
self.db_ignore = config.get('mybackup', 'db_ignore')
self.lock_time = config.get('mybackup', 'lock_time')
self.lock = config.getboolean('mybackup', 'lock')
self.filesize = config.get('mybackup', 'filesize')
self.owner = getpwnam(config.get('mybackup', 'owner'))
def initConfCatalog(self, confCatalog):
config = ConfigParser.ConfigParser()
config.readfp(open(confCatalog))
self.user_catalog = config.get('mycatalog', 'user')
self.password_catalog = config.get('mycatalog', 'password')
self.host_catalog = config.get('mycatalog', 'host')
self.backup_db_catalog = config.get('mycatalog', 'dbname')
self.ret_catalog = config.get('mycatalog', 'retention')
self.engine = config.get('mycatalog', 'engine')
def connection(self):
try:
syslog.syslog(syslog.LOG_INFO, "Connecting to database...")
self.conn = MySQLdb.connect(self.host, self.user, self.password, cursorclass = cursors.DictCursor)
self.cursor = self.conn.cursor()
self.conn.autocommit(1)
syslog.syslog(syslog.LOG_INFO, "Connecting to database catalog...")
self.conn_catalog = MySQLdb.connect(self.host_catalog, self.user_catalog, self.password_catalog, cursorclass = cursors.DictCursor)
self.cursor_catalog = self.conn_catalog.cursor()
self.conn_catalog.autocommit(1)
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
sys.exit(1)
def close(self):
try:
syslog.syslog(syslog.LOG_INFO, "Closing connection")
self.conn.close()
syslog.syslog(syslog.LOG_INFO, "Closing connection Catalog")
self.conn_catalog.close()
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
def killLock(self):
self.conn.ping(1)
self.cursor.execute(""" SELECT ID, DB, TIME, INFO
FROM information_schema.PROCESSLIST
WHERE TIME>%d
AND USER=%s
AND COMMAND='Query'
AND STATE='Waiting for table' INFO like '%LOCK TABLES%'""", (int(lock_time), self.user))
if len(self.cursor.fetchone()) > 0:
lock = self.cursor.fetchone()
self.cursor.execute("""KILL %s""", (lock['ID'],))
self.conn_catalog.ping(1)
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='KILLED',
st_compress='KILLED',
updated_at=NOW()
WHERE db=%s
AND host=%s
AND created_at = DATE(NOW())""", (lock['DB'], self.hostname))
self.mysqlcheck(lock['DB'])
syslog.syslog(syslog.LOG_ERR, "Killed Database: %s Time: %s Query: %s" % (lock['DB'], lock['TIME'], lock['INFO']))
def mysqlcheck(self, database, mysqlcheck="/usr/bin/mysqlcheck"):
syslog.syslog(syslog.LOG_INFO, "run mysqlcheck for database: %s" % database)
try:
subprocess.check_call([ mysqlcheck, "--auto-repair", "-B", database, "-u%s" % self.user, "-p%s" % self.password])
self.conn_catalog.ping(1)
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='REPAIRED',
st_compress='REPAIRED',
updated_at=NOW()
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (database, self.hostname))
syslog.syslog(syslog.LOG_INFO, "End repair database: %s" % database)
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
def getdatabsesstats(self):
'Collect info for leela-agent'
self.conn_catalog.select_db(self.backup_db_catalog)
self.conn_catalog.ping(1)
self.cursor_catalog.execute("""SELECT CURRENT_DATE, db, datadir_size
FROM mybackup_catalog
WHERE created_at = CURRENT_DATE
AND db NOT IN ('mysql', 'teste', 'information_schema', %s)
ORDER BY 1""", (self.backup_db_catalog,))
return self.cursor_catalog.fetchall()
def showDatabases(self, tipo):
"""Return all the databases. Excluded databases can be specified"""
try:
self.conn_catalog.select_db(self.backup_db_catalog)
self.conn_catalog.ping(1)
self.cursor_catalog.execute(""" SELECT db
FROM mybackup_catalog
WHERE (
st_backup IS NULL
OR st_backup='ERROR'
OR st_backup='REPAIRED'
OR st_compress IS NULL
OR st_compress='ERROR'
)
AND created_at = DATE(NOW())
AND myisam = %s
AND host = %s
ORDER BY attempts, datadir_size""", (int(tipo), self.hostname))
result = self.cursor_catalog.fetchall()
databases = []
fd = open(self.db_ignore)
excludes = fd.read().strip().split()
fd.close()
for database in result:
databases.append(database['db'])
if excludes:
for excludedb in excludes:
try:
databases.remove(excludedb)
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='SKIP',
st_compress='SKIP',
updated_at=NOW(),
started_at=NOW(),
ended_at=NOW()
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (excludedb, self.hostname))
syslog.syslog(syslog.LOG_INFO, "[INFO] - SKIP backup database: %s" % excludedb)
except ValueError:
pass
return databases
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
sys.exit(1)
def mysqldump(self, database, dbfile, mysqldump="/usr/bin/mysqldump"):
syslog.syslog(syslog.LOG_INFO, "run mysqldump for database: %s" % database)
if self.lock:
lock_param = "--lock-tables"
else:
lock_param = "--skip-lock-tables"
try:
file_err = dbfile[:-7]
with nested(open("%s.err" % file_err, "w+"), open(dbfile, "w+")) as (err, f):
#p1 = subprocess.Popen([ mysqldump, "-u%s" % self.user, "-p%s" % self.password, database, "--opt", "-R", "--triggers", lock_param, "--force", "--max_allowed_packet=134217728" ], stdout=subprocess.PIPE, stderr=err, env={'LD_PRELOAD': 'libpreload-mysqldump.so'})
p1 = subprocess.Popen([ mysqldump, "-u%s" % self.user, database, "--opt", "-R", "--triggers", lock_param, "--force", "--max_allowed_packet=134217728" ], stdout=subprocess.PIPE, stderr=err, env={'MYSQL_PWD': self.password})
p2 = subprocess.Popen(["/bin/gzip", "--fast"], stdin=p1.stdout, stdout=f, stderr=subprocess.PIPE)
output2 = p2.communicate()[1]
returncode2 = p2.returncode
with open("%s.err" % file_err) as f:
output1 = f.readlines()
p1.poll()
returncode1 = p1.returncode
os.remove("%s.err" % file_err)
errors=[]
warnings=[]
warncodes=['1449','1556','1356']
search = [ 'Got error: (\d+):', 'Couldn\'t execute.*\((\d+)\)' ]
for msg in output1:
msg = msg.strip()
errcode = ''
for s in search:
r = re.compile(s)
m = r.split(msg)
if len(m) > 1:
errcode = m[1]
continue
if errcode and errcode in warncodes:
warnings.append('WARNING %s - %s' % ( errcode, msg ))
else:
errors.append('ERROR %s - %s' % ( errcode, msg ))
warnings_text='\n'.join(warnings)
errors_text='\n'.join(errors) + '\n' + warnings_text
for msg in warnings:
syslog.syslog(syslog.LOG_WARNING, "Database: %s" % database + " - %s" % msg)
for msg in errors:
syslog.syslog(syslog.LOG_ERR, "Database: %s" % database + " - %s" % msg)
syslog.syslog(syslog.LOG_INFO, "end mysqldump for database: %s" % database)
if len(errors) == 0 and len(warnings) > 0:
returncode1 = -1
self.conn_catalog.ping(1)
if returncode1 > 0:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET st_backup='ERROR',
message=%s
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (errors_text, database, self.hostname))
elif returncode1 < 0:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET st_backup='WARNING',
message=%s
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (warnings_text, database, self.hostname))
else:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='OK'
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (database, self.hostname))
if returncode2 > 0:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_compress='WARNING',
message=%s
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (output2, database, self.hostname))
syslog.syslog(syslog.LOG_WARNING, "[WARNING] - compressing file for database: %s" % database + " Message: %s" % output2)
elif returncode2 < 0:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_compress='ERROR',
st_backup='ERROR',
message=%s
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (output2, database, self.hostname))
syslog.syslog(syslog.LOG_ERR, "[ERROR] - Error dumping/compressing file for database: %s" % database + " Message: %s" % output2)
else:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_compress='OK'
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (database, self.hostname))
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
ended_at=NOW()
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (database, self.hostname))
except Exception, e:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='ERROR',
st_compress='ERROR',
ended_at=NOW()
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (database, self.hostname))
syslog.syslog(syslog.LOG_ERR, str(e))
def prepareBackup(self):
try:
if not os.path.isdir(self.backup_path):
syslog.syslog(syslog.LOG_INFO, "Creating backup path %s" % self.backup_path)
os.makedirs(self.backup_path)
os.chown(self.backup_root_path, self.owner.pw_uid, self.owner.pw_gid)
os.chown(self.mybackup_path, self.owner.pw_uid, self.owner.pw_gid)
os.chown(self.backup_path, self.owner.pw_uid, self.owner.pw_gid)
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
def postBackup(self, database):
syslog.syslog(syslog.LOG_INFO, "check backup file for database: %s" % database)
try:
self.conn_catalog.ping(1)
backupFile = "%s/%s.sql.gz" % (self.backup_path, database)
backupEmptyFile = "%s/%s.empty.sql" % (self.backup_path, database)
if os.path.isfile(backupEmptyFile):
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='EMPTY',
st_compress='EMPTY',
filename=%s,
file_size=0,
updated_at=NOW()
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (backupEmptyFile, database, self.hostname))
elif os.path.isfile(backupFile) and os.stat(backupFile).st_size > int(self.filesize):
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
filename=%s,
file_size=%s,
updated_at=NOW()
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (backupFile, os.stat(backupFile).st_size, database, self.hostname))
os.chown(backupFile, self.owner.pw_uid, self.owner.pw_gid)
else:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='ERROR',
st_compress='ERROR',
filename=%s,
file_size=0,
updated_at=NOW()
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (backupFile, database, self.hostname))
os.chown(backupFile, self.owner.pw_uid, self.owner.pw_gid)
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
def showTables(self, database):
"""Return all tables for a given database"""
try:
tables = []
sql="show tables from `%s`" % database
self.conn.ping(1)
self.cursor.execute(sql)
result = self.cursor.fetchall()
for table in result:
tables.append(table[table.keys()[0]])
return tables
except MySQLdb.OperationalError, oe:
"""Get '1049 - Unknow Database' Exception"""
if oe[0] == 1049:
raise DroppedDatabase(oe)
raise oe
def runBackup(self, databases):
for database in databases:
try:
self.conn_catalog.ping(1)
syslog.syslog(syslog.LOG_INFO, "Start backup database: %s" % database)
tables = self.showTables(database)
if len(tables) > 0:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
attempts=attempts+1,
started_at=now(),
updated_at=now()
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (database, self.hostname))
self.mysqldump(database, os.path.join(self.backup_path, "%s.sql.gz" % database))
else:
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='EMPTY',
st_compress='EMPTY',
started_at=now(),
ended_at=now(),
updated_at=now()
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (database, self.hostname))
dbfile = "%s/%s.empty.sql" % (self.backup_path, database)
self.writeEmptyFile(dbfile)
self.postBackup(database)
except MySQLdb.OperationalError, oe:
syslog.syslog(syslog.LOG_ERR, str(oe))
sys.exc_clear()
continue
except DroppedDatabase, dd:
syslog.syslog(syslog.LOG_WARNING, str(dd))
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='DROPED',
st_compress='DROPED',
updated_at=NOW(),
started_at=NOW(),
ended_at=NOW(),
message=%s
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (dd, database, self.hostname))
sys.exc_clear()
continue
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
self.cursor_catalog.execute("""UPDATE mybackup_catalog SET
st_backup='ERROR',
st_compress='ERROR',
ended_at=now(),
updated_at=now(),
message=%s
WHERE db=%s
AND host=%s
AND created_at=DATE(NOW())""", (e, database, self.hostname))
continue
def writeEmptyFile(self, dbfile):
try:
fd = open(dbfile, "w")
fd.close()
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
def summary(self):
self.conn_catalog.ping(1)
self.cursor_catalog.execute("""INSERT INTO mybackup_execution (host, datadir_size, file_size, started_at, ended_at, created_at)
SELECT host, SUM(datadir_size), SUM(file_size),MIN(started_at),MAX(ended_at), NOW()
FROM mybackup_catalog
WHERE created_at=DATE(NOW())
AND host=%s""", (self.hostname,))
def cleanBackup(self):
try:
self.conn_catalog.ping(1)
self.cursor_catalog.execute("""DELETE FROM mybackup_catalog
WHERE created_at < DATE(NOW() - INTERVAL %s DAY) """, (int(self.ret_catalog),))
syslog.syslog(syslog.LOG_INFO, "clean table catalog completed")
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
if __name__ == '__main__':
try :
lock = open('/var/lock/mybackup', 'w')
fcntl.flock(lock.fileno(), fcntl.LOCK_EX | fcntl.LOCK_NB)
mybackup = MyBackup()
syslog.syslog(syslog.LOG_INFO, "=================== Start ===================")
mybackup.prepareBackup()
databases = mybackup.showDatabases(1)
mybackup.runBackup(databases)
mybackup.summary()
mybackup.cleanBackup()
mybackup.close()
syslog.syslog(syslog.LOG_INFO, "=================== End ===================")
except Exception, e:
syslog.syslog(syslog.LOG_ERR, str(e))
print str(e)
sys.exit(1)
