#!/usr/bin/python
#--*-- coding:utf-8 --*--
import os
import time
import logging
import MySQLdb as mdb
from datetime import datetime
# LOG INFO
log='/data/scripts/logs/'
isExists = os.path.exists(log)
if not isExists:
os.makedirs(log)
logging.basicConfig(level=logging.INFO,
format='%(asctime)s: %(levelname)-4s %(message)s',
filename= log + 'backup_mysql_' + time.strftime('%Y%m%d_%H%M%S', time.localtime()) + '.log',
filemode='w',
datefmt='%Y%m%d %X')
class MYSQL:
def __init__(self,ip,user,pwd,db,port):
self.ip = ip
self.user = user
self.pwd = pwd
self.db = db
self.port = port
def __GetConnect(self):
conn = mdb.connect(self.ip,self.user,self.pwd,self.db,self.port)
cur = conn.cursor(cursorclass=mdb.cursors.DictCursor)
return cur
def Query(self,sql):
cur = self.__GetConnect()
cur.execute(sql)
res = cur.fetchall()
cur.close()
return res
def dellog(log):
f = list(os.listdir(log))
logging.info('--------------------------------------------------')
logging.info("Delete >60 days backup log %s" % log)
for i in range(len(f)):
filedate = os.path.getmtime(log + '/' + f[i])
currdate = time.time()
num = (currdate - filedate) / (60 * 60 * 24 * 31 * 12)
if num > 5:
if os.path.isfile(log + f[i]):
try:
os.remove(log + '/' + f[i])
logging.info(u"Delete %s" % f[i])
except Exception as e:
logging.info(e)
logging.info('--------------------------------------------------')
def delpath(path,flag):
f = list(os.listdir(path))
logging.info("Delete >30 days backup file %s" % path)
for i in range(len(f)):
filedate = os.path.getmtime(path + '/' + f[i])
currdate = time.time()
num = (currdate - filedate) / (60 * 60 * 24 * 31)
if flag == 'day':
if num > 3:
if os.path.isfile(path + f[i]):
try:
os.remove(path + '/' + f[i])
logging.info(u"Delete %s" % f[i])
except Exception as e:
logging.info(e)
if flag == 'mon':
if num > 12:
if os.path.isfile(path + f[i]):
try:
os.remove(path + '/' + f[i])
logging.info(u"Delete %s" % f[i])
except Exception as e:
logging.info(e)
if flag == 'year':
if num > 12 * 5:
if os.path.isfile(path + f[i]):
try:
os.remove(path + '/' + f[i])
logging.info(u"Delete %s" % f[i])
except Exception as e:
logging.info(e)
def main():
user = 'dumper'
pwd = '123456'
schema = 'information_schema'
port = 3306
pre = '100.110.1.'
iplist = ('10','11','12','13','15','17','58')
for i in iplist:
ip = pre + i
# create mysql connect instance
mysql = MYSQL(ip,user,pwd,schema,port)
# all db of need backup
sql = 'select schema_name from schemata where schema_name not in ("information_schema","performance_schema","sys")'
# query result is tuple,but all element is dictionary
dblist = mysql.Query(sql)
logging.info('Begin backup ' + ip + ' to 10.110.0.1')
#
for db in dblist:
path = '/media/dbbackup/'
ti = time.strftime('%Y%m%d_%H%M%S', time.localtime())
# db is dictionary,so get the values attributes
dbname = db.values()[0]
# local dir for backup
path = path + ip + '/' + dbname + '/'
# make if local dir is not exists
isExists = os.path.exists(path)
if not isExists:
os.makedirs(path)
fname = path + dbname + '_' + str(ti) + '.sql.gz'
cmd = '/usr/local/mysql/bin/mysqldump -h%s -u%s -p%s --single-transaction --master-data=2 -R --triggers --hex-blob -B %s| gzip > %s' %(ip,user,pwd,dbname,fname)
# begin backup
logging.info('Begin ' + dbname )
os.system(cmd)
# delete backup more than 30 days
delpath(path,'day')
# 判断是否有初、年初,如是多保留一份
dat = time.strftime('%Y%m%d', time.localtime())
mon = '%d%02d01' % (time.localtime().tm_year, time.localtime().tm_mon)
year = '%d0101' % (time.localtime().tm_year)
if dat == mon:
mpath = path + 'month/'
isExists = os.path.exists(mpath)
if not isExists:
os.makedirs(mpath)
cmd='cp '+ fname + ' ' + mpath
os.system(cmd)
# delete backup more than 12 months
delpath(mpath,'mon')
if dat == year:
ypath = path + 'year/'
isExists = os.path.exists(ypath)
if not isExists:
os.makedirs(ypath)
cmd='cp '+ fname + ' ' + ypath
os.system(cmd)
# delete backup more than 5 years
delpath(ypath,'year')
# delete backup log more than 6 years
dellog(log)
if __name__ == '__main__':
logging.info('##################################################')
main()
logging.info('##################################################')