您想学习如何执行Zabbix数据库分区吗？ 在本教程中，我们将向您展示如何在运行Ubuntu Linux的计算机上对Zabbix MySQL数据库进行分区。

•Zabbix版本：4.0.5
•Linux：Ubuntu 18.04
•MySQL：Ver 14.14 Distrib 5.7.25

我们的教程考虑了全新的Zabbix安装。

硬件清单：

以下部分介绍了用于创建此Zabbix教程的设备列表。

上面列出的每一件硬件都可以在亚马逊网站上找到。

Zabbix Playlist:

在此页面上，我们可以快速访问与Zabbix安装相关的视频列表。

不要忘记订阅我们命名的youtube频道 FKIT.

Zabbix相关教程：

在此页面上，我们提供了与Zabbix安装相关的教程列表的快速访问。

教程 - Zabbix数据库分区

使用以下命令安装所需的软件。

# apt-get update
# apt-get install perl libdbi-perl libdatetime-perl libdbd-mysql-perl

我们需要从Zabbix数据库中划分以下表：

• history
• history_log
• history_str
• history_text
• history_uint
• trends
• trends_uint

您的Zabbix服务器可能已经在其数据库中拥有监控信息。

访问MySQL控制台并验证上面列出的7个表的Zabbix最早的输入日期：

# mysql -u root -p

use zabbix;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_log`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_str`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_text`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_uint`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `trends`;
SELECT FROM_UNIXTIME(MIN(clock)) FROM `trends_uint`;

对于这些MySQL命令中的每一个，输出应该类似于：

+---------------------------------------+
| FROM_UNIXTIME(MIN(clock)) |
+---------------------------------------+
| 2019-03-22 17:48:07                       |
+---------------------------------------+
1 row in set (0.00 sec)

重要！ 记下每个表格显示的日期。

现在，让我们按日期对每个历史表进行分区：

首先，让我们对History表进行分区。

# mysql -u root -p
# use zabbix;

