#!/bin/bash
###############
# DESCRIPTION: list databases and their size in MB
# VARIABLES:
#   LIMIT(int): only show the biggest $LIMIT number of databases
###############

[[ $LIMIT =~ ^[0-9]+$ ]] \
  && result_limit="LIMIT $LIMIT" \
  || result_limit=""

query="SELECT
  table_schema AS 'DATABASE',
  ROUND((SUM(data_length + index_length) / 1024 / 1024), 2) AS 'SIZE (MB)'
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema','roundcube','phpmyadmin')
GROUP BY table_schema
ORDER BY \`SIZE (MB)\` DESC
$result_limit;"

mysql -te "$query"