历史记录表进行处理
[root@zabbix-mysql01 ~]# mysql -uroot -pRoot@123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 12Server version: 8.0.23 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use zabbixReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> mysql> mysql> truncate table history; optimize table history; Query OK, 0 rows affected (0.01 sec)mysql> optimize table history; truncate table history_str; +----------------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+----------------+----------+----------+-------------------------------------------------------------------+| zabbix.history | optimize | note | Table does not support optimize, doing recreate + analyze instead || zabbix.history | optimize | status | OK |+----------------+----------+----------+-------------------------------------------------------------------+2 rows in set (0.01 sec)mysql> truncate table history_str; optimize table history_str; Query OK, 0 rows affected (0.01 sec)mysql> optimize table history_str; truncate table history_uint; optimize table history_uint; truncate table history_log; +--------------------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+--------------------+----------+----------+-------------------------------------------------------------------+| zabbix.history_str | optimize | note | Table does not support optimize, doing recreate + analyze instead || zabbix.history_str | optimize | status | OK |+--------------------+----------+----------+-------------------------------------------------------------------+2 rows in set (0.02 sec)mysql> truncate table history_uint; optimize table history_log; Query OK, 0 rows affected (0.01 sec)mysql> optimize table history_uint; truncate table history_text; optimize table history_text; +---------------------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+---------------------+----------+----------+-------------------------------------------------------------------+| zabbix.history_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead || zabbix.history_uint | optimize | status | OK |+---------------------+----------+----------+-------------------------------------------------------------------+2 rows in set (0.01 sec)mysql> truncate table history_log; truncate table trends; Query OK, 0 rows affected (0.01 sec)mysql> optimize table history_log; optimize table trends; truncate table trends_uint; ptimize table trends_uint; +--------------------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+--------------------+----------+----------+-------------------------------------------------------------------+| zabbix.history_log | optimize | note | Table does not support optimize, doing recreate + analyze instead || zabbix.history_log | optimize | status | OK |+--------------------+----------+----------+-------------------------------------------------------------------+2 rows in set (0.02 sec)mysql> truncate table history_text; Query OK, 0 rows affected (0.00 sec)mysql> optimize table history_text; +---------------------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+---------------------+----------+----------+-------------------------------------------------------------------+| zabbix.history_text | optimize | note | Table does not support optimize, doing recreate + analyze instead || zabbix.history_text | optimize | status | OK |+---------------------+----------+----------+-------------------------------------------------------------------+2 rows in set (0.02 sec)mysql> truncate table trends; Query OK, 0 rows affected (0.01 sec)mysql> optimize table trends; +---------------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+---------------+----------+----------+-------------------------------------------------------------------+| zabbix.trends | optimize | note | Table does not support optimize, doing recreate + analyze instead || zabbix.trends | optimize | status | OK |+---------------+----------+----------+-------------------------------------------------------------------+2 rows in set (0.01 sec)mysql> truncate table trends_uint; Query OK, 0 rows affected (0.01 sec)mysql> optimize table trends_uint;+--------------------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+--------------------+----------+----------+-------------------------------------------------------------------+| zabbix.trends_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead || zabbix.trends_uint | optimize | status | OK |+--------------------+----------+----------+-------------------------------------------------------------------+2 rows in set (0.01 sec)mysql>
运行分区脚本
[root@zabbix-mysql01 ~]# sh partitiontables_zabbix.sh mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history create partitions p20210320mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_log create partitions p20210320mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_str create partitions p20210320mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_text create partitions p20210320mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_uint create partitions p20210320mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history create partitions p20210321mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_log create partitions p20210321mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_str create partitions p20210321mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_text create partitions p20210321mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_uint create partitions p20210321mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history create partitions p20210322mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_log create partitions p20210322mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_str create partitions p20210322mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_text create partitions p20210322mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_uint create partitions p20210322mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history create partitions p20210323mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_log create partitions p20210323mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_str create partitions p20210323mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_text create partitions p20210323mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_uint create partitions p20210323mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history create partitions p20210324mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_log create partitions p20210324mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_str create partitions p20210324mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_text create partitions p20210324mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_uint create partitions p20210324mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history create partitions p20210325mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_log create partitions p20210325mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_str create partitions p20210325mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_text create partitions p20210325mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_uint create partitions p20210325mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history create partitions p20210326mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_log create partitions p20210326mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_str create partitions p20210326mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_text create partitions p20210326mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_uint create partitions p20210326mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history create partitions p20210327mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_log create partitions p20210327mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_str create partitions p20210327mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_text create partitions p20210327mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table history_uint create partitions p20210327mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends create partitions p202103mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends_uint create partitions p202103mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends create partitions p202104mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends_uint create partitions p202104mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends create partitions p202105mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends_uint create partitions p202105mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends create partitions p202106mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends_uint create partitions p202106mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends create partitions p202107mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends_uint create partitions p202107mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends create partitions p202108mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.table trends_uint create partitions p202108mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.
配置定时任务
the crontab remove old partitions every day 每天的定时任务，是由linux的crontab去实现的，需确保Linux服务器的crond启动成功。否则会引起新分区无法自动创建的问题 shell# crontab -e1 0 * * * /usr/sbin/partitiontables_zabbix.shShell# chmod 700 /usr/sbin/partitiontables_zabbix.sh
