.::: Create Event Scheduler to run Query & List Store Procedure in MySQL, MariaDB :::.

 

1. refer link

https://teguhth.blogspot.com/2020/12/database-size-table-capacity-in-mysql.html
http://teguhth.blogspot.com/2022/08/create-store-procedure-for-database.html
https://teguhth.blogspot.com/2022/08/create-event-scheduler-to-run-query.htm

2. create event scheduler using existing store procedure

CREATE EVENT event_sp_database_size_info
    ON SCHEDULE EVERY '1' day
    STARTS '2022-08-01 02:00:00'
   do call sp_database_size_info ;

CREATE EVENT event_sp_table_size_info
   ON SCHEDULE EVERY '1' day
   STARTS '2022-08-01 02:00:00'
  do call sp_table_size_info;
 

  3. create event scheduler using using script query

CREATE EVENT event_sp_database_size_info_query
    ON SCHEDULE EVERY '1' day
    STARTS '2022-08-01 03:00:00'
   do insert into teguhthtools.tbl_dbsize
SELECT @@hostname as ServerName, current_timestamp() as DateTime,s.schema_name as SchemaName,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00)) TotalSizeMB,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00)) DataUsedMB,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00)) DataFreeMB  
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema GROUP BY s.schema_name ORDER BY SchemaName ;

CREATE EVENT event_sp_table_size_info_query
   ON SCHEDULE EVERY '1' day
   STARTS '2022-08-01 03:00:00'
  do
insert into teguhthtools.tbl_tablesizeinfo
SELECT @@hostname as ServerName,current_timestamp() as DateTime,
    table_schema,
    table_name,
    table_type,
    row_format,
    table_rows,
    round(1.0*data_length/1024/1024, 2) as DataSizeMB,
    round(index_length/1024/1024, 2) as IndexSizeMB,
    round(data_free/1024/1024, 2) as FreeSizeMB,
    round((data_length + index_length)/1024/1024, 2) as TotalSizeMB
FROM
    information_schema.tables
WHERE
 table_type ='base table' and table_schema not in('performance_schema','mysql','information_schema')
ORDER BY table_name;

 

4. check list even scheduler 

 
SHOW EVENTS where name ='event_sp_database_size_info' or name ='event_sp_table_size_info' or
name ='event_sp_database_size_info_query' or name ='event_sp_table_size_info_query';