mysql_audit_db_size

# easy copy-paste for templates
pager sed 's/^| //g' | sed 's/\s\+|\s\+/\t/g' | sed 's/ |$//'

# db size
SELECT table_schema AS "Database", SUM(ROUND((data_length + index_length)/1024/1024, 2)) AS "Size (MB)", SUM(ROUND(data_free/1024/1024, 2)) as "Free (MB)" FROM information_schema.TABLES WHERE table_schema not in ('mysql', 'sys', 'information_schema', 'performance_schema') GROUP BY table_schema; 


# top 10 table sizes per database. Everything below top 10 will be aggregated as "other"
SELECT table_schema, table_name, sum(size) FROM (SELECT table_schema, CASE WHEN rn <= 10 THEN table_name ELSE 'other' END as table_name, size FROM (SELECT table_schema, table_name, ROUND((data_length + index_length)/1024/1024, 2) as size,          @row_number:=CASE WHEN @schema = table_schema THEN @row_number + 1 ELSE 1 END AS rn,    @schema:=table_schema FROM information_schema.TABLES , (select @row_number:=0, @schema:='') as t   WHERE table_schema not in ('performance_schema', 'information_schema', 'MySQL', 'sys', 'Claranet_DB', 'PERCONA_SCHEMA') ORDER BY table_schema, size DESC) f) f group by table_schema, table_name ORDER BY size;


# tables count per order of magnitude
SELECT cast(concat(left(size,1), repeat('0', length(size)-1)) as int) as m, count(*) as c from (SELECT ROUND((data_length + index_length)/1024/1024, 0) as size FROM information_schema.TABLES WHERE table_schema not in ('performance_schema', 'information_schema', 'MySQL', 'sys', 'Claranet_DB', 'PERCONA_SCHEMA')) f group by m order by m;