Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Coleta de informações sobre possiveis deadlocks no mysql 5.6 minuto a minuto
import pymysql
import os
import json
import logging
from datetime import datetime
def get_connection(secret_dict):
port = int(secret_dict['port']) if 'port' in secret_dict else 3306
dbname = secret_dict['dbname'] if 'dbname' in secret_dict else None
# Try to obtain a connection to the db
try:
conn = pymysql.connect(secret_dict['host'], user=secret_dict['username'],
passwd=secret_dict['password'], port=port, db=dbname, connect_timeout=5)
logger.info("get_connection: Connection established")
return conn
except pymysql.OperationalError:
return None
def get_credentials():
secret_dict = {}
secret_dict['host'] = os.environ['MYSQL_HOST']
secret_dict['username'] = os.environ['MYSQL_USERNAME']
secret_dict['password'] = os.environ['MYSQL_PASSWORD']
secret_dict['port'] = os.environ['MYSQL_PORT']
return secret_dict
def get_querys():
querys = ["SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id"]
querys.append("select * from information_schema.processlist")
querys.append("SHOW ENGINE INNODB STATUS")
querys.append("show open tables")
querys.append("show global status")
return querys
def handler(event, context):
conn = get_connection(get_credentials())
with conn.cursor() as cur:
for query in get_querys():
logger.info("QUERY: "+query)
cur.execute(query)
for row in cur:
for i in row:
logger.info(i)
if __name__ == "__main__":
filename='/var/log/collect-information/collect_information_mysql_theads-{:%Y-%m-%d-%H-%M-%S}.log'.format(datetime.now())
logging.basicConfig(filename=filename, filemode='a', format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger()
logger.setLevel(logging.INFO)
#handler1 = logging.StreamHandler(sys.stdout)
#handler1.setLevel(logging.INFO)
formatter = logging.Formatter(
'%(asctime)s - %(name)s - %(levelname)s - %(message)s')
#handler1.setFormatter(formatter)
#logger.addHandler(handler1)
handler(None, None)
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
select * from information_schema.processlist;
SHOW ENGINE INNODB STATUS;
show open tables;
show global status;
* * * * * sh /srv/exec_collect_information_db.sh >> /var/log/collect-information/crontab.log 2>&1
export MYSQL_HOST=$HOST
export MYSQL_USERNAME=$USERNAME
export MYSQL_PASSWORD=$PASSWORD
export MYSQL_PORT=$PORT
python /srv/collect_information_db.py
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.