zabbix 启用分区表

最新推荐文章于 2023-08-07 15:59:21 发布

运维自动化&云计算

最新推荐文章于 2023-08-07 15:59:21 发布

阅读量1.2k

点赞数

分类专栏： zabbix运维 DB-MYSQL|ORACLE|SQLSERVER

本文链接：https://blog.csdn.net/h106140873/article/details/103499075

版权

DB-MYSQL|ORACLE|SQLSERVER 同时被 2 个专栏收录

78 篇文章 1 订阅

订阅专栏

zabbix运维

26 篇文章 2 订阅

订阅专栏

参照

https://www.zabbix.org/wiki/Docs/howto/mysql_partition#partition_create

注意：zabbix 启用分区表后需要关闭Housekeeper

分区的过程其实很简单，按着做就是。

假设你当前在/root目录

mkdir Partitioning
cd Partitioning
wget wget https://www.zabbix.org/mw/images/1/10/Partitioning.zip
unzip Partitioning.zip

查看目录

[root@gt-zabbix Partitioning]# ll
total 36
-rw-r--r-- 1 root root 8021 Jun 11  2019 partition_create.sql
-rw-r--r-- 1 root root 2383 Jun 11  2019 partition_delete.sql
-rw-r--r-- 1 root root 5547 Dec 11 19:58 Partitioning.zip
-rw-r--r-- 1 root root 1063 Dec 11 20:05 partition_maintenance_all.sh
-rw-r--r-- 1 root root  750 Jun  3  2019 partition_maintenance_all.sql
-rw-r--r-- 1 root root  683 Jun 11  2019 partition_maintenance.sql
-rw-r--r-- 1 root root 3604 Jun 11  2019 partition_verify.sql

执行

mysql < partition_create.sql
mysql < partition_maintenance_all.sql

想看数据库的存储过程

MariaDB [(none)]> show procedure status;
+--------+---------------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db     | Name                      | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------+---------------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| zabbix | partition_create          | PROCEDURE | root@localhost | 2019-12-11 18:56:37 | 2019-12-11 18:56:37 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| zabbix | partition_drop            | PROCEDURE | root@localhost | 2019-12-11 18:56:56 | 2019-12-11 18:56:56 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| zabbix | partition_maintenance     | PROCEDURE | root@localhost | 2019-12-11 18:57:08 | 2019-12-11 18:57:08 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| zabbix | partition_maintenance_all | PROCEDURE | root@localhost | 2019-12-11 19:39:48 | 2019-12-11 19:39:48 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| zabbix | partition_verify          | PROCEDURE | root@localhost | 2019-12-11 20:13:44 | 2019-12-11 20:13:44 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------+---------------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
5 rows in set (0.01 sec)

如果要看过程的创建代码：

MariaDB [(none)]> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [zabbix]> show create procedure  partition_maintenance_all;
+---------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure                 | sql_mode | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | character_set_client | collation_connection | Database Collation |
+---------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| partition_maintenance_all |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
                CALL partition_maintenance(SCHEMA_NAME, 'history', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'history_log', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'history_str', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'history_text', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 28, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14);
                CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14);
END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

自动执行分表脚本：

[root@gt-zabbix Partitioning]# cat  partition_maintenance_all.sh
#!/bin/bash

echo 'Partitioned table maintenance tasks begin at '`date +%Y%m%d%H%M%S`'.' > /tmp/partition_maintenance_all_mail.tmp
echo '' >> /tmp/partition_maintenance_all_mail.tmp

mysql -D zabbix -e 'CALL partition_maintenance_all("zabbix");' >> /tmp/partition_maintenance_all_mail.tmp 2>&1

echo '' >> /tmp/partition_maintenance_all_mail.tmp
echo 'Partitioned table maintenance tasks finish at '`date +%Y%m%d%H%M%S`'.' >> /tmp/partition_maintenance_all_mail.tmp

#cat /tmp/partition_maintenance_all_mail.tmp | mailx -r 'ITmonitor@didichuxing.com' -s 'Partitioned table maintenance' gaoyuebruce@didiglobal.com

#CURL_DATA="{
#  \"content\": \""`sed ":a;N;s/\n/<br \/>/;s/\t/ /g;ta" /tmp/partition_maintenance_all_mail.tmp`"\",
#  \"sender\": \"erpmonitor@didiglobal.com\",
#  \"subject\": \"Partitioned table maintenance\",
#  \"tos\": [
#    \"gaoyuebruce@didiglobal.com\"
#  ]
#}
#"

#curl -X POST --header "Content-Type: application/json" --header "Accept: */*" -d "$CURL_DATA" "http://10.89.139.46:8088/api/v2/notification/email/send?token=【token值】"

结合我前面文章写的python发邮箱脚本可实现把执行结果发送了邮箱

https://blog.csdn.net/h106140873/article/details/97772503

/usr/lib/zabbix/alertscripts/send.py 'huangshumao@gtland.cn' 'partition_maintenance_all_mail' "`cat /tmp/partition_maintenance_all_mail.tmp`"

创建定时执行任务crontab -euroot

[root@gt-zabbix Partitioning]# crontab -luroot
#*　　*　　*　　*　　*　　command
#分　时　日　月　周　命令
#第1列表示分钟1～59 每分钟用*或者 */1表示
#第2列表示小时1～23（0表示0点）
#第3列表示日期1～31
#第4列表示月份1～12
#第5列标识号星期0～6（0表示星期天）
#第6列要运行的命令
#每天1点30分钟执行一次同步
30 1 * * *  /usr/bin/sh /root/Partitioning/partition_maintenance_all.sh