Check database size on disk and see the table sizes with SQL

check-db-size.sh

df -h | grep 'mysql\|Filesystem'

check-db-size.sql

# Show the DB size overall
SELECT table_schema "Data Base Name",
    SUM( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
    SUM( data_free )/ 1024 / 1024 "Free Space in MB"
    FROM information_schema.TABLES
    GROUP BY table_schema;

# show the largest database tables (top 30):

SELECT table_schema, table_name AS 'Table', ROUND(((data_length) / 1024 / 1024), 2) AS 'DataSpace' , ROUND(((index_length) / 1024 / 1024), 2) AS 'IndexSpace' , ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS 'TableSize (Gb)', ROUND(((data_free) / 1024 / 1024), 2) AS 'DeadSpace', ROUND(((data_length + index_length + data_free) / 1024 / 1024), 2) AS 'DiskSpace', ROUND(((data_free) / 1024 / 1024)/((data_length + index_length + data_free) / 1024 / 1024), 2) * 10 as 'WastedPercent' FROM information_schema.TABLES WHERE table_schema != 'jmc' union SELECT table_schema, 'all' , SUM(ROUND(((data_length) / 1024 / 1024), 2)) AS 'DataSpace' , SUM(ROUND(((index_length) / 1024 / 1024), 2)) as 'IndexSpace' , SUM(ROUND(((data_length + index_length) / 1024 / 1024), 2)) as 'TableSize', SUM(ROUND(((data_free) / 1024 / 1024), 2)) as 'DeadSpace' , SUM(ROUND(((data_length + index_length + data_free) / 1024 / 1024), 2)) as 'DiskSpace', (SUM(ROUND(((data_length + index_length) / 1024 / 1024), 2)))/( SUM(ROUND(((data_length + index_length + data_free) / 1024 / 1024), 2))) * 10 as 'WastedPercent' FROM information_schema.TABLES WHERE table_schema = 'jmc' GROUP BY table_schema ORDER BY 5 DESC LIMIT 30;