监控系统项目实施--安装与部署-- MySQL数据库--分区表创建2
16lz
2021-03-20
历史记录表进行处理
[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更多相关文章
- 必备技能！Shell编程实战案例分享（PDF版）！
- 如何不耍流氓的做运维之-SHELL脚本
- Spark SQL用UDF实现按列特征重分区
- 视频：RDD的特性介绍及源码阅读必备基础
- 视频：JDBCRDD源码及自定义JDBCRDD的分区策略
- 自动安装nginx脚本
- 必读：Spark与kafka010整合
- Kafka单节点至集群的安装部署及注意事项
- 关于OSIsoft PI软件存在访问控制漏洞和跨站脚本漏洞的情况通报