设为首页 收藏本站

运维网

 找回密码
 立即注册
查看: 582|回复: 0

[经验分享] Centos7.2 安装 zabbix server 3.4 及初始化工作

[复制链接]
YunVN网友  发表于 2019-1-19 11:31:34 |阅读模式
　　Centos7.2 安装 zabbix server 3.4
　　一、安装  zabbix server 3.4
　　参考连接：
　　https://www.zabbix.com/download?zabbix=3.4&os_distribution=centos&os_version=7&db=MySQL
　　https://www.zabbix.com/documentation/3.4/zh/manual/installation/install_from_packages#from_distribution_packages
　　关闭防火墙
# systemctl stop firewalld.service                 #关闭防火墙
# systemctl disable firewalld.service               #开机不启动防火墙　　关闭selinux
# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config    #在/etc/selinux/config 把SELNUX=enforcing换成SELINUX=disabled
# grep SELINUX=disabled /etc/selinux/config       #过滤刚刚换的disabled
SELINUX=disabled
# setenforce 0                                #清空selinux的配置　　MySQL数据库的安装
tar xvf mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar
rpm -e --nodeps mariadb-libs
rpm -ivh mysql-community-client-5.7.20-1.el7.x86_64
rpm -ivh mysql-community-libs-5.7.20-1.el7.x86_64
rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64
rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64
rpm -ivh mysql-community-libs-compat-5.7.20-1.el7.x86_64
systemctl start mysqld  #修改密码
cat /var/log/mysqld.log | grep passwordsystemctl enable mysqld
alter user root@'localhost' identified by '';　　Zabbix Server 3.4安装及配置
　　wget http://repo.zabbix.com/zabbix/3.4/rhel/7/x86_64/zabbix-release-3.4-2.el7.noarch.rpm
　　rpm -ivh zabbix-release-3.4-2.el7.noarch.rpm
　　安装163的yum源，zabbix必须的php两个所需模块bcmath和mbstring  epel yum 源没有， 只有网易163源中有
　　wget http://mirrors.163.com/.help/CentOS7-Base-163.repo
　　# cat /etc/yum.repos.d/CentOS7-Base-163.repo
# CentOS-Base.repo
#
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client.  You should use this for CentOS updates
# unless you are manually picking other mirrors.
#
# If the mirrorlist= does not work for you, as a fall back you can try the
# remarked out baseurl= line instead.
#
#
[base]
name=CentOS-7 - Base - 163.com
#mirrorlist=http://mirrorlist.centos.org/?release=7&arch=$basearch&repo=os
baseurl=http://mirrors.163.com/centos/7/os/$basearch/
gpgcheck=1
gpgkey=http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7
#released updates
[updates]
name=CentOS-7 - Updates - 163.com
#mirrorlist=http://mirrorlist.centos.org/?release=7&arch=$basearch&repo=updates
baseurl=http://mirrors.163.com/centos/7/updates/$basearch/
gpgcheck=1
gpgkey=http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7
#additional packages that may be useful
[extras]
name=CentOS-7 - Extras - 163.com
#mirrorlist=http://mirrorlist.centos.org/?release=7&arch=$basearch&repo=extras
baseurl=http://mirrors.163.com/centos/7/extras/$basearch/
gpgcheck=1
gpgkey=http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7
#additional packages that extend functionality of existing packages
[centosplus]
name=CentOS-7 - Plus - 163.com
baseurl=http://mirrors.163.com/centos/7/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7　　# yum install zabbix-server-mysql zabbix-web-mysql zabbix-agent
　　# mysql -uroot -p
　　# password
　　mysql> create database zabbix character set utf8 collate utf8_bin;
　　mysql> grant all privileges on zabbix.* to zabbix@localhost identified by 'password';
　　mysql> quit;
　　# zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix  -D zabbix -p
　　配置zabbix数据库
　　# vi /etc/zabbix/zabbix_server.conf
　　DBHost=localhost
　　DBName=zabbix
　　DBUser=zabbix
　　DBPassword=password
　　# systemctl start zabbix-server zabbix-agent httpd
　　# systemctl enable zabbix-server zabbix-agent httpd
　　配置 PHP zabbix 前台
　　vim  /etc/httpd/conf.d/zabbix.conf
　　# php_value date.timezone Europe/Riga
　　替换为：
　　php_value date.timezone Asia/Shanghai
　　配置 zabbix前台
　　Connect to your newly installed Zabbix frontend: http://server_ip_or_name/zabbix
　　Follow steps described in Zabbix documentation: Installing frontend
　　zabbix  帮助文档
　　https://www.zabbix.com/documentation/3.4/manual/quickstart/login
　　二、zabbix agent 安装
　　wget http://repo.zabbix.com/zabbix/3.4/rhel/7/x86_64/zabbix-release-3.4-2.el7.noarch.rpm
　　rpm -ivh zabbix-release-3.4-2.el7.noarch.rpm
　　yum install  zabbix-agent
　　systemctl start zabbix-agent
　　systemctl enable zabbix-agent
　　客户端配置：
　　# cat /etc/zabbix/zabbix_agentd.conf  | grep -v "^#"| grep -v '^$'
PidFile=/var/run/zabbix/zabbix_agentd.pid
LogFile=/var/log/zabbix/zabbix_agentd.log
LogFileSize=0
Server=192.168.1.51
ServerActive=192.168.1.51:10051
HostnameItem=system.run[ifconfig | grep "inet" | grep -E -o "(2[0-4][0-9]|25[0-5]|1[0-9][0-9]|[1-9]?[0-9])(.(2[0-4][0-9]|25[0-5]|1[0-9][0-9]|[1-9]?[0-9])){3}"| grep "192.168.1"|head -1]
RefreshActiveChecks=120
Include=/etc/zabbix/zabbix_agentd.d/*.conf　　三、zabbix做分区
mysql> use zabbix;
mysql> alter table trends rename trends_bak;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `trends` (
       `itemid` bigint(20) unsigned NOT NULL,
       `clock` int(11) NOT NULL DEFAULT '0',
       `num` int(11) NOT NULL DEFAULT '0',
       `value_min` double(16,4) NOT NULL DEFAULT '0.0000',
       `value_avg` double(16,4) NOT NULL DEFAULT '0.0000',
       `value_max` double(16,4) NOT NULL DEFAULT '0.0000',
       PRIMARY KEY (`itemid`,`clock`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
     PARTITION BY RANGE (clock)
     (
      PARTITION p2018_02 VALUES LESS THAN (UNIX_TIMESTAMP("2018-03-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_03 VALUES LESS THAN (UNIX_TIMESTAMP("2018-04-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_04 VALUES LESS THAN (UNIX_TIMESTAMP("2018-05-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_05 VALUES LESS THAN (UNIX_TIMESTAMP("2018-06-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_06 VALUES LESS THAN (UNIX_TIMESTAMP("2018-07-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_07 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_08 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_09 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_10 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_11 VALUES LESS THAN (UNIX_TIMESTAMP("2018-12-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_12 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-01 00:00:00")) ENGINE = InnoDB);
Query OK, 0 rows affected (0.11 sec)

mysql> alter table trends_uint rename trends_uint_bak;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `trends_uint` (
       `itemid` bigint(20) unsigned NOT NULL,
       `clock` int(11) NOT NULL DEFAULT '0',
       `num` int(11) NOT NULL DEFAULT '0',
       `value_min` bigint(20) unsigned NOT NULL DEFAULT '0',
       `value_avg` bigint(20) unsigned NOT NULL DEFAULT '0',
       `value_max` bigint(20) unsigned NOT NULL DEFAULT '0',
       PRIMARY KEY (`itemid`,`clock`)
     )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
     PARTITION BY RANGE (clock)
     (PARTITION p2017_11 VALUES LESS THAN (UNIX_TIMESTAMP("2017-12-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2017_12 VALUES LESS THAN (UNIX_TIMESTAMP("2018-01-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_01 VALUES LESS THAN (UNIX_TIMESTAMP("2018-02-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_02 VALUES LESS THAN (UNIX_TIMESTAMP("2018-03-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_03 VALUES LESS THAN (UNIX_TIMESTAMP("2018-04-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_04 VALUES LESS THAN (UNIX_TIMESTAMP("2018-05-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_05 VALUES LESS THAN (UNIX_TIMESTAMP("2018-06-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_06 VALUES LESS THAN (UNIX_TIMESTAMP("2018-07-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_07 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_08 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_09 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_10 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_11 VALUES LESS THAN (UNIX_TIMESTAMP("2018-12-01 00:00:00")) ENGINE = InnoDB,
      PARTITION p2018_12 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-01 00:00:00")) ENGINE = InnoDB);
Query OK, 0 rows affected (0.13 sec)

mysql> alter table history rename history_bak;
Query OK, 0 rows affected (0.02 sec)
mysql> alter table history_uint rename history_uint_bak;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table history_text rename history_text_bak;
Query OK, 0 rows affected (0.01 sec)
mysql> alter table history_str rename history_str_bak;
Query OK, 0 rows affected (0.01 sec)
mysql> alter table history_log rename history_log_bak;
Query OK, 0 rows affected (0.01 sec)

mysql> 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_index` (`itemid`,`clock`,`value`,`ns`)
     )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
     PARTITION BY RANGE (clock)
     (PARTITION p2018_02_01 VALUES LESS THAN (1517500800) ENGINE = InnoDB,
      PARTITION p2018_02_02 VALUES LESS THAN (1517587200) ENGINE = InnoDB,
      PARTITION p2018_02_03 VALUES LESS THAN (1517673600) ENGINE = InnoDB,
      PARTITION p2018_02_04 VALUES LESS THAN (1517760000) ENGINE = InnoDB,
      PARTITION p2018_02_05 VALUES LESS THAN (1517846400) ENGINE = InnoDB,
      PARTITION p2018_02_06 VALUES LESS THAN (1517932800) ENGINE = InnoDB,
      PARTITION p2018_02_07 VALUES LESS THAN (1518019200) ENGINE = InnoDB,
      PARTITION p2018_02_08 VALUES LESS THAN (1518105600) ENGINE = InnoDB,
      PARTITION p2018_02_09 VALUES LESS THAN (1518192000) ENGINE = InnoDB,
      PARTITION p2018_02_10 VALUES LESS THAN (1518278400) ENGINE = InnoDB,
      PARTITION p2018_02_11 VALUES LESS THAN (1518364800) ENGINE = InnoDB,
      PARTITION p2018_02_12 VALUES LESS THAN (1518451200) ENGINE = InnoDB,
      PARTITION p2018_02_13 VALUES LESS THAN (1518537600) ENGINE = InnoDB,
      PARTITION p2018_02_14 VALUES LESS THAN (1518624000) ENGINE = InnoDB,
      PARTITION p2018_02_15 VALUES LESS THAN (1518710400) ENGINE = InnoDB,
      PARTITION p2018_02_16 VALUES LESS THAN (1518796800) ENGINE = InnoDB,
      PARTITION p2018_02_17 VALUES LESS THAN (1518883200) ENGINE = InnoDB,
      PARTITION p2018_02_18 VALUES LESS THAN (1518969600) ENGINE = InnoDB,
      PARTITION p2018_02_19 VALUES LESS THAN (1519056000) ENGINE = InnoDB,
      PARTITION p2018_02_20 VALUES LESS THAN (1519142400) ENGINE = InnoDB,
      PARTITION p2018_02_21 VALUES LESS THAN (1519228800) ENGINE = InnoDB,
      PARTITION p2018_02_22 VALUES LESS THAN (1519315200) ENGINE = InnoDB,
      PARTITION p2018_02_23 VALUES LESS THAN (1519401600) ENGINE = InnoDB,
      PARTITION p2018_02_24 VALUES LESS THAN (1519488000) ENGINE = InnoDB,
      PARTITION p2018_02_25 VALUES LESS THAN (1519574400) ENGINE = InnoDB,
      PARTITION p2018_02_26 VALUES LESS THAN (1519660800) ENGINE = InnoDB,
      PARTITION p2018_02_27 VALUES LESS THAN (1519747200) ENGINE = InnoDB,
      PARTITION p2018_02_28 VALUES LESS THAN (1519833600) ENGINE = InnoDB,
      PARTITION p2018_03_01 VALUES LESS THAN (1519920000) ENGINE = InnoDB,
      PARTITION p2018_03_02 VALUES LESS THAN (1520006400) ENGINE = InnoDB,
      PARTITION p2018_03_03 VALUES LESS THAN (1520092800) ENGINE = InnoDB,
      PARTITION p2018_03_04 VALUES LESS THAN (1520179200) ENGINE = InnoDB,
      PARTITION p2018_03_05 VALUES LESS THAN (1520265600) ENGINE = InnoDB,
      PARTITION p2018_03_06 VALUES LESS THAN (1520352000) ENGINE = InnoDB,
      PARTITION p2018_03_07 VALUES LESS THAN (1520438400) ENGINE = InnoDB,
      PARTITION p2018_03_08 VALUES LESS THAN (1520524800) ENGINE = InnoDB,
      PARTITION p2018_03_09 VALUES LESS THAN (1520611200) ENGINE = InnoDB,
      PARTITION p2018_03_10 VALUES LESS THAN (1520697600) ENGINE = InnoDB,
      PARTITION p2018_03_11 VALUES LESS THAN (1520784000) ENGINE = InnoDB,
      PARTITION p2018_03_12 VALUES LESS THAN (1520870400) ENGINE = InnoDB,
      PARTITION p2018_03_13 VALUES LESS THAN (1520956800) ENGINE = InnoDB,
      PARTITION p2018_03_14 VALUES LESS THAN (1521043200) ENGINE = InnoDB,
      PARTITION p2018_03_15 VALUES LESS THAN (1521129600) ENGINE = InnoDB,
      PARTITION p2018_03_16 VALUES LESS THAN (1521216000) ENGINE = InnoDB,
      PARTITION p2018_03_17 VALUES LESS THAN (1521302400) ENGINE = InnoDB,
      PARTITION p2018_03_18 VALUES LESS THAN (1521388800) ENGINE = InnoDB,
      PARTITION p2018_03_19 VALUES LESS THAN (1521475200) ENGINE = InnoDB,
      PARTITION p2018_03_20 VALUES LESS THAN (1521561600) ENGINE = InnoDB,
      PARTITION p2018_03_21 VALUES LESS THAN (1521648000) ENGINE = InnoDB,
      PARTITION p2018_03_22 VALUES LESS THAN (1521734400) ENGINE = InnoDB,
      PARTITION p2018_03_23 VALUES LESS THAN (1521820800) ENGINE = InnoDB,
      PARTITION p2018_03_24 VALUES LESS THAN (1521907200) ENGINE = InnoDB,
      PARTITION p2018_03_25 VALUES LESS THAN (1521993600) ENGINE = InnoDB,
      PARTITION p2018_03_26 VALUES LESS THAN (1522080000) ENGINE = InnoDB,
      PARTITION p2018_03_27 VALUES LESS THAN (1522166400) ENGINE = InnoDB,
      PARTITION p2018_03_28 VALUES LESS THAN (1522252800) ENGINE = InnoDB);
Query OK, 0 rows affected (0.67 sec)

mysql> CREATE TABLE `history_uint` (
       `itemid` bigint(20) unsigned NOT NULL,
       `clock` int(11) NOT NULL DEFAULT '0',
       `value` bigint(20) unsigned NOT NULL DEFAULT '0',
       `ns` int(11) NOT NULL DEFAULT '0',
       KEY `history_uint_index` (`itemid`,`clock`,`value`,`ns`)
     )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
     PARTITION BY RANGE (clock)
     (PARTITION p2018_02_01 VALUES LESS THAN (1517500800) ENGINE = InnoDB,
      PARTITION p2018_02_02 VALUES LESS THAN (1517587200) ENGINE = InnoDB,
      PARTITION p2018_02_03 VALUES LESS THAN (1517673600) ENGINE = InnoDB,
      PARTITION p2018_02_04 VALUES LESS THAN (1517760000) ENGINE = InnoDB,
      PARTITION p2018_02_05 VALUES LESS THAN (1517846400) ENGINE = InnoDB,
      PARTITION p2018_02_06 VALUES LESS THAN (1517932800) ENGINE = InnoDB,
      PARTITION p2018_02_07 VALUES LESS THAN (1518019200) ENGINE = InnoDB,
      PARTITION p2018_02_08 VALUES LESS THAN (1518105600) ENGINE = InnoDB,
      PARTITION p2018_02_09 VALUES LESS THAN (1518192000) ENGINE = InnoDB,
      PARTITION p2018_02_10 VALUES LESS THAN (1518278400) ENGINE = InnoDB,
      PARTITION p2018_02_11 VALUES LESS THAN (1518364800) ENGINE = InnoDB,
      PARTITION p2018_02_12 VALUES LESS THAN (1518451200) ENGINE = InnoDB,
      PARTITION p2018_02_13 VALUES LESS THAN (1518537600) ENGINE = InnoDB,
      PARTITION p2018_02_14 VALUES LESS THAN (1518624000) ENGINE = InnoDB,
      PARTITION p2018_02_15 VALUES LESS THAN (1518710400) ENGINE = InnoDB,
      PARTITION p2018_02_16 VALUES LESS THAN (1518796800) ENGINE = InnoDB,
      PARTITION p2018_02_17 VALUES LESS THAN (1518883200) ENGINE = InnoDB,
      PARTITION p2018_02_18 VALUES LESS THAN (1518969600) ENGINE = InnoDB,
      PARTITION p2018_02_19 VALUES LESS THAN (1519056000) ENGINE = InnoDB,
      PARTITION p2018_02_20 VALUES LESS THAN (1519142400) ENGINE = InnoDB,
      PARTITION p2018_02_21 VALUES LESS THAN (1519228800) ENGINE = InnoDB,
      PARTITION p2018_02_22 VALUES LESS THAN (1519315200) ENGINE = InnoDB,
      PARTITION p2018_02_23 VALUES LESS THAN (1519401600) ENGINE = InnoDB,
      PARTITION p2018_02_24 VALUES LESS THAN (1519488000) ENGINE = InnoDB,
      PARTITION p2018_02_25 VALUES LESS THAN (1519574400) ENGINE = InnoDB,
      PARTITION p2018_02_26 VALUES LESS THAN (1519660800) ENGINE = InnoDB,
      PARTITION p2018_02_27 VALUES LESS THAN (1519747200) ENGINE = InnoDB,
      PARTITION p2018_02_28 VALUES LESS THAN (1519833600) ENGINE = InnoDB,
      PARTITION p2018_03_01 VALUES LESS THAN (1519920000) ENGINE = InnoDB,
      PARTITION p2018_03_02 VALUES LESS THAN (1520006400) ENGINE = InnoDB,
      PARTITION p2018_03_03 VALUES LESS THAN (1520092800) ENGINE = InnoDB,
      PARTITION p2018_03_04 VALUES LESS THAN (1520179200) ENGINE = InnoDB,
      PARTITION p2018_03_05 VALUES LESS THAN (1520265600) ENGINE = InnoDB,
      PARTITION p2018_03_06 VALUES LESS THAN (1520352000) ENGINE = InnoDB,
      PARTITION p2018_03_07 VALUES LESS THAN (1520438400) ENGINE = InnoDB,
      PARTITION p2018_03_08 VALUES LESS THAN (1520524800) ENGINE = InnoDB,
      PARTITION p2018_03_09 VALUES LESS THAN (1520611200) ENGINE = InnoDB,
      PARTITION p2018_03_10 VALUES LESS THAN (1520697600) ENGINE = InnoDB,
      PARTITION p2018_03_11 VALUES LESS THAN (1520784000) ENGINE = InnoDB,
      PARTITION p2018_03_12 VALUES LESS THAN (1520870400) ENGINE = InnoDB,
      PARTITION p2018_03_13 VALUES LESS THAN (1520956800) ENGINE = InnoDB,
      PARTITION p2018_03_14 VALUES LESS THAN (1521043200) ENGINE = InnoDB,
      PARTITION p2018_03_15 VALUES LESS THAN (1521129600) ENGINE = InnoDB,
      PARTITION p2018_03_16 VALUES LESS THAN (1521216000) ENGINE = InnoDB,
      PARTITION p2018_03_17 VALUES LESS THAN (1521302400) ENGINE = InnoDB,
      PARTITION p2018_03_18 VALUES LESS THAN (1521388800) ENGINE = InnoDB,
      PARTITION p2018_03_19 VALUES LESS THAN (1521475200) ENGINE = InnoDB,
      PARTITION p2018_03_20 VALUES LESS THAN (1521561600) ENGINE = InnoDB,
      PARTITION p2018_03_21 VALUES LESS THAN (1521648000) ENGINE = InnoDB,
      PARTITION p2018_03_22 VALUES LESS THAN (1521734400) ENGINE = InnoDB,
      PARTITION p2018_03_23 VALUES LESS THAN (1521820800) ENGINE = InnoDB,
      PARTITION p2018_03_24 VALUES LESS THAN (1521907200) ENGINE = InnoDB,
      PARTITION p2018_03_25 VALUES LESS THAN (1521993600) ENGINE = InnoDB,
      PARTITION p2018_03_26 VALUES LESS THAN (1522080000) ENGINE = InnoDB,
      PARTITION p2018_03_27 VALUES LESS THAN (1522166400) ENGINE = InnoDB,
      PARTITION p2018_03_28 VALUES LESS THAN (1522252800) ENGINE = InnoDB);
Query OK, 0 rows affected (0.60 sec)

mysql>  CREATE TABLE `history_str` (
       `itemid` bigint(20) unsigned NOT NULL,
       `clock` int(11) NOT NULL DEFAULT '0',
       `value` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
       `ns` int(11) NOT NULL DEFAULT '0',
       KEY `history_str_1` (`itemid`,`clock`)
     )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
     PARTITION BY RANGE (clock)
     (PARTITION p2018_02_01 VALUES LESS THAN (1517500800) ENGINE = InnoDB,
      PARTITION p2018_02_02 VALUES LESS THAN (1517587200) ENGINE = InnoDB,
      PARTITION p2018_02_03 VALUES LESS THAN (1517673600) ENGINE = InnoDB,
      PARTITION p2018_02_04 VALUES LESS THAN (1517760000) ENGINE = InnoDB,
      PARTITION p2018_02_05 VALUES LESS THAN (1517846400) ENGINE = InnoDB,
      PARTITION p2018_02_06 VALUES LESS THAN (1517932800) ENGINE = InnoDB,
      PARTITION p2018_02_07 VALUES LESS THAN (1518019200) ENGINE = InnoDB,
      PARTITION p2018_02_08 VALUES LESS THAN (1518105600) ENGINE = InnoDB,
      PARTITION p2018_02_09 VALUES LESS THAN (1518192000) ENGINE = InnoDB,
      PARTITION p2018_02_10 VALUES LESS THAN (1518278400) ENGINE = InnoDB,
      PARTITION p2018_02_11 VALUES LESS THAN (1518364800) ENGINE = InnoDB,
      PARTITION p2018_02_12 VALUES LESS THAN (1518451200) ENGINE = InnoDB,
      PARTITION p2018_02_13 VALUES LESS THAN (1518537600) ENGINE = InnoDB,
      PARTITION p2018_02_14 VALUES LESS THAN (1518624000) ENGINE = InnoDB,
      PARTITION p2018_02_15 VALUES LESS THAN (1518710400) ENGINE = InnoDB,
      PARTITION p2018_02_16 VALUES LESS THAN (1518796800) ENGINE = InnoDB,
      PARTITION p2018_02_17 VALUES LESS THAN (1518883200) ENGINE = InnoDB,
      PARTITION p2018_02_18 VALUES LESS THAN (1518969600) ENGINE = InnoDB,
      PARTITION p2018_02_19 VALUES LESS THAN (1519056000) ENGINE = InnoDB,
      PARTITION p2018_02_20 VALUES LESS THAN (1519142400) ENGINE = InnoDB,
      PARTITION p2018_02_21 VALUES LESS THAN (1519228800) ENGINE = InnoDB,
      PARTITION p2018_02_22 VALUES LESS THAN (1519315200) ENGINE = InnoDB,
      PARTITION p2018_02_23 VALUES LESS THAN (1519401600) ENGINE = InnoDB,
      PARTITION p2018_02_24 VALUES LESS THAN (1519488000) ENGINE = InnoDB,
      PARTITION p2018_02_25 VALUES LESS THAN (1519574400) ENGINE = InnoDB,
      PARTITION p2018_02_26 VALUES LESS THAN (1519660800) ENGINE = InnoDB,
      PARTITION p2018_02_27 VALUES LESS THAN (1519747200) ENGINE = InnoDB,
      PARTITION p2018_02_28 VALUES LESS THAN (1519833600) ENGINE = InnoDB,
      PARTITION p2018_03_01 VALUES LESS THAN (1519920000) ENGINE = InnoDB,
      PARTITION p2018_03_02 VALUES LESS THAN (1520006400) ENGINE = InnoDB,
      PARTITION p2018_03_03 VALUES LESS THAN (1520092800) ENGINE = InnoDB,
      PARTITION p2018_03_04 VALUES LESS THAN (1520179200) ENGINE = InnoDB,
      PARTITION p2018_03_05 VALUES LESS THAN (1520265600) ENGINE = InnoDB,
      PARTITION p2018_03_06 VALUES LESS THAN (1520352000) ENGINE = InnoDB,
      PARTITION p2018_03_07 VALUES LESS THAN (1520438400) ENGINE = InnoDB,
      PARTITION p2018_03_08 VALUES LESS THAN (1520524800) ENGINE = InnoDB,
      PARTITION p2018_03_09 VALUES LESS THAN (1520611200) ENGINE = InnoDB,
      PARTITION p2018_03_10 VALUES LESS THAN (1520697600) ENGINE = InnoDB,
      PARTITION p2018_03_11 VALUES LESS THAN (1520784000) ENGINE = InnoDB,
      PARTITION p2018_03_12 VALUES LESS THAN (1520870400) ENGINE = InnoDB,
      PARTITION p2018_03_13 VALUES LESS THAN (1520956800) ENGINE = InnoDB,
      PARTITION p2018_03_14 VALUES LESS THAN (1521043200) ENGINE = InnoDB,
      PARTITION p2018_03_15 VALUES LESS THAN (1521129600) ENGINE = InnoDB,
      PARTITION p2018_03_16 VALUES LESS THAN (1521216000) ENGINE = InnoDB,
      PARTITION p2018_03_17 VALUES LESS THAN (1521302400) ENGINE = InnoDB,
      PARTITION p2018_03_18 VALUES LESS THAN (1521388800) ENGINE = InnoDB,
      PARTITION p2018_03_19 VALUES LESS THAN (1521475200) ENGINE = InnoDB,
      PARTITION p2018_03_20 VALUES LESS THAN (1521561600) ENGINE = InnoDB,
      PARTITION p2018_03_21 VALUES LESS THAN (1521648000) ENGINE = InnoDB,
      PARTITION p2018_03_22 VALUES LESS THAN (1521734400) ENGINE = InnoDB,
      PARTITION p2018_03_23 VALUES LESS THAN (1521820800) ENGINE = InnoDB,
      PARTITION p2018_03_24 VALUES LESS THAN (1521907200) ENGINE = InnoDB,
      PARTITION p2018_03_25 VALUES LESS THAN (1521993600) ENGINE = InnoDB,
      PARTITION p2018_03_26 VALUES LESS THAN (1522080000) ENGINE = InnoDB,
      PARTITION p2018_03_27 VALUES LESS THAN (1522166400) ENGINE = InnoDB,
      PARTITION p2018_03_28 VALUES LESS THAN (1522252800) ENGINE = InnoDB);
Query OK, 0 rows affected (0.60 sec)

mysql> CREATE TABLE `history_log` (
       `itemid` bigint(20) unsigned NOT NULL,
       `clock` int(11) NOT NULL DEFAULT '0',
       `timestamp` int(11) NOT NULL DEFAULT '0',
       `source` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
       `severity` int(11) NOT NULL DEFAULT '0',
       `value` text COLLATE utf8_bin NOT NULL,
       `logeventid` int(11) NOT NULL DEFAULT '0',
       `ns` int(11) NOT NULL DEFAULT '0',
       KEY `history_log_1` (`itemid`,`clock`)
     )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
     PARTITION BY RANGE (clock)
     (PARTITION p2018_02_01 VALUES LESS THAN (1517500800) ENGINE = InnoDB,
      PARTITION p2018_02_02 VALUES LESS THAN (1517587200) ENGINE = InnoDB,
      PARTITION p2018_02_03 VALUES LESS THAN (1517673600) ENGINE = InnoDB,
      PARTITION p2018_02_04 VALUES LESS THAN (1517760000) ENGINE = InnoDB,
      PARTITION p2018_02_05 VALUES LESS THAN (1517846400) ENGINE = InnoDB,
      PARTITION p2018_02_06 VALUES LESS THAN (1517932800) ENGINE = InnoDB,
      PARTITION p2018_02_07 VALUES LESS THAN (1518019200) ENGINE = InnoDB,
      PARTITION p2018_02_08 VALUES LESS THAN (1518105600) ENGINE = InnoDB,
      PARTITION p2018_02_09 VALUES LESS THAN (1518192000) ENGINE = InnoDB,
      PARTITION p2018_02_10 VALUES LESS THAN (1518278400) ENGINE = InnoDB,
      PARTITION p2018_02_11 VALUES LESS THAN (1518364800) ENGINE = InnoDB,
      PARTITION p2018_02_12 VALUES LESS THAN (1518451200) ENGINE = InnoDB,
      PARTITION p2018_02_13 VALUES LESS THAN (1518537600) ENGINE = InnoDB,
      PARTITION p2018_02_14 VALUES LESS THAN (1518624000) ENGINE = InnoDB,
      PARTITION p2018_02_15 VALUES LESS THAN (1518710400) ENGINE = InnoDB,
      PARTITION p2018_02_16 VALUES LESS THAN (1518796800) ENGINE = InnoDB,
      PARTITION p2018_02_17 VALUES LESS THAN (1518883200) ENGINE = InnoDB,
      PARTITION p2018_02_18 VALUES LESS THAN (1518969600) ENGINE = InnoDB,
      PARTITION p2018_02_19 VALUES LESS THAN (1519056000) ENGINE = InnoDB,
      PARTITION p2018_02_20 VALUES LESS THAN (1519142400) ENGINE = InnoDB,
      PARTITION p2018_02_21 VALUES LESS THAN (1519228800) ENGINE = InnoDB,
      PARTITION p2018_02_22 VALUES LESS THAN (1519315200) ENGINE = InnoDB,
      PARTITION p2018_02_23 VALUES LESS THAN (1519401600) ENGINE = InnoDB,
      PARTITION p2018_02_24 VALUES LESS THAN (1519488000) ENGINE = InnoDB,
      PARTITION p2018_02_25 VALUES LESS THAN (1519574400) ENGINE = InnoDB,
      PARTITION p2018_02_26 VALUES LESS THAN (1519660800) ENGINE = InnoDB,
      PARTITION p2018_02_27 VALUES LESS THAN (1519747200) ENGINE = InnoDB,
      PARTITION p2018_02_28 VALUES LESS THAN (1519833600) ENGINE = InnoDB,
      PARTITION p2018_03_01 VALUES LESS THAN (1519920000) ENGINE = InnoDB,
      PARTITION p2018_03_02 VALUES LESS THAN (1520006400) ENGINE = InnoDB,
      PARTITION p2018_03_03 VALUES LESS THAN (1520092800) ENGINE = InnoDB,
      PARTITION p2018_03_04 VALUES LESS THAN (1520179200) ENGINE = InnoDB,
      PARTITION p2018_03_05 VALUES LESS THAN (1520265600) ENGINE = InnoDB,
      PARTITION p2018_03_06 VALUES LESS THAN (1520352000) ENGINE = InnoDB,
      PARTITION p2018_03_07 VALUES LESS THAN (1520438400) ENGINE = InnoDB,
      PARTITION p2018_03_08 VALUES LESS THAN (1520524800) ENGINE = InnoDB,
      PARTITION p2018_03_09 VALUES LESS THAN (1520611200) ENGINE = InnoDB,
      PARTITION p2018_03_10 VALUES LESS THAN (1520697600) ENGINE = InnoDB,
      PARTITION p2018_03_11 VALUES LESS THAN (1520784000) ENGINE = InnoDB,
      PARTITION p2018_03_12 VALUES LESS THAN (1520870400) ENGINE = InnoDB,
      PARTITION p2018_03_13 VALUES LESS THAN (1520956800) ENGINE = InnoDB,
      PARTITION p2018_03_14 VALUES LESS THAN (1521043200) ENGINE = InnoDB,
      PARTITION p2018_03_15 VALUES LESS THAN (1521129600) ENGINE = InnoDB,
      PARTITION p2018_03_16 VALUES LESS THAN (1521216000) ENGINE = InnoDB,
      PARTITION p2018_03_17 VALUES LESS THAN (1521302400) ENGINE = InnoDB,
      PARTITION p2018_03_18 VALUES LESS THAN (1521388800) ENGINE = InnoDB,
      PARTITION p2018_03_19 VALUES LESS THAN (1521475200) ENGINE = InnoDB,
      PARTITION p2018_03_20 VALUES LESS THAN (1521561600) ENGINE = InnoDB,
      PARTITION p2018_03_21 VALUES LESS THAN (1521648000) ENGINE = InnoDB,
      PARTITION p2018_03_22 VALUES LESS THAN (1521734400) ENGINE = InnoDB,
      PARTITION p2018_03_23 VALUES LESS THAN (1521820800) ENGINE = InnoDB,
      PARTITION p2018_03_24 VALUES LESS THAN (1521907200) ENGINE = InnoDB,
      PARTITION p2018_03_25 VALUES LESS THAN (1521993600) ENGINE = InnoDB,
      PARTITION p2018_03_26 VALUES LESS THAN (1522080000) ENGINE = InnoDB,
      PARTITION p2018_03_27 VALUES LESS THAN (1522166400) ENGINE = InnoDB,
      PARTITION p2018_03_28 VALUES LESS THAN (1522252800) ENGINE = InnoDB);
Query OK, 0 rows affected (0.62 sec)

mysql>  CREATE TABLE `history_text` (
       `itemid` bigint(20) unsigned NOT NULL,
       `clock` int(11) NOT NULL DEFAULT '0',
       `value` text COLLATE utf8_bin NOT NULL,
       `ns` int(11) NOT NULL DEFAULT '0',
       KEY `history_text_1` (`itemid`,`clock`)
     )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
     PARTITION BY RANGE (clock)
     (PARTITION p2018_02_01 VALUES LESS THAN (1517500800) ENGINE = InnoDB,
      PARTITION p2018_02_02 VALUES LESS THAN (1517587200) ENGINE = InnoDB,
      PARTITION p2018_02_03 VALUES LESS THAN (1517673600) ENGINE = InnoDB,
      PARTITION p2018_02_04 VALUES LESS THAN (1517760000) ENGINE = InnoDB,
      PARTITION p2018_02_05 VALUES LESS THAN (1517846400) ENGINE = InnoDB,
      PARTITION p2018_02_06 VALUES LESS THAN (1517932800) ENGINE = InnoDB,
      PARTITION p2018_02_07 VALUES LESS THAN (1518019200) ENGINE = InnoDB,
      PARTITION p2018_02_08 VALUES LESS THAN (1518105600) ENGINE = InnoDB,
      PARTITION p2018_02_09 VALUES LESS THAN (1518192000) ENGINE = InnoDB,
      PARTITION p2018_02_10 VALUES LESS THAN (1518278400) ENGINE = InnoDB,
      PARTITION p2018_02_11 VALUES LESS THAN (1518364800) ENGINE = InnoDB,
      PARTITION p2018_02_12 VALUES LESS THAN (1518451200) ENGINE = InnoDB,
      PARTITION p2018_02_13 VALUES LESS THAN (1518537600) ENGINE = InnoDB,
      PARTITION p2018_02_14 VALUES LESS THAN (1518624000) ENGINE = InnoDB,
      PARTITION p2018_02_15 VALUES LESS THAN (1518710400) ENGINE = InnoDB,
      PARTITION p2018_02_16 VALUES LESS THAN (1518796800) ENGINE = InnoDB,
      PARTITION p2018_02_17 VALUES LESS THAN (1518883200) ENGINE = InnoDB,
      PARTITION p2018_02_18 VALUES LESS THAN (1518969600) ENGINE = InnoDB,
      PARTITION p2018_02_19 VALUES LESS THAN (1519056000) ENGINE = InnoDB,
      PARTITION p2018_02_20 VALUES LESS THAN (1519142400) ENGINE = InnoDB,
      PARTITION p2018_02_21 VALUES LESS THAN (1519228800) ENGINE = InnoDB,
      PARTITION p2018_02_22 VALUES LESS THAN (1519315200) ENGINE = InnoDB,
      PARTITION p2018_02_23 VALUES LESS THAN (1519401600) ENGINE = InnoDB,
      PARTITION p2018_02_24 VALUES LESS THAN (1519488000) ENGINE = InnoDB,
      PARTITION p2018_02_25 VALUES LESS THAN (1519574400) ENGINE = InnoDB,
      PARTITION p2018_02_26 VALUES LESS THAN (1519660800) ENGINE = InnoDB,
      PARTITION p2018_02_27 VALUES LESS THAN (1519747200) ENGINE = InnoDB,
      PARTITION p2018_02_28 VALUES LESS THAN (1519833600) ENGINE = InnoDB,
      PARTITION p2018_03_01 VALUES LESS THAN (1519920000) ENGINE = InnoDB,
      PARTITION p2018_03_02 VALUES LESS THAN (1520006400) ENGINE = InnoDB,
      PARTITION p2018_03_03 VALUES LESS THAN (1520092800) ENGINE = InnoDB,
      PARTITION p2018_03_04 VALUES LESS THAN (1520179200) ENGINE = InnoDB,
      PARTITION p2018_03_05 VALUES LESS THAN (1520265600) ENGINE = InnoDB,
      PARTITION p2018_03_06 VALUES LESS THAN (1520352000) ENGINE = InnoDB,
      PARTITION p2018_03_07 VALUES LESS THAN (1520438400) ENGINE = InnoDB,
      PARTITION p2018_03_08 VALUES LESS THAN (1520524800) ENGINE = InnoDB,
      PARTITION p2018_03_09 VALUES LESS THAN (1520611200) ENGINE = InnoDB,
      PARTITION p2018_03_10 VALUES LESS THAN (1520697600) ENGINE = InnoDB,
      PARTITION p2018_03_11 VALUES LESS THAN (1520784000) ENGINE = InnoDB,
      PARTITION p2018_03_12 VALUES LESS THAN (1520870400) ENGINE = InnoDB,
      PARTITION p2018_03_13 VALUES LESS THAN (1520956800) ENGINE = InnoDB,
      PARTITION p2018_03_14 VALUES LESS THAN (1521043200) ENGINE = InnoDB,
      PARTITION p2018_03_15 VALUES LESS THAN (1521129600) ENGINE = InnoDB,
      PARTITION p2018_03_16 VALUES LESS THAN (1521216000) ENGINE = InnoDB,
      PARTITION p2018_03_17 VALUES LESS THAN (1521302400) ENGINE = InnoDB,
      PARTITION p2018_03_18 VALUES LESS THAN (1521388800) ENGINE = InnoDB,
      PARTITION p2018_03_19 VALUES LESS THAN (1521475200) ENGINE = InnoDB,
      PARTITION p2018_03_20 VALUES LESS THAN (1521561600) ENGINE = InnoDB,
      PARTITION p2018_03_21 VALUES LESS THAN (1521648000) ENGINE = InnoDB,
      PARTITION p2018_03_22 VALUES LESS THAN (1521734400) ENGINE = InnoDB,
      PARTITION p2018_03_23 VALUES LESS THAN (1521820800) ENGINE = InnoDB,
      PARTITION p2018_03_24 VALUES LESS THAN (1521907200) ENGINE = InnoDB,
      PARTITION p2018_03_25 VALUES LESS THAN (1521993600) ENGINE = InnoDB,
      PARTITION p2018_03_26 VALUES LESS THAN (1522080000) ENGINE = InnoDB,
      PARTITION p2018_03_27 VALUES LESS THAN (1522166400) ENGINE = InnoDB,
      PARTITION p2018_03_28 VALUES LESS THAN (1522252800) ENGINE = InnoDB);
Query OK, 0 rows affected (0.62 sec)　　配置分区策略，采用外部分区的方式
　　yum install perl-Sys-Syslog
　　yum install per-DateTime
　　yum install perl-DateTime
　　yum install perl-DBD-MySQL
　　vim /etc/rsyslog.conf
  local6.*                                                /var/log/zabbix-partition.log　　

　　systemctl restart  rsyslog
　　cat  /var/lib/mysql/mysql_partition.pl
#!/usr/bin/perl
use strict;
use Data::Dumper;
use DBI;
use Sys::Syslog qw(:standard :macros);
use DateTime;
use POSIX qw(strftime);
openlog("mysql_zbx_part", "ndelay,pid", LOG_LOCAL6);
my $db_schema = 'zabbix';
my $dsn = 'DBI:mysql:'.$db_schema.':mysql_socket=/data/mysql/mysql.sock';
my $db_user_name = 'zabbix';
my $db_password = 'i5$4)Ci10(sn';
my $tables = {'history' => { 'period' => 'day', 'keep_history' => '60'},
'history_log' => { 'period' => 'day', 'keep_history' => '60'},
'history_str' => { 'period' => 'day', 'keep_history' => '60'},
'history_text' => { 'period' => 'day', 'keep_history' => '60'},
'history_uint' => { 'period' => 'day', 'keep_history' => '60'},
'trends' => { 'period' => 'month', 'keep_history' => '3'},
'trends_uint' => { 'period' => 'month', 'keep_history' => '3'},
# comment next 5 lines if you partition zabbix database starting from 2.2
# they usually used for zabbix database before 2.2
     #'acknowledges' => { 'period' => 'month', 'keep_history' => '23'},
     #'alerts' => { 'period' => 'month', 'keep_history' => '6'},
     #'auditlog' => { 'period' => 'month', 'keep_history' => '24'},
     #  'events' => { 'period' => 'month', 'keep_history' => '12'},
     #  'service_alarms' => { 'period' => 'month', 'keep_history' => '6'},
};
my $amount_partitions = 30;
my $curr_tz = 'Asia/Shanghai';
my $part_tables;
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);
unless ( check_have_partition() ) {
print "Your installation of MySQL does not support table partitioning.\n";
syslog(LOG_CRIT, 'Your installation of MySQL does not support table partitioning.');
exit 1;
}
my $sth = $dbh->prepare(qq{SELECT table_name, partition_name, lower(partition_method) as partition_method,
rtrim(ltrim(partition_expression)) as partition_expression,
partition_description, table_rows
FROM information_schema.partitions
WHERE partition_name IS NOT NULL AND table_schema = ?});
$sth->execute($db_schema);
while (my $row =  $sth->fetchrow_hashref()) {
$part_tables->{$row->{'table_name'}}->{$row->{'partition_name'}} = $row;
}
$sth->finish();
foreach my $key (sort keys %{$tables}) {
unless (defined($part_tables->{$key})) {
syslog(LOG_ERR, 'Partitioning for "'.$key.'" is not found! The table might be not partitioned.');
next;
}
create_next_partition($key, $part_tables->{$key}, $tables->{$key}->{'period'});
remove_old_partitions($key, $part_tables->{$key}, $tables->{$key}->{'period'}, $tables->{$key}->{'keep_history'})
}
delete_old_data();
$dbh->disconnect();
sub check_have_partition {
my $result = 0;
# MySQL 5.5
   #my $sth = $dbh->prepare(qq{SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'have_partitioning'});
# MySQL 5.6
my $sth = $dbh->prepare(qq{SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition'});
$sth->execute();
my $row = $sth->fetchrow_array();
$sth->finish();
# MySQL 5.5
#return 1 if $row eq 'YES';
# MySQL 5.6
return 1 if $row eq 'ACTIVE';
}
sub create_next_partition {
my $table_name = shift;
my $table_part = shift;
my $period = shift;
for (my $curr_part = 0; $curr_part < $amount_partitions; $curr_part++) {
my $next_name = name_next_part($tables->{$table_name}->{'period'}, $curr_part);
my $found = 0;
foreach my $partition (sort keys %{$table_part}) {
if ($next_name eq $partition) {
syslog(LOG_INFO, &quot;Next partition for $table_name table has already been created. It is $next_name&quot;);
$found = 1;
}
}
if ( $found == 0 ) {
syslog(LOG_INFO, &quot;Creating a partition for $table_name table ($next_name)&quot;);
my $query = 'ALTER TABLE '.&quot;$db_schema.$table_name&quot;.' ADD PARTITION (PARTITION '.$next_name.
' VALUES less than (UNIX_TIMESTAMP(&quot;'.date_next_part($tables->{$table_name}->{'period'}, $curr_part).'&quot;) div 1))';
syslog(LOG_DEBUG, $query);
$dbh->do($query);
}
}
}
sub remove_old_partitions {
my $table_name = shift;
my $table_part = shift;
my $period = shift;
my $keep_history = shift;
my $curr_date = DateTime->now;
$curr_date->set_time_zone( $curr_tz );
if ( $period eq 'day' ) {
$curr_date->add(days => -$keep_history);
$curr_date->add(hours => -$curr_date->strftime('%H'));
$curr_date->add(minutes => -$curr_date->strftime('%M'));
$curr_date->add(seconds => -$curr_date->strftime('%S'));
}
elsif ( $period eq 'week' ) {
}
elsif ( $period eq 'month' ) {
$curr_date->add(months => -$keep_history);
$curr_date->add(days => -$curr_date->strftime('%d')+1);
$curr_date->add(hours => -$curr_date->strftime('%H'));
$curr_date->add(minutes => -$curr_date->strftime('%M'));
$curr_date->add(seconds => -$curr_date->strftime('%S'));
}
foreach my $partition (sort keys %{$table_part}) {
if ($table_part->{$partition}->{'partition_description'} epoch) {
syslog(LOG_INFO, &quot;Removing old $partition partition from $table_name table&quot;);
my $query = &quot;ALTER TABLE $db_schema.$table_name DROP PARTITION $partition&quot;;
syslog(LOG_DEBUG, $query);
$dbh->do($query);
}
}
}
sub name_next_part {
my $period = shift;
my $curr_part = shift;
my $name_template;
my $curr_date = DateTime->now;
$curr_date->set_time_zone( $curr_tz );
if ( $period eq 'day' ) {
my $curr_date = $curr_date->truncate( to => 'day' );
$curr_date->add(days => 1 + $curr_part);
$name_template = $curr_date->strftime('p%Y_%m_%d');
}
elsif ($period eq 'week') {
my $curr_date = $curr_date->truncate( to => 'week' );
$curr_date->add(days => 7 * $curr_part);
$name_template = $curr_date->strftime('p%Y_%m_w%W');
}
elsif ($period eq 'month') {
my $curr_date = $curr_date->truncate( to => 'month' );
$curr_date->add(months => 1 + $curr_part);
$name_template = $curr_date->strftime('p%Y_%m');
}
return $name_template;
}
sub date_next_part {
my $period = shift;
my $curr_part = shift;
my $period_date;
my $curr_date = DateTime->now;
$curr_date->set_time_zone( $curr_tz );
if ( $period eq 'day' ) {
my $curr_date = $curr_date->truncate( to => 'day' );
$curr_date->add(days => 2 + $curr_part);
$period_date = $curr_date->strftime('%Y-%m-%d');
}
elsif ($period eq 'week') {
my $curr_date = $curr_date->truncate( to => 'week' );
$curr_date->add(days => 7 * $curr_part + 1);
$period_date = $curr_date->strftime('%Y-%m-%d');
}
elsif ($period eq 'month') {
my $curr_date = $curr_date->truncate( to => 'month' );
$curr_date->add(months => 2 + $curr_part);
$period_date = $curr_date->strftime('%Y-%m-%d');
}
return $period_date;
}
sub delete_old_data {
$dbh->do(&quot;DELETE FROM sessions WHERE lastaccess < UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH)&quot;);
$dbh->do(&quot;TRUNCATE housekeeper&quot;);
$dbh->do(&quot;DELETE FROM auditlog_details WHERE NOT EXISTS (SELECT NULL FROM auditlog WHERE auditlog.auditid = auditlog_details.auditid)&quot;);
}　　chmod +x mysql_partition.pl
　　yum install ntpdate
　　crontab -l
　　*/5 * * * * /usr/sbin/ntpdate ntp.api.bz
　　20 11  * * * /var/lib/mysql/scripts/mysql_partition.pl
　　定期备份配置数据
mysqldump  -uroot -p  --single-transaction -R  --default-character-set=utf8 --triggers --events  --hex-blob    --databases zabbix   --ignore-table=zabbix.history  --ignore-table=zabbix.history_uint  --ignore-table=zabbix.history_log  --ignore-table=zabbix.history_str --ignore-table=zabbix.history_text --ignore-table=zabbix.trends --ignore-table=zabbix.trends_uint > zabbix_`date`.sql　　关闭 housekeeper ，防止出现select itemid,min(clock) from history_uint group by itemid 语句 ，改语句执行非常的慢，对数据库的插入性能，系统的负载等有非常大影响
　　关闭方法：
　　Administration -> General -> Housekeeping.
　　关闭history ，trends表的清理策略
　　systemctl restart zabbix-server
　　zabbix的端口
　　tcp6       0      0 :::10050                :::*                    LISTEN      1248/zabbix_agentd
　　tcp6       0      0 :::10051                :::*                    LISTEN      1974/zabbix_server
　　zabbix的日志
　　# ll /var/log/zabbix/*  #日志体系
　　-rw-rw-r--  1 zabbix zabbix 18578 Feb 28 18:46 /var/log/zabbix/zabbix_agentd.log
　　-rw-rw-r--. 1 zabbix zabbix 79759 Feb 28 18:18 /var/log/zabbix/zabbix_server.log
　　参考：
　　http://www.zabbix.org/wiki/Docs/howto/mysql_partitioning
　　http://www.zabbix.org/wiki/Docs/howto/mysql_partition
　　常用的语句：
　　select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='history';




运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表，该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有，请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意，请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息，一经发现立即删除。若您因此触犯法律，一切后果自负，我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集，我们仅提供一个展示、介绍、观摩学习的平台，我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责，亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏，不得用于商业或者其他用途，否则，一切后果均由您自己承担，我们对此不承担任何法律责任
7、如涉及侵犯版权等问题，请您及时通知我们，我们将立即采取措施予以解决
8、联系人Email：admin@iyunv.com 网址：www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址：https://www.yunweiku.com/thread-665142-1-1.html 上篇帖子： 处理Zabbix历史数据库办法二 下篇帖子： Zabbix3.4使用自带模板监控MySQL
回复

使用道具

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群，最新一手资源尽在官方微信交流群！快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:iyunvcom@gmail.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

豫ICP备20007574号-1 Copyright © 2012-2025

关于我们

发展历程

联系我们

关注我们

广告服务

广告案例

隐私条款

免责声明

用户登录

用户注册

版主守则

申请版主

手机版

金币获取

帮助中心

每日签到

Good Good

Study

Day Day

UP


客服E-mail:kefu@iyunv.com 客服QQ：1061981298


QQ群⑦：运维网交流群⑦ QQ群⑧：运维网交流群⑧ k8s群：运维网kubernetes交流群


提醒：禁止发布任何违反国家法律、法规的言论与图片等内容；本站内容均来自个人观点与网络等信息，非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来，其版权均归原作者及其网站所有，我们尊重他人的合法权益，如有内容侵犯您的合法权益，请及时与我们联系进行核实删除！



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表