def check_mysql(host, port, username, password, server_id, tags):
try:
conn = MySQLdb.connect(host=host,
user=username,
passwd=password,
port=int(port),
connect_timeout=3,
charset='utf8')
cur = conn.cursor()
conn.select_db('information_schema')
#cur.execute('flush hosts;')
############################# CHECK MYSQL ####################################################
mysql_variables = func.get_mysql_variables(cur)
mysql_status = func.get_mysql_status(cur)
time.sleep(1)
mysql_status_2 = func.get_mysql_status(cur)
############################# GET VARIABLES ###################################################
version = func.get_item(mysql_variables, 'version')
key_buffer_size = func.get_item(mysql_variables, 'key_buffer_size')
sort_buffer_size = func.get_item(mysql_variables, 'sort_buffer_size')
join_buffer_size = func.get_item(mysql_variables, 'join_buffer_size')
max_connections = func.get_item(mysql_variables, 'max_connections')
max_connect_errors = func.get_item(mysql_variables,
'max_connect_errors')
open_files_limit = func.get_item(mysql_variables, 'open_files_limit')
table_open_cache = func.get_item(mysql_variables, 'table_open_cache')
max_tmp_tables = func.get_item(mysql_variables, 'max_tmp_tables')
max_heap_table_size = func.get_item(mysql_variables,
'max_heap_table_size')
max_allowed_packet = func.get_item(mysql_variables,
'max_allowed_packet')
############################# GET INNODB INFO ##################################################
#innodb variables
innodb_version = func.get_item(mysql_variables, 'innodb_version')
innodb_buffer_pool_instances = func.get_item(
mysql_variables, 'innodb_buffer_pool_instances')
innodb_buffer_pool_size = func.get_item(mysql_variables,
'innodb_buffer_pool_size')
innodb_doublewrite = func.get_item(mysql_variables,
'innodb_doublewrite')
innodb_file_per_table = func.get_item(mysql_variables,
'innodb_file_per_table')
innodb_flush_log_at_trx_commit = func.get_item(
mysql_variables, 'innodb_flush_log_at_trx_commit')
innodb_flush_method = func.get_item(mysql_variables,
'innodb_flush_method')
innodb_force_recovery = func.get_item(mysql_variables,
'innodb_force_recovery')
innodb_io_capacity = func.get_item(mysql_variables,
'innodb_io_capacity')
innodb_read_io_threads = func.get_item(mysql_variables,
'innodb_read_io_threads')
innodb_write_io_threads = func.get_item(mysql_variables,
'innodb_write_io_threads')
#innodb status
innodb_buffer_pool_pages_total = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_total'))
innodb_buffer_pool_pages_data = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_data'))
innodb_buffer_pool_pages_dirty = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_dirty'))
innodb_buffer_pool_pages_flushed = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_flushed'))
innodb_buffer_pool_pages_free = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_free'))
innodb_buffer_pool_pages_misc = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_misc'))
innodb_page_size = int(func.get_item(mysql_status, 'Innodb_page_size'))
innodb_pages_created = int(
func.get_item(mysql_status, 'Innodb_pages_created'))
innodb_pages_read = int(
func.get_item(mysql_status, 'Innodb_pages_read'))
innodb_pages_written = int(
func.get_item(mysql_status, 'Innodb_pages_written'))
innodb_row_lock_current_waits = int(
func.get_item(mysql_status, 'Innodb_row_lock_current_waits'))
#innodb persecond info
innodb_buffer_pool_read_requests_persecond = int(
func.get_item(
mysql_status_2, 'Innodb_buffer_pool_read_requests')) - int(
func.get_item(mysql_status,
'Innodb_buffer_pool_read_requests'))
innodb_buffer_pool_reads_persecond = int(
func.get_item(mysql_status_2, 'Innodb_buffer_pool_reads')) - int(
func.get_item(mysql_status, 'Innodb_buffer_pool_reads'))
innodb_buffer_pool_write_requests_persecond = int(
func.get_item(
mysql_status_2, 'Innodb_buffer_pool_write_requests')) - int(
func.get_item(mysql_status,
'Innodb_buffer_pool_write_requests'))
innodb_buffer_pool_pages_flushed_persecond = int(
func.get_item(
mysql_status_2, 'Innodb_buffer_pool_pages_flushed')) - int(
func.get_item(mysql_status,
'Innodb_buffer_pool_pages_flushed'))
innodb_rows_deleted_persecond = int(
func.get_item(mysql_status_2, 'Innodb_rows_deleted')) - int(
func.get_item(mysql_status, 'Innodb_rows_deleted'))
innodb_rows_inserted_persecond = int(
func.get_item(mysql_status_2, 'Innodb_rows_inserted')) - int(
func.get_item(mysql_status, 'Innodb_rows_inserted'))
innodb_rows_read_persecond = int(
func.get_item(mysql_status_2, 'Innodb_rows_read')) - int(
func.get_item(mysql_status, 'Innodb_rows_read'))
innodb_rows_updated_persecond = int(
func.get_item(mysql_status_2, 'Innodb_rows_updated')) - int(
func.get_item(mysql_status, 'Innodb_rows_updated'))
############################# GET STATUS ##################################################
connect = 1
uptime = func.get_item(mysql_status, 'Uptime')
open_files = func.get_item(mysql_status, 'Open_files')
open_tables = func.get_item(mysql_status, 'Open_tables')
threads_connected = func.get_item(mysql_status, 'Threads_connected')
threads_running = func.get_item(mysql_status, 'Threads_running')
threads_created = func.get_item(mysql_status, 'Threads_created')
threads_cached = func.get_item(mysql_status, 'Threads_cached')
threads_waits = mysql.get_waits(conn)
connections = func.get_item(mysql_status, 'Connections')
aborted_clients = func.get_item(mysql_status, 'Aborted_clients')
aborted_connects = func.get_item(mysql_status, 'Aborted_connects')
key_blocks_not_flushed = func.get_item(mysql_status,
'Key_blocks_not_flushed')
key_blocks_unused = func.get_item(mysql_status, 'Key_blocks_unused')
key_blocks_used = func.get_item(mysql_status, 'Key_blocks_used')
############################# GET STATUS PERSECOND ##################################################
connections_persecond = int(
func.get_item(mysql_status_2, 'Connections')) - int(
func.get_item(mysql_status, 'Connections'))
bytes_received_persecond = (
int(func.get_item(mysql_status_2, 'Bytes_received')) -
int(func.get_item(mysql_status, 'Bytes_received'))) / 1024
bytes_sent_persecond = (
int(func.get_item(mysql_status_2, 'Bytes_sent')) -
int(func.get_item(mysql_status, 'Bytes_sent'))) / 1024
com_select_persecond = int(func.get_item(
mysql_status_2, 'Com_select')) - int(
func.get_item(mysql_status, 'Com_select'))
com_insert_persecond = int(func.get_item(
mysql_status_2, 'Com_insert')) - int(
func.get_item(mysql_status, 'Com_insert'))
com_update_persecond = int(func.get_item(
mysql_status_2, 'Com_update')) - int(
func.get_item(mysql_status, 'Com_update'))
com_delete_persecond = int(func.get_item(
mysql_status_2, 'Com_delete')) - int(
func.get_item(mysql_status, 'Com_delete'))
com_commit_persecond = int(func.get_item(
mysql_status_2, 'Com_commit')) - int(
func.get_item(mysql_status, 'Com_commit'))
com_rollback_persecond = int(
func.get_item(mysql_status_2, 'Com_rollback')) - int(
func.get_item(mysql_status, 'Com_rollback'))
questions_persecond = int(func.get_item(
mysql_status_2, 'Questions')) - int(
func.get_item(mysql_status, 'Questions'))
queries_persecond = int(func.get_item(
mysql_status_2, 'Queries')) - int(
func.get_item(mysql_status, 'Queries'))
transaction_persecond = (
int(func.get_item(mysql_status_2, 'Com_commit')) +
int(func.get_item(mysql_status_2, 'Com_rollback'))) - (
int(func.get_item(mysql_status, 'Com_commit')) +
int(func.get_item(mysql_status, 'Com_rollback')))
created_tmp_disk_tables_persecond = int(
func.get_item(mysql_status_2, 'Created_tmp_disk_tables')) - int(
func.get_item(mysql_status, 'Created_tmp_disk_tables'))
created_tmp_files_persecond = int(
func.get_item(mysql_status_2, 'Created_tmp_files')) - int(
func.get_item(mysql_status, 'Created_tmp_files'))
created_tmp_tables_persecond = int(
func.get_item(mysql_status_2, 'Created_tmp_tables')) - int(
func.get_item(mysql_status, 'Created_tmp_tables'))
table_locks_immediate_persecond = int(
func.get_item(mysql_status_2, 'Table_locks_immediate')) - int(
func.get_item(mysql_status, 'Table_locks_immediate'))
table_locks_waited_persecond = int(
func.get_item(mysql_status_2, 'Table_locks_waited')) - int(
func.get_item(mysql_status, 'Table_locks_waited'))
key_read_requests_persecond = int(
func.get_item(mysql_status_2, 'Key_read_requests')) - int(
func.get_item(mysql_status, 'Key_read_requests'))
key_reads_persecond = int(func.get_item(
mysql_status_2, 'Key_reads')) - int(
func.get_item(mysql_status, 'Key_reads'))
key_write_requests_persecond = int(
func.get_item(mysql_status_2, 'Key_write_requests')) - int(
func.get_item(mysql_status, 'Key_write_requests'))
key_writes_persecond = int(func.get_item(
mysql_status_2, 'Key_writes')) - int(
func.get_item(mysql_status, 'Key_writes'))
############################# GET MYSQL HITRATE ##################################################
if (string.atof(func.get_item(mysql_status, 'Qcache_hits')) +
string.atof(func.get_item(mysql_status, 'Com_select'))) <> 0:
query_cache_hitrate = string.atof(
func.get_item(mysql_status, 'Qcache_hits')) / (
string.atof(func.get_item(mysql_status, 'Qcache_hits')) +
string.atof(func.get_item(mysql_status, 'Com_select')))
query_cache_hitrate = "%9.2f" % query_cache_hitrate
else:
query_cache_hitrate = 0
if string.atof(func.get_item(mysql_status, 'Connections')) <> 0:
thread_cache_hitrate = 1 - string.atof(
func.get_item(mysql_status, 'Threads_created')) / string.atof(
func.get_item(mysql_status, 'Connections'))
thread_cache_hitrate = "%9.2f" % thread_cache_hitrate
else:
thread_cache_hitrate = 0
if string.atof(func.get_item(mysql_status, 'Key_read_requests')) <> 0:
key_buffer_read_rate = 1 - string.atof(
func.get_item(mysql_status, 'Key_reads')) / string.atof(
func.get_item(mysql_status, 'Key_read_requests'))
key_buffer_read_rate = "%9.2f" % key_buffer_read_rate
else:
key_buffer_read_rate = 0
if string.atof(func.get_item(mysql_status, 'Key_write_requests')) <> 0:
key_buffer_write_rate = 1 - string.atof(
func.get_item(mysql_status, 'Key_writes')) / string.atof(
func.get_item(mysql_status, 'Key_write_requests'))
key_buffer_write_rate = "%9.2f" % key_buffer_write_rate
else:
key_buffer_write_rate = 0
if (string.atof(func.get_item(mysql_status, 'Key_blocks_used')) +
string.atof(func.get_item(mysql_status,
'Key_blocks_unused'))) <> 0:
key_blocks_used_rate = string.atof(
func.get_item(mysql_status, 'Key_blocks_used')
) / (string.atof(func.get_item(mysql_status, 'Key_blocks_used')) +
string.atof(func.get_item(mysql_status, 'Key_blocks_unused')))
key_blocks_used_rate = "%9.2f" % key_blocks_used_rate
else:
key_blocks_used_rate = 0
if (string.atof(func.get_item(
mysql_status, 'Created_tmp_disk_tables')) + string.atof(
func.get_item(mysql_status, 'Created_tmp_tables'))) <> 0:
created_tmp_disk_tables_rate = string.atof(
func.get_item(mysql_status, 'Created_tmp_disk_tables')) / (
string.atof(
func.get_item(mysql_status, 'Created_tmp_disk_tables'))
+ string.atof(
func.get_item(mysql_status, 'Created_tmp_tables')))
created_tmp_disk_tables_rate = "%9.2f" % created_tmp_disk_tables_rate
else:
created_tmp_disk_tables_rate = 0
if string.atof(max_connections) <> 0:
connections_usage_rate = string.atof(
threads_connected) / string.atof(max_connections)
connections_usage_rate = "%9.2f" % connections_usage_rate
else:
connections_usage_rate = 0
if string.atof(open_files_limit) <> 0:
open_files_usage_rate = string.atof(open_files) / string.atof(
open_files_limit)
open_files_usage_rate = "%9.2f" % open_files_usage_rate
else:
open_files_usage_rate = 0
if string.atof(table_open_cache) <> 0:
open_tables_usage_rate = string.atof(open_tables) / string.atof(
table_open_cache)
open_tables_usage_rate = "%9.2f" % open_tables_usage_rate
else:
open_tables_usage_rate = 0
#repl
slave_status = cur.execute('show slave status;')
if slave_status <> 0:
role = 'slave'
role_new = 's'
else:
role = 'master'
role_new = 'm'
############################# INSERT INTO SERVER ##################################################
sql = "insert into mysql_status(server_id,host,port,tags,connect,role,uptime,version,max_connections,max_connect_errors,open_files_limit,table_open_cache,max_tmp_tables,max_heap_table_size,max_allowed_packet,open_files,open_tables,threads_connected,threads_running,threads_waits,threads_created,threads_cached,connections,aborted_clients,aborted_connects,connections_persecond,bytes_received_persecond,bytes_sent_persecond,com_select_persecond,com_insert_persecond,com_update_persecond,com_delete_persecond,com_commit_persecond,com_rollback_persecond,questions_persecond,queries_persecond,transaction_persecond,created_tmp_tables_persecond,created_tmp_disk_tables_persecond,created_tmp_files_persecond,table_locks_immediate_persecond,table_locks_waited_persecond,key_buffer_size,sort_buffer_size,join_buffer_size,key_blocks_not_flushed,key_blocks_unused,key_blocks_used,key_read_requests_persecond,key_reads_persecond,key_write_requests_persecond,key_writes_persecond,innodb_version,innodb_buffer_pool_instances,innodb_buffer_pool_size,innodb_doublewrite,innodb_file_per_table,innodb_flush_log_at_trx_commit,innodb_flush_method,innodb_force_recovery,innodb_io_capacity,innodb_read_io_threads,innodb_write_io_threads,innodb_buffer_pool_pages_total,innodb_buffer_pool_pages_data,innodb_buffer_pool_pages_dirty,innodb_buffer_pool_pages_flushed,innodb_buffer_pool_pages_free,innodb_buffer_pool_pages_misc,innodb_page_size,innodb_pages_created,innodb_pages_read,innodb_pages_written,innodb_row_lock_current_waits,innodb_buffer_pool_pages_flushed_persecond,innodb_buffer_pool_read_requests_persecond,innodb_buffer_pool_reads_persecond,innodb_buffer_pool_write_requests_persecond,innodb_rows_read_persecond,innodb_rows_inserted_persecond,innodb_rows_updated_persecond,innodb_rows_deleted_persecond,query_cache_hitrate,thread_cache_hitrate,key_buffer_read_rate,key_buffer_write_rate,key_blocks_used_rate,created_tmp_disk_tables_rate,connections_usage_rate,open_files_usage_rate,open_tables_usage_rate) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param = (
server_id, host, port, tags, connect, role, uptime, version,
max_connections, max_connect_errors, open_files_limit,
table_open_cache, max_tmp_tables, max_heap_table_size,
max_allowed_packet, open_files, open_tables, threads_connected,
threads_running, threads_waits, threads_created, threads_cached,
connections, aborted_clients, aborted_connects,
connections_persecond, bytes_received_persecond,
bytes_sent_persecond, com_select_persecond, com_insert_persecond,
com_update_persecond, com_delete_persecond, com_commit_persecond,
com_rollback_persecond, questions_persecond, queries_persecond,
transaction_persecond, created_tmp_tables_persecond,
created_tmp_disk_tables_persecond, created_tmp_files_persecond,
table_locks_immediate_persecond, table_locks_waited_persecond,
key_buffer_size, sort_buffer_size, join_buffer_size,
key_blocks_not_flushed, key_blocks_unused, key_blocks_used,
key_read_requests_persecond, key_reads_persecond,
key_write_requests_persecond, key_writes_persecond, innodb_version,
innodb_buffer_pool_instances, innodb_buffer_pool_size,
innodb_doublewrite, innodb_file_per_table,
innodb_flush_log_at_trx_commit, innodb_flush_method,
innodb_force_recovery, innodb_io_capacity, innodb_read_io_threads,
innodb_write_io_threads, innodb_buffer_pool_pages_total,
innodb_buffer_pool_pages_data, innodb_buffer_pool_pages_dirty,
innodb_buffer_pool_pages_flushed, innodb_buffer_pool_pages_free,
innodb_buffer_pool_pages_misc, innodb_page_size,
innodb_pages_created, innodb_pages_read, innodb_pages_written,
innodb_row_lock_current_waits,
innodb_buffer_pool_pages_flushed_persecond,
innodb_buffer_pool_read_requests_persecond,
innodb_buffer_pool_reads_persecond,
innodb_buffer_pool_write_requests_persecond,
innodb_rows_read_persecond, innodb_rows_inserted_persecond,
innodb_rows_updated_persecond, innodb_rows_deleted_persecond,
query_cache_hitrate, thread_cache_hitrate, key_buffer_read_rate,
key_buffer_write_rate, key_blocks_used_rate,
created_tmp_disk_tables_rate, connections_usage_rate,
open_files_usage_rate, open_tables_usage_rate)
func.mysql_exec(sql, param)
func.update_db_status_init(role_new, version, host, port, tags)
#check mysql process
processlist = cur.execute(
"select * from information_schema.processlist where DB !='information_schema' and command !='Sleep';"
)
if processlist:
for line in cur.fetchall():
sql = "insert into mysql_processlist(server_id,host,port,tags,pid,p_user,p_host,p_db,command,time,status,info) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param = (server_id, host, port, tags, line[0], line[1],
line[2], line[3], line[4], line[5], line[6], line[7])
func.mysql_exec(sql, param)
#check mysql connected
connected = cur.execute(
"select SUBSTRING_INDEX(host,':',1) as connect_server, user connect_user,db connect_db, count(SUBSTRING_INDEX(host,':',1)) as connect_count from information_schema.processlist where db is not null and db!='information_schema' and db !='performance_schema' group by connect_server ;"
)
if connected:
for line in cur.fetchall():
sql = "insert into mysql_connected(server_id,host,port,tags,connect_server,connect_user,connect_db,connect_count) values(%s,%s,%s,%s,%s,%s,%s,%s);"
param = (server_id, host, port, tags, line[0], line[1],
line[2], line[3])
func.mysql_exec(sql, param)
#check mysql replication
master_thread = cur.execute(
"select * from information_schema.processlist where COMMAND = 'Binlog Dump' or COMMAND = 'Binlog Dump GTID';"
)
slave_status = cur.execute('show slave status;')
datalist = []
if master_thread >= 1:
datalist.append(int(1))
if slave_status <> 0:
datalist.append(int(1))
else:
datalist.append(int(0))
else:
datalist.append(int(0))
if slave_status <> 0:
datalist.append(int(1))
else:
datalist.append(int(0))
if slave_status <> 0:
gtid_mode = cur.execute(
"select * from information_schema.global_variables where variable_name='gtid_mode';"
)
result = cur.fetchone()
if result:
gtid_mode = result[1]
else:
gtid_mode = 'OFF'
datalist.append(gtid_mode)
read_only = cur.execute(
"select * from information_schema.global_variables where variable_name='read_only';"
)
result = cur.fetchone()
datalist.append(result[1])
slave_info = cur.execute('show slave status;')
result = cur.fetchone()
master_server = result[1]
master_port = result[3]
slave_io_run = result[10]
slave_sql_run = result[11]
delay = result[32]
current_binlog_file = result[9]
current_binlog_pos = result[21]
master_binlog_file = result[5]
master_binlog_pos = result[6]
datalist.append(master_server)
datalist.append(master_port)
datalist.append(slave_io_run)
datalist.append(slave_sql_run)
datalist.append(delay)
datalist.append(current_binlog_file)
datalist.append(current_binlog_pos)
datalist.append(master_binlog_file)
datalist.append(master_binlog_pos)
datalist.append(0)
elif master_thread >= 1:
gtid_mode = cur.execute(
"select * from information_schema.global_variables where variable_name='gtid_mode';"
)
result = cur.fetchone()
if result:
gtid_mode = result[1]
else:
gtid_mode = 'OFF'
datalist.append(gtid_mode)
read_only = cur.execute(
"select * from information_schema.global_variables where variable_name='read_only';"
)
result = cur.fetchone()
datalist.append(result[1])
datalist.append('---')
datalist.append('---')
datalist.append('---')
datalist.append('---')
datalist.append('---')
datalist.append('---')
datalist.append('---')
master = cur.execute('show master status;')
master_result = cur.fetchone()
datalist.append(master_result[0])
datalist.append(master_result[1])
binlog_file = cur.execute('show master logs;')
binlogs = 0
if binlog_file:
for row in cur.fetchall():
binlogs = binlogs + row[1]
datalist.append(binlogs)
else:
datalist = []
result = datalist
if result:
sql = "insert into mysql_replication(server_id,tags,host,port,is_master,is_slave,gtid_mode,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,master_binlog_space) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param = (server_id, tags, host, port, result[0], result[1],
result[2], result[3], result[4], result[5], result[6],
result[7], result[8], result[9], result[10], result[11],
result[12], result[13])
func.mysql_exec(sql, param)
cur.close()
exit
except MySQLdb.Error, e:
logger_msg = "check mysql %s:%s failure: %d %s" % (
host, port, e.args[0], e.args[1])
logger.warning(logger_msg)
logger_msg = "check mysql %s:%s failure: sleep 3 seconds and check again." % (
host, port)
logger.warning(logger_msg)
time.sleep(3)
try:
conn = MySQLdb.connect(host=host,
user=username,
passwd=password,
port=int(port),
connect_timeout=3,
charset='utf8')
cur = conn.cursor()
conn.select_db('information_schema')
except MySQLdb.Error, e:
logger_msg = "check mysql second %s:%s failure: %d %s" % (
host, port, e.args[0], e.args[1])
logger.warning(logger_msg)
connect = 0
sql = "insert into mysql_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)"
param = (server_id, host, port, tags, connect)
func.mysql_exec(sql, param)
def fetch_citizen(name,
url=DEFAULT_RSI_URL,
endpoint='/citizens',
skip_orgs=False):
result = {}
url = url.rstrip('/')
citizen_url = "{}/{}/{}".format(url.rstrip('/'), endpoint.strip('/'), name)
orgapiurl = '{}/{}'.format(url.rstrip('/'), 'api/orgs/getOrgMembers')
page = _requests.get(citizen_url, headers=None)
print(page)
if page.status_code == 404:
print(f'Received a 404 Error Code from query to {citizen_url}.')
if page.status_code == 200:
soup = _bs(page.text, features='lxml')
_ = [_.text for _ in soup.select(".info .value")[:3]]
result['username'] = get_item(_, 0, '')
result['handle'] = get_item(_, 1, '')
result['title'] = get_item(_, 2, '')
result['title_icon'] = get_item(soup.select(".info .icon img"), 0, '')
if result['title_icon']:
result['title_icon'] = '{}/{}'.format(url,
result['title_icon']['src'])
result['avatar'] = "{}/{}".format(
url,
soup.select('.profile .thumb img')[0]['src'].lstrip('/'))
result['url'] = citizen_url
if soup.select('.profile-content .bio'):
result['bio'] = soup.select('.profile-content .bio')[0].text.strip(
'\nBio').strip()
else:
result['bio'] = ''
result['citizen_record'] = soup.select(
'.citizen-record .value')[0].text
try:
result['citizen_record'] = int(result['citizen_record'][1:])
except:
print(
'Encountered unexpceted citizen_record. Making citizen_record 1000000000.'
)
result['citizen_record'] = 1000000000
pass
_ = {
_.select_one('span').text:
_re.sub(r'\s+', ' ',
_.select_one('.value').text.strip()).replace(' ,', ',')
for _ in soup.select('.profile-content > .left-col .entry')
}
result['enlisted'] = get_item(_, 'Enlisted', '')
result['location'] = get_item(_, 'Location', '')
result['languages'] = get_item(_, 'Fluency', '')
result['languages'] = result['languages'].replace(',', '').split()
if not skip_orgs:
orgs_page = _requests.get("{}/organizations".format(citizen_url))
if orgs_page.status_code == 200:
orgsoup = _bs(orgs_page.text, features='lxml')
result['orgs'] = []
for org in orgsoup.select('.orgs-content .org'):
orgname, sid, rank = [
_.text for _ in org.select('.info .entry .value')
]
if orgname[0] == '\xa0':
orgname = sid = rank = 'REDACTED'
roles = []
r = _requests.post(orgapiurl,
data={
'symbol': sid,
'search': name
})
if r.status_code == 200:
r = r.json()
if r['success'] == 1:
apisoup = _bs(r['data']['html'], features='lxml')
roles = [
_.text
for _ in apisoup.select('.rolelist .role')
]
orgdata = {
'name': orgname,
'sid': sid,
'rank': rank,
'roles': roles,
}
try:
orgdata['icon'] = '{}/{}'.format(
url,
org.select('.thumb img')[0]['src'].lstrip('/'))
except IndexError:
pass
result['orgs'].append(orgdata)
return result
def check_mysql(host,port,username,password,server_id,tags):
try:
conn=MySQLdb.connect(host=host,user=username,passwd=password,port=int(port),connect_timeout=3,charset='utf8')
cur=conn.cursor()
conn.select_db('information_schema')
#cur.execute('flush hosts;')
############################# CHECK MYSQL ####################################################
mysql_variables = func.get_mysql_variables(cur)
mysql_status = func.get_mysql_status(cur)
time.sleep(1)
mysql_status_2 = func.get_mysql_status(cur)
############################# GET VARIABLES ###################################################
version = func.get_item(mysql_variables,'version')
key_buffer_size = func.get_item(mysql_variables,'key_buffer_size')
sort_buffer_size = func.get_item(mysql_variables,'sort_buffer_size')
join_buffer_size = func.get_item(mysql_variables,'join_buffer_size')
max_connections = func.get_item(mysql_variables,'max_connections')
max_connect_errors = func.get_item(mysql_variables,'max_connect_errors')
open_files_limit = func.get_item(mysql_variables,'open_files_limit')
table_open_cache = func.get_item(mysql_variables,'table_open_cache')
max_tmp_tables = func.get_item(mysql_variables,'max_tmp_tables')
max_heap_table_size = func.get_item(mysql_variables,'max_heap_table_size')
max_allowed_packet = func.get_item(mysql_variables,'max_allowed_packet')
############################# GET INNODB INFO ##################################################
#innodb variables
innodb_version = func.get_item(mysql_variables,'innodb_version')
innodb_buffer_pool_instances = func.get_item(mysql_variables,'innodb_buffer_pool_instances')
innodb_buffer_pool_size = func.get_item(mysql_variables,'innodb_buffer_pool_size')
innodb_doublewrite = func.get_item(mysql_variables,'innodb_doublewrite')
innodb_file_per_table = func.get_item(mysql_variables,'innodb_file_per_table')
innodb_flush_log_at_trx_commit = func.get_item(mysql_variables,'innodb_flush_log_at_trx_commit')
innodb_flush_method = func.get_item(mysql_variables,'innodb_flush_method')
innodb_force_recovery = func.get_item(mysql_variables,'innodb_force_recovery')
innodb_io_capacity = func.get_item(mysql_variables,'innodb_io_capacity')
innodb_read_io_threads = func.get_item(mysql_variables,'innodb_read_io_threads')
innodb_write_io_threads = func.get_item(mysql_variables,'innodb_write_io_threads')
#innodb status
innodb_buffer_pool_pages_total = int(func.get_item(mysql_status,'Innodb_buffer_pool_pages_total'))
innodb_buffer_pool_pages_data = int(func.get_item(mysql_status,'Innodb_buffer_pool_pages_data'))
innodb_buffer_pool_pages_dirty = int(func.get_item(mysql_status,'Innodb_buffer_pool_pages_dirty'))
innodb_buffer_pool_pages_flushed = int(func.get_item(mysql_status,'Innodb_buffer_pool_pages_flushed'))
innodb_buffer_pool_pages_free = int(func.get_item(mysql_status,'Innodb_buffer_pool_pages_free'))
innodb_buffer_pool_pages_misc = int(func.get_item(mysql_status,'Innodb_buffer_pool_pages_misc'))
innodb_page_size = int(func.get_item(mysql_status,'Innodb_page_size'))
innodb_pages_created = int(func.get_item(mysql_status,'Innodb_pages_created'))
innodb_pages_read = int(func.get_item(mysql_status,'Innodb_pages_read'))
innodb_pages_written = int(func.get_item(mysql_status,'Innodb_pages_written'))
innodb_row_lock_current_waits = int(func.get_item(mysql_status,'Innodb_row_lock_current_waits'))
#innodb persecond info
innodb_buffer_pool_read_requests_persecond = int(func.get_item(mysql_status_2,'Innodb_buffer_pool_read_requests')) - int(func.get_item(mysql_status,'Innodb_buffer_pool_read_requests'))
innodb_buffer_pool_reads_persecond = int(func.get_item(mysql_status_2,'Innodb_buffer_pool_reads')) - int(func.get_item(mysql_status,'Innodb_buffer_pool_reads'))
innodb_buffer_pool_write_requests_persecond = int(func.get_item(mysql_status_2,'Innodb_buffer_pool_write_requests')) - int(func.get_item(mysql_status,'Innodb_buffer_pool_write_requests'))
innodb_buffer_pool_pages_flushed_persecond = int(func.get_item(mysql_status_2,'Innodb_buffer_pool_pages_flushed')) - int(func.get_item(mysql_status,'Innodb_buffer_pool_pages_flushed'))
innodb_rows_deleted_persecond = int(func.get_item(mysql_status_2,'Innodb_rows_deleted')) - int(func.get_item(mysql_status,'Innodb_rows_deleted'))
innodb_rows_inserted_persecond = int(func.get_item(mysql_status_2,'Innodb_rows_inserted')) - int(func.get_item(mysql_status,'Innodb_rows_inserted'))
innodb_rows_read_persecond = int(func.get_item(mysql_status_2,'Innodb_rows_read')) - int(func.get_item(mysql_status,'Innodb_rows_read'))
innodb_rows_updated_persecond = int(func.get_item(mysql_status_2,'Innodb_rows_updated')) - int(func.get_item(mysql_status,'Innodb_rows_updated'))
############################# GET STATUS ##################################################
connect = 1
uptime = func.get_item(mysql_status,'Uptime')
open_files = func.get_item(mysql_status,'Open_files')
open_tables = func.get_item(mysql_status,'Open_tables')
threads_connected = func.get_item(mysql_status,'Threads_connected')
threads_running = func.get_item(mysql_status,'Threads_running')
threads_created = func.get_item(mysql_status,'Threads_created')
threads_cached = func.get_item(mysql_status,'Threads_cached')
threads_waits = mysql.get_waits(conn)
connections = func.get_item(mysql_status,'Connections')
aborted_clients = func.get_item(mysql_status,'Aborted_clients')
aborted_connects = func.get_item(mysql_status,'Aborted_connects')
key_blocks_not_flushed = func.get_item(mysql_status,'Key_blocks_not_flushed')
key_blocks_unused = func.get_item(mysql_status,'Key_blocks_unused')
key_blocks_used = func.get_item(mysql_status,'Key_blocks_used')
############################# GET STATUS PERSECOND ##################################################
connections_persecond = int(func.get_item(mysql_status_2,'Connections')) - int(func.get_item(mysql_status,'Connections'))
bytes_received_persecond = (int(func.get_item(mysql_status_2,'Bytes_received')) - int(func.get_item(mysql_status,'Bytes_received')))/1024
bytes_sent_persecond = (int(func.get_item(mysql_status_2,'Bytes_sent')) - int(func.get_item(mysql_status,'Bytes_sent')))/1024
com_select_persecond = int(func.get_item(mysql_status_2,'Com_select')) - int(func.get_item(mysql_status,'Com_select'))
com_insert_persecond = int(func.get_item(mysql_status_2,'Com_insert')) - int(func.get_item(mysql_status,'Com_insert'))
com_update_persecond = int(func.get_item(mysql_status_2,'Com_update')) - int(func.get_item(mysql_status,'Com_update'))
com_delete_persecond = int(func.get_item(mysql_status_2,'Com_delete')) - int(func.get_item(mysql_status,'Com_delete'))
com_commit_persecond = int(func.get_item(mysql_status_2,'Com_commit')) - int(func.get_item(mysql_status,'Com_commit'))
com_rollback_persecond = int(func.get_item(mysql_status_2,'Com_rollback')) - int(func.get_item(mysql_status,'Com_rollback'))
questions_persecond = int(func.get_item(mysql_status_2,'Questions')) - int(func.get_item(mysql_status,'Questions'))
queries_persecond = int(func.get_item(mysql_status_2,'Queries')) - int(func.get_item(mysql_status,'Queries'))
transaction_persecond = (int(func.get_item(mysql_status_2,'Com_commit')) + int(func.get_item(mysql_status_2,'Com_rollback'))) - (int(func.get_item(mysql_status,'Com_commit')) + int(func.get_item(mysql_status,'Com_rollback')))
created_tmp_disk_tables_persecond = int(func.get_item(mysql_status_2,'Created_tmp_disk_tables')) - int(func.get_item(mysql_status,'Created_tmp_disk_tables'))
created_tmp_files_persecond = int(func.get_item(mysql_status_2,'Created_tmp_files')) - int(func.get_item(mysql_status,'Created_tmp_files'))
created_tmp_tables_persecond = int(func.get_item(mysql_status_2,'Created_tmp_tables')) - int(func.get_item(mysql_status,'Created_tmp_tables'))
table_locks_immediate_persecond = int(func.get_item(mysql_status_2,'Table_locks_immediate')) - int(func.get_item(mysql_status,'Table_locks_immediate'))
table_locks_waited_persecond = int(func.get_item(mysql_status_2,'Table_locks_waited')) - int(func.get_item(mysql_status,'Table_locks_waited'))
key_read_requests_persecond = int(func.get_item(mysql_status_2,'Key_read_requests')) - int(func.get_item(mysql_status,'Key_read_requests'))
key_reads_persecond = int(func.get_item(mysql_status_2,'Key_reads')) - int(func.get_item(mysql_status,'Key_reads'))
key_write_requests_persecond = int(func.get_item(mysql_status_2,'Key_write_requests')) - int(func.get_item(mysql_status,'Key_write_requests'))
key_writes_persecond = int(func.get_item(mysql_status_2,'Key_writes')) - int(func.get_item(mysql_status,'Key_writes'))
############################# GET MYSQL HITRATE ##################################################
if (string.atof(func.get_item(mysql_status,'Qcache_hits')) + string.atof(func.get_item(mysql_status,'Com_select'))) <> 0:
query_cache_hitrate = string.atof(func.get_item(mysql_status,'Qcache_hits')) / (string.atof(func.get_item(mysql_status,'Qcache_hits')) + string.atof(func.get_item(mysql_status,'Com_select')))
query_cache_hitrate = "%9.2f" %query_cache_hitrate
else:
query_cache_hitrate = 0
if string.atof(func.get_item(mysql_status,'Connections')) <> 0:
thread_cache_hitrate = 1 - string.atof(func.get_item(mysql_status,'Threads_created')) / string.atof(func.get_item(mysql_status,'Connections'))
thread_cache_hitrate = "%9.2f" %thread_cache_hitrate
else:
thread_cache_hitrate = 0
if string.atof(func.get_item(mysql_status,'Key_read_requests')) <> 0:
key_buffer_read_rate = 1 - string.atof(func.get_item(mysql_status,'Key_reads')) / string.atof(func.get_item(mysql_status,'Key_read_requests'))
key_buffer_read_rate = "%9.2f" %key_buffer_read_rate
else:
key_buffer_read_rate = 0
if string.atof(func.get_item(mysql_status,'Key_write_requests')) <> 0:
key_buffer_write_rate = 1 - string.atof(func.get_item(mysql_status,'Key_writes')) / string.atof(func.get_item(mysql_status,'Key_write_requests'))
key_buffer_write_rate = "%9.2f" %key_buffer_write_rate
else:
key_buffer_write_rate = 0
if (string.atof(func.get_item(mysql_status,'Key_blocks_used'))+string.atof(func.get_item(mysql_status,'Key_blocks_unused'))) <> 0:
key_blocks_used_rate = string.atof(func.get_item(mysql_status,'Key_blocks_used')) / (string.atof(func.get_item(mysql_status,'Key_blocks_used'))+string.atof(func.get_item(mysql_status,'Key_blocks_unused')))
key_blocks_used_rate = "%9.2f" %key_blocks_used_rate
else:
key_blocks_used_rate = 0
if (string.atof(func.get_item(mysql_status,'Created_tmp_disk_tables'))+string.atof(func.get_item(mysql_status,'Created_tmp_tables'))) <> 0:
created_tmp_disk_tables_rate = string.atof(func.get_item(mysql_status,'Created_tmp_disk_tables')) / (string.atof(func.get_item(mysql_status,'Created_tmp_disk_tables'))+string.atof(func.get_item(mysql_status,'Created_tmp_tables')))
created_tmp_disk_tables_rate = "%9.2f" %created_tmp_disk_tables_rate
else:
created_tmp_disk_tables_rate = 0
if string.atof(max_connections) <> 0:
connections_usage_rate = string.atof(threads_connected)/string.atof(max_connections)
connections_usage_rate = "%9.2f" %connections_usage_rate
else:
connections_usage_rate = 0
if string.atof(open_files_limit) <> 0:
open_files_usage_rate = string.atof(open_files)/string.atof(open_files_limit)
open_files_usage_rate = "%9.2f" %open_files_usage_rate
else:
open_files_usage_rate = 0
if string.atof(table_open_cache) <> 0:
open_tables_usage_rate = string.atof(open_tables)/string.atof(table_open_cache)
open_tables_usage_rate = "%9.2f" %open_tables_usage_rate
else:
open_tables_usage_rate = 0
#repl
slave_status=cur.execute('show slave status;')
if slave_status <> 0:
role='slave'
role_new='s'
else:
role='master'
role_new='m'
############################# INSERT INTO SERVER ##################################################
sql = "insert into mysql_status(server_id,host,port,tags,connect,role,uptime,version,max_connections,max_connect_errors,open_files_limit,table_open_cache,max_tmp_tables,max_heap_table_size,max_allowed_packet,open_files,open_tables,threads_connected,threads_running,threads_waits,threads_created,threads_cached,connections,aborted_clients,aborted_connects,connections_persecond,bytes_received_persecond,bytes_sent_persecond,com_select_persecond,com_insert_persecond,com_update_persecond,com_delete_persecond,com_commit_persecond,com_rollback_persecond,questions_persecond,queries_persecond,transaction_persecond,created_tmp_tables_persecond,created_tmp_disk_tables_persecond,created_tmp_files_persecond,table_locks_immediate_persecond,table_locks_waited_persecond,key_buffer_size,sort_buffer_size,join_buffer_size,key_blocks_not_flushed,key_blocks_unused,key_blocks_used,key_read_requests_persecond,key_reads_persecond,key_write_requests_persecond,key_writes_persecond,innodb_version,innodb_buffer_pool_instances,innodb_buffer_pool_size,innodb_doublewrite,innodb_file_per_table,innodb_flush_log_at_trx_commit,innodb_flush_method,innodb_force_recovery,innodb_io_capacity,innodb_read_io_threads,innodb_write_io_threads,innodb_buffer_pool_pages_total,innodb_buffer_pool_pages_data,innodb_buffer_pool_pages_dirty,innodb_buffer_pool_pages_flushed,innodb_buffer_pool_pages_free,innodb_buffer_pool_pages_misc,innodb_page_size,innodb_pages_created,innodb_pages_read,innodb_pages_written,innodb_row_lock_current_waits,innodb_buffer_pool_pages_flushed_persecond,innodb_buffer_pool_read_requests_persecond,innodb_buffer_pool_reads_persecond,innodb_buffer_pool_write_requests_persecond,innodb_rows_read_persecond,innodb_rows_inserted_persecond,innodb_rows_updated_persecond,innodb_rows_deleted_persecond,query_cache_hitrate,thread_cache_hitrate,key_buffer_read_rate,key_buffer_write_rate,key_blocks_used_rate,created_tmp_disk_tables_rate,connections_usage_rate,open_files_usage_rate,open_tables_usage_rate) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param = (server_id,host,port,tags,connect,role,uptime,version,max_connections,max_connect_errors,open_files_limit,table_open_cache,max_tmp_tables,max_heap_table_size,max_allowed_packet,open_files,open_tables,threads_connected,threads_running,threads_waits,threads_created,threads_cached,connections,aborted_clients,aborted_connects,connections_persecond,bytes_received_persecond,bytes_sent_persecond,com_select_persecond,com_insert_persecond,com_update_persecond,com_delete_persecond,com_commit_persecond,com_rollback_persecond,questions_persecond,queries_persecond,transaction_persecond,created_tmp_tables_persecond,created_tmp_disk_tables_persecond,created_tmp_files_persecond,table_locks_immediate_persecond,table_locks_waited_persecond,key_buffer_size,sort_buffer_size,join_buffer_size,key_blocks_not_flushed,key_blocks_unused,key_blocks_used,key_read_requests_persecond,key_reads_persecond,key_write_requests_persecond,key_writes_persecond,innodb_version,innodb_buffer_pool_instances,innodb_buffer_pool_size,innodb_doublewrite,innodb_file_per_table,innodb_flush_log_at_trx_commit,innodb_flush_method,innodb_force_recovery,innodb_io_capacity,innodb_read_io_threads,innodb_write_io_threads,innodb_buffer_pool_pages_total,innodb_buffer_pool_pages_data,innodb_buffer_pool_pages_dirty,innodb_buffer_pool_pages_flushed,innodb_buffer_pool_pages_free,innodb_buffer_pool_pages_misc,innodb_page_size,innodb_pages_created,innodb_pages_read,innodb_pages_written,innodb_row_lock_current_waits,innodb_buffer_pool_pages_flushed_persecond,innodb_buffer_pool_read_requests_persecond,innodb_buffer_pool_reads_persecond,innodb_buffer_pool_write_requests_persecond,innodb_rows_read_persecond,innodb_rows_inserted_persecond,innodb_rows_updated_persecond,innodb_rows_deleted_persecond,query_cache_hitrate,thread_cache_hitrate,key_buffer_read_rate,key_buffer_write_rate,key_blocks_used_rate,created_tmp_disk_tables_rate,connections_usage_rate,open_files_usage_rate,open_tables_usage_rate)
func.mysql_exec(sql,param)
func.update_db_status_init(role_new,version,host,port,tags)
#check mysql process
processlist=cur.execute("select * from information_schema.processlist where DB !='information_schema' and command !='Sleep';")
if processlist:
for line in cur.fetchall():
sql="insert into mysql_processlist(server_id,host,port,tags,pid,p_user,p_host,p_db,command,time,status,info) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param=(server_id,host,port,tags,line[0],line[1],line[2],line[3],line[4],line[5],line[6],line[7])
func.mysql_exec(sql,param)
#check mysql connected
connected=cur.execute("select SUBSTRING_INDEX(host,':',1) as connect_server, user connect_user,db connect_db, count(SUBSTRING_INDEX(host,':',1)) as connect_count from information_schema.processlist where db is not null and db!='information_schema' and db !='performance_schema' group by connect_server ;");
if connected:
for line in cur.fetchall():
sql="insert into mysql_connected(server_id,host,port,tags,connect_server,connect_user,connect_db,connect_count) values(%s,%s,%s,%s,%s,%s,%s,%s);"
param =(server_id,host,port,tags,line[0],line[1],line[2],line[3])
func.mysql_exec(sql,param)
#check mysql replication
master_thread=cur.execute("select * from information_schema.processlist where COMMAND = 'Binlog Dump' or COMMAND = 'Binlog Dump GTID';")
slave_status=cur.execute('show slave status;')
datalist=[]
if master_thread >= 1:
datalist.append(int(1))
if slave_status <> 0:
datalist.append(int(1))
else:
datalist.append(int(0))
else:
datalist.append(int(0))
if slave_status <> 0:
datalist.append(int(1))
else:
datalist.append(int(0))
if slave_status <> 0:
gtid_mode=cur.execute("select * from information_schema.global_variables where variable_name='gtid_mode';")
result=cur.fetchone()
if result:
gtid_mode=result[1]
else:
gtid_mode='OFF'
datalist.append(gtid_mode)
read_only=cur.execute("select * from information_schema.global_variables where variable_name='read_only';")
result=cur.fetchone()
datalist.append(result[1])
slave_info=cur.execute('show slave status;')
result=cur.fetchone()
master_server=result[1]
master_port=result[3]
slave_io_run=result[10]
slave_sql_run=result[11]
delay=result[32]
current_binlog_file=result[9]
current_binlog_pos=result[21]
master_binlog_file=result[5]
master_binlog_pos=result[6]
datalist.append(master_server)
datalist.append(master_port)
datalist.append(slave_io_run)
datalist.append(slave_sql_run)
datalist.append(delay)
datalist.append(current_binlog_file)
datalist.append(current_binlog_pos)
datalist.append(master_binlog_file)
datalist.append(master_binlog_pos)
datalist.append(0)
elif master_thread >= 1:
gtid_mode=cur.execute("select * from information_schema.global_variables where variable_name='gtid_mode';")
result=cur.fetchone()
if result:
gtid_mode=result[1]
else:
gtid_mode='OFF'
datalist.append(gtid_mode)
read_only=cur.execute("select * from information_schema.global_variables where variable_name='read_only';")
result=cur.fetchone()
datalist.append(result[1])
datalist.append('---')
datalist.append('---')
datalist.append('---')
datalist.append('---')
datalist.append('---')
datalist.append('---')
datalist.append('---')
master=cur.execute('show master status;')
master_result=cur.fetchone()
datalist.append(master_result[0])
datalist.append(master_result[1])
binlog_file=cur.execute('show master logs;')
binlogs=0
if binlog_file:
for row in cur.fetchall():
binlogs = binlogs + row[1]
datalist.append(binlogs)
else:
datalist=[]
result=datalist
if result:
sql="insert into mysql_replication(server_id,tags,host,port,is_master,is_slave,gtid_mode,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,master_binlog_space) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param=(server_id,tags,host,port,result[0],result[1],result[2],result[3],result[4],result[5],result[6],result[7],result[8],result[9],result[10],result[11],result[12],result[13])
func.mysql_exec(sql,param)
cur.close()
exit
except MySQLdb.Error,e:
logger_msg="check mysql %s:%s failure: %d %s" %(host,port,e.args[0],e.args[1])
logger.warning(logger_msg)
logger_msg="check mysql %s:%s failure: sleep 3 seconds and check again." %(host,port)
logger.warning(logger_msg)
time.sleep(3)
try:
conn=MySQLdb.connect(host=host,user=username,passwd=password,port=int(port),connect_timeout=3,charset='utf8')
cur=conn.cursor()
conn.select_db('information_schema')
except MySQLdb.Error,e:
logger_msg="check mysql second %s:%s failure: %d %s" %(host,port,e.args[0],e.args[1])
logger.warning(logger_msg)
connect = 0
sql="insert into mysql_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)"
param=(server_id,host,port,tags,connect)
func.mysql_exec(sql,param)
# Get data from Mercado Libre page
import requests
import pandas as pd
# Import my Access Token
from config import access_token
# Import my functions
from functions import get_item, get_all_items_from_seller, get_questions_about_item, get_total_visits, get_visits_per_item, get_brand_info, get_feedback_on_item
# Get one item looking URL in Browser
r = requests.get("https://api.mercadolibre.com/items/MLA817802367#json")
item_chocolate = r.json()
item = get_item('MLA817802367')
# Define parameters
seller_id = item['seller_id']
user_id = seller_id
item_id = item['id']
# Get all the items from seller
all_items = get_all_items_from_seller(seller_id)
# Get Questions
questions_item = get_questions_about_item(item_id, access_token)
# Quantity of visitas
total_visits = get_total_visits(user_id,
date_from=item['start_time'],
date_to='2019-12-02T15:45:47.000Z')
def check_replication(group_id, pri_id, sta_id, is_switch):
p_id = ""
s_id = ""
p_conn = ""
s_conn = ""
if is_switch == 0:
p_id = pri_id
s_id = sta_id
else:
p_id = sta_id
s_id = pri_id
try:
p_conn = get_connect(p_id)
s_conn = get_connect(s_id)
func.mysql_exec("begin;", '')
func.mysql_exec(
"insert into mysql_dr_p_his SELECT *,DATE_FORMAT(sysdate(),'%%Y%%m%%d%%H%%i%%s') from mysql_dr_p where server_id in (%s, %s);"
% (pri_id, sta_id), '')
func.mysql_exec(
"delete from mysql_dr_p where server_id in (%s, %s);" %
(pri_id, sta_id), '')
func.mysql_exec(
"insert into mysql_dr_s_his SELECT *,DATE_FORMAT(sysdate(),'%%Y%%m%%d%%H%%i%%s') from mysql_dr_s where server_id in (%s, %s);"
% (pri_id, sta_id), '')
func.mysql_exec(
"delete from mysql_dr_s where server_id in (%s, %s);" %
(pri_id, sta_id), '')
logger.info("Generate replication primary info for server: %s begin:" %
(p_id))
if p_conn:
p_cur = p_conn.cursor()
# check role
slave_status = p_cur.execute('show slave status;')
if slave_status <> 0:
role = 'slave'
logger.warn(
"The primary server: %s configured in replication group is NOT match the role!"
% (p_id))
else:
role = 'master'
mysql_variables = func.get_mysql_variables(p_cur)
gtid_mode = func.get_item(mysql_variables, 'gtid_mode')
read_only = func.get_item(mysql_variables, 'read_only')
log_bin = func.get_item(mysql_variables, 'log_bin')
master = p_cur.execute('show master status;')
master_result = p_cur.fetchone()
binlog_file = '---'
binlog_pos = '---'
if master_result:
binlog_file = master_result[0]
binlog_pos = master_result[1]
binlogs = 0
if log_bin == 'ON':
binlog_file = p_cur.execute('show master logs;')
if binlog_file:
for row in p_cur.fetchall():
binlogs = binlogs + row[1]
else:
binlogs = 0
sql = "insert into mysql_dr_p(server_id,gtid_mode,read_only,master_binlog_file,master_binlog_pos,master_binlog_space) values(%s,%s,%s,%s,%s,%s)"
param = (p_id, gtid_mode, read_only, binlog_file, binlog_pos,
binlogs)
func.mysql_exec(sql, param)
logger.info("Generate replication primary info for server: %s end:" %
(p_id))
logger.info("Generate replication standby info for server: %s begin:" %
(s_id))
if s_conn:
s_cur = s_conn.cursor()
# check role
slave_status = s_cur.execute('show slave status;')
if slave_status <> 0:
role = 'slave'
mysql_variables = func.get_mysql_variables(s_cur)
gtid_mode = func.get_item(mysql_variables, 'gtid_mode')
read_only = func.get_item(mysql_variables, 'read_only')
slave_info = s_cur.execute('show slave status;')
result = s_cur.fetchone()
if result:
master_server = result[1]
master_port = result[3]
slave_io_run = result[10]
slave_sql_run = result[11]
delay = result[32]
current_binlog_file = result[9]
current_binlog_pos = result[21]
master_binlog_file = result[5]
master_binlog_pos = result[6]
sql = "insert into mysql_dr_s(server_id,gtid_mode,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,master_binlog_space) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param = (s_id, gtid_mode, read_only, master_server,
master_port, slave_io_run, slave_sql_run, delay,
current_binlog_file, current_binlog_pos,
master_binlog_file, master_binlog_pos, 0)
func.mysql_exec(sql, param)
logger.info(
"Generate replication standby info for server: %s end:"
% (s_id))
else:
role = 'master'
logger.warn(
"The standby server: %s configured in replication group is NOT match the role!"
% (s_id))
# generate mysql replication alert
alert.gen_alert_mysql_replcation(s_id)
logger.info("Generate replication standby info for server: %s end:" %
(s_id))
func.mysql_exec("commit;", '')
except Exception, e:
logger.error(e)
func.mysql_exec("rollback;", '')
logger.info(
"Generate mysql instance information for server: %s port: %s begin:"
% (host, port))
cur = conn.cursor()
conn.select_db('information_schema')
#cur.execute('flush hosts;')
############################# CHECK MYSQL ####################################################
mysql_variables = func.get_mysql_variables(cur)
mysql_status = func.get_mysql_status(cur)
time.sleep(1)
mysql_status_2 = func.get_mysql_status(cur)
############################# GET VARIABLES ###################################################
version = func.get_item(mysql_variables, 'version')
key_buffer_size = func.get_item(mysql_variables, 'key_buffer_size')
sort_buffer_size = func.get_item(mysql_variables, 'sort_buffer_size')
join_buffer_size = func.get_item(mysql_variables, 'join_buffer_size')
max_connections = func.get_item(mysql_variables, 'max_connections')
max_connect_errors = func.get_item(mysql_variables,
'max_connect_errors')
open_files_limit = func.get_item(mysql_variables, 'open_files_limit')
table_open_cache = func.get_item(mysql_variables, 'table_open_cache')
max_tmp_tables = func.get_item(mysql_variables, 'max_tmp_tables')
max_heap_table_size = func.get_item(mysql_variables,
'max_heap_table_size')
max_allowed_packet = func.get_item(mysql_variables,
'max_allowed_packet')
log_bin = func.get_item(mysql_variables, 'log_bin')
def check_mysql(host, port, username, password, server_id, tags):
logger_msg = "[BBQ]begin check mysql %s:%s " % (host, port)
logger.info(logger_msg)
try:
conn = MySQLdb.connect(host=host,
user=username,
passwd=password,
port=int(port),
connect_timeout=3,
charset='utf8')
cur = conn.cursor()
conn.select_db('information_schema')
#cur.execute('flush hosts;')
saveMysqlStatus = {}
saveMysqlStatus['host'] = host
saveMysqlStatus['port'] = port
saveMysqlStatus['server_id'] = server_id
saveMysqlStatus['tags'] = tags
saveMysqlStatus['connect'] = 1
############################# CHECK MYSQL ####################################################
mysql_variables = func.get_mysql_variables(cur)
mysql_status = func.get_mysql_status(cur)
logger_msg = "[BBQ]get mysql %s:%s status1 " % (host, port)
logger.info(logger_msg)
time.sleep(1)
mysql_status_2 = func.get_mysql_status(cur)
logger_msg = "[BBQ]get mysql %s:%s status2 " % (host, port)
logger.info(logger_msg)
############################# GET VARIABLES ###################################################
version = func.get_item(mysql_variables, 'version')
saveMysqlStatus['version'] = version
saveMysqlStatus['innodb_stats_on_metadata'] = func.get_item(
mysql_variables, 'innodb_stats_on_metadata')
saveMysqlStatus['sync_binlog'] = func.get_item(mysql_variables,
'sync_binlog')
saveMysqlStatus['key_buffer_size'] = func.get_item(
mysql_variables, 'key_buffer_size')
saveMysqlStatus['sort_buffer_size'] = func.get_item(
mysql_variables, 'sort_buffer_size')
saveMysqlStatus['join_buffer_size'] = func.get_item(
mysql_variables, 'join_buffer_size')
saveMysqlStatus['max_connections'] = func.get_item(
mysql_variables, 'max_connections')
saveMysqlStatus['max_connect_errors'] = func.get_item(
mysql_variables, 'max_connect_errors')
saveMysqlStatus['open_files_limit'] = func.get_item(
mysql_variables, 'open_files_limit')
saveMysqlStatus['table_open_cache'] = func.get_item(
mysql_variables, 'table_open_cache')
saveMysqlStatus['max_tmp_tables'] = func.get_item(
mysql_variables, 'max_tmp_tables')
saveMysqlStatus['max_heap_table_size'] = func.get_item(
mysql_variables, 'max_heap_table_size')
saveMysqlStatus['max_allowed_packet'] = func.get_item(
mysql_variables, 'max_allowed_packet')
############################# GET INNODB INFO ##################################################
#innodb variables
saveMysqlStatus['innodb_version'] = func.get_item(
mysql_variables, 'innodb_version')
saveMysqlStatus['innodb_buffer_pool_instances'] = func.get_item(
mysql_variables, 'innodb_buffer_pool_instances')
saveMysqlStatus['innodb_buffer_pool_size'] = func.get_item(
mysql_variables, 'innodb_buffer_pool_size')
saveMysqlStatus['innodb_doublewrite'] = func.get_item(
mysql_variables, 'innodb_doublewrite')
saveMysqlStatus['innodb_file_per_table'] = func.get_item(
mysql_variables, 'innodb_file_per_table')
saveMysqlStatus['innodb_flush_log_at_trx_commit'] = func.get_item(
mysql_variables, 'innodb_flush_log_at_trx_commit')
saveMysqlStatus['innodb_flush_method'] = func.get_item(
mysql_variables, 'innodb_flush_method')
saveMysqlStatus['innodb_force_recovery'] = func.get_item(
mysql_variables, 'innodb_force_recovery')
saveMysqlStatus['innodb_io_capacity'] = func.get_item(
mysql_variables, 'innodb_io_capacity')
saveMysqlStatus['innodb_read_io_threads'] = func.get_item(
mysql_variables, 'innodb_read_io_threads')
saveMysqlStatus['innodb_write_io_threads'] = func.get_item(
mysql_variables, 'innodb_write_io_threads')
#innodb status
saveMysqlStatus['innodb_buffer_pool_pages_total'] = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_total'))
saveMysqlStatus['innodb_buffer_pool_pages_data'] = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_data'))
saveMysqlStatus['innodb_buffer_pool_pages_dirty'] = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_dirty'))
saveMysqlStatus['innodb_buffer_pool_pages_flushed'] = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_flushed'))
saveMysqlStatus['innodb_buffer_pool_pages_free'] = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_free'))
saveMysqlStatus['innodb_buffer_pool_pages_misc'] = int(
func.get_item(mysql_status, 'Innodb_buffer_pool_pages_misc'))
saveMysqlStatus['innodb_page_size'] = int(
func.get_item(mysql_status, 'Innodb_page_size'))
saveMysqlStatus['innodb_pages_created'] = int(
func.get_item(mysql_status, 'Innodb_pages_created'))
saveMysqlStatus['innodb_pages_read'] = int(
func.get_item(mysql_status, 'Innodb_pages_read'))
saveMysqlStatus['innodb_pages_written'] = int(
func.get_item(mysql_status, 'Innodb_pages_written'))
saveMysqlStatus['innodb_row_lock_current_waits'] = int(
func.get_item(mysql_status, 'Innodb_row_lock_current_waits'))
#innodb persecond info
saveMysqlStatus['innodb_buffer_pool_read_requests_persecond'] = int(
func.get_item(
mysql_status_2, 'Innodb_buffer_pool_read_requests')) - int(
func.get_item(mysql_status,
'Innodb_buffer_pool_read_requests'))
saveMysqlStatus['innodb_buffer_pool_reads_persecond'] = int(
func.get_item(mysql_status_2, 'Innodb_buffer_pool_reads')) - int(
func.get_item(mysql_status, 'Innodb_buffer_pool_reads'))
saveMysqlStatus['innodb_buffer_pool_write_requests_persecond'] = int(
func.get_item(
mysql_status_2, 'Innodb_buffer_pool_write_requests')) - int(
func.get_item(mysql_status,
'Innodb_buffer_pool_write_requests'))
saveMysqlStatus['innodb_buffer_pool_pages_flushed_persecond'] = int(
func.get_item(
mysql_status_2, 'Innodb_buffer_pool_pages_flushed')) - int(
func.get_item(mysql_status,
'Innodb_buffer_pool_pages_flushed'))
saveMysqlStatus['innodb_rows_deleted_persecond'] = int(
func.get_item(mysql_status_2, 'Innodb_rows_deleted')) - int(
func.get_item(mysql_status, 'Innodb_rows_deleted'))
saveMysqlStatus['innodb_rows_inserted_persecond'] = int(
func.get_item(mysql_status_2, 'Innodb_rows_inserted')) - int(
func.get_item(mysql_status, 'Innodb_rows_inserted'))
saveMysqlStatus['innodb_rows_read_persecond'] = int(
func.get_item(mysql_status_2, 'Innodb_rows_read')) - int(
func.get_item(mysql_status, 'Innodb_rows_read'))
saveMysqlStatus['innodb_rows_updated_persecond'] = int(
func.get_item(mysql_status_2, 'Innodb_rows_updated')) - int(
func.get_item(mysql_status, 'Innodb_rows_updated'))
############################# GET STATUS ##################################################
saveMysqlStatus['uptime'] = func.get_item(mysql_status, 'Uptime')
saveMysqlStatus['open_files'] = func.get_item(mysql_status,
'Open_files')
saveMysqlStatus['open_tables'] = func.get_item(mysql_status,
'Open_tables')
saveMysqlStatus['threads_connected'] = func.get_item(
mysql_status, 'Threads_connected')
saveMysqlStatus['threads_running'] = func.get_item(
mysql_status, 'Threads_running')
#saveMysqlStatus['threads_created'] = func.get_item(mysql_status,'Threads_created')
saveMysqlStatus['threads_created'] = int(
func.get_item(mysql_status_2, 'Threads_created')) - int(
func.get_item(mysql_status, 'Threads_created'))
saveMysqlStatus['threads_cached'] = func.get_item(
mysql_status, 'Threads_cached')
saveMysqlStatus['threads_waits'] = mysql.get_waits(conn)
saveMysqlStatus['connections'] = func.get_item(mysql_status,
'Connections')
saveMysqlStatus['aborted_clients'] = func.get_item(
mysql_status, 'Aborted_clients')
saveMysqlStatus['aborted_connects'] = func.get_item(
mysql_status, 'Aborted_connects')
saveMysqlStatus['key_blocks_not_flushed'] = func.get_item(
mysql_status, 'Key_blocks_not_flushed')
saveMysqlStatus['key_blocks_unused'] = func.get_item(
mysql_status, 'Key_blocks_unused')
saveMysqlStatus['key_blocks_used'] = func.get_item(
mysql_status, 'Key_blocks_used')
############################# GET STATUS PERSECOND ##################################################
saveMysqlStatus['connections_persecond'] = int(
func.get_item(mysql_status_2, 'Connections')) - int(
func.get_item(mysql_status, 'Connections'))
saveMysqlStatus['bytes_received_persecond'] = (
int(func.get_item(mysql_status_2, 'Bytes_received')) -
int(func.get_item(mysql_status, 'Bytes_received'))) / 1024
saveMysqlStatus['bytes_sent_persecond'] = (
int(func.get_item(mysql_status_2, 'Bytes_sent')) -
int(func.get_item(mysql_status, 'Bytes_sent'))) / 1024
saveMysqlStatus['com_select_persecond'] = int(
func.get_item(mysql_status_2, 'Com_select')) - int(
func.get_item(mysql_status, 'Com_select'))
saveMysqlStatus['com_insert_persecond'] = int(
func.get_item(mysql_status_2, 'Com_insert')) - int(
func.get_item(mysql_status, 'Com_insert'))
saveMysqlStatus['com_update_persecond'] = int(
func.get_item(mysql_status_2, 'Com_update')) - int(
func.get_item(mysql_status, 'Com_update'))
saveMysqlStatus['com_delete_persecond'] = int(
func.get_item(mysql_status_2, 'Com_delete')) - int(
func.get_item(mysql_status, 'Com_delete'))
saveMysqlStatus['com_commit_persecond'] = int(
func.get_item(mysql_status_2, 'Com_commit')) - int(
func.get_item(mysql_status, 'Com_commit'))
saveMysqlStatus['com_rollback_persecond'] = int(
func.get_item(mysql_status_2, 'Com_rollback')) - int(
func.get_item(mysql_status, 'Com_rollback'))
saveMysqlStatus['questions_persecond'] = int(
func.get_item(mysql_status_2, 'Questions')) - int(
func.get_item(mysql_status, 'Questions'))
saveMysqlStatus['queries_persecond'] = int(
func.get_item(mysql_status_2, 'Queries')) - int(
func.get_item(mysql_status, 'Queries'))
saveMysqlStatus['transaction_persecond'] = (
int(func.get_item(mysql_status_2, 'Com_commit')) +
int(func.get_item(mysql_status_2, 'Com_rollback'))) - (
int(func.get_item(mysql_status, 'Com_commit')) +
int(func.get_item(mysql_status, 'Com_rollback')))
saveMysqlStatus['created_tmp_disk_tables_persecond'] = int(
func.get_item(mysql_status_2, 'Created_tmp_disk_tables')) - int(
func.get_item(mysql_status, 'Created_tmp_disk_tables'))
saveMysqlStatus['created_tmp_files_persecond'] = int(
func.get_item(mysql_status_2, 'Created_tmp_files')) - int(
func.get_item(mysql_status, 'Created_tmp_files'))
saveMysqlStatus['created_tmp_tables_persecond'] = int(
func.get_item(mysql_status_2, 'Created_tmp_tables')) - int(
func.get_item(mysql_status, 'Created_tmp_tables'))
saveMysqlStatus['table_locks_immediate_persecond'] = int(
func.get_item(mysql_status_2, 'Table_locks_immediate')) - int(
func.get_item(mysql_status, 'Table_locks_immediate'))
saveMysqlStatus['table_locks_waited_persecond'] = int(
func.get_item(mysql_status_2, 'Table_locks_waited')) - int(
func.get_item(mysql_status, 'Table_locks_waited'))
saveMysqlStatus['key_read_requests_persecond'] = int(
func.get_item(mysql_status_2, 'Key_read_requests')) - int(
func.get_item(mysql_status, 'Key_read_requests'))
saveMysqlStatus['key_reads_persecond'] = int(
func.get_item(mysql_status_2, 'Key_reads')) - int(
func.get_item(mysql_status, 'Key_reads'))
saveMysqlStatus['key_write_requests_persecond'] = int(
func.get_item(mysql_status_2, 'Key_write_requests')) - int(
func.get_item(mysql_status, 'Key_write_requests'))
saveMysqlStatus['key_writes_persecond'] = int(
func.get_item(mysql_status_2, 'Key_writes')) - int(
func.get_item(mysql_status, 'Key_writes'))
############################# GET MYSQL HITRATE ##################################################
if (string.atof(func.get_item(mysql_status, 'Qcache_hits')) +
string.atof(func.get_item(mysql_status, 'Com_select'))) <> 0:
query_cache_hitrate = string.atof(
func.get_item(mysql_status, 'Qcache_hits')) / (
string.atof(func.get_item(mysql_status, 'Qcache_hits')) +
string.atof(func.get_item(mysql_status, 'Com_select')))
query_cache_hitrate = "%9.2f" % query_cache_hitrate
else:
query_cache_hitrate = 0
saveMysqlStatus['query_cache_hitrate'] = query_cache_hitrate
if string.atof(func.get_item(mysql_status, 'Connections')) <> 0:
thread_cache_hitrate = 1 - string.atof(
func.get_item(mysql_status, 'Threads_created')) / string.atof(
func.get_item(mysql_status, 'Connections'))
thread_cache_hitrate = "%9.2f" % thread_cache_hitrate
else:
thread_cache_hitrate = 0
saveMysqlStatus['thread_cache_hitrate'] = thread_cache_hitrate
if string.atof(func.get_item(mysql_status, 'Key_read_requests')) <> 0:
key_buffer_read_rate = 1 - string.atof(
func.get_item(mysql_status, 'Key_reads')) / string.atof(
func.get_item(mysql_status, 'Key_read_requests'))
key_buffer_read_rate = "%9.2f" % key_buffer_read_rate
else:
key_buffer_read_rate = 0
saveMysqlStatus['key_buffer_read_rate'] = key_buffer_read_rate
if string.atof(func.get_item(mysql_status, 'Key_write_requests')) <> 0:
key_buffer_write_rate = 1 - string.atof(
func.get_item(mysql_status, 'Key_writes')) / string.atof(
func.get_item(mysql_status, 'Key_write_requests'))
key_buffer_write_rate = "%9.2f" % key_buffer_write_rate
else:
key_buffer_write_rate = 0
saveMysqlStatus['key_buffer_write_rate'] = key_buffer_write_rate
if (string.atof(func.get_item(mysql_status, 'Key_blocks_used')) +
string.atof(func.get_item(mysql_status,
'Key_blocks_unused'))) <> 0:
key_blocks_used_rate = string.atof(
func.get_item(mysql_status, 'Key_blocks_used')
) / (string.atof(func.get_item(mysql_status, 'Key_blocks_used')) +
string.atof(func.get_item(mysql_status, 'Key_blocks_unused')))
key_blocks_used_rate = "%9.2f" % key_blocks_used_rate
else:
key_blocks_used_rate = 0
saveMysqlStatus['key_blocks_used_rate'] = key_blocks_used_rate
if (string.atof(func.get_item(
mysql_status, 'Created_tmp_disk_tables')) + string.atof(
func.get_item(mysql_status, 'Created_tmp_tables'))) <> 0:
created_tmp_disk_tables_rate = string.atof(
func.get_item(mysql_status, 'Created_tmp_disk_tables')) / (
string.atof(
func.get_item(mysql_status, 'Created_tmp_disk_tables'))
+ string.atof(
func.get_item(mysql_status, 'Created_tmp_tables')))
created_tmp_disk_tables_rate = "%9.2f" % created_tmp_disk_tables_rate
else:
created_tmp_disk_tables_rate = 0
saveMysqlStatus[
'created_tmp_disk_tables_rate'] = created_tmp_disk_tables_rate
max_connections = saveMysqlStatus.get('max_connections')
threads_connected = saveMysqlStatus.get('threads_connected')
if string.atof(max_connections) <> 0:
connections_usage_rate = string.atof(
threads_connected) / string.atof(max_connections)
connections_usage_rate = "%9.2f" % connections_usage_rate
else:
connections_usage_rate = 0
saveMysqlStatus['connections_usage_rate'] = connections_usage_rate
open_files_limit = saveMysqlStatus.get('open_files_limit')
open_files = saveMysqlStatus.get('open_files')
if string.atof(open_files_limit) <> 0:
open_files_usage_rate = string.atof(open_files) / string.atof(
open_files_limit)
open_files_usage_rate = "%9.2f" % open_files_usage_rate
else:
open_files_usage_rate = 0
saveMysqlStatus['open_files_usage_rate'] = open_files_usage_rate
table_open_cache = saveMysqlStatus.get('table_open_cache')
open_tables = saveMysqlStatus.get('open_tables')
if string.atof(table_open_cache) <> 0:
open_tables_usage_rate = string.atof(open_tables) / string.atof(
table_open_cache)
open_tables_usage_rate = "%9.2f" % open_tables_usage_rate
else:
open_tables_usage_rate = 0
saveMysqlStatus['open_tables_usage_rate'] = open_tables_usage_rate
#repl
read_only = func.get_item(mysql_variables, 'read_only')
slave_hosts = cur.execute('show slave hosts;')
slave_status = cur.execute('show slave status;')
if (slave_status == 0) or (slave_status <> 0 and slave_hosts > 0 and
(not cmp(read_only, "OFF"))):
role = 'master'
role_new = 'm'
else:
role = 'slave'
role_new = 's'
saveMysqlStatus['role'] = role
############################# disk size ##################################################
disk_size_m = 0
saveMysqlStatus['disk_size_m'] = disk_size_m
############################# INSERT INTO SERVER ##################################################
updSqls = []
updSqls.append(
"replace into mysql_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status where host='%s' and port=%s"
% (host, port))
updSqls.append("delete from mysql_status where host='%s' and port=%s" %
(host, port))
cols = []
vals = []
for key, val in saveMysqlStatus.iteritems():
cols.append(key)
vals.append(str(val))
insSql = "insert into mysql_status(%s) VALUES ('%s')" % (
",".join(cols), "','".join(vals))
updSqls.append(insSql)
#logger.info(updSqls)
func.mysql_exec_many(updSqls)
logger_msg = "[BBQ]save mysql %s:%s status " % (host, port)
logger.info(logger_msg)
func.update_db_status_init(server_id, role_new, version, host, port,
tags)
# save other
func.other_save("mysql_status", saveMysqlStatus)
#check mysql process
processlist = cur.execute(
'select * from information_schema.processlist where DB !="information_schema" and command !="Sleep";'
)
if processlist:
for line in cur.fetchall():
sql = "insert into mysql_processlist(server_id,host,port,tags,pid,p_user,p_host,p_db,command,time,status,info) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param = (server_id, host, port, tags, line[0], line[1],
line[2], line[3], line[4], line[5], line[6], line[7])
func.mysql_exec(sql, param)
#check mysql connected
connected = cur.execute(
"select SUBSTRING_INDEX(host,':',1) as connect_server, user connect_user,db connect_db, count(SUBSTRING_INDEX(host,':',1)) as connect_count from information_schema.processlist where db is not null and db!='information_schema' and db !='performance_schema' group by connect_server, connect_user, connect_db ;"
)
if connected:
for line in cur.fetchall():
sql = "insert into mysql_connected(server_id,host,port,tags,connect_server,connect_user,connect_db,connect_count) values(%s,%s,%s,%s,%s,%s,%s,%s);"
param = (server_id, host, port, tags, line[0], line[1],
line[2], line[3])
func.mysql_exec(sql, param)
#check mysql replication
master_thread = cur.execute(
"select * from information_schema.processlist where COMMAND = 'Binlog Dump' or COMMAND = 'Binlog Dump GTID';"
)
slave_status = cur.execute('show slave status;')
datalist = []
if master_thread >= 1:
datalist.append(int(1))
if not cmp("slave", role):
datalist.append(int(1))
else:
datalist.append(int(0))
else:
datalist.append(int(0))
if not cmp("slave", role):
datalist.append(int(1))
else:
datalist.append(int(0))
gtid_mode = cur.execute(
"select * from information_schema.global_variables where variable_name='gtid_mode';"
)
result = cur.fetchone()
if result:
gtid_mode = result[1]
else:
gtid_mode = 'OFF'
datalist.append(gtid_mode)
read_only = cur.execute(
"show variables where variable_name like 'read_only';")
result = cur.fetchone()
datalist.append(result[1])
master_binlog_file = '---'
master_binlog_pos = '---'
master_binlog_space = 0
slave_info = cur.execute('show slave status;')
if slave_info == 0:
for i in range(0, 7):
datalist.append('-1')
else:
result = cur.fetchone()
master_server = result[1]
master_port = result[3]
master_binlog_file = result[5]
master_binlog_pos = result[6]
current_binlog_file = result[9]
slave_io_run = result[10]
slave_sql_run = result[11]
current_binlog_pos = result[21]
delay = result[32]
# delay use hearbeat
masterHosts = func.mysql_query(
"select host from db_servers_mysql where host='%s' or replicate_ip='%s' limit 1"
% (master_server, master_server))
if masterHosts != 0:
masterHost = masterHosts[0][0]
connMaster = MySQLdb.connect(host=masterHost,
user=username,
passwd=password,
port=int(master_port),
connect_timeout=3,
charset='utf8')
curMaster = connMaster.cursor()
connMaster.select_db('information_schema')
master_variables = func.get_mysql_variables(curMaster)
master_server_id = master_variables.get("server_id")
connMaster.close()
# query delay by master_server_id
qHb = "select UNIX_TIMESTAMP()-UNIX_TIMESTAMP(STR_TO_DATE(substring_index(ts,'.',1),'%%Y-%%m-%%dT%%H:%%i:%%s')) as d from dhdba.heartbeat where server_id='%s';" % (
master_server_id)
cur.execute(qHb)
lines = cur.fetchone()
if lines:
delay = int(lines[0])
if delay < 0:
delay = 0
datalist.append(master_server)
datalist.append(master_port)
datalist.append(slave_io_run)
datalist.append(slave_sql_run)
datalist.append(delay)
datalist.append(current_binlog_file)
datalist.append(current_binlog_pos)
master = cur.execute('show master status;')
if master != 0:
master_result = cur.fetchone()
master_binlog_file = master_result[0]
master_binlog_pos = master_result[1]
binlog_file = cur.execute('show master logs;')
if binlog_file:
for row in cur.fetchall():
master_binlog_space = master_binlog_space + int(row[1])
datalist.append(master_binlog_file)
datalist.append(master_binlog_pos)
datalist.append(master_binlog_space)
result = datalist
if result:
func.mysql_exec(
"replace into mysql_replication_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication where host='%s' and port=%s"
% (host, port), '')
func.mysql_exec(
"delete from mysql_replication where host='%s' and port=%s" %
(host, port), '')
sql = "insert into mysql_replication(server_id,tags,host,port,is_master,is_slave,gtid_mode,read_only,master_server,master_port,slave_io_run,slave_sql_run,delay,current_binlog_file,current_binlog_pos,master_binlog_file,master_binlog_pos,master_binlog_space) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param = (server_id, tags, host, port, result[0], result[1],
result[2], result[3], result[4], result[5], result[6],
result[7], result[8], result[9], result[10], result[11],
result[12], result[13])
func.mysql_exec(sql, param)
cur.close()
except MySQLdb.Error, e:
logger_msg = "check mysql %s:%s failure: %d %s" % (
host, port, e.args[0], e.args[1])
logger.warning(logger_msg)
logger_msg = "check mysql %s:%s failure: sleep 3 seconds and check again." % (
host, port)
logger.warning(logger_msg)
time.sleep(3)
try:
conn = MySQLdb.connect(host=host,
user=username,
passwd=password,
port=int(port),
connect_timeout=3,
charset='utf8')
cur = conn.cursor()
conn.select_db('information_schema')
except MySQLdb.Error, e:
logger_msg = "check mysql second %s:%s failure: %d %s" % (
host, port, e.args[0], e.args[1])
logger.warning(logger_msg)
connect = 0
sql = "replace into mysql_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)"
param = (server_id, host, port, tags, connect)
func.mysql_exec(sql, param)
print("Running driver.py")
# create table
import create_table
# insert items into the table
print("Inserting items into table")
time.sleep(5)
functions.insert("Harry Potter", "J.K. Rowling", "Fiction", "10", "4.1")
functions.insert("Cat in the Hat", "Dr. Seuss", "Fiction", "5", "4.6")
functions.insert("Wocket in my Pocket", "Dr. Seuss", "Fiction", "4", "3.7")
# return a specific item from the table
print("Getting Item from database:")
time.sleep(2)
print(functions.get_item("Harry Potter", "J.K. Rowling"))
print("")
# update an item in the table and return it
print("Updating previous item and returning it:")
time.sleep(2)
functions.update_item("Harry Potter", "J.K. Rowling", "NumInStock", "22")
print(functions.get_item("Harry Potter", "J.K. Rowling"))
print("")
# return items from a certain Author in a table
print("Getting all books by Dr. Seuss:")
time.sleep(2)
functions.query_by_author("Dr. Seuss")
print("")