– – – -Database objects: Execute the below queries for every database using Super user credentials
select db, object_type , count from sys.schema_object_overview soo
– – -Databases:
select “Databases”, count(object_schema) from sys.innodb_buffer_stats_by_schema ibsbs
where object_schema not in (“mysql”,”InnoDB System”, “sys”);
– – – -High Data Volume Tables
SELECT
table_schema AS `Database`,
table_name AS `Table`,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
table_schema NOT IN (‘information_schema’, ‘performance_schema’, ‘mysql’, ‘sys’)
ORDER BY
(data_length + index_length) DESC;
– – -Data for Partitioning / Bucketing
SELECT
TABLE_SCHEMA,
TABLE_NAME,
PARTITION_NAME,
PARTITION_ORDINAL_POSITION,
PARTITION_METHOD,
SUBPARTITION_NAME,
SUBPARTITION_ORDINAL_POSITION,
SUBPARTITION_METHOD,
PARTITION_EXPRESSION,
SUBPARTITION_EXPRESSION,
TABLE_ROWS
FROM
information_schema.PARTITIONS
where TABLE_SCHEMA not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”) ;
– – -Count of stored procedures and Functions
SELECT ROUTINE_TYPE, COUNT(*) AS count
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”)
GROUP BY ROUTINE_TYPE;
– – -List of stored procedure
select ROUTINE_SCHEMA ,ROUTINE_NAME , ROUTINE_DEFINITION,SQL_DATA_ACCESS,ROUTINE_TYPE FROM information_schema.ROUTINES where ROUTINE_TYPE =” PROCEDURE” and ROUTINE_SCHEMA not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”);
– – -Count of external Tables/Views in MySQL
SELECT TABLE_SCHEMA, TABLE_TYPE, COUNT(*) AS “count”
FROM information_schema.TABLES
WHERE TABLE_SCHEMA not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”)
GROUP BY TABLE_TYPE,TABLE_SCHEMA;
– – -List of External Tables
SELECT TABLE_SCHEMA, TABLE_NAME ,TABLE_TYPE,TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”)
– – –Total I/O Usage by Days
SELECT
EVENT_NAME,
SUM(COUNT_READ) AS total_reads,
SUM(SUM_TIMER_READ) AS total_read_time,
SUM(COUNT_WRITE) AS total_writes,
SUM(SUM_TIMER_WRITE) AS total_write_time
FROM performance_schema.file_summary_by_event_name
GROUP BY EVENT_NAME;
– – – –Database Volume:
SELECT
table_schema AS ‘Database’,
ROUND(SUM(data_length) / 1024 / 1024, 2) AS ‘Data Size (MB)’,
ROUND(SUM(index_length) / 1024 / 1024, 2) AS ‘Index Size (MB)’,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS ‘Total Size (MB)’
FROM information_schema.tables
GROUP BY table_schema;
– – (15day/1month/6months whatever is possible for extraction)
Leaplogic