def _get_blocked_hosts(session, advices, details):
title = "Blocked Hosts"
stmt = """select host, ip, COUNT_HOST_BLOCKED_ERRORS, @@max_connect_errors
from performance_schema.host_cache where COUNT_HOST_BLOCKED_ERRORS>0;
"""
result = session.run_sql(stmt)
if result.has_data():
rows = result.fetch_all()
if len(rows) > 0:
output = util.output(title, "")
for row in rows:
output += util.output("{} ({})".format(row[0], row[1]), row[2],
1)
if advices:
output += util.print_red(
"You have blocked host(s), please use mysqladmin flush-hosti and maybe increase max_connect_errors ({})."
.format(row[3]))
if details:
stmt = """select HOST, IP, COUNT_HOST_BLOCKED_ERRORS, FIRST_SEEN, LAST_ERROR_SEEN
from performance_schema.host_cache where COUNT_HOST_BLOCKED_ERRORS>0
"""
stmt = """SELECT HOST, IP, COUNT_HOST_BLOCKED_ERRORS, concat("'", FIRST_SEEN, "'") FIRST_SEEN,
concat("'", LAST_SEEN, "'") LAST_SEEN
FROM performance_schema.host_cache where COUNT_HOST_BLOCKED_ERRORS>0"""
output2, nbrows = util.run_and_print("Blocked Hosts", stmt,
session)
output += output2
else:
output = util.output(title, "none")
return output
def get_tables_without_pk(session, advices=False, details=False):
tbl_no_pk = 0
title = "Tables without PK"
stmt = """SELECT concat(tables.table_schema, '/' , tables.table_name) as `Table Name`, tables.engine as `Engine`,
tables.table_rows as `Rows` FROM information_schema.tables LEFT JOIN (
SELECT table_schema , table_name
FROM information_schema.statistics
GROUP BY table_schema, table_name, index_name HAVING
SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks
ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name
WHERE puks.table_name is null
AND tables.table_type = 'BASE TABLE' AND Engine='InnoDB'"""
result = session.run_sql(stmt)
output = util.output(title, "")
for row in result.fetch_all():
tbl_no_pk += 1
output = util.output(title, tbl_no_pk)
if advices:
if tbl_no_pk > 0:
output += util.print_red(
"It's not recommended to have tables without Primary Key")
if details and tbl_no_pk > 0:
output2, nbrows = util.run_and_print("Details", stmt, session)
output += output2
return output
def get_engines(session, advices=False, details=False):
got_inno = False
other = False
title = "Engines Used"
stmt = """select count(*) as '# TABLES', CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(sum(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') INDEXES,
CONCAT(sum(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2)), 'G') 'TOTAL SIZE',
engine as ENGINE from information_schema.TABLES
where TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
and data_length group by engine;"""
result = session.run_sql(stmt)
output = util.output(title, "")
for row in result.fetch_all():
output += util.output(row[4],
"{} table(s) ({})".format(row[0], row[3]), 1)
if row[4] == "InnoDB":
got_inno = True
else:
other = True
if advices:
if not got_inno and other:
output += util.print_red("It's recommended to only use InnoDB")
if details:
output2, nbrows = util.run_and_print("Details".format(title), stmt,
session)
output += output2
return output
def get_routines(session, advices=False, details=False):
title = "User Defined Routines"
stmt = "select routine_schema, count(*) `amount` from information_schema.routines where definer not in ('mysql.sys@localhost') group by routine_schema"
result = session.run_sql(stmt)
output = util.output(title, "")
for row in result.fetch_all():
output += util.output(row[0], row[1], 1)
if advices and row[0] in "mysql":
output += util.print_red(
"Custom routines in {} schema are not supported in MDS".format(
row[0]))
if details:
stmt = """select routine_schema, routine_name, definer from information_schema.routines
where routine_schema = '{}' and definer not in ('mysql.sys@localhost')""".format(
row[0])
output2, nbrows = util.run_and_print(
"Routines in {}".format(row[0]), stmt, session)
output += output2
return output
def get_largest_innodb_tables(session, branch, details=False, limit=10):
if not details:
return ""
title = "Top {} largest InnoDB Tables".format(str(limit))
stmt = """SELECT NAME as `Table Name`, TABLE_ROWS as `Rows`, format_bytes(data_length) `Data Size`,
format_bytes(PAGE_SIZE) `Page Size`, SPACE_TYPE `Space Type`,
format_bytes(index_length) `Index Size`,
format_bytes(data_length+index_length) `Total Size`,
format_bytes(data_free) `Data Free`,
format_bytes(FILE_SIZE) `File Size`,
format_bytes((FILE_SIZE/10 - (data_length/10 +
index_length/10))*10) `Wasted Size`
FROM information_schema.TABLES as t
JOIN information_schema.INNODB_TABLESPACES as it
ON it.name = concat(table_schema,"/",table_name)
ORDER BY (data_length + index_length) desc limit %s""" % str(
limit)
if branch == "56":
stmt = """SELECT CONCAT(table_schema, '.', table_name) as `Table Name`, CONCAT(ROUND(table_rows / 1000000, 2), 'M') as `Rows`,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') `Data Size`,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') `Index Size`,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') `Total Size`
FROM information_schema.TABLES as t
ORDER BY (data_length + index_length) desc limit %s""" % str(
limit)
else:
if branch == "57":
stmt = """SELECT TABLE_NAME as `Table Name`, TABLE_ROWS as `Rows`, sys.format_bytes(data_length) `Data Size`,
sys.format_bytes(index_length) `Index Size`,
sys.format_bytes(data_length+index_length) `Total Size`,
sys.format_bytes(data_free) `Data Free`
FROM information_schema.TABLES as t
ORDER BY (data_length + index_length) desc limit %s""" % str(
limit)
output, nbrows = util.run_and_print(title, stmt, session)
return output
def get_configured_variables(session, details=False):
title = "Global Variables Configured"
stmt = """SELECT count(*), VARIABLE_SOURCE
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE != 'COMPILED' GROUP BY VARIABLE_SOURCE"""
result = session.run_sql(stmt)
output = util.output(title, "")
for row in result.fetch_all():
output += util.output(row[1], row[0], 1)
if details:
stmt = """SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE != 'COMPILED'"""
output2, nbrows = util.run_and_print("Details".format(title), stmt,
session)
output += output2
return output