def get_linux_disk_info(datadirs): #get the info related to datadir datadir = datadirs['@@datadir'] output = util.output("Datadir", datadir) output2, datadir_physical = _get_physical_info(datadir) output += output2 for key in datadirs: if key == "@@datadir": continue output += util.output(key[2:], datadirs[key]) if datadirs[key] == "./" or datadirs[ key] == datadir or not datadirs[key]: continue else: if datadirs[key].startswith("./"): path_to_check = datadir + datadirs[key][1:] else: path_to_check = datadirs[key] output2, physical_disk2 = _get_physical_info(path_to_check) if datadir_physical != physical_disk2: output += output2 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_innodb_log_details_57(session): title = "InnoDB Logs" stmt = """SELECT sys.format_bytes(Log_File_Size) Log_File_Size, sys.format_bytes(Log_File_Size * Nb_files) Tot_Log_Size, Nb_Files, Flush_at_trx_commit FROM ( SELECT variable_value as Log_File_Size FROM performance_schema.global_variables WHERE variable_name = 'innodb_log_file_size') A, ( SELECT variable_value as Flush_at_Trx_commit FROM performance_schema.global_variables WHERE variable_name = 'innodb_flush_log_at_trx_commit') B, ( SELECT variable_value as Nb_Files FROM performance_schema.global_variables WHERE variable_name = 'innodb_log_files_in_group') C """ result = session.run_sql(stmt) row = result.fetch_one() output = util.output(title, "") output += util.output("File Size", row[0], 1) output += util.output("Nb of Files", row[2], 1) output += util.output("Total Size", row[1], 1) output += util.output("Flush at Commit", row[3], 1) return output
def get_dataset(session, branch): stmt = """SELECT format_bytes(SUM(data_length)) Data, format_bytes(SUM(index_length)) Indexes, format_bytes(SUM(data_length)+sum(index_length)) 'Total Size' FROM information_schema.TABLES GROUP BY NULL""" if branch == "56": stmt = """SELECT CONCAT(ROUND(SUM(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') Data, CONCAT(ROUND(SUM(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') Indexes, CONCAT(ROUND(SUM(data_length+index_length)/( 1024 * 1024 * 1024 ), 2), 'G') 'Total Size' FROM information_schema.TABLES GROUP BY NULL""" else: if branch == "57": stmt = """SELECT sys.format_bytes(SUM(data_length)) Data, sys.format_bytes(SUM(index_length)) Indexes, sys.format_bytes(SUM(data_length)+sum(index_length)) 'Total Size' FROM information_schema.TABLES GROUP BY NULL""" result = session.run_sql(stmt) output = util.output("Dataset", "") while result.has_data(): object_res = result.fetch_one_object() if object_res: for key in object_res: output += util.output(key, object_res[key], 1) result.next_result() else: break return output
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_ahi_details(session, advices): title = "Adaptive Hash Index" stmt = """SELECT ROUND( ( SELECT Variable_value FROM sys.metrics WHERE Variable_name = 'adaptive_hash_searches' ) / ( ( SELECT Variable_value FROM sys.metrics WHERE Variable_name = 'adaptive_hash_searches_btree' ) + ( SELECT Variable_value FROM sys.metrics WHERE Variable_name = 'adaptive_hash_searches' ) ) * 100,2 ) 'AHIRatio', ROUND( ( SELECT Variable_value FROM sys.metrics WHERE Variable_name = 'adaptive_hash_searches' ) / ( ( SELECT Variable_value FROM sys.metrics WHERE Variable_name = 'adaptive_hash_searches_btree' ) + ( SELECT Variable_value FROM sys.metrics WHERE Variable_name = 'adaptive_hash_searches' ) ) * 100 ) 'AHIRatioInt', ( SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'innodb_adaptive_hash_index' ) AHIEnabled, ( SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'innodb_adaptive_hash_index_parts' ) AHIParts""" result = session.run_sql(stmt) row = result.fetch_one() output = util.output(title, "") output += util.output("AHI Enabled", row[2], 1) output += util.output("AHI Parts", row[3], 1) output += util.output("AHI Ratio", row[0], 1) if advices and row[2] == "ON": output += util.print_orange("AHI is not recommended for all workloads") return output
def get_users_auth_plugins(session, advices=False): title = "Authentication Plugins" stmt = "select plugin, count(*) 'nb of users' from mysql.user group by plugin" result = session.run_sql(stmt) output = util.output("Authentication Plugins", "") for row in result.fetch_all(): output += util.output(row[0], "{} user(s)".format(row[1]), 1) if row[0] not in mds_allowed_auth_plugins: if advices: output += util.print_red("{} is not supported in MDS".format( row[0])) return output
def get_dataset(session): stmt = """SELECT format_bytes(SUM(data_length)) Data, format_bytes(SUM(index_length)) Indexes, format_bytes(SUM(data_length)+sum(index_length)) 'Total Size' FROM information_schema.TABLES GROUP BY NULL""" result = session.run_sql(stmt) output = util.output("Dataset", "") while result.has_data(): object_res = result.fetch_one_object() for key in object_res: output += util.output(key, object_res[key], 1) result.next_result() return output
def get_linux_memory_usage(advices): command = "free -h" all_info = subprocess.check_output(command, shell=True).strip() for line in all_info.decode("utf-8").split("\n"): if "Mem:" in line: line_sp = line.split() memory = util.output("Total Memory", line_sp[1]) memory += util.output("Memory Used", line_sp[2]) memory += util.output("Memory Free", line_sp[3]) memory += util.output("Filesystem Cache", line_sp[5]) if "Swap:" in line: line_sp = line.split() memory += util.output("Total Swap", line_sp[1]) memory += util.output("Swap Used", line_sp[2]) memory += util.output("Swap Free", line_sp[3]) swappiness = util.get_sysctl_value('vm.swappiness') memory += util.output("Swappiness", swappiness) if advices: if int(swappiness) <= 10 and int(swappiness) > 0: memory += util.print_green("\nYour swappiness value is good") elif int(swappiness) == 0: memory += util.print_red("0 as swappiness is dangerous, you should set it to 5") else: memory += util.print_red("Your swappiness value is to high, you should set it to a value between 1 and 10") return memory
def _get_innodb_log_details_56(session): title = "InnoDB Logs" stmt = """SELECT concat(round(@@innodb_log_file_size/1024/1024), " MiB") Log_File_Size, concat(round((@@innodb_log_file_size * @@innodb_log_files_in_group)/1024/1024), " MiB") Tot_Log_Size, @@innodb_log_files_in_group Nb_files, @@innodb_flush_log_at_trx_commit Flush_at_trx_commit""" result = session.run_sql(stmt) row = result.fetch_one() output = util.output(title, "") output += util.output("File Size", row[0], 1) output += util.output("Nb of Files", row[2], 1) output += util.output("Total Size", row[1], 1) output += util.output("Flush at Commit", row[3], 1) return output
def get_largest_innodb_tables(session, limit=10): try: from prettytable import PrettyTable except: return (util.print_red( "Error importing module prettytable, check if it's installed")) 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) result = session.run_sql(stmt) headers = [] for col in result.get_columns(): headers.append(col.get_column_label()) tab = PrettyTable(headers) tab.align = 'r' output = util.output("Top %s largest InnoDB Tables" % str(limit), "") for row in result.fetch_all(): tab.add_row(row) tab.align[result.get_columns()[0].get_column_label()] = 'l' output += str(tab) + "\n" return output
def get_tables_without_pk(session): try: from prettytable import PrettyTable except: return (util.print_red( "Error importing module prettytable, check if it's installed")) stmt = """SELECT concat(tables.table_schema, '/' , tables.table_name) as `Table Name`, tables.engine as `Engine`, tables.table_rows as `Row` 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) headers = [] for col in result.get_columns(): headers.append(col.get_column_label()) tab = PrettyTable(headers) tab.align = 'r' output = util.output("Tables without PK", "") for row in result.fetch_all(): tab.add_row(row) tab.align[result.get_columns()[0].get_column_label()] = 'l' output += str(tab) + "\n" return output
def get_users_privileges(session, advices=False): output = "" output_err = "" bad_users = 0 sw_user = False title = "MDS Incompatible Privileges" # get all users stmt = "select user, host from mysql.user" result = session.run_sql(stmt) for row in result.fetch_all(): stmt = "show grants for `{}`@`{}`".format(row[0], row[1]) result2 = session.run_sql(stmt) sw_user = True for row2 in result2.fetch_all(): m = re.match('GRANT (.*) ON', row2[0]) if m: tab_grants = m.group(1).split(',') for priv in tab_grants: if priv.strip() not in mds_allowed_privileges: if sw_user: bad_users += 1 sw_user = False output_err += util.print_red( "{}@{} has a privilege not supported in MDS: {}". format(row[0], row[1], priv.strip())) output += util.output(title, "{} user(s)".format(bad_users)) if advices: output += output_err return output
def check_reserved_keywords(session, advices=False, details=False): title = "Use of 8.0 Reserved Keywords" output = util.output(title, "") output += _check_reserved_keywords_schema(session, advices, details) output += _check_reserved_keywords_table(session, advices, details) output += _check_reserved_keywords_column(session, advices, details) return output
def _get_buffer_pool_details_56(session): title = "InnoDB Buffer Pool" stmt = """SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct, concat(round(@@innodb_buffer_pool_size/1024/1024)," MiB") BP_Size, @@innodb_buffer_pool_instances BP_instances FROM (SELECT variable_value num FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_data') A, (SELECT variable_value num FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_total') B""" result = session.run_sql(stmt) row = result.fetch_one() output = util.output(title, "") output += util.output("BP Size", row[1], 1) output += util.output("BP Instance(s)", row[2], 1) output += util.output("BP filled at ", "{}%".format(row[0]), 1) return output
def version_info(session): supported = False stmt = "select @@version_comment, @@version, @@version_compile_machine" result = session.run_sql(stmt) row = result.fetch_one() type = _get_type_by_pid(session) output = util.output( "MySQL Version", "{} ({}) - {} {}".format(row[0], row[1], row[2], type)) if int(row[1][0]) >= 8 and row[0].startswith('MySQL'): supported = True version = row[1] stmt = "show variables like 'aurora_version'" result = session.run_sql(stmt) row = result.fetch_one() if row: output += util.output("Aurora", "{}".format(row[1])) return supported, output, version
def _get_ahi_details_56(session, advices): title = "Adaptive Hash Index" stmt = "select @@innodb_adaptive_hash_index" result = session.run_sql(stmt) row = result.fetch_one() output = util.output(title, row[0]) if advices and row[0] == 1: output += util.print_orange("AHI is not recommended for all workloads") return output
def _check_reserved_keywords_column(session, advices, details): bad_column = [] stmt = """select column_name from information_schema.columns where TABLE_SCHEMA not in ('mysql', 'performance_schema', 'sys', 'information_schema')""" result = session.run_sql(stmt) for row in result.fetch_all(): if row[0].upper() in mysql_80_keywords: bad_column.append(row[0]) output = util.output("in column name", len(bad_column), 1) if advices and len(bad_column) > 0: output += util.print_orange( "It's recommended to not use reserved keywords, or it's mandatory to quote them with `backticks`" ) if details and len(bad_column) > 0: for el in bad_column: output += util.output(el.upper(), util.print_red_inline("RESERVED"), 1) return output
def version_info(session): supported = False stmt = "select @@version_comment, @@version, @@version_compile_machine" result = session.run_sql(stmt) row = result.fetch_one() output = util.output("MySQL Version", "%s (%s) - %s" % (row[0], row[1], row[2])) if int(row[1][0]) >= 8 and row[0].startswith('MySQL'): supported = True return supported, 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_buffer_pool_details(session, branch): title = "InnoDB Buffer Pool" usesys = "" if branch == "57": usesys = "sys." stmt = """SELECT ROUND(A.num * 100.0 / B.num) BufferPoolFull, BP_Size, BP_instances, FORMAT(F.num * 100.0 / E.num,2) DiskReadRatio, ROUND(F.num*100/E.num) DiskReadRatioInt FROM ( SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_data') A, ( SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_total') B, ( SELECT {}format_bytes(variable_value) as BP_Size FROM performance_schema.global_variables WHERE variable_name = 'innodb_buffer_pool_size') C, ( SELECT variable_value as BP_instances FROM performance_schema.global_variables WHERE variable_name = 'innodb_buffer_pool_instances') D, ( SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') E, ( SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') F """.format(usesys) result = session.run_sql(stmt) row = result.fetch_one() output = util.output(title, "") output += util.output("BP Size", row[1], 1) output += util.output("BP Instance(s)", row[2], 1) output += util.output("BP filled at ", "{}%".format(row[0]), 1) output += util.output("Disk Read Ratio", "{}%".format(row[3]), 1) 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
def get_flush_commands(session, advices): title = "Flush Commands" stmt = "show global status like 'Com_flush'" result = session.run_sql(stmt) row = result.fetch_one() output = util.output(title, "{}".format(row[1])) if advices: if int(row[1]) > 0: output += util.print_orange( "Pay attention that FLUSH commands like FLUSH PRIVILEGES will break MDS Inbound Replication" ) output += util.print_orange("Consider using FLUSH LOCAL instead") return output
def _get_physical_info(path): command = "df -h %s | tail -n 1" % path all_info = subprocess.check_output(command, shell=True).strip() physical_disk = "" for line in all_info.decode("utf-8").split("\n"): line_sp = line.split() physical_disk = line_sp[0] output = util.output("Physical drive", physical_disk, 1) output += util.output("Size", line_sp[1], 1) output += util.output("Used (%s)" % line_sp[4], line_sp[2], 1) output += util.output("Free", line_sp[3], 1) output += util.output("Mount point", line_sp[5], 1) filesystem, attributes = _get_mount_options(line_sp[0]) output += util.output("Filesystem", filesystem, 1) output += util.output("Mount attributes", attributes, 1) return output, physical_disk
def get_tables_in_mysql(session, branch, advices=False, details=False): title = "Extra tables in mysql schema" stmt = "select count(*) from information_schema.tables where table_schema='mysql'" output = "" tot_tbl = 28 if branch == "57": tot_tbl = 31 if branch == "80": tot_tbl = 38 result = session.run_sql(stmt) row = result.fetch_one() if row[0] > tot_tbl: output = util.output(title, int(row[0]) - tot_tbl) if advices: output += util.print_red( "Extra tables in mysql schema are not supported in MDS") # TODO add which one return output
def get_replication_info(session, advices, branch, releasever): supported = False if branch == "80": if int(releasever) > 19: stmt = """select @@server_id, @@binlog_checksum, @@binlog_encryption, @@binlog_format, @@binlog_row_image, @@binlog_row_metadata, @@enforce_gtid_consistency, @@gtid_mode, @@log_bin, @@log_bin_basename, @@transaction_write_set_extraction, @@sync_binlog, @@binlog_transaction_dependency_tracking, @@lower_case_table_names, @@binlog_transaction_compression""" else: stmt = """select @@server_id, @@binlog_checksum, @@binlog_encryption, @@binlog_format, @@binlog_row_image, @@binlog_row_metadata, @@enforce_gtid_consistency, @@gtid_mode, @@log_bin, @@log_bin_basename, @@transaction_write_set_extraction, @@sync_binlog, @@binlog_transaction_dependency_tracking, @@lower_case_table_names""" else: if branch == "56": stmt = """select @@server_id, @@binlog_checksum, "n/a", @@binlog_format, @@binlog_row_image, "n/a", @@enforce_gtid_consistency, @@gtid_mode, @@log_bin, @@sync_binlog, @@log_bin_basename, "n/a", "n/a", @@lower_case_table_names""" else: if branch == "57": if int(releasever) > 21: stmt = """select @@server_id, @@binlog_checksum, @@binlog_format, @@binlog_row_image, @@enforce_gtid_consistency, @@gtid_mode, @@log_bin, @@log_bin_basename, @@transaction_write_set_extraction, @@sync_binlog, @@binlog_transaction_dependency_tracking, @@lower_case_table_names""" else: stmt = """select @@server_id, @@binlog_checksum, @@binlog_format, @@binlog_row_image, @@enforce_gtid_consistency, @@gtid_mode, @@log_bin, @@sync_binlog, @@log_bin_basename, @@transaction_write_set_extraction, @@lower_case_table_names""" result = session.run_sql(stmt) object_res = result.fetch_one_object() if object_res: output = ("\n") output += util.output("Replication Information", "") for key in object_res: if key == "@@server_id": output += util.output("Server Id", "{}".format(object_res[key]), 1) if advices and object_res[key] == 1: output += util.print_orange( "You should consider a unique server id") next if key == "@@binlog_checksum": output += util.output("Binlog Checksum", "{}".format(object_res[key]), 1) next if key == "@@binlog_encryption": output += util.output("Binlog Encryption", "{}".format(object_res[key]), 1) next if key == "@@binlog_format": output += util.output("Binlog Format", "{}".format(object_res[key]), 1) if advices and object_res[key] != "ROW": output += util.print_red( "You should only use Binary Log format = ROW !") next if key == "@@binlog_row_image": output += util.output("Binlog Row Image", "{}".format(object_res[key]), 1) next if key == "@@binlog_row_metadata": output += util.output("Binlog Row Metadata", "{}".format(object_res[key]), 1) next if key == "@@gtid_mode": output += util.output("GTID Mode", "{}".format(object_res[key]), 1) if advices and object_res[key] != "ON": output += util.print_red("You should enable GTIDs !") next if key == "@@gtid_consistency": output += util.output("Enforce GTID Consistency", "{}".format(object_res[key]), 1) next if key == "@@log_bin": output += util.output("Log Bin", "{}".format(object_res[key]), 1) if advices and object_res[key] != 1: output += util.print_red("You should enable binary logs !") next if key == "@@sync_binlog": output += util.output("Sync Binlog", "{}".format(object_res[key]), 1) next if key == "@@log_bin_basename": output += util.output("Log Bin Basename", "{}".format(object_res[key]), 1) next if key == "@@transaction_write_set_extraction": output += util.output("Trx Write Set Extraction", "{}".format(object_res[key]), 1) next if key == "@@binlog_transaction_dependency_tracking": output += util.output("Binlog Trx Dependency Tracking", "{}".format(object_res[key]), 1) next if key == "lower_case_table_names" and object_res[key] == 1: output += util.output("Lower Case Table Names", "{}".format(object_res[key]), 1) if advices: output += util.print_orange( "For MDS Inbound Replication, lower_case_table_names should be 0" ) return output
def _get_innodb_log_details(session): title = "InnoDB Logs" stmt = """SELECT CONCAT( ( SELECT FORMAT_BYTES( STORAGE_ENGINES->>'$."InnoDB"."LSN"' - STORAGE_ENGINES->>'$."InnoDB"."LSN_checkpoint"' ) FROM performance_schema.log_status), " / ", format_bytes( (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'innodb_log_file_size' ) * ( SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'innodb_log_files_in_group')) ) CheckpointInfo, ( SELECT ROUND((( SELECT STORAGE_ENGINES->>'$."InnoDB"."LSN"' - STORAGE_ENGINES->>'$."InnoDB"."LSN_checkpoint"' FROM performance_schema.log_status) / (( SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'innodb_log_file_size' ) * ( SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'innodb_log_files_in_group')) * 100),2) ) AS CheckpointAge, format_bytes( ( SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE variable_name = 'innodb_log_file_size') ) AS InnoDBLogFileSize, ( SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE variable_name = 'innodb_log_files_in_group' ) AS NbFiles, ( SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_redo_log_enabled' ) AS RedoEnabled, ( SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'innodb_flush_log_at_trx_commit' ) AS FlushAtCommit """ result = session.run_sql(stmt) row = result.fetch_one() output = util.output(title, "") output += util.output("File Size", row[2], 1) output += util.output("Nb of Files", row[3], 1) output += util.output("Checkpoint Info", row[0], 1) output += util.output("CheckPointAge", "{}%".format(row[1]), 1) output += util.output("Flush at Commit", row[5], 1) output += util.output("Redo Log", row[4], 1) return output