Getting MySQL DB size

Many a times you want to find the size of database (schema) for performance monitoring or to allocate more disk space or to take some proactive action on db size.  You can pull relevant information from MySQL INFORMATION_SCHEMA with the following query.  It sums up each table data and its index size for all tables in a single schema. 

SELECT s.schema_name,
  CONCAT(IFNULL(ROUND((SUM(t.data_length) +
       SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size,
  CONCAT(IFNULL(ROUND((SUM(t.data_length))/1024/1024,2),0.00),"Mb") data_size,
  CONCAT(IFNULL(ROUND((SUM(t.index_length))/1024/1024,2),0.00),"Mb") index_size,
  CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length)) -
         SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used,
  CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
  IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length)) -
       SUM(t.data_free))/((SUM(t.data_length) + 
       SUM(t.index_length)))*100),2),0) pct_used,
  SUM(t.table_rows) total_rows,
  COUNT(t.table_name) total_tables,
  CONCAT(ROUND(MAX(t.data_length+t.index_length)/1024/1024, 0.00), 
      "Mb") biggest_table_size,
  GROUP_CONCAT(distinct t.engine) engines_used,
  GROUP_CONCAT(distinct t.table_collation) collations_used
FROM INFORMATION_SCHEMA.SCHEMATA s
  LEFT JOIN INFORMATION_SCHEMA.TABLES t 
ON s.schema_name = t.table_schema
WHERE s.schema_name = "test_dw_advertising"
GROUP BY s.schema_name\G


*************************** 1. row ***************************
       schema_name: test_dw_advertising
        total_size: 212151.14Mb
         data_size: 154660.83Mb
        index_size: 57490.31Mb
         data_used: 210039.45Mb
         data_free: 2111.69Mb
          pct_used: 99.00
        total_rows: 309940227
      total_tables: 118
biggest_table_size: 74360Mb
      engines_used: InnoDB,MyISAM
   collations_used: latin1_swedish_ci
1 row in set (1 min 38.26 sec)

The total size of allocated (including free data size) is nearly 212G, out of which data used ~155G and indexes used ~57G for 118 tables leaving 2G free (99% of disk allocated is used). Biggest table used 74.3G disk space and this schema has both InnoDB and MyISAM tables using single collation latin1_swedish_ci.

HTH,
Shiva