Shulou(Shulou.com)06/02 Report--

Zabbix data cleanup it is very dangerous to operate the database on the server. We must operate it carefully here. There are two ways to clean up the data: 1. Change the historical data and trend preservation time on zabbix 2. Through the operation of the zabbxi mysql database to clean up here the author is relatively weak, properly used the second, to retain 30 days of data, and in the operation must pay attention to: turn off the alarm-> alarm media, today's backup data caused false positives, a large number of text messages sent, be sure to remember the painful experience, otherwise you have to leave! The basic operation statements for using tables to clean up data are as follows: Zabbix database query size MariaDB [(none)] > select table_schema, concat (truncate (sum (data_length) / 1024 as data_size,concat 2), 'mb') as data_size,concat (truncate (sum (index_length) / 1024 lem2),' mb') as index_size from information_schema.tables group by table_schema order by data_size desc +-+ | table_schema | data_size | index_size | +-+ | zabbix | | 63447.98 mb | 74299.04mb | | information_schema | 0.08mb | 0.08mb | +-+ data size: 61g Index size: 37g query all table sizes in Zabbix database MariaDB [(none)] > select table_name | (data_length+index_length) / 1024 Compact 1024 as total_mb,table_rows from information_schema.tables where table_schema='zabbix' order by total_mb desc Execute sql to check the data size before the specified date: SELECT table_schema as `Database`, table_name AS `Table`, round (data_length + index_length)), 2) `Size in MB`from information_schema.TABLES where CREATE_TIME

< '2019-09-01 00:00:00' and table_name='history.ibd';查看当前目录大与50M 的文件# find ./ -size +50M | xargs ls -Slh删除2019年11月22日11点以前的数据[root@128lastsummer ~]# date +%s -d "Nov 22, 2019 11:00:00"1574391600MariaDB [zabbix]>

Delete from history where clock

< 1574391600;MariaDB [zabbix]>

Delete from history_uint where clock

< 1574391600;MariaDB [zabbix]>

Optimize table history;MariaDB [zabbix] > optimize table history_unit; backup and recovery zabbix history tables [root@128lastsummer ~] # mysqldump-uzabbix-p zabbix history > / tmp/zabbix_history.sqlMariaDB [zabbix] > source / tmp/zabbix_history.sql; query the number of data items in each table select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = 'name of the database' order by table_rows desc

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.