监控系统项目实施--安装与部署-- MySQL数据库--分区表创建
16lz
2021-03-20
该文档源自github
https://github.com/zabbix-book/partitiontables_zabbix/archive/master.zip
Readme.md
partitiontables_zabbix
This is a scripts for partitiontables of MySQL zabbix version is 2.2 3.0 3.2 3.4 4.0support Zabbix 3.0 4.0MySQL version 5.6 5.7 8.0 本项目是《Zabbix企业级分布式监控系统》第2版中第3章节中的代码，如需使用，请注明出处即可，遵循Apache 2.0开源协议
1. configration
Before run this scripts,maybe you should modify it
修改脚本中的账号信息，如下所示
# MySQL connect informationZABBIX_USER="zabbix"ZABBIX_PWD="zabbix"ZABBIX_DB="zabbix"ZABBIX_PORT="3306"ZABBIX_HOST="127.0.0.1"MYSQL_BIN="mysql"# How days you will keep history days,default is 30 历史数据存储保留时间HISTORY_DAYS=30# How months you will keep trend days,default is 12 趋势数据存储保留时间TREND_MONTHS=122. run it
运行脚本
shell# bash partitiontables_zabbix.sh table history create partitions p20180716table history_log create partitions p20180716table history_str create partitions p20180716table history_text create partitions p20180716table history_uint create partitions p20180716table history create partitions p20180717table history_log create partitions p20180717table history_str create partitions p20180717table history_text create partitions p20180717table history_uint create partitions p20180717......table trends create partitions p201807table trends_uint create partitions p201807table trends create partitions p201808table trends_uint create partitions p2018083. check partitions
验证脚本是否执行成功
shell# mysql -uzabbix -pzabbix zabbixMariaDB [zabbix]> show create table history\G;*************************** 1. row ***************************Table: historyCreate Table: CREATE TABLE `history` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT '0', `value` double(16,4) NOT NULL DEFAULT '0.0000', `ns` int(11) NOT NULL DEFAULT '0', KEY `history_1` (`itemid`,`clock`)) ENGINE=InnoDB DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE ( clock)(PARTITION p20180716 VALUES LESS THAN (1531756799) ENGINE = InnoDB,PARTITION p20180717 VALUES LESS THAN (1531843199) ENGINE = InnoDB,PARTITION p20180718 VALUES LESS THAN (1531929599) ENGINE = InnoDB,PARTITION p20180719 VALUES LESS THAN (1532015999) ENGINE = InnoDB,PARTITION p20180720 VALUES LESS THAN (1532102399) ENGINE = InnoDB,PARTITION p20180721 VALUES LESS THAN (1532188799) ENGINE = InnoDB,PARTITION p20180722 VALUES LESS THAN (1532275199) ENGINE = InnoDB,PARTITION p20180723 VALUES LESS THAN (1532361599) ENGINE = InnoDB) */1 row in set (0.00 sec)ERROR: No query specifiedMariaDB [zabbix]>当看到PARTITION的时候，说明分区已创建成功。
if you see this,everything is OK.
4. set crontab
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#truncate table(only with delete data)
if you database is so big,you should clean data first,then run this scripts
当运行此脚本的时候，Zabbix库有存量数据，此时，建议清空想表的数据，然后再执行此脚本
mysql> use zabbix; mysql> truncate table history; mysql> optimize table history; mysql> truncate table history_str; mysql> optimize table history_str; mysql> truncate table history_uint; mysql> optimize table history_uint; mysql> truncate table trends; mysql> optimize table trends; mysql> truncate table trends_uint; mysql> optimize table trends_uint; partitiontables_zabbix.sh
#!/bin/bash# author: itnihao# mail: itnihao#qq.com# Apache License Version 2.0# date: 2018-06-06# funtion: create parition for zabbix MySQL # repo: https://github.com/zabbix-book/partitiontables_zabbix#配置环境变量ZABBIX_USER="zabbix"ZABBIX_PWD="Root@123456"ZABBIX_DB="zabbix"ZABBIX_PORT="3306"ZABBIX_HOST="127.0.0.1"MYSQL_BIN="mysql"#历史数据保留时间，单位天HISTORY_DAYS=30#趋势数据保留时间，单位月TREND_MONTHS=12HISTORY_TABLE="history history_log history_str history_text history_uint"TREND_TABLE="trends trends_uint"#MYSQL连接命令MYSQL_CMD=$(echo ${MYSQL_BIN} -u${ZABBIX_USER} -p${ZABBIX_PWD} -P${ZABBIX_PORT} -h${ZABBIX_HOST} ${ZABBIX_DB})function create_partitions_history() { #给历史表创建分区 for PARTITIONS_CREATE_EVERY_DAY in $(date +"%Y%m%d") $(date +"%Y%m%d" --date='1 days') $(date +"%Y%m%d" --date='2 days') $(date +"%Y%m%d" --date='3 days') $(date +"%Y%m%d" --date='4 days') $(date +"%Y%m%d" --date='5 days') $(date +"%Y%m%d" --date='6 days') $(date +"%Y%m%d" --date='7 days') do TIME_PARTITIONS=$(date -d "$(echo ${PARTITIONS_CREATE_EVERY_DAY} 23:59:59)" +%s) for TABLE_NAME in ${HISTORY_TABLE} do SQL1=$(echo "show create table ${TABLE_NAME};") RET1=$(${MYSQL_CMD} -e "${SQL1}"|grep "PARTITION BY RANGE"|wc -l) #表结构中的表分区不存在，则创建 if [ "${RET1}" == "0" ];then SQL2=$(echo "ALTER TABLE $TABLE_NAME PARTITION BY RANGE( clock ) (PARTITION p${PARTITIONS_CREATE_EVERY_DAY} VALUES LESS THAN (${TIME_PARTITIONS}));") RET2=$(${MYSQL_CMD} -e "${SQL2}") if [ "${RET2}" != "" ];then echo ${RET2} echo "${SQL2}" else printf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_DAY}\n" ${TABLE_NAME} fi continue fi #表结构中的表分区已经存在，则创建分区 if [ "${RET1}" != "0" ];then SQL3=$(echo "show create table ${TABLE_NAME};") RET3=$(${MYSQL_CMD} -e "${SQL3}"|grep "p${PARTITIONS_CREATE_EVERY_DAY}"|wc -l) if [ "${RET3}" == "0" ];then TIME_PARTITIONS=$(date -d "$(echo ${PARTITIONS_CREATE_EVERY_DAY} 23:59:59)" +%s) SQL4=$(echo "ALTER TABLE $TABLE_NAME ADD PARTITION (PARTITION p${PARTITIONS_CREATE_EVERY_DAY} VALUES LESS THAN (${TIME_PARTITIONS}));") RET4=$(${MYSQL_CMD} -e "${SQL4}") if [ "${RET4}" != "" ];then echo ${RET4} echo "${SQL4}" else printf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_DAY}\n" ${TABLE_NAME} fi fi fi done done}function drop_partitions_history() { #删除历史表分区 for PARTITIONS_DELETE_DAYS_AGO in $(date +"%Y%m%d" --date="${HISTORY_DAYS} days ago") do for TABLE_NAME in ${HISTORY_TABLE} do SQL=$(echo -e "show create table ${TABLE_NAME};") RET=$(${MYSQL_CMD} -e "${SQL}"|grep "p${PARTITIONS_DELETE_DAYS_AGO}"|wc -l) if [ "${RET}" == "1" ];then SQL=$(echo "ALTER TABLE ${TABLE_NAME} DROP PARTITION p${PARTITIONS_DELETE_DAYS_AGO};") RET=$(${MYSQL_CMD} -e "${SQL}") if [ "${RET}" != "" ];then echo ${RET} echo "${SQL}" else printf "table %-12s drop partitions p${PARTITIONS_DELETE_DAYS_AGO}\n" ${TABLE_NAME} fi fi done done}function create_partitions_trend() { #创建趋势表分区 for PARTITIONS_CREATE_EVERY_MONTHS in $(date +"%Y%m") $(date +"%Y%m" --date='1 months') $(date +"%Y%m" --date='2 months') $(date +"%Y%m" --date='3 months') $(date +"%Y%m" --date='4 months') $(date +"%Y%m" --date='5 months') do TIME_PARTITIONS=$(date -d "$(echo ${PARTITIONS_CREATE_EVERY_MONTHS}01 00:00:00)" +%s) for TABLE_NAME in ${TREND_TABLE} do SQL1=$(echo "show create table ${TABLE_NAME};") RET1=$(${MYSQL_CMD} -e "${SQL1}"|grep "PARTITION BY RANGE"|wc -l) #表结构中的表分区不存在，则创建 if [ "${RET1}" == "0" ];then SQL2=$(echo "ALTER TABLE $TABLE_NAME PARTITION BY RANGE( clock ) (PARTITION p${PARTITIONS_CREATE_EVERY_MONTHS} VALUES LESS THAN (${TIME_PARTITIONS}));") RET2=$(${MYSQL_CMD} -e "${SQL2}") if [ "${RET2}" != "" ];then echo ${RET2} echo "${SQL2}" else printf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_MONTHS}\n" ${TABLE_NAME} fi continue fi #表结构中的表分区已经存在，则创建分区 if [ "${RET1}" != "0" ];then SQL3=$(echo "show create table ${TABLE_NAME};") RET3=$(${MYSQL_CMD} -e "${SQL3}"|grep "p${PARTITIONS_CREATE_EVERY_MONTHS}"|wc -l) if [ "${RET3}" == "0" ];then SQL4=$(echo "ALTER TABLE ${TABLE_NAME} ADD PARTITION (PARTITION p${PARTITIONS_CREATE_EVERY_MONTHS} VALUES LESS THAN (${TIME_PARTITIONS}));") RET4=$(${MYSQL_CMD} -e "${SQL4}") if [ "${RET4}" != "" ];then echo ${RET4} echo "${SQL4}" else printf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_MONTHS}\n" ${TABLE_NAME} fi fi fi done done}function drop_partitions_trend() { #删除趋势表分区 for PARTITIONS_DELETE_MONTHS_AGO in $(date +"%Y%m" --date="${TREND_MONTHS} months ago") do for TABLE_NAME in ${TREND_TABLE} do SQL=$(echo "show create table ${TABLE_NAME};") RET=$(${MYSQL_CMD} -e "${SQL}"|grep "p${PARTITIONS_DELETE_MONTHS_AGO}"|wc -l) if [ "${RET}" == "1" ];then SQL=$(echo "ALTER TABLE ${TABLE_NAME} DROP PARTITION p${PARTITIONS_DELETE_MONTHS_AGO};") RET=$(${MYSQL_CMD} -e "${SQL}") if [ "${RET}" != "" ];then echo ${RET} echo "${SQL}" else printf "table %-12s drop partitions p${PARTITIONS_DELETE_MONTHS_AGO}\n" ${TABLE_NAME} fi fi done done}function main() { create_partitions_history create_partitions_trend drop_partitions_history drop_partitions_trend}main更多相关文章
- 监控系统项目实施--安装与部署-- MySQL数据库--分区表创建2
- 使用CLI创建VPC
- 5s！用浏览器打造一个开箱即用的Linux系统
- AWS Elasticsearch Service 建立snapshot
- Spark SQL用UDF实现按列特征重分区
- 视频：RDD的特性介绍及源码阅读必备基础
- 视频：JDBCRDD源码及自定义JDBCRDD的分区策略
- 图表控件AnyChart如何使用JavaScript创建发散条形图
- RHEL7.6安装Oracle11g数据库--4、创建数据库