MySQL database monthly growth problem

If there is no historical monitoring data, MySQL cannot record it internally.

Write a monitoring script, du a data directory every day.The database itself does not record data increments

You can roughly count the amount of data at the table or library level, collect it twice, and calculate the difference.

select table_schema, table_name, engine, table_type, sum(table_rows) as rows, round(sum(data_length)/1024/1024, 2) as `data(MB)`, round(sum(index_length)/1024/1024, 2) as `index(MB)`, round(sum(data_length+index_length)/1024/1024, 2) as `total_size(MB)`from information_schema.tableswhere table_schema not in ('mysql', 'information_schema', 'performance_schema', 'sys') and table_type not in('VIEW')group by table_schema, table_name, engine, table_typeorder by table_schema, table_name;

If there is a delete, it is not allowed, of course, if you want an overview, it is OK

• For a single instance, the size of each database of the instance can be counted by the following statement:

SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb FROM information_schema.TABLES group by table_schema

• The next thing to do is to write the results of the current day/month into the database table, such as dba_statistic.tmp_database_info:

insert into tmp_database_info (instance_id,business_ip,database_name,db_size,createdate)

• If you want to count multiple instances, you need to loop through all instances through an automated script, and each instance performs the above select and insert operations
• We write clickhouse, the script is written in python