class FileBackup(object):
_file_backup_db = 'DbBak'
_db = None
def __init__(self):
dbconfig = {'host':backup_config.BACKUP_INFO_DB_IP,
'port':backup_config.BACKUP_INFO_DB_PORT,
'user':AppConfig.MYSQL_ADMIN_USR,
'passwd':AppConfig.MYSQL_ADMIN_PSWORD,
'db':self._file_backup_db,
'charset':'utf8'}
self._db = MySQL_lightweight(dbconfig)
def get_file_backup_info_from_db(self, st_date=time.strftime("%Y-%m-%d",time.localtime(time.time()-24*60*60)), en_date=time.strftime('%Y-%m-%d',time.localtime(time.time()))):
if not (is_valid_date(st_date) and is_valid_date(en_date)):
raise Exception('It is not a valid date.')
sql = "SELECT name,file_name,file_size,bak_keep_host FROM file_backup WHERE TIME >= '%s' AND TIME < '%s' order by name" % (st_date, en_date)
print sql
self._db.query(sql)
result = self._db.fetchAllArray()
return result
def get_file_backup_info(self, st_date=time.strftime("%Y-%m-%d",time.localtime(time.time()-24*60*60)),
en_date=time.strftime('%Y-%m-%d',time.localtime(time.time()))):
if not (is_valid_date(st_date) and is_valid_date(en_date)):
raise Exception('It is not a valid date.')
result = {
'total': 0,
'success': 0,
'failed': 0,
'noback': 0,
'data_size': 0,
'disk_use': 0,
'bak_server': '',
'info': {}
}
need_backup = AppConfig.FILE_BACKUP
need_backup = list(need_backup)
result['total'] = len(need_backup)
items = self.get_file_backup_info_from_db()
for item in items:
if item['name'] in need_backup:
result['success'] += 1
need_backup.remove(item['name'])
result['data_size'] += item['file_size']
result['failed'] = len(need_backup)
result['info'] = items
result['bak_server'] = AppConfig.FILE_BACKUP_server
return result
def get_latest_server_use(self, ip):
if not (ip):
raise Exception('It is not a valid ip.')
sql = "Select IP, Path, DiskUse, AddTime from Backup_ServerUseFraction where IP='%s' Order by AddTime Desc limit 1;" % ip
print sql
self._db.query(sql)
result = self._db.fetchOneRow()
return result
class FileBackup(object):
_file_backup_db = backup_config.FILE_BACKUP_DB
_file_backup_servers = backup_config.FILE_BACKUP_SERVERS
_file_backup_types = backup_config.FILE_BACKUP_TYPES
_db = None
def __init__(self):
dbconfig = {
'host': backup_config.BACKUP_INFO_DB_IP,
'port': backup_config.BACKUP_INFO_DB_PORT,
'user': AppConfig.MYSQL_ADMIN_USR,
'passwd': AppConfig.MYSQL_ADMIN_PSWORD,
'db': backup_config.FILE_BACKUP_DB,
'charset': 'utf8'
}
self._db = MySQL_lightweight(dbconfig)
def get_file_backup_info_from_db(
self,
st_date=time.strftime("%Y-%m-%d",
time.localtime(time.time() - 24 * 60 * 60)),
en_date=time.strftime('%Y-%m-%d', time.localtime(time.time()))):
if not (is_valid_date(st_date) and is_valid_date(en_date)):
raise Exception('It is not a valid date.')
sql = "SELECT name,file_name,file_size,bak_keep_host FROM file_backup WHERE TIME >= '%s' AND TIME < '%s' order by name" % (
st_date, en_date)
print sql
self._db.query(sql)
result = self._db.fetchAllArray()
return result
def get_file_backup_info(
self,
st_date=time.strftime("%Y-%m-%d",
time.localtime(time.time() - 24 * 60 * 60)),
en_date=time.strftime('%Y-%m-%d', time.localtime(time.time()))):
if not (is_valid_date(st_date) and is_valid_date(en_date)):
raise Exception('It is not a valid date.')
result = {
'total': 0,
'success': 0,
'failed': 0,
'noback': 0,
'data_size': 0,
'disk_use': 0,
'bak_server': '',
'info': {}
}
need_backup = backup_config.FILE_BACKUP_TYPES
need_backup = list(need_backup)
result['total'] = len(need_backup)
items = self.get_file_backup_info_from_db()
for item in items:
if item['name'] in need_backup and item['file_size'] != 0:
result['success'] += 1
need_backup.remove(item['name'])
result['data_size'] += item['file_size']
result['failed'] = len(need_backup)
result['info'] = items
result['bak_servers'] = backup_config.FILE_BACKUP_SERVERS
return result
def get_latest_server_use(self, ip):
if not (ip):
raise Exception('It is not a valid ip.')
sql = "Select IP, Path, DiskUse, AddTime from Backup_ServerUseFraction where IP='%s' Order by AddTime Desc limit 1;" % ip
print sql
self._db.query(sql)
result = self._db.fetchOneRow()
return result
class MonitorArchive():
_dbmonitor_db = 'DBMonitor'
_db = None
_monitor_ip_lists = monitor_config.MONITOR_IP_LIST_917
_monitor_ip_dict = monitor_config.MONITOR_IP_DICT_917
_monitor_type = monitor_config.MONITOR_TYPE
def __init__(self):
dbconfig = {
'host': monitor_config.DBMONITOR_DB_IP,
'port': monitor_config.DBMONITOR_DB_PORT,
'user': AppConfig.MYSQL_ADMIN_USR,
'passwd': AppConfig.MYSQL_ADMIN_PSWORD,
'db': self._dbmonitor_db,
'charset': 'utf8'
}
print dbconfig
self._db = MySQL_lightweight(dbconfig)
def get_mtype_list(self):
return self._monitor_type
def get_hc_para(self, date, ip, port='3306', monitor_type='questions'):
if not (self.check_date(date) and ip):
print 'get_hc_para -- parameters error'
return False
st_time = date + ' 09:00:00'
en_time = date + ' 11:00:00'
sql = "select GroupName, MTime, %s from DBMonitor_History where IP='%s' and Port='%s' and MTime>='%s' and MTime<='%s' order by MTime" % (
monitor_type, ip, port, st_time, en_time)
print sql
self._db.query(sql)
rows = self._db.fetchAllArray()
for row in rows:
print row['MTime'], row[monitor_type]
return rows
def archive_dashboard(self, date, monitor_type='questions'):
if not (self.check_date(date)):
print 'archive_dashboard -- parameters error'
return False
st_time = date + ' 09:00:00'
en_time = date + ' 11:00:00'
sql = "select GroupName,IP,max(%s) as max_value from DBMonitor_History where MTime>='%s' and MTime<='%s' group by IP order by GroupName" % (
monitor_type, st_time, en_time)
print sql
self._db.query(sql)
rows = self._db.fetchAllArray()
for row in rows:
row['max_show'] = row['max_value'] if row['max_value'] else '0'
row['max_show'] = float2humanread(row['max_value'])
return rows
def archive_instance(self, ip, date):
if not (self.check_date(date)):
print 'archive_dashboard -- parameters error'
return False
st_time = date + ' 09:00:00'
en_time = date + ' 11:00:00'
mtype_list = "`,`".join(self._monitor_type)
#print mtype_list
sql = "select MTime,GroupName,IP,`%s` from DBMonitor_History where IP='%s' and MTime>='%s' and MTime<='%s' order by MTime" % (
mtype_list, ip, st_time, en_time)
#sql = "select MTime,GroupName,IP,questions,tps from DBMonitor_History where IP='%s' and MTime>='%s' and MTime<='%s' order by MTime" % (ip, st_time, en_time)
print sql
self._db.query(sql)
rows = self._db.fetchAllArray()
hcs = {}
for mtype in self._monitor_type:
hcs[mtype] = {
"chart": {
"type": "line"
},
"title": {
"text": mtype
},
"xAxis": {
"categories": []
},
"yAxis": {
"title": {
"text": "Value/秒"
}
},
"series": [{
"name": mtype,
"data": []
}]
}
for row in rows:
tmp_time = row["MTime"].strftime('%H:%M')
for mtype in self._monitor_type:
hcs[mtype]["xAxis"]["categories"].append(tmp_time)
hcs[mtype]["series"][0]["data"].append(row[mtype])
# for mtype in hcs:
# print hcs[mtype]
return hcs
def check_date(self, date):
"""
Description: check date if it is a formatted date
Parameters:
### date: '2000-03-03'
Example:
### check_date('2000-03-03')
### return True
"""
if not (date and (type(date) is str or type(date) is unicode)):
print 'check_date -- parameters error'
return False
date = date.encode("utf-8") if type(date) is unicode else date
match = re.match(r'^(\d{4})-(\d{2})-(\d{2})$', date)
return True if match else False
def backup_917_in_cat(self, ip=None, date=None):
if not (ip and date):
print 'monitor -- parameters error: need ip and date'
return False
product = "db-mysql-" + str(ip) + "-3306"
url = 'http://cat.dp/cat/r/database?op=view&forceDownload=json&timeRange=2'
url += ('&product=%s&date=%s' % (product, date))
print url
result = requests.get(url, timeout=20).json()
if result.has_key('lineCharts') and result['lineCharts']:
f_out = '917archive/' + str(ip) + '_' + str(date)
with open(f_out, 'w') as f:
f.write(json.dumps(result))
return result
def list_stat_type(self, ip=None, date=None):
if not (ip and date):
print 'monitor -- parameters error: need ip and date'
return False
product = "db-mysql-" + str(ip) + "-3306"
url = 'http://cat.dp/cat/r/database?op=view&forceDownload=json&timeRange=2'
url += ('&product=%s&date=%s' % (product, date))
print url
result = requests.get(url, timeout=20).json()
if result.has_key('lineCharts') and result['lineCharts']:
for lineChart in result['lineCharts']:
sub = lineChart['id'].split(':')
print sub[2]
return True
def json_into_db(self, ip=None, date=None):
if not (ip and date):
print 'monitor -- parameters error: need ip and date'
return False
product = "db-mysql-" + str(ip) + "-3306"
f_name = '917archive/' + str(ip) + '_' + str(date)
print f_name
count = 0
if os.path.exists(f_name):
with open(f_name, 'r') as f:
dump = f.read()
data_json = json.loads(dump) if type(dump) is str else ''
#print type(data_json)
simple_json = {}
start_timestamp = None
if data_json.has_key('lineCharts') and data_json['lineCharts']:
for lineChart in data_json['lineCharts']:
start_timestamp = lineChart['start']
sub = lineChart['id'].split(':')
mtype = sub[2]
simple_json[mtype] = lineChart['datas'][0]
if start_timestamp:
start_timestamp = int(
time.mktime(
time.strptime(start_timestamp,
'%Y/%m/%d %H:%M'))) * 1000
if simple_json:
for minute in range(0, 120):
sentinel = start_timestamp + 60000 * minute
MTime = time.localtime(sentinel / 1000)
MTime = time.strftime('%Y-%m-%d %H:%M:%S', MTime)
GroupName = self._monitor_ip_dict[ip]
keys = 'GroupName,IP,Port,MTime'
values = "'%s','%s',%s,'%s'" % (GroupName, ip, '3306',
MTime)
for mtype in self._monitor_type:
keys += (',`' + mtype + '`')
value = str(simple_json[mtype][str(
sentinel)]) if simple_json[mtype].has_key(
str(sentinel)) else '0'
values += (',' + value)
sql = 'INSERT INTO DBMonitor_History (%s) VALUES(%s)' % (
keys, values)
#print sql
count += 1
ret = self._db.insert(sql)
if ret:
print ip
print ret
print count
return True
class MonitorArchive():
_dbmonitor_db = 'DBMonitor'
_db = None
_monitor_ip_lists = monitor_config.MONITOR_IP_LIST_917
_monitor_ip_dict = monitor_config.MONITOR_IP_DICT_917
_monitor_type = monitor_config.MONITOR_TYPE
def __init__(self):
dbconfig = {'host':monitor_config.DBMONITOR_DB_IP,
'port':monitor_config.DBMONITOR_DB_PORT,
'user':AppConfig.MYSQL_ADMIN_USR,
'passwd':AppConfig.MYSQL_ADMIN_PSWORD,
'db':self._dbmonitor_db,
'charset':'utf8'}
print dbconfig
self._db = MySQL_lightweight(dbconfig)
def get_mtype_list(self):
return self._monitor_type
def get_hc_para(self, date, ip, port='3306', monitor_type='questions'):
if not (self.check_date(date) and ip):
print 'get_hc_para -- parameters error'
return False
st_time = date + ' 09:00:00'
en_time = date + ' 11:00:00'
sql = "select GroupName, MTime, %s from DBMonitor_History where IP='%s' and Port='%s' and MTime>='%s' and MTime<='%s' order by MTime" % (monitor_type, ip, port, st_time, en_time)
print sql
self._db.query(sql)
rows = self._db.fetchAllArray();
for row in rows:
print row['MTime'], row[monitor_type]
return rows
def archive_dashboard(self, date, monitor_type='questions'):
if not (self.check_date(date)):
print 'archive_dashboard -- parameters error'
return False
st_time = date + ' 09:00:00'
en_time = date + ' 11:00:00'
sql = "select GroupName,IP,max(%s) as max_value from DBMonitor_History where MTime>='%s' and MTime<='%s' group by IP order by GroupName" % (monitor_type, st_time, en_time)
print sql
self._db.query(sql)
rows = self._db.fetchAllArray();
for row in rows:
row['max_show'] = row['max_value'] if row['max_value'] else '0'
row['max_show'] = float2humanread(row['max_value'])
return rows
def archive_instance(self, ip, date):
if not (self.check_date(date)):
print 'archive_dashboard -- parameters error'
return False
st_time = date + ' 09:00:00'
en_time = date + ' 11:00:00'
mtype_list = "`,`".join(self._monitor_type)
#print mtype_list
sql = "select MTime,GroupName,IP,`%s` from DBMonitor_History where IP='%s' and MTime>='%s' and MTime<='%s' order by MTime" % (mtype_list,ip, st_time, en_time)
#sql = "select MTime,GroupName,IP,questions,tps from DBMonitor_History where IP='%s' and MTime>='%s' and MTime<='%s' order by MTime" % (ip, st_time, en_time)
print sql
self._db.query(sql)
rows = self._db.fetchAllArray();
hcs = {}
for mtype in self._monitor_type:
hcs[mtype] = {
"chart": {"type": "line"},
"title": {"text": mtype},
"xAxis": {"categories": []},
"yAxis": {"title": {"text": "Value/秒"}},
"series": [
{"name": mtype,"data": []}
]
}
for row in rows:
tmp_time = row["MTime"].strftime('%H:%M')
for mtype in self._monitor_type:
hcs[mtype]["xAxis"]["categories"].append(tmp_time)
hcs[mtype]["series"][0]["data"].append(row[mtype])
# for mtype in hcs:
# print hcs[mtype]
return hcs
def check_date(self,date):
"""
Description: check date if it is a formatted date
Parameters:
### date: '2000-03-03'
Example:
### check_date('2000-03-03')
### return True
"""
if not (date and (type(date) is str or type(date) is unicode)):
print 'check_date -- parameters error'
return False
date = date.encode("utf-8") if type(date) is unicode else date
match = re.match(r'^(\d{4})-(\d{2})-(\d{2})$', date)
return True if match else False
def backup_917_in_cat(self, ip=None, date=None):
if not (ip and date):
print 'monitor -- parameters error: need ip and date'
return False
product = "db-mysql-" + str(ip) + "-3306"
url = 'http://cat.dp/cat/r/database?op=view&forceDownload=json&timeRange=2'
url += ('&product=%s&date=%s' % (product,date))
print url
result = requests.get(url, timeout=20).json()
if result.has_key('lineCharts') and result['lineCharts']:
f_out = '917archive/' + str(ip) + '_' + str(date)
with open(f_out, 'w') as f:
f.write(json.dumps(result))
return result
def list_stat_type(self, ip=None, date=None):
if not (ip and date):
print 'monitor -- parameters error: need ip and date'
return False
product = "db-mysql-" + str(ip) + "-3306"
url = 'http://cat.dp/cat/r/database?op=view&forceDownload=json&timeRange=2'
url += ('&product=%s&date=%s' % (product,date))
print url
result = requests.get(url, timeout=20).json()
if result.has_key('lineCharts') and result['lineCharts']:
for lineChart in result['lineCharts']:
sub = lineChart['id'].split(':')
print sub[2]
return True
def json_into_db(self, ip=None, date=None):
if not (ip and date):
print 'monitor -- parameters error: need ip and date'
return False
product = "db-mysql-" + str(ip) + "-3306"
f_name = '917archive/' + str(ip) + '_' + str(date)
print f_name
count = 0
if os.path.exists(f_name):
with open(f_name, 'r') as f:
dump = f.read()
data_json = json.loads(dump) if type(dump) is str else ''
#print type(data_json)
simple_json = {}
start_timestamp = None
if data_json.has_key('lineCharts') and data_json['lineCharts']:
for lineChart in data_json['lineCharts']:
start_timestamp = lineChart['start']
sub = lineChart['id'].split(':')
mtype = sub[2]
simple_json[mtype] = lineChart['datas'][0]
if start_timestamp:
start_timestamp = int(time.mktime(time.strptime(start_timestamp,'%Y/%m/%d %H:%M'))) * 1000
if simple_json:
for minute in range(0,120):
sentinel = start_timestamp + 60000 * minute
MTime = time.localtime(sentinel/1000)
MTime = time.strftime('%Y-%m-%d %H:%M:%S', MTime)
GroupName = self._monitor_ip_dict[ip]
keys = 'GroupName,IP,Port,MTime'
values = "'%s','%s',%s,'%s'" % (GroupName,ip,'3306',MTime)
for mtype in self._monitor_type:
keys += (',`' + mtype + '`')
value = str(simple_json[mtype][str(sentinel)]) if simple_json[mtype].has_key(str(sentinel)) else '0'
values += (',' + value)
sql = 'INSERT INTO DBMonitor_History (%s) VALUES(%s)' % (keys, values)
#print sql
count += 1
ret = self._db.insert(sql)
if ret:
print ip
print ret
print count
return True