HOW TO TO CALCULATE TABLE SIZE IN MB AND CREATE EXCEL SHEET IN MYSQL 5.7 USING CENTOS 7

 HOW TO TO CALCULATE TABLE SIZE IN MB AND CREATE EXCEL SHEET IN MYSQL 5.7 USING CENTOS 7

1.Given command below:-

SELECT 

     table_schema as `Database`, 

     table_name AS `Table`, 

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 

FROM information_schema.TABLES 

WHERE table_schema = 'mamc_disney'

order BY (data_length + index_length) DESC;

2.create excel sheet, we are simple using outfile function

SELECT 

     table_schema as `Database`, 

     table_name AS `Table`, 

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 

FROM information_schema.TABLES 

WHERE table_schema = 'your_database_name'

order BY (data_length + index_length) DESC

INTO OUTFILE '/var/lib/mysql-files/pankaj.xlxs'

FIELDS TERMINATED BY ',';

Notes:-In mysql predifine  this directory and we can change also not fixed.