Automated Mysql Tables Growth Checking in windows

Step 1: Create a sql file(Table_growth.sql) with below Script


use information_schema;
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');

SET @FOLDER = 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/';
SET @PREFIX = 'Growth';
SET @EXT    = '.csv';

SET @CMD = CONCAT("(SELECT 'Schema Name','Table Name', 'Table Type','No.of Records', 'Size in MB', 'Time')
union ","( SELECT table_schema,TABLE_NAME, table_type,TABLE_ROWS, ROUND((data_length + index_length) / 1024 / 1024, 2)  Size_in_MB,
  NOW() FROM `information_schema`.`tables`  WHERE `table_schema` not in (
'information_schema',
'mysql',
'performance_schema',
'sys',
'mysql_innodb_cluster_metadata')  and table_type='BASE Table' 
INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT, "' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' )");

PREPARE statement FROM @CMD;

EXECUTE statement;

Step 2: Create one bat file(growth.bat) with below script.

mysql -u root -p"P@ssw0rd" -h 127.0.0.1 <"D:\Table_growth.sql"

make sure host name.

Step 3: Create Task scheduler with above mentioned bat file. Schedule the task as your requirement. i.e Daily,weekly, monthly etc..

Step 4: Run the task or (it will run on schedule time automatically)

Step 4: File will be generated in below path.

C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/