def audit_target(self, target_id, snapshot_id='',save_audit=False, envtype='', additional_info=''):
def calculate_max_memory(**mem_param_rec):
try:
max_memory = mem_param_rec['key_buffer_size'] + \
mem_param_rec['query_cache_size'] + \
mem_param_rec['tmp_table_size'] + \
mem_param_rec['innodb_buffer_pool_size'] + \
mem_param_rec['innodb_additional_mem_pool_size'] + \
mem_param_rec['innodb_log_buffer_size'] + \
(mem_param_rec['max_connections'] * (mem_param_rec['sort_buffer_size'] + \
mem_param_rec['read_buffer_size'] + \
mem_param_rec['read_rnd_buffer_size'] + \
mem_param_rec['join_buffer_size'] + \
mem_param_rec['thread_stack'] + \
mem_param_rec['binlog_cache_size']))
return str(max_memory)
except:
return '0'
db_conn = self.get_conn_conf_by_id(target_id)
audit_rec = {}
if db_conn:
# first time when we check 'envtype' is provided or not and trying to get it from target's record
if 'envtype' in db_conn.keys():
if not envtype:
envtype = str(list(db_conn['envtype'])[0])
del db_conn['envtype']
db_conn_4disply = db_conn.copy()
# hide the password and then print
if 'password' in db_conn_4disply.keys():
db_conn_4disply['password'] = '******'
print("\nAuditing ID={1}, connecting to [{0}]...".format(db_conn_4disply, target_id))
try:
target_db = DBManager(True, False, **db_conn)
except Exception as err:
print("For ID={0}, audit failed ...".format(target_id))
print(err)
return
additional_info += 'connected to: {0}'.format(db_conn_4disply['host']) if 'host' in db_conn_4disply.keys() else 'connected to: {0}'.format('localhost')
audit_rec['snapshot_id'] = snapshot_id if snapshot_id else datetime.now().strftime('%S%M%H%d%m%Y%f')
audit_rec['host'] = dict(target_db.execute_query(sqls4mysql['host'], True)[0])['host']
audit_rec['osversion'] = dict(target_db.execute_query(sqls4mysql['osversion'], True)[0])['osversion']
envtype = envtype.upper()
audit_rec['envtype'] = self.get_env_type(audit_rec['host']) if not envtype or envtype not in ('PROD','STG','QA','DEV') else envtype
audit_rec['dbversion'] = dict(target_db.execute_query(sqls4mysql['dbversion'], True)[0])['dbversion']
# checking if INNODB is enabled
innodb = str(target_db.get_global_variable('have_innodb')).upper()
additional_info += ', Have_InnoDB is set to ({0})'.format(innodb)
# calculating MAX memory
memory_params = dict([(row[0][1],long(row[1][1])) for row in target_db.execute_query(sqls4mysql['memory_params'],True)])
audit_rec['max_req_memory'] = calculate_max_memory(**memory_params)
audit_rec['additional_info'] = additional_info
for dblist in target_db.execute_query(sqls4mysql['dblist'], True):
dbname = dict(dblist)['Database']
audit_rec['dbname'] = dbname
audit_rec['dbsize'] = dict(target_db.execute_query(sqls4mysql['dbsize'].format(dbname), True)[0])['dbsize']
audit_rec['free_space'] = dict(target_db.execute_query(sqls4mysql['free_space'].format(dict(dblist)['Database']), True)[0])['free_space']
if save_audit:
cmd = sqls4mysql['insert_aud_rec'].format(audit_rec['snapshot_id'],
audit_rec['host'],
audit_rec['osversion'],
audit_rec['envtype'],
audit_rec['dbversion'],
audit_rec['dbname'],
audit_rec['dbsize'],
audit_rec['free_space'],
audit_rec['max_req_memory'],
audit_rec['additional_info'])
self.execute_dml(cmd, False, message='', additional_fail_msg='', additional_ok_msg='')
else:
print(audit_rec)
target_db.close()
print('OK')
else:
print("Couldn't find record with id={0} ...".format(target_id))