ALTER TABLE `history` PARTITION BY RANGE ( clock)
(PARTITION p2019_03_22 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-23 00:00:00")) ENGINE = InnoDB);

在我们的示例中，名为p2019_03_22的文件将在2019-03-23之前包含所有MySQL信息。

如果您的目标日期是2019-03-22，则需要在2019-03-23之前输入所有内容。

其次，让我们对history_log表进行分区。

ALTER TABLE `history_log` PARTITION BY RANGE ( clock)
(PARTITION p2019_03_22 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-23 00:00:00")) ENGINE = InnoDB);

第三，让我们对history_str表进行分区。

ALTER TABLE `history_str` PARTITION BY RANGE ( clock)
(PARTITION p2019_03_22 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-23 00:00:00")) ENGINE = InnoDB);

第四，让我们对history_text表进行分区。

ALTER TABLE `history_text` PARTITION BY RANGE ( clock)
(PARTITION p2019_03_22 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-23 00:00:00")) ENGINE = InnoDB);

第五，让我们对history_uint表进行分区。

ALTER TABLE `history_uint` PARTITION BY RANGE ( clock)
(PARTITION p2019_03_22 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-23 00:00:00")) ENGINE = InnoDB);

现在，让我们按月对每个趋势表进行分区：

首先，让我们对Trends表进行分区。

ALTER TABLE `trends` PARTITION BY RANGE ( clock)
(PARTITION p2019_03 VALUES LESS THAN (UNIX_TIMESTAMP("2019-04-01 00:00:00")) ENGINE = InnoDB);

在我们的示例中，名为p2019_03的文件将在2019-04-01之前包含所有MySQL信息。

如果您的目标日期是2019-03，则需要在2019-04-01之前输入所有内容。

其次，让我们对trends_uint表进行分区。

ALTER TABLE `trends_uint` PARTITION BY RANGE ( clock)
(PARTITION p2019_03 VALUES LESS THAN (UNIX_TIMESTAMP("2019-04-01 00:00:00")) ENGINE = InnoDB);

Zabbix数据库分区已成功创建。

教程 - Zabbix数据库分区脚本

我们将使用Perl脚本执行以下任务：

提前创建10天的MySQL数据库分区，以防止服务停止。

删除不再需要的旧MySQL分区。

# mkdir /downloads/zabbix_script
# cd /downloads/zabbix_script
# vi zabbix_partition_creator.pl

以下是zabbix_partition_creator.pl脚本的内容：

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_LOCAL0);
my $db_schema = 'zabbix';
my $dsn = 'DBI:mysql:'.$db_schema.':mysql_socket=/var/run/mysqld/mysqld.sock';
my $db_user_name = 'zabbix';
my $db_password = 'kamisama123';
my $tables = { 'history' => { 'period' => 'day', 'keep_history' => '30'},
'history_log' => { 'period' => 'day', 'keep_history' => '30'},
'history_str' => { 'period' => 'day', 'keep_history' => '30'},
'history_text' => { 'period' => 'day', 'keep_history' => '30'},
'history_uint' => { 'period' => 'day', 'keep_history' => '30'},
'trends' => { 'period' => 'month', 'keep_history' => '2'},
'trends_uint' => { 'period' => 'month', 'keep_history' => '2'},
};
my $amount_partitions = 10;
my $curr_tz = 'America/Sao_Paulo';
my $part_tables;
my $dbh = DBI->connect($dsn, $db_user_name, $db_password, {'ShowErrorStatement' => 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 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, "Next partition for $table_name table has already been created. It is $next_name");
$found = 1;
}
}
if ( $found == 0 ) {
syslog(LOG_INFO, "Creating a partition for $table_name table ($next_name)");
my $query = 'ALTER TABLE '."$db_schema.$table_name".' ADD PARTITION (PARTITION '.$next_name.
' VALUES less than (UNIX_TIMESTAMP("'.date_next_part($tables->{$table_name}->{'period'}, $curr_part).'") 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'} <= $curr_date->epoch) {
syslog(LOG_INFO, "Removing old $partition partition from $table_name table");
my $query = "ALTER TABLE $db_schema.$table_name DROP PARTITION $partition";
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("DELETE FROM sessions WHERE lastaccess < UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH)");
$dbh->do("TRUNCATE housekeeper");
$dbh->do("DELETE FROM auditlog_details WHERE NOT EXISTS (SELECT NULL FROM auditlog WHERE auditlog.auditid = auditlog_details.auditid)");
}

您需要更改脚本的以下部分以反映您的MySQL环境。

my $db_schema = 'zabbix';
my $dsn = 'DBI:mysql:'.$db_schema.':mysql_socket=/var/run/mysqld/mysqld.sock';
my $db_user_name = 'zabbix';
my $db_password = 'kamisama123';
my $tables = { 'history' => { 'period' => 'day', 'keep_history' => '30'},
'history_log' => { 'period' => 'day', 'keep_history' => '30'},
'history_str' => { 'period' => 'day', 'keep_history' => '30'},
'history_text' => { 'period' => 'day', 'keep_history' => '30'},
'history_uint' => { 'period' => 'day', 'keep_history' => '30'},
'trends' => { 'period' => 'month', 'keep_history' => '2'},
'trends_uint' => { 'period' => 'month', 'keep_history' => '2'},
};
my $amount_partitions = 10;
my $curr_tz = 'America/Sao_Paulo';

在我们的示例中，我们使用zabbix MySQL用户名和kamisama123 MySQL密码。

在我们的示例中，我们保留了30天的历史记录表内容。

在我们的示例中，我们保留了2个月的趋势表内容。

在我们的示例中，我们设置了America / Sao_Paulo日期时区。

现在，您需要手动运行脚本而不会出现错误。

# cd /downloads/zabbix_script
# chmod 700 zabbix_partition_creator.pl
# ./zabbix_partition_creator.pl

访问MySQL控制台并验证系统是否提前创建了10天的历史记录分区。

# mysql -u root -p

use zabbix;
show create table history;

/*!50100 PARTITION BY RANGE ( clock)
(PARTITION p2019_03_22 VALUES LESS THAN (1553310000) ENGINE = InnoDB,
PARTITION p2019_03_23 VALUES LESS THAN (1553396400) ENGINE = InnoDB,
PARTITION p2019_03_24 VALUES LESS THAN (1553482800) ENGINE = InnoDB,
PARTITION p2019_03_25 VALUES LESS THAN (1553569200) ENGINE = InnoDB,
PARTITION p2019_03_26 VALUES LESS THAN (1553655600) ENGINE = InnoDB,
PARTITION p2019_03_27 VALUES LESS THAN (1553742000) ENGINE = InnoDB,
PARTITION p2019_03_28 VALUES LESS THAN (1553828400) ENGINE = InnoDB,
PARTITION p2019_03_29 VALUES LESS THAN (1553914800) ENGINE = InnoDB,
PARTITION p2019_03_30 VALUES LESS THAN (1554001200) ENGINE = InnoDB,
PARTITION p2019_03_31 VALUES LESS THAN (1554087600) ENGINE = InnoDB,
PARTITION p2019_04_01 VALUES LESS THAN (1554174000) ENGINE = InnoDB) */

访问MySQL控制台并验证系统是否提前创建了10个月的趋势分区。

# mysql -u root -p

use zabbix;
show create table trends;

/*!50100 PARTITION BY RANGE ( clock)
(PARTITION p2019_03 VALUES LESS THAN (1554087600) ENGINE = InnoDB,
PARTITION p2019_04 VALUES LESS THAN (1556679600) ENGINE = InnoDB,
PARTITION p2019_05 VALUES LESS THAN (1559358000) ENGINE = InnoDB,
PARTITION p2019_06 VALUES LESS THAN (1561950000) ENGINE = InnoDB,
PARTITION p2019_07 VALUES LESS THAN (1564628400) ENGINE = InnoDB,
PARTITION p2019_08 VALUES LESS THAN (1567306800) ENGINE = InnoDB,
PARTITION p2019_09 VALUES LESS THAN (1569898800) ENGINE = InnoDB,
PARTITION p2019_10 VALUES LESS THAN (1572577200) ENGINE = InnoDB,
PARTITION p2019_11 VALUES LESS THAN (1575165600) ENGINE = InnoDB,
PARTITION p2019_12 VALUES LESS THAN (1577844000) ENGINE = InnoDB,
PARTITION p2020_01 VALUES LESS THAN (1580522400) ENGINE = InnoDB) */ |

现在，让我们安排Perl脚本每天运行。

# crontab -e

1  0  *  *  * /downloads/scripts/zabbix_partition_creator.pl

恭喜！ 您已经对Zabbix MySQL数据库表进行了分区。