def table_use_dailyreport():
mailto = map(
lambda x: x.user.email,
filter(lambda x: len(x.user.email) > 0,
User_profile.objects.filter(task_email__gt=0)))
sql = "SELECT a1.DBTAG, a1.TABLE_SCHEMA, a1.TABLE_NAME, a1.`TOTAL(M)`, a1.`DATA(M)`, a1.`INDEX(M)`FROM\
mon_tbsize a1 INNER JOIN(SELECT a.DBTAG, a.`TOTAL(M)` FROM mon_tbsize a LEFT JOIN mon_tbsize b ON a.DBTAG = b.DBTAG AND\
a.`TOTAL(M)` <= b.`TOTAL(M)` GROUP BY\
a.DBTAG, a.`TOTAL(M)` HAVING\
COUNT(b.`TOTAL(M)`) <= 5 ) b1 ON a1.DBTAG = b1.DBTAG\
AND a1.`TOTAL(M)` = b1.`TOTAL(M)` ORDER BY\
b1.`TOTAL(M)` DESC,a1.DBTAG DESC"
max_tbdata, max_tbcols = mysql_query(sql, user, passwd, host, int(port),
dbname)
sql = "select DBTAG,TABLE_SCHEMA,TABLE_NAME,round(AUTO_INCREMENT/MAX_VALUE*100,2) as used_percent,\
COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,IS_UNSIGNED,IS_INT,MAX_VALUE,AUTO_INCREMENT,INDEX_NAME,\
SEQ_IN_INDEX,update_time from mon_autoinc_status order by AUTO_INCREMENT/MAX_VALUE desc limit 20"
max_incre, max_increcols = mysql_query(sql, user, passwd, host, int(port),
dbname)
html_content = loader.render_to_string('include/mail_template.html',
locals())
sendmail('Big Table And Auto_Increment_Use Report', mailto, html_content)
def sendmail_monitor(instance_id, mailto, data, alarm_type):
instance = Db_instance.objects.get(id=instance_id)
dbinfo = 'IP: ' + instance.ip + '\n' + 'PORT: ' + instance.port + '\n'
title = ''
if data == 'ok':
alarm_information = alarm_list[alarm_type] + ' ok'
title = instance.ip + ':' + instance.port + '[' + instance.comments + ']' + '---------' + alarm_information
elif alarm_type in [1, 2, 3]:
title = instance.ip + ':' + instance.port + '[' + instance.comments + ']' + '---------' + alarm_list[
alarm_type]
mon_sqllist = data
elif alarm_type == 6:
title = instance.ip + ':' + instance.port + '[' + instance.comments + ']' + '---------' + 'too many connections'
alarm_information = 'values: ' + str(data)
elif alarm_type == 5:
title = instance.ip + ':' + instance.port + '[' + instance.comments + ']' + '---------' + alarm_list[
alarm_type]
alarm_information = 'values: ' + str(data)
elif alarm_type == 4:
alarm_information = 'SLAVE_IO_THREAD:' + data[
'iothread'] + '\nSLAVE_SQL_THREAD:' + data['sqlthread'] + '\n'
title = instance.ip + ':' + instance.port + '[' + instance.comments + ']' + '---------' + alarm_list[
alarm_type]
elif alarm_type == 7:
title = instance.ip + ':' + instance.port + '[' + instance.comments + ']' + '---------' + alarm_list[
alarm_type]
alarm_information = "MySQL Server Down"
html_content = loader.render_to_string('include/mail_template.html',
locals())
sendmail(title, mailto, html_content)
def get_dupreport(hosttag, email=''):
import os
if incept.pttool_switch != 0:
mailto = []
mailto.append(email)
try:
db = Db_name.objects.get(dbtag=hosttag)
tar_port, tar_passwd, tar_username, tar_host, tar_dbname = get_dbcon(
db)
except Exception, e:
print e
return "please check your db set"
if os.path.isfile(incept.pttool_path + '/pt-duplicate-key-checker'):
cmd = incept.pttool_path + '/pt-duplicate-key-checker' + ' -u %s -p %s -P %d -h %s -d %s ' % (
tar_username, tar_passwd, int(tar_port), tar_host, tar_dbname)
dup_result = commands.getoutput(cmd)
dup_result = db.dbtag + '\n' + dup_result
if email != '':
html_content = loader.render_to_string(
'include/mail_template.html', locals())
sendmail('DUPKEY CHECK ON ' + db.dbtag, mailto, html_content)
else:
return dup_result
else:
return 'pt-tool path set wrong'
def get_dupreport_all():
import os
mailto = []
if incept.pttool_switch != 0 and os.path.isfile(
incept.pttool_path + '/pt-duplicate-key-checker'):
mailto = map(
lambda x: x.user.email,
filter(lambda x: len(x.user.email) > 0,
User_profile.objects.filter(task_email__gt=0)))
ins_li = list(Db_instance.objects.filter(db_type='mysql'))
for insname in ins_li:
for i in insname.db_name_set.all():
for x in i.instance.exclude(id=insname.id):
print x
ins_li.remove(x)
dup_result = ''
for i in ins_li:
try:
result_tmp = get_dupreport_byins(i)
if result_tmp:
dup_result = dup_result + 'ip:' + i.ip + '\nport:' + str(
i.port) + '\n' + result_tmp + '\n\n\n\n\n\n'
except:
pass
html_content = loader.render_to_string('include/mail_template.html',
locals())
sendmail('DUPKEY CHECK ON FOR ALL', mailto, html_content)
def get_dupreport(hosttag, db, email=''):
import os
pc = prpcrypt()
if incept.pttool_switch != 0:
mailto = []
mailto.append(email)
try:
instance = Db_instance.objects.get(id=int(hosttag))
db_account = Db_account.objects.get(instance=instance,
db_account_role='admin')
tar_dbname = db
tar_port = instance.port
tar_host = instance.ip
tar_username = db_account.user
tar_passwd = pc.decrypt(db_account.passwd)
except Exception, e:
print e
return "please check your db set"
f = incept.pttool_path + '/pt-duplicate-key-checker'
if os.path.isfile(incept.pttool_path + '/pt-duplicate-key-checker'):
cmd = incept.pttool_path + '/pt-duplicate-key-checker' + ' -u %s -p %s -P %d -h %s -d %s ' % (
tar_username, tar_passwd, int(tar_port), tar_host, tar_dbname)
dup_result = commands.getoutput(cmd)
dup_result = tar_host + ':' + tar_port + '----' + db + '\n' + dup_result
if email != '':
html_content = loader.render_to_string(
'include/mail_template.html', locals())
sendmail(
'DUPKEY CHECK ON ' + tar_host + ':' + tar_port + '----' +
db, mailto, html_content)
else:
return dup_result
else:
return 'pt-tool path set wrong'
def get_data(a, sql):
tar_port, tar_passwd, tar_username, tar_host, tar_dbname = get_dbcon(a)
#print tar_port+tar_passwd+tar_username+tar_host
if tar_username <> 0:
try:
results, col = mysql_query(sql, tar_username, tar_passwd, tar_host,
tar_port, tar_dbname)
except Exception, e:
#wrong_message
results, col = ([str(e)], ''), ['error']
#results,col = mysql_query(wrong_msg,user,passwd,host,int(port),dbname)
if col[0] == 'error':
mailto = map(
lambda x: x.user.email,
filter(lambda x: len(x.user.email) > 0,
User_profile.objects.filter(task_email__gt=0)))
html_content = loader.render_to_string(
'include/mail_template.html', locals())
msg = 'Table check error:' + results[0][0]
sendmail(msg, mailto, html_content)
return results, col
try:
print i.dbtag
sql = "select '{}',a.* from (select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,from_unixtime(max(PARTITION_DESCRIPTION)) \
as max_date from information_schema.PARTITIONS where PARTITION_NAME is not null \
and PARTITION_METHOD='RANGE' and TABLE_SCHEMA='{}' group by TABLE_SCHEMA,TABLE_NAME ) \
a where a.max_date < date_add(now(), interval 1 month);".format(
i.dbtag, i.dbname)
data, col = get_data(i, sql)
if len(data) > 0 and len(col) > 1:
partResult = partResult + data
except Exception, e:
print e
if len(partResult) > 0:
html_content = loader.render_to_string('include/mail_template.html',
locals())
sendmail('TABLE PARTITION USED CHECK', mailto, html_content)
#use /admin/ to set crontab (per day)
@shared_task
def table_check():
#archive history data
sql = "insert into mon_autoinc_status_his (TABLE_SCHEMA ,TABLE_NAME,COLUMN_NAME,DATA_TYPE,\
COLUMN_TYPE,IS_UNSIGNED,IS_INT,MAX_VALUE,AUTO_INCREMENT,INDEX_NAME,\
SEQ_IN_INDEX,DBTAG,update_time) select TABLE_SCHEMA ,TABLE_NAME,COLUMN_NAME,DATA_TYPE,\
COLUMN_TYPE,IS_UNSIGNED,IS_INT,MAX_VALUE,AUTO_INCREMENT,INDEX_NAME,\
SEQ_IN_INDEX,DBTAG,update_time from mon_autoinc_status"
mysql_exec(sql)
sql = "insert into mon_tbsize_his (TABLE_SCHEMA,TABLE_NAME,`DATA(M)`,\
`INDEX(M)`,`TOTAL(M)`,DBTAG,update_time) select TABLE_SCHEMA,TABLE_NAME,`DATA(M)`,\