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.
