Партиционирование базы данных MySQL Partitioning Zabbix

[Артём Мамзиков]

phpBB [video]

Партиция базы данных Заббикса.
Если Заббикс начинает: тормозить, выпадать в ошибки, ему не хватает памяти, слишком долго работает хаусскипер housekeeper (дом работница))) очистка базы данных истории. Нужно что то делать наращивать мощность железа ставить более производительный жесткий диск.
Либо попробовать сделать Партицию базы данных.
Секционирование (англ. partitioning) — разделение хранимых объектов баз данных
(таких как таблиц, индексов, материализованных представлений)
на отдельные части с раздельными параметрами физического хранения

Подготовка проверка настройка mysql
Резервное копирование и дамп таблиц
Разбивка партицирование вручную для текущей уже истории
Загрузка истории из дампа уже в партицированые таблицы
Выбор способа дальнейшего автоматического партицирования и удаление истории
1 способ создание таблицы manage_partitions в mysql zabbixdb по ней будут работать процедуры и событие, создание 5 процедур и 1 событие в mysql
2 Способ скрипт на perl добавленный в cron

Проверены оба способа на версии Zabbix 4.2.1

Ниже будем работать со следующим таблицами (количество таблиц и как часто будут создаваться и хранится файлы можете задать на свое усмотрение)
файл на каждый 1 день (8 таблиц) (срок хранения истории 90 дней)
history
history_text
history_uint
history_log
history_str
trends_uint
trends
events

файл на каждый 1 месяц (4 таблицы) (срок хранения истории 90 дней)
service_alarms
acknowledges
alerts
auditlog

Выше версии 2.2 указываю что следующие таблицы можно не разбивать я все же их разбил

Показать

events
service_alarms
acknowledges
alerts
auditlog
Если эти таблицы занимают у вас не много места и вы их не будете трогать, вам ненужно выполнять пункт
изменить первичный ключ на индекс и удалить зависимости идущих от других таблиц на таблицы которые нам нужно разбить, иначе выйдет ошибка и не даст нам произвести очистку и разбитие таблиц на партацию!!!
И другие команды связанные с данными таблицами!!!

так же изменим таблицу housekeeper

Как работает стандартная очистка housekeeper

Показать

Например у нас мониторится пинг каждые 30 секунд, значит каждые 30 секунд будет сохранятся значение в базу данных в историю. И так с каждого элемента данных, а таких элементов может быть не одна тысяча и время запроса разное очень критичные каждую секунду какие то раз в 30 минут и тп. Данные хранятся в виде количества секунд, прошедших с начала «эпохи Юникса». Диапазон значений: 1970-01-01 00:00:00 Есть конвектор онлайн из секунд в дату или онлайн из секунд в дату 2. А теперь представим Очистка истории ищет в таблицах устаревшее время и удалят старые данные за уже не нужный период времени истории в этот же момент пишется куча новых данных в эти же таблицы. Очистка работает медленно. если железо слабое то может длится не один день и просто не успевать чистить старое с появлением нового.
Все базы и таблицы хранятся в одном файле /var/lib/mysql/ibdata1
Просмотреть размер можно командами
cd /var/lib/mysql/
ls -l --block-size=G

После очистки таблиц файл ibdata1 не уменьшается в размерах а просто освобождается внутри для новых данных!

В файле конфигурации Заббикс сервера nano /usr/local/etc/zabbix_server.conf есть 2 параметра отвечающие за очистку таблиц истории в базе данных.
Например у меня было так
HousekeepingFrequency=24 (запуск каждые 24 часа)
MaxHousekeeperDelete=0 (без ограничение на удаление)

Опция: HousekeepingFrequency
Как часто Zabbix будет выполнять очистку (в часах) устаревшую информацию из базы данных.
Чтобы предотвратить перегрузку Housekeeper, не более 4 раз часов устаревшей информации удаляются за один цикл обслуживания для каждого элемента. Для снижения нагрузки на запуск сервера очистка откладывается на 30 минут после запуска сервера.
При HousekeepingFrequency = 0 может быть выполнена только с использованием параметра управления во время выполнения (т е запуск вручную командой или из крона).
В этом случае период устаревшей информации, удаленной за один цикл обслуживания, в 4 раза период с последнего цикла ведения очистки, но не менее 4 часов и не более 4 дней.
Обязательно: нет
Диапазон: 0-24
HousekeepingFrequency = 24

Опция: MaxHousekeeperDelete
Таблица «Очистка» содержит «задания» в формате:
[housekeeper id], [Имя таблицы], [поле], [значение].
Не более чем строки 'MaxHousekeeperDelete' (соответствует [Имя таблицы], [поле], [значение])
будет удалено за одну задачу за один цикл обслуживания.
Если установлено значение 0, то ограничение не используется вообще. В этом случае вы должны знать, что вы делаете!
Обязательно: нет
Диапазон: 0-1000000
По умолчанию:
# MaxHousekeeperDelete = 5000
MaxHousekeeperDelete = 0

Еще есть такой конфиг пример 3 версии

Показать

В src/zabbix_server/housekeeper/housekeeper.c в строке 236 определено следующее::
или Debian лежит тут /usr/local/sbin название файла zabbix_server
min_clock = MIN(now - keep_history * SEC_PER_DAY, min_clock + 4 * CONFIG_HOUSEKEEPING_FREQUENCY * SEC_PER_HOUR);

У этого есть недостаток:
если вы изменяете элемент от значения по умолчанию "храните историю в днях" 60 к,
скажем, 2, это займет очень много времени для всех старых данных, которые будут очищены.
Я предлагаю изменить это:
min_clock = MIN(now - keep_history * SEC_PER_DAY, min_clock + 32 * CONFIG_HOUSEKEEPING_FREQUENCY * SEC_PER_HOUR);

В текущем коде, если у вас есть почасовая уборка работает, он будет только удалять" в настоящее время наименьшее значение часов + 4 часа " стоит данных.
При изменении истории хранения от 60 дней до 2, это занимает очень много времени
Либо жестко закодированное значение 4 должно быть параметром config с достаточными предупреждениями о возможности табличных и транзакционных журналов,
либо значение по умолчанию должно быть немного увеличено, чтобы ускорить очистку старой истории.

ps ax | grep houskeeper Сколько времени запущен процесс хаускипера

Отключим housekeeper
nano /usr/local/etc/zabbix_server.conf
Меняем параметр HousekeepingFrequency=0 (старая версия DisableHousekeeping=1)
Сохраняем F2 Y

Данный параметр задает запуск по расписанию планировщик событий например Крон или запуск вручную командой

Показать

имеется возможность отключить автоматическую очистку истории, указав HousekeepingFrequency равным 0. В этом случае процедуру очистки истории можно запустить только с помощью опции контроля управления housekeeper_execute и периодом удаления устаревшей информации является 4 кратный период начиная с последнего цикла удаления истории, но не менее чем 4 часа и не более 4 дней.
Пример использования административных функций для вызова выполнения очистки базы данных:
Ручной запуск командой shell> zabbix_server -c /usr/local/etc/zabbix_server.conf -R housekeeper_execute
или zabbix_server -R housekeeper_execute

Перезапускаем службу Заббикс сервера для применения настроек конфигурации
service zabbix-server restart

mysql -V версия MySql
На момент написания Версия Server version: 10.1.38-MariaDB-0+deb9u1 Debian 9.8

Заходим в mysql mysql -uroot -pПароль
Проверяем что Партиционирование у нас активно командой
SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition';
Проверяем переменную должно быть Да
show variables like 'have_symlink';
Далее проверяем есть ли у нас BlackHole (черная дыра, данные идут «вникуда») для таблицы housekeeper он нам нужен будет.
у меня его нет по умолчанию.
SHOW ENGINES;
Если вы получили BlackHole YES то все хорошо, если этого параметра нет устанавливаем.
Устанавливается командой
INSTALL SONAME 'ha_blackhole';
Информация о blackhole
Повторно проверяем
SHOW ENGINES;
Теперь должно все появится.

Blackhole.jpg

Можно проверить его работу создав тестовую базу и таблицу

Показать

CREATE DATABASE testdb character set utf8 collate utf8_bin; создать базу тест
GRANT ALL PRIVILEGES on testdb.* to root@localhost IDENTIFIED BY ''; права на базу тест
use testdb выбор базы тест
CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE; создать таблицу
INSERT INTO test VALUES(1,'record one'), (2,'record two'); создать строки в таблице
SELECT * FROM test; выбрать все в таблице просмотр таблицы
В таблице должно быть пусто данные ушли в никуда
выходим из тестовой базы exit

show databases; команда список баз данных

Выбираем базу данных Заббикса
use zabbixdb
show tables; можно просмотреть таблицы в базе
И выполняем команду
ALTER TABLE housekeeper ENGINE = BLACKHOLE;
Это нужно так как при отключенном housekeeper для элементов, Zabbix сервера и веб- интерфейсом будет продолжать писать служебную информацию для будущего использования в таблицу housekeepe. А так данные пойдут в никуда в черную дыру.

Убедитесь в том, что первый планировщик событий включен значение должно быть ON (Обычно выключен)
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
Команда включения
SET GLOBAL event_scheduler = ON;
Выходим из mysql exit

Вы также должны поместить строку в « my.cnf файл» , в раздел [mysqld] как «event_scheduler = on» в случае перезагрузки.
nano /etc/mysql/mariadb.conf.d/50-server.cnf

Дальше делаем резервную копию обязательно даже если у вас свежо установленная база и в не ничего нет!!!!
Можно скопировать всю папку mysql находится она /var/lib/mysql
Через консоль mc или командой

Изменения будут в 3х базах information_schema (performance_schema), mysql, zabbixdb

описание mysqldump

Показать

Данная утилита позволяет получить дамп (``моментальный снимок'') содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

MySQLDUMP
Если данных много может занять длительное время больше суток!

Выгрузка по команде происходит в текущую директорию (папку) можно перейти в root папку набрав cd root и выполнить команды

Смотрим например от какого числа у нас начинается например таблица (история) имя таблицы history (это в дальнейшем нужно будет для создания файлов под историю от какого числа начинать)
SELECT FROM_UNIXTIME(MIN(CLOCK)) FROM `history`;
describe history; - Получение информации о столбцах кому интересно

Снимаем дамп копии Вся база Заббикс сервера и отдельно таблицы которые будем партировать.
mysqldump -u root -p zabbixdb > zabbixdb.sql
mysqldump -u root -p zabbixdb history > history.sql
mysqldump -u root -p zabbixdb history_uint > history_uint.sql
mysqldump -u root -p zabbixdb history_log > history_log.sql
mysqldump -u root -p zabbixdb history_str > history_str.sql
mysqldump -u root -p zabbixdb history_text > history_text.sql
mysqldump -u root -p zabbixdb acknowledges > acknowledges.sql
mysqldump -u root -p zabbixdb alerts > alerts.sql
mysqldump -u root -p zabbixdb auditlog > auditlog.sql
mysqldump -u root -p zabbixdb events > events.sql
mysqldump -u root -p zabbixdb trends > trends.sql
mysqldump -u root -p zabbixdb trends_uint > trends_uint.sql
mysqldump -u root -p zabbixdb service_alarms > service_alarms.sql
mysqldump -u root -p zabbixdb housekeeper > housekeeper.sql

должно получится так

Показать

Проверяем командой ls -l или смотрим в mc

mysqldumpzabbix.jpg

Без функции create чтоб в дальнейшем не пере затереть таблицы, а подлить данные (не содержит команды create создать)
В конце имени содержит букву h не перепутайте.
mysqldump -u root -p --no-create-info --lock-tables zabbixdb history > historyh.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb history_uint > history_uinth.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb history_log > history_logh.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb history_str > history_strh.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb history_text > history_texth.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb acknowledges > acknowledgesh.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb alerts > alertsh.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb auditlog > auditlogh.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb events > eventsh.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb trends > trendsh.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb trends_uint > trends_uinth.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb service_alarms > service_alarmsh.sql
mysqldump -u root -p --no-create-info --lock-tables zabbixdb housekeeper > housekeeperh.sql

должно получится так

Показать

Проверяем командой ls -l или смотрим в mc

mysqldumpzabbix2.jpg

Проверяем все ли файлы появились командой ls -l или mc (файлы выгрузятся в ту папку в которой вы находились на момент запуска команды)

останавливаем Заббикс Сервер
service zabbix-server stop

Еще раз напоминаю если вы решили не трогать данные таблицы (events, service_alarms, acknowledges, alerts, auditlog) данный пункт выполнять не нужно! сразу переходим к пункту очистки таблицы
Заходим в mysql mysql -uroot -pПароль
mysql> use zabbixdb; выбор базы данных
Дальше нам нужно изменить первичный ключ на индекс и удалить зависимости идущих от других таблиц на таблицы которые нам нужно разбить, иначе выйдет ошибка и не даст нам произвести очистку и разбитие таблиц на партацию!!!
Водим следующие команды
ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditlog_0` (`auditid`);
(команда удаляет ключ первичный PRIMARY и создает тоже самое но индекс auditlog_0)
ALTER TABLE `auditlog` DROP FOREIGN KEY `c_auditlog_1`;
(удаляет связь с таблицей пользователей user)
ALTER TABLE `auditlog_details` DROP FOREIGN KEY `c_auditlog_details_1`;
(удаляет связь из таблицы auditlog_details в таблицу auditlog)

Пример для таблицы auditlog скрины и команды

Показать

Просмотр в phpmyadmin
До удаления

zabbixtabl1

zabbixtabl2

В командной строке
use zabbixdb; выбираем базу данных
Разные команды
show tables; просмотр таблиц в базе
describe auditlog; - Получение информации о столбцах

show create table auditlog; просмотр данных о таблице (так же партицирование если оно есть)

просмотреть все внешние ключи таблицы или столбца
Для таблицы:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'zabbixdb' AND
REFERENCED_TABLE_NAME = 'auditlog';

Для столбца:
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '<database>' AND
REFERENCED_COLUMN_NAME = '<column>';

Если вы используете InnoDB и определенный FK, вы можете запросить базу данных information_schema,
например:
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'zabbixdb'
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'auditlog';

zabbixtabl3

zabbixtabl4

еще команды

Посмотрим после выполнения команд
ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditlog_0` (`auditid`);
(команда удаляет ключ первичный PRIMARY и создает тоже самое но индекс auditlog_0)

zabbixtabl5

ALTER TABLE `auditlog` DROP FOREIGN KEY `c_auditlog_1`;
(удаляет связь с таблицей пользователей user)

zabbixtabl6

ALTER TABLE `auditlog_details` DROP FOREIGN KEY `c_auditlog_details_1`;
(удаляет связь из таблицы auditlog_details в таблицу auditlog)
До удаления

zabbixtabl8

После выполнения команды удаления

zabbixtabl9

zabbixtabl7

смысл понятен дальше без описания
ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `service_alarms_0` (`servicealarmid`);
ALTER TABLE `service_alarms` DROP FOREIGN KEY `c_service_alarms_1`;

ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alerts_0` (`alertid`);
ALTER TABLE `alerts` DROP FOREIGN KEY `c_alerts_1`, DROP FOREIGN KEY `c_alerts_2`, DROP FOREIGN KEY `c_alerts_3`, DROP FOREIGN KEY `c_alerts_4`, DROP FOREIGN KEY `c_alerts_5`, DROP FOREIGN KEY `c_alerts_6`;

ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledges_0` (`acknowledgeid`);
ALTER TABLE `acknowledges` DROP FOREIGN KEY `c_acknowledges_1`, DROP FOREIGN KEY `c_acknowledges_2`;

ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `events_0` (`eventid`);
ALTER TABLE `event_recovery` DROP PRIMARY KEY, ADD KEY `event_recovery_0` (`eventid`);
ALTER TABLE `event_recovery` DROP FOREIGN KEY `c_event_recovery_1`, DROP FOREIGN KEY `c_event_recovery_2`, DROP FOREIGN KEY `c_event_recovery_3`;
ALTER TABLE `event_suppress` DROP FOREIGN KEY `c_event_suppress_1`;
ALTER TABLE `event_tag` DROP FOREIGN KEY `c_event_tag_1`;
ALTER TABLE `problem` DROP FOREIGN KEY `c_problem_1`, DROP FOREIGN KEY `c_problem_2`;

Пример команд для возврата связей как было до удаления на всякий случай

Показать

Когда сделана партицирование таблиц связи на данные таблицы не восстанавливаются все работает нормально и без них, если их востановить возможно заблокируется файл и невозможно будет его удалить, или куда идут связи будут оставаться какие либо данные от удаленных уже таблиц.

ALTER TABLE `auditlog` DROP INDEX `auditlog_0`,ADD PRIMARY KEY (`auditid`);
ALTER TABLE `auditlog` ADD CONSTRAINT `c_auditlog_1` FOREIGN KEY (`userid`) REFERENCES `users`(`userid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `auditlog_details` ADD CONSTRAINT `c_auditlog_details_1` FOREIGN KEY (`auditid`) REFERENCES `auditlog`(`auditid`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `service_alarms` DROP INDEX `service_alarms_0`,ADD PRIMARY KEY (`servicealarmid`);
ALTER TABLE `service_alarms` ADD CONSTRAINT `c_service_alarms_1` FOREIGN KEY (`serviceid`) REFERENCES `services`(`serviceid`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `alerts` DROP INDEX `alerts_0`,ADD PRIMARY KEY (`alertid`);
ALTER TABLE `alerts` ADD CONSTRAINT `c_alerts_1` FOREIGN KEY (`actionid`) REFERENCES `actions`(`actionid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `alerts` ADD CONSTRAINT `c_alerts_2` FOREIGN KEY (`eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `alerts` ADD CONSTRAINT `c_alerts_3` FOREIGN KEY (`userid`) REFERENCES `users`(`userid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `alerts` ADD CONSTRAINT `c_alerts_4` FOREIGN KEY (`mediatypeid`) REFERENCES `media_type`(`mediatypeid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `alerts` ADD CONSTRAINT `c_alerts_5` FOREIGN KEY (`p_eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `alerts` ADD CONSTRAINT `c_alerts_6` FOREIGN KEY (`acknowledgeid`) REFERENCES `acknowledges`(`acknowledgeid`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `acknowledges` DROP INDEX `acknowledges_0`,ADD PRIMARY KEY (`acknowledgeid`);
ALTER TABLE `acknowledges` ADD CONSTRAINT `c_acknowledges_1` FOREIGN KEY (`userid`) REFERENCES `users`(`userid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `acknowledges` ADD CONSTRAINT `c_acknowledges_2` FOREIGN KEY (`eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `events` DROP INDEX `events_0`,ADD PRIMARY KEY (`eventid`);

ALTER TABLE `event_recovery` DROP INDEX `event_recovery_0`,ADD PRIMARY KEY (`eventid`);
ALTER TABLE `event_recovery` ADD CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `event_recovery` ADD CONSTRAINT `c_event_recovery_2` FOREIGN KEY (`r_eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `event_recovery` ADD CONSTRAINT `c_event_recovery_3` FOREIGN KEY (`c_eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `event_suppress` ADD CONSTRAINT `c_event_suppress_1` FOREIGN KEY (`eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `event_tag` ADD CONSTRAINT `c_event_tag_1` FOREIGN KEY (`eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `problem` ADD CONSTRAINT `c_problem_1` FOREIGN KEY (`eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `problem` ADD CONSTRAINT `c_problem_2` FOREIGN KEY (`r_eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT;

для старой версии заббикс 1,8
ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_log` ADD UNIQUE INDEX `history_log_2`(`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;
ALTER TABLE `history_text` ADD UNIQUE INDEX `history_text_2`(`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP PRIMARY KEY, ADD INDEX `history_log_0` (`id`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD INDEX `history_text_0` (`id`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;

Очищаем таблицы от данных.
truncate table history;
truncate table history_uint;
truncate table history_text;
truncate table history_str;
truncate table history_log;
truncate table alerts;
truncate table service_alarms;
truncate table trends;
truncate table trends_uint;
truncate table acknowledges;
truncate table auditlog;
truncate table events;

Создаем вручную нужное количество файлов (дней) для восстановления истории которую нам нужно востановить. (мы смотрели дату начала истории при создании копий таблиц)
Партация создается до указанного числа например до >2019-05-20 значит таблица будет за 19 число и имя будет P2019_05_19
Например у вас сегодня 1 число месяца у вас должен быть файл с именем pГГГГ_ММ_01 это файл будет содержать данные до ("ГГГГ-ММ-02 00:00:00") до 2 числа месяца и тд

файлы создадутся в cd /var/lib/mysql/zabbixdb
1 день таблицы
history
history_text
history_uint
history_log
history_str
trends_uint
trends
events

Повторяем команду меня название таблицы events на другие таблицы
Пример для 2019 05 20 до 2019 05 27 будет 8 файлов на 8 дней истории
ALTER TABLE `events` PARTITION BY RANGE ( clock)
(PARTITION p2019_05_20 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-21 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_05_21 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-22 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_05_22 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-23 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_05_23 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-24 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_05_24 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-25 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_05_25 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-26 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_05_26 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-27 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_05_27 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-28 00:00:00") div 1) ENGINE = InnoDB);

месяц
service_alarms
acknowledges
alerts
auditlog

создаем по 2 файла истории за 2 месяца за май и июнь
Повторяем команду меня название таблицы service_alarms

ALTER TABLE `service_alarms` PARTITION BY RANGE ( clock)
(PARTITION p2019_05_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-01 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_06_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-07-01 00:00:00") div 1) ENGINE = InnoDB);

ALTER TABLE `acknowledges` PARTITION BY RANGE ( clock)
(PARTITION p2019_05_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-01 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_06_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-07-01 00:00:00") div 1) ENGINE = InnoDB);

ALTER TABLE `alerts` PARTITION BY RANGE ( clock)
(PARTITION p2019_05_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-01 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_06_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-07-01 00:00:00") div 1) ENGINE = InnoDB);

ALTER TABLE `auditlog` PARTITION BY RANGE ( clock)
(PARTITION p2019_05_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-01 00:00:00") div 1) ENGINE = InnoDB,
PARTITION p2019_06_01 VALUES LESS THAN (UNIX_TIMESTAMP("2019-07-01 00:00:00") div 1) ENGINE = InnoDB);

Пример ввода команд)

Показать

PARTITION.jpg

Если просмотреть файлы в папке ls -l /var/lib/mysql/zabbixdb
Они так же должны присутствовать пример в mc

filespart.jpg

Удалить не правильно созданные пример
ALTER TABLE `history_log` DROP PARTITION p2019_05_20;

show create table history; Проверить просмотр данных о таблице так же партицирование создалось или нет пример таблица history

Показать

PARTITION1.jpg

PARTITION2.jpg

Выходим из mysql набрав exit

Восстанавливаем данные, в партиционированные таблицы которая без функции create в конце есть буква h не перепутайте
mysql -u root -p zabbixdb < historyh.sql
mysql -u root -p zabbixdb < history_uinth.sql
mysql -u root -p zabbixdb < history_texth.sql
mysql -u root -p zabbixdb < history_logh.sql
mysql -u root -p zabbixdb < history_strh.sql
mysql -u root -p zabbixdb < acknowledgesh.sql
mysql -u root -p zabbixdb < alertsh.sql
mysql -u root -p zabbixdb < auditlogh.sql
mysql -u root -p zabbixdb < eventsh.sql
mysql -u root -p zabbixdb < service_alarmsh.sql
mysql -u root -p zabbixdb < trendsh.sql
mysql -u root -p zabbixdb < trends_uinth.sql

Выглядит это так

vostanov.jpg

Если у вас вылетела ошибка с параметром clock значит у вас не хватает файлов с партацией данного времени, некуда записывать данные от определенного времени.


Далее у нас есть 2 варианта создания новых файлов партицирования и их удаления. Первый это из MySql , Второй скриптом добавленного (в планировщик заданий) Cron. Выбирайте на свой вкус. У меня сделано первым способом.

Первый способ!
Создаем таблицу manage_partitions в базе данных zabbixdb настроек секционирования. В этой таблице будут хранится настройки секционирования для таблиц Zabbix.

Заходим в mysql mysql -uroot -pПароль
mysql> use zabbixdb; выбор базы данных
Создание таблицы
CREATE TABLE `manage_partitions` (
`tablename` VARCHAR(64) NOT NULL COMMENT 'Имя секционируемой таблицы',
`period` VARCHAR(64) NOT NULL COMMENT 'Период секционирования: day week month',
`keep_history` INT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Количество дней недель месяцев хранения секций',
`last_updated` DATETIME DEFAULT NULL COMMENT 'Время последнего добавления секции',
`comments` VARCHAR(128) DEFAULT '1' COMMENT 'Комментарии',
PRIMARY KEY (`tablename`)
) ENGINE=INNODB;

Просмотреть можно командой SHOW CREATE TABLE `manage_partitions`;
Должно быть так

manage_partitions.jpg

обозначение периода времени

Показать

day День
week Неделю
month Месяц
year Год

расшифровка времени года

Неделя ( 00.. 53), где 53 это количество недель в году
12.7 Функции даты и времени

С Проба сделать с неделями некоторые таблицы чтоб не было так много файлов

Показать

Создавать файл
каждый 1 день хранить 90 дней
history
history_text
history_uint

каждую неделю хранить 12 недель
history_log
history_str
trends_uint
trends
events

каждые месяц хранить 3 месяца
service_alarms
acknowledges
alerts
auditlog

day День
week Неделю
month Месяц
year Год

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends_uint', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('events', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('service_alarms', 'month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('acknowledges', 'month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('alerts', 'month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('auditlog', 'month', 3, now(), '');

Так же нужно заполнить таблицу ”manage_partitions” в соответствии с выбранным диапазоном разбиения и сроком хранения секций.
где day', 90 - 90 дней , month', 3,- 3 месяца. Здесь именно указать месяц или дни в дальнейшем по этим параметрам будут создаваться новый файлы таблиц а так же удаляться старые с истечением срока хранения!

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends_uint', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('events', 'week', 12, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('service_alarms', 'month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('acknowledges', 'month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('alerts', 'month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('auditlog', 'month', 3, now(), '');

ИЛИ ТАК кому как удобнее

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends_uint', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('events', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('service_alarms', ' month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('acknowledges', ' month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('alerts', ' month', 3, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('auditlog', ' month', 3, now(), '');

Получаем
SELECT * FROM manage_partitions;

manage_partitions1.jpg

Или же еще с неделями возможен вариант

manage_partitions

Создадим 5 процедур и 1 событие

Процедура проверки наличия требуемых секций
Заходим в mysql mysql -uroot -pПароль
DELIMITER $$

USE `zabbixdb`$$

DROP PROCEDURE IF EXISTS `create_next_partitions`$$

CREATE PROCEDURE `create_next_partitions`(IN_SCHEMANAME VARCHAR(64))
COMMENT 'Процедура проверки наличия требуемых секций'
BEGIN
DECLARE TABLENAME_TMP VARCHAR(64);
DECLARE PERIOD_TMP VARCHAR(12);
DECLARE DONE INT DEFAULT 0;

DECLARE get_prt_tables CURSOR FOR
SELECT `tablename`, `period`
FROM manage_partitions;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN get_prt_tables;

loop_create_part: LOOP
IF DONE THEN
LEAVE loop_create_part;
END IF;

FETCH get_prt_tables INTO TABLENAME_TMP, PERIOD_TMP;

CASE WHEN PERIOD_TMP = 'day' THEN
CALL `create_partition_by_day`(IN_SCHEMANAME, TABLENAME_TMP);
WHEN PERIOD_TMP = 'week' THEN
CALL `create_partition_by_week`(IN_SCHEMANAME, TABLENAME_TMP);
WHEN PERIOD_TMP = 'month' THEN
CALL `create_partition_by_month`(IN_SCHEMANAME, TABLENAME_TMP);
ELSE
BEGIN
ITERATE loop_create_part;
END;
END CASE;

UPDATE manage_partitions set last_updated = NOW() WHERE tablename = TABLENAME_TMP;
END LOOP loop_create_part;

CLOSE get_prt_tables;
END$$
DELIMITER ;

Процедура проверки наличия требуемых секций.jpg

Создание секции по дням
mysql>
DELIMITER $$

USE `zabbixdb`$$

DROP PROCEDURE IF EXISTS `create_partition_by_day`$$

CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
COMMENT 'Создание секции по дням'
BEGIN
DECLARE ROWS_CNT INT UNSIGNED;
DECLARE BEGINTIME TIMESTAMP;
DECLARE ENDTIME INT UNSIGNED;
DECLARE PARTITIONNAME VARCHAR(16);
SET BEGINTIME = DATE(NOW()) + INTERVAL 1 DAY;
SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m_%d' );

SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 DAY) div 1;

SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;

IF ROWS_CNT = 0 THEN
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
END IF;
END$$


DELIMITER ;

Процедура Создание секции по дням.jpg

Создание секции по неделям
mysql>
DELIMITER $$

USE `zabbixdb`$$

DROP PROCEDURE IF EXISTS `create_partition_by_week`$$

CREATE PROCEDURE `create_partition_by_week`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
COMMENT 'Создание секции по неделям'
BEGIN
DECLARE ROWS_CNT INT UNSIGNED;
DECLARE BEGINTIME TIMESTAMP;
DECLARE ENDTIME INT UNSIGNED;
DECLARE PARTITIONNAME VARCHAR(16);
SET BEGINTIME = DATE(NOW()) + INTERVAL 1 WEEK;
SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m_%d_%u' );

SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 WEEK) div 1;

SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;

IF ROWS_CNT = 0 THEN
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
END IF;
END$$


DELIMITER ;

Создание секций по месяцам
mysql>
DELIMITER $$

USE `zabbixdb`$$

DROP PROCEDURE IF EXISTS `create_partition_by_month`$$

CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
COMMENT 'Создание секций по месяцам'
BEGIN
DECLARE ROWS_CNT INT UNSIGNED;
DECLARE BEGINTIME TIMESTAMP;
DECLARE ENDTIME INT UNSIGNED;
DECLARE PARTITIONNAME VARCHAR(16);
SET BEGINTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH);
SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m' );

SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH) div 1;

SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;

IF ROWS_CNT = 0 THEN
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
END IF;
END$$


DELIMITER ;

Процедура Создание секций по месяцам.jpg

Проверка наличия устаревших секций и удаление
mysql>

DELIMITER $$

USE `zabbixdb`$$

DROP PROCEDURE IF EXISTS `drop_partitions`$$

CREATE PROCEDURE `drop_partitions`(IN_SCHEMANAME VARCHAR(64))
COMMENT 'Проверка наличия устаревших секций и удаление'
BEGIN
DECLARE TABLENAME_TMP VARCHAR(64);
DECLARE PARTITIONNAME_TMP VARCHAR(64);
DECLARE VALUES_LESS_TMP INT;
DECLARE PERIOD_TMP VARCHAR(12);
DECLARE KEEP_HISTORY_TMP INT;
DECLARE KEEP_HISTORY_BEFORE INT;
DECLARE DONE INT DEFAULT 0;
DECLARE get_partitions CURSOR FOR
SELECT p.`table_name`, p.`partition_name`, LTRIM(RTRIM(p.`partition_description`)), mp.`period`, mp.`keep_history`
FROM information_schema.partitions p
JOIN manage_partitions mp ON mp.tablename = p.table_name
WHERE p.table_schema = IN_SCHEMANAME
ORDER BY p.table_name, p.subpartition_ordinal_position;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN get_partitions;

loop_check_prt: LOOP
IF DONE THEN
LEAVE loop_check_prt;
END IF;

FETCH get_partitions INTO TABLENAME_TMP, PARTITIONNAME_TMP, VALUES_LESS_TMP, PERIOD_TMP, KEEP_HISTORY_TMP;
CASE WHEN PERIOD_TMP = 'day' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP DAY));
WHEN PERIOD_TMP = 'month' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP MONTH - INTERVAL DAY(NOW())-1 DAY));
ELSE
BEGIN
ITERATE loop_check_prt;
END;
END CASE;

IF KEEP_HISTORY_BEFORE >= VALUES_LESS_TMP THEN
CALL drop_old_partition(IN_SCHEMANAME, TABLENAME_TMP, PARTITIONNAME_TMP);
END IF;
END LOOP loop_check_prt;

CLOSE get_partitions;
END$$


DELIMITER ;


Ниже под сполером ошибочный скрипт подписчик поправил Процедура не создалась из-за ошибки в синтаксисе.
Кто делал так и была ошибка выше выложен уже без ошибки в чем была ошибка под сполером

Ошибка

Показать

Отличие скриптов моего и подписчика, у меня были доп строки откуда они взялись.... возможно скопировались с чем то...
на 3 команды больше
FETCH get_partitions INTO TABLENAME_TMP, PARTITIONNAME_TMP, VALUES_LESS_TMP, PERIOD_TMP, KEEP_HISTORY_TMP;
CASE WHEN PERIOD_TMP = 'week' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP WEEK));

старая версия с ошибкой

Показать

DELIMITER $$

USE `zabbixdb`$$

DROP PROCEDURE IF EXISTS `drop_partitions`$$

CREATE PROCEDURE `drop_partitions`(IN_SCHEMANAME VARCHAR(64))
COMMENT 'Проверка наличия устаревших секций и удаление'
BEGIN
DECLARE TABLENAME_TMP VARCHAR(64);
DECLARE PARTITIONNAME_TMP VARCHAR(64);
DECLARE VALUES_LESS_TMP INT;
DECLARE PERIOD_TMP VARCHAR(12);
DECLARE KEEP_HISTORY_TMP INT;
DECLARE KEEP_HISTORY_BEFORE INT;
DECLARE DONE INT DEFAULT 0;
DECLARE get_partitions CURSOR FOR
SELECT p.`table_name`, p.`partition_name`, LTRIM(RTRIM(p.`partition_description`)), mp.`period`, mp.`keep_history`
FROM information_schema.partitions p
JOIN manage_partitions mp ON mp.tablename = p.table_name
WHERE p.table_schema = IN_SCHEMANAME
ORDER BY p.table_name, p.subpartition_ordinal_position;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN get_partitions;

loop_check_prt: LOOP
IF DONE THEN
LEAVE loop_check_prt;
END IF;

FETCH get_partitions INTO TABLENAME_TMP, PARTITIONNAME_TMP, VALUES_LESS_TMP, PERIOD_TMP, KEEP_HISTORY_TMP;
CASE WHEN PERIOD_TMP = 'day' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP DAY));
FETCH get_partitions INTO TABLENAME_TMP, PARTITIONNAME_TMP, VALUES_LESS_TMP, PERIOD_TMP, KEEP_HISTORY_TMP;
CASE WHEN PERIOD_TMP = 'week' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP WEEK));
WHEN PERIOD_TMP = 'month' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP MONTH - INTERVAL DAY(NOW())-1 DAY));
ELSE
BEGIN
ITERATE loop_check_prt;
END;
END CASE;

IF KEEP_HISTORY_BEFORE >= VALUES_LESS_TMP THEN
CALL drop_old_partition(IN_SCHEMANAME, TABLENAME_TMP, PARTITIONNAME_TMP);
END IF;
END LOOP loop_check_prt;

CLOSE get_partitions;
END$$


DELIMITER ;

Процедура Проверка наличия устаревших секций и удаление.jpg

Удаление указанной секции
mysql>
DELIMITER $$

USE `zabbixdb`$$

DROP PROCEDURE IF EXISTS `drop_old_partition`$$

CREATE PROCEDURE `drop_old_partition`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64), IN_PARTITIONNAME VARCHAR(64))
COMMENT 'Удаление указанной секции'
BEGIN
DECLARE ROWS_CNT INT UNSIGNED;

SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = IN_PARTITIONNAME;

IF ROWS_CNT = 1 THEN
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' DROP PARTITION ', IN_PARTITIONNAME, ';' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", IN_PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` not exists") AS result;
END IF;
END$$


DELIMITER ;

Процедура Удаление указанной секции.jpg

Управление разбиениями, планировщик (events)
DELIMITER $$
USE `zabbixdb` $$
CREATE EVENT `e_part_manage`
ON SCHEDULE EVERY 1 DAY
STARTS '2019-05-29 00:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Управление созданием и удалением секций'
DO BEGIN
CALL zabbix.drop_partitions('zabbixdb');
CALL zabbix.create_next_partitions('zabbixdb');
END$$

DELIMITER ;

Событие Управление разбиениями, планировщик (events).jpg

Процедуры Событие в phpmyadmin.jpg

То что есть ошибки токена в phpmyadmin ничего страшного все будет работать просто так отображается в phpmyadmin

Событие.jpg

Как запустить процедуры вручную указываем схему нашу БД zabbixbd и соответствующую таблицу для этой процедуры

manage_partitions пример.jpg

Событие e_part_manage заданное на определенное время вызывает call 2 процедуры
create_next_partitions эта же процедура запускает еще 3 процедуры create_partition_by_ 1 day 2 week 3 month перебирая все соответствия переменных из таблицы manage_partitions имя таблицы и период на какой срок создать партацию день неделю месяц от текущей даты (т.е. это не значит что у вас неделя будет с понедельника а месяц с начала месяца отсчет будет в днях с момента запуска процедур) (записывается время последнего добавления раздела last_updated)
запускается 2 процедура drop_partitions Проверка наличия устаревших секций и удаление по таблице manage_partitions перебор по переменным столбцам соответствие имя таблицы соответствие ее к день неделя или месяц далее значение в днях старше скольких дней не должно быть партаций.
Кому интересно видосик про создания событий
MySQL 8 CREATE EVENT работа с событиями

Второй способ через Скрипт

Разбиение с помощью внешнего скрипта

Показать

Внешний хранимый скрипт можно использовать вместо хранимых процедур. Хотя это должно быть запланировано вне базы данных, обычно это будет проще и легче отлаживать. Предлагается добавить ежедневную работу cron. Скрипт поддерживает создание новых разделов и удаление старых.

Примечание. Перед добавлением сценария в качестве задания cron требуемый период хранения и тип раздела для каждой таблицы должны быть установлены в самом сценарии. Установка 0 для keep_history позволит сохранить только один активный в данный момент раздел.
Примечание. Выберите нужную версию Zabbix и раскомментируйте и закомментируйте нужные строки (zabbix 2.2 без возможности включения определенных элементов управления домработницей). Для zabbix начиная с 2.2 прокомментируйте 5 строк, как это предлагается внутри скрипта.
Примечание. В настоящее время сценарий предполагает использование версии MySQL до версии 5.6. Для MySQL версии 5.6 и выше, 2 строки внутри скрипта должны быть закомментированы / некомментированы.
Примечание. Если вы используете systemd / journald, вы можете увидеть вывод скрипта в syslog или с помощью journalctl -t mysql_zbx_part.

Скрипт содержит следующие таблицы
30 дней
history
history_log
history_str
history_text
history_uint
2 месяца
trends
trends_uint
Следующие таблицы на ваше усмотрение рекомендуют их разбивать если заббикс ниже версии 2.2
acknowledges 23 месяца
alerts 6 месяцев
auditlog 24 месяца
events 12 месяцев
service_alarms 6 месяцев

Скриптом что нужно будет сделать

Установка Perl и DateTime

Показать

yum install -y perl-Sys-Syslog - установка Perl
Устанавливаем дополнительные пакеты для запуска функций используемого скрипта разметки Perl.
yum install perl-CPAN perl-DateTime-TimeZone perl-DBD-MySQL perl-Sys-Syslog

Debian
При ошибке Can't locate DateTime.pm
установить модуль DateTime
cpan :: cpanminus
Потверждаем YES
установка
cpanm DateTime
или
perl -MCPAN -e shell
install DateTime
У кого скрипт ругается на DBD::mysql устанавливаем его
install DBD::mysql если так не ставится альтернативный вариант пакетом
apt-get install libdbd-mysql-perl

У меня отличается путь mysql_socket=/var/lib/mysql/mysql.sock'; новый mysql_socket=/var/run/mysqld/mysqld.sock'; или mysql_socket=/run/mysqld/mysqld.sock';

Создаем папку
mkdir /etc/zabbix
Создаем скрип
nano /etc/zabbix/zabbix_partitioning.pl
изменить имя базы логин пароль подключения к базе и другие параметры если вам нужно иначе.
закомментировать # команды для заббикс версии 2.2 если у вас более новая
Закоментировать MySql версию 5.5 или 5.6 в зависимости что у вас ниже 5.5 или выше 5.6
Проверено на Версия Server version: 10.1.38-MariaDB-0+deb9u1 Debian 9.8 все работает.

Скрипт

Показать

#!/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_LOCAL0);

my $db_schema = 'zabbixdb';
my $dsn = 'DBI:mysql:'.$db_schema.':mysql_socket=/var/run/mysqld/mysqld.sock';
my $db_user_name = 'Пользователь для подключения к базе данных zabbixdb';
my $db_password = '<Пароль>';
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'},

# комментируйте следующие 5 строк, если вы разбиваете базу данных zabbix, начиная с 2.2
# они обычно использовались для базы данных zabbix до 2.2 т е не будут разбавится следующие 5 таблиц acknowledges alerts auditlog events service_alarms
# количество разделов 10 - 1 текущий + на 9 дней вперед создание файлов - amount_partitions = 10

# '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 = 10;

my $curr_tz = 'Europe/Moscow';

my $part_tables;

my $dbh = DBI->connect($dsn, $db_user_name, $db_password, {'ShowErrorStatement' => 1});

unless ( check_have_partition() ) {
print "Ваша установка MySQL не поддерживает разбиение таблиц.\n";
syslog(LOG_CRIT, 'Ваша установка MySQL не поддерживает разбиение таблиц.');
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, 'Разметка для "'.$key.'" не найден! Таблица может быть не разделена.');
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, "Следующий раздел для $table_name уже создан. Это $next_name");
$found = 1;
}
}

if ( $found == 0 ) {
syslog(LOG_INFO, "Создание раздела для таблицы $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, "Удаление старого раздела $partition из таблицы $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)");
}

chmod +x /etc/zabbix/zabbix_partitioning.pl - назначаем права
./zabbix_partitioning.pl - выполним (запустим) скрипт
Проверяем заходим в mysql выбираем базу данных zabbixdb
проверяем партицирование
show create table history_uint; просмотр данных о таблице (так же партицирование если оно есть)
выходим из mysql
Добавим задание (в планировщик заданий) cron и запустите его дважды в ночь (в маловероятном случае, если оно не сработает в первый раз)
vi /etc/cron.d/zabbix_part
# Zabbix ежедневное разбиение таблицы
# Zabbix daily table partitioning
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
00 23 * * * root perl /etc/zabbix/zabbix_partitioning.pl 1>/var/log/zabbix/zabbix_partitioning.log 2>&1
00 2 * * * root perl /etc/zabbix/zabbix_partitioning.pl 1>/var/log/zabbix/zabbix_partitioning.log 2>&1

формат: *минута *час *день_месяца *месяц *день_недели *команда изменить путь на свой где лежит скрипт вывод логов в папку

После всего периодически стоит проверять логи
создались ли файлы и удаляются ли старые
После перезапуска рекомендую проверить логи чтобы не было никаких серьезных ошибок там
nano /tmp/zabbix_server.log - тут будет писаться если не хватает какого то файла таблицы на текущий день тобиш некуда писать данные
nano /var/log/zabbix/zabbix_partitioning.log - если используется скрипт через cron
так же можно посмотреть
nano /var/log/mysql/error.log
nano /var/log/apache2/error.log
и другие логи по данному пути /var/log/

Примеры ошибк

Показать

В некоторых случаях при отсутствии файлов не войдет на веб интерфейс

vxodbezfiles.jpg

Так же может войти и вроде как все работать но по факту некоторые проверки просто стоят на месте ничего не мониторят.
nano /tmp/zabbix_server.log есть ошибка но отсутствие файла
Был такой нюанс указали service_alarms хранить 30 месяцев в место 3
ниже было еще trends и trends_uint и все вставало на файле с 30 месяцами все 3 файла не создавались данная ошибка просматривалась в логах, а все невнимательность)

Видос про партицию на основе скрипта en
MySQL Database partitioning for ZABBIX

Источники
zabbix.org mysql_partitioning
Разбиение (секционирование) в бд zabbix MySQL

[Артём Мамзиков]

Не вместилось все в одну статью пришлось делать 2 частью)

Для инфы плюсы минусы нюансы

Показать

Если у вас обычный HDD и база более 100 Гб желательно делать партицирование

Комментарий: "Перейти на table partitioning вместо housekeeper.
Хаускипер не плох на малых бд. Но в больших инсталляциях это не лучший способ чистить историю.
т е Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям
На нижнем уровне для myISAM таблиц, это физически разные файлы, по 3 на каждую партицию (описание таблицы, файл индексов, файл данных).
Для innoDB таблиц в конфигурации по умолчанию – разные пространства таблиц в файлах innoDB
(не забываем, что innoDB позволяет настраивать индивидуальные хранилища на уровне баз данных или даже конкретных таблиц)."

Разбиение выполняется путем логического разделения одной большой таблицы на маленькие физические фрагменты. Разбиение может принести несколько преимуществ:

В некоторых ситуациях производительность запросов может быть значительно увеличена, особенно когда наиболее интенсивно используемая область таблицы представляет собой отдельный раздел или небольшое количество разделов. Такой раздел и его индексы легче помещаются в память, чем индекс всей таблицы.
Когда запросы или обновления используют большой процент одного раздела, производительность может быть увеличена просто за счет более выгодного последовательного доступа к этому разделу на диске вместо использования индекса и произвольного доступа для чтения для всей таблицы. В нашем случае используются индексы типа B-Tree (itemid, clock), которые существенно повышают производительность при секционировании.
Массовая INSERT и DELETE могут быть выполнены простым удалением или добавлением разделов, если такая возможность запланирована при создании раздела. Оператор ALTER TABLE будет работать намного быстрее, чем любой оператор для массовой вставки или удаления.
Невозможно использовать табличные пространства для таблиц InnoDB в MySQL. Вы получаете один каталог - одну базу данных. Таким образом, для переноса файла раздела таблицы он должен быть физически скопирован на другой носитель, а затем на него есть ссылка с использованием символической ссылки.
Примечание. Начиная с MySQL 5.6, существует возможность указать расположение табличного пространства. Однако есть некоторые ограничения .
Эти преимущества обычно становятся очевидными только тогда, когда таблица становится очень большой. Будет ли таблица извлекать выгоду из разделения, в конечном счете, зависит от приложения, однако существует практическое правило, которое будет полезно, когда размер таблицы превысит объем памяти сервера базы данных.

В настоящее время MySQL поддерживает разделение из коробки. Поддержка секционирования начинается с версии MySQL 5.1, поэтому, если у вас более старая версия MySQL, вам придется обновить ее. Кроме того, сервер MySQL должен быть скомпилирован с поддержкой разметки.

Типы разделов в MySQL
Следующие типы разделения могут быть выполнены в MySQL:

Разделение диапазона
Таблица делится на диапазоны, установленные в ключевом столбце или списке столбцов; диапазоны значений, предназначенные для отдельных разделов, не должны перекрываться. Например, диапазоны дат или диапазоны отдельных идентификаторов бизнес-объектов.
Другие типы разбиения
Есть также хэш, список и ключевые типы разделения. Обсуждение этих типов разбиения выходит за рамки данной статьи, так как мы собираемся выполнить только разбиение по диапазонам. Точно так же мы не будем обсуждать разбиение на разделы.
Выбор способа управления разделами
Здесь представлены два решения для управления разделами:

используя процедуры MySQL
используя внешний скрипт
Хотя хранимые процедуры могут показаться более ограниченным решением, отладка любых проблем может быть значительно более сложной. В настоящее время предлагается внешний сценарий.

Перед выполнением разбиения в Zabbix необходимо рассмотреть несколько аспектов:

1 Разделение по диапазонам будет использоваться для разделения таблиц.
2 Housekeeping больше не будет нужtн для некоторых типов данных. Эта функциональность Zabbix для очистки старой истории и данных трендов из базы данных может контролироваться в Администрирование -> Прочее -> Housekeeping.
3 Значения History storage period (in days)и Trend storage period (in days)поле в конфигурации элемента не будут больше использоваться в качестве старых данные будут удалены от диапазона , т.е. всего раздела. Они могут (и должны быть) переопределены Administration -> General -> Housekeeping- период должен соответствовать периоду, на который мы ожидаем сохранить разделы.
4 Если данные необходимо хранить в течение более длительного периода времени, в то время как дисковое пространство ограничено, можно использовать символические ссылки для устаревших разделов. Чтобы проверить, присутствует ли эта функциональность в MySQL, выполните:
show variables like 'have_symlink';

Ограничения
Убедитесь, что диапазоны разделов не перекрываются при создании / добавлении новых разделов, в противном случае усилия вернут ошибку.
Таблица MySQL либо полностью разделена, либо не разделена вовсе. Не следует оставлять такие записи, которые не помещаются ни в один из созданных разделов.
При попытке создать таблицы с большим количеством разделов вы, вероятно, столкнетесь с ошибками типа: «Не удается создать / записать в файл». Чтобы избежать подобных ситуаций, увеличьте значение параметра open_files_limit в файле конфигурации MySQL.
Секционированные таблицы не поддерживают внешние ключи из-за внутреннего ограничения MySQL . Подготовка к разбиению требует удаления внешних ключей.
Секционированные таблицы не поддерживают кэш запросов из-за внутреннего ограничения MySQL .
Все столбцы, используемые в выражении для выбора разделов многораздельной таблицы, должны быть частью каждого уникального ключа, который может иметь таблица. Другими словами, каждый уникальный ключ в таблице должен использовать каждый столбец в выражении для выбора разделов таблицы. Поэтому при подготовке к разбиению первичные ключи удаляются и вместо них создаются обычные индексы.
Максимальное количество разделов не может превышать 1024 (8192, начиная с MySQL 5.6.7), включая подразделы.
Есть несколько других ограничений при использовании разбиения, обсуждение которых выходит за рамки этой статьи. Подробнее об этих ограничениях читайте на сайте MySQL.
рекомендации
Используйте MySQL 5.5 или выше. Он был оптимизирован и более стабилен для секционированных таблиц.
Попробуйте использовать Use XtraDB, а не чистый InnoDB. Этот внутренний движок включен в такие вилки MySQL, как MariaDB и Percona.
TokuDB не подходит для рабочей нагрузки, создаваемой Zabbix. Кажется, он не так хорош для выбора из таблиц (с очень большими числами).
Оптимизируйте, оптимизируйте и оптимизируйте снова. Прошло много времени с тех пор, как MySQL стал намного больше, чем обычная электронная таблица, и теперь он требует тщательной тонкой настройки параметров конфигурации.

Известные вопросы
При использовании сценария оболочки для управления разделами разделы могут не создаваться. Это происходит, когда база данных недоступна в момент запуска скрипта cron.
Возможно, стоит создать несколько дополнительных разделов за один раз.
Табличные идентификаторы могут быть узким местом в Zabbix. Он был введен для управления идентификаторами объектов в распределенном мониторинге. Его использование будет значительно сокращено с удалением распределенного мониторинга на основе узлов в Zabbix 2.4.

Тут будут просто разные команда инфа что я пробовал до партицирования базы данных заббикса

Регишься здесь и решаешь задачи, пару часов порешаешь - проблем со скл не будет
Учебник по скл - описание синтаксиса и логики работы запросов

Ошибки партицирования

Показать

nano /var/log/mysql/error.log
Ошибка 1217 (23000): не удается удалить или обновить родительскую строку: ограничение внешнего ключа не выполняется
Ошибка 1050 (42S01): таблица './zabbixdb / history_log#P#p2019_05_20 ' уже существует
Ошибка 1505 (HY000): управление разделами в несекционированной таблице невозможно
Ошибка 1064 (42000): у вас есть ошибка в синтаксисе SQL; проверьте руководство, которое соответствует вашей версии сервера MariaDB для правильного синтаксиса для использования рядом " в строке 5
Ошибка 1146 (42S02): таблица 'zabbixdb. history_log' не существует
Ошибка 1025 (HY000): ошибка при переименовании './zabbixdb / history_log' to './zabbixdb / #sql2-24b-c3f4' (errno: 1 "операция не разрешена")
Ошибка 1932 (42S02): таблица 'zabbixdb.history_log ' не существует в движке
Ошибка вида #1062 - Duplicate entry '1' for key 'PRIMARY', может возникать при переносе на другой хостинг, у меня возникла, когда я переносил сайт на oscommerse...будем решать ее в этой статье...
Duplicate entry '1' for key 'PRIMARY' - то есть вы пытаетесь создать то, что уже создано(в phpmyadmin). Обычно такая ошибка возникает, когда вы поверх уже установленной базы (БД) движка, пытаетесь сверху накинуть примерно такую же базу, но таблицы к примеру уже такие были созданы и поэтому вылазит такая ошибка.

мне не помогло
в этом случае можно временно отключить проверку внешних ключей:
-- отключаем проверку
SET foreign_key_checks = 0;

-- выполняем нужные запросы

-- включаем проверку назад
SET foreign_key_checks = 1;

Открываем файл БД в программе notepad++ и меняем INSERT INTO на REPLACE INTO. Теперь мы не будем создавать то, что уже есть, а будем перезаписывать. Теперь заливаем еще раз эту БД и ошибка должна исчезнуть.

Что можно посмотреть графики данные

Показать

График в заббикс сервере - Внутренний процесс zabbix занят %
график 3: Zabbix server: Zabbix busy housekeeper processes, in %

График Процесс сбора данных zabbix занят %
Zabbix server: Zabbix busy unreachable poller processes, in %
StartPollers=37 было 27 (проба 50)
# StartPollersUnreachable=2 (проба 50)

График Внутренний процесс zabbix занят %
Zabbix server: Zabbix busy history syncer processes, in %
#HistoryCacheSize=16M
CacheSize=2200M

Недоступен в zabbix опроса-процессы более чем на 75% занят
Zabbix server: Zabbix busy unreachable poller processes, in %

Процесс сбора данных zabbix занят %
Zabbix server: Zabbix busy unreachable poller processes, in %

Удаление истории из веб интерфейса php на какие таблицы ссылается по умолчанию

Показать

События и оповещения
events_mode - Активировать внутреннюю очистку истории
events_trigger - Период хранения данных триггеров
events_internal - Период хранения внутренних данных
events_discovery - Период хранения данных о событиях сетевого обнаружения
events_autoreg - Период хранения данных авто регистрации

Услуги
services_mode - Активировать внутреннюю очистку истории
services - Период хранения данных

Аудит
audit_mode - Активировать внутреннюю очистку истории
audit - Период хранения данных

Сессии пользователей
sessions_mode - Активировать внутреннюю очистку истории
sessions - Период хранения данных

История
history_mode - Активировать внутреннюю очистку истории
history_global - Переопределить период хранения истории элементов данных
history - Период хранения данных


Динамика изменений
trends_mode - Активировать внутреннюю очистку истории
trends_global - Переопределить период хранения динамики изменения элементов данных
trends - Период хранения данных

После партицирования спустя некоторое время было замечено, что некоторые таблицы так же накапливают историю.
Нужно что то с этим делать.
Сразу откинем партицированные таблицы: acknowledges ; alerts ; auditlog ; history ; history_text ; history_uint ; history_log ; history_str ; trends_uint ; trends ; events ; service_alarms

Варианты очистки таблиц:
1. Добавить в партицирование и удалять файлами
2. Проверять на потерянные данные (связи по ID)
3. Удалять данные например старше 3х месяцев ( при условии что есть столбец даты clock)
2-3 способ будет давать некоторую нагрузку на базу.

Связи которые мы удалили при партицировании

Показать

Раньше данные удалились построчно по дате далее по связи ключам между таблицами, так как сейчас удаление по дате отключено и связи нарушены (даже если бы они были файлик просто удаляются данные пропадают они бы не помогли)

Какие у нас были связи до партицирования:
auditlog с users по столбцу userid - При удалении Пользователя заббиск удалялся для него Аудит.
auditlog_details с auditlog по столбцу auditid - При удалении Аудита у нас удалялись подробности что изменено. Будем чистить

service_alarms с services по столбцу serviceid - При удалении Сервисов удалялись Оповещения для сервисов.

alerts с actions по столбцу actionid - Отправка сообщений - Действие При удалении действия очистить связанное в alerts
alerts с events по столбцу eventid - Отправка сообщений - События (обе таблицы в партицировании)
alerts с events по столбцам p_eventid eventid - Аналогично выше.
alerts с users по столбцу userid - Отправка сообщений - Пользователи (при удалении пользователя удалялось все что с ним связанно в alerts)
alerts с media_type по столбцу mediatypeid - Отправка сообщений -Способы оповещения (при удалении способа так же удалялось все что с ним связно в aletrs)
alerts с acknowledges по столбцу acknowledgeid - Отправка сообщений - Подтверждения события (обе таблицы в партицировании)

acknowledges с users по столбцу userid - При удалении Пользователя заббиск удалялся для него Подтверждения событий.
acknowledges с events по столбцу eventid - Подтверждения событий - События (обе таблицы в партицировании)

event_recovery с events по столбцу eventid - Восстановление событий - События . Будем чистить
event_recovery с events по столбцам r_eventid eventid - Он же выше
event_recovery с events по столбцам c_eventid eventid - Он же выше

event_suppress с events по столбцу eventid - Подавление событий - События . Будем чистить

event_tag с events по столбцу eventid - Тег события - События . Будем чистить

problem с events по столбцу eventid - Проблемы - События . Будем чистит
problem с events по столбцам r_eventid eventid - Он же выше

Что означают данные команды
Обозначения в переводе на Русский

ALTER TABLE - ИЗМЕНИТЬ ТАБЛИЦУ

ADD CONSTRAINT - ДОБАВИТЬ ОГРАНИЧЕНИЕ после того, как таблица уже создана.
Следующий SQL добавляет ограничение с именем "PK_Person", которое является ограничением первичного ключа для нескольких столбцов (ID и LastName):

FOREIGN KEY - ВНЕШНИЙ КЛЮЧ

references - используется для определения того, какая таблица и столбец используются во взаимосвязи внешнего ключа. Это означает, что запись в таблице хобби должна иметь идентификатор person_id, который существует в таблице person, иначе во время вставки вы получите сообщение об ошибке, что ключ не существует.
references позволяет нам указать столбец целевой таблицы, на который ссылается внешний ключ.

ON DELETE CASCADE- ПРИ УДАЛЕНИИ КАСКАДА позволяет вам удалить запись родительского ключа (лично) и соответствующие дочерние записи (в хобби) без необходимости сначала удалять все дочерние записи.
CASCADE: строки ссылок будут автоматически удаляться вместе с указанными.

ON UPDATE RESTRICT - ПРИ ОБНОВЛЕНИИ ОГРАНИЧИТЬ
RESTRICT: он не позволит удалить эту (родительскую) запись, не удаляя зависимые записи (записи, которые ссылаются на внешний ключ)
RESTRICT: отклоняет операцию удаления или обновления для родительской таблицы.
Сервер MySQL отклоняет операцию удаления или обновления для родительской таблицы, если в ссылочной таблице имеется соответствующее значение внешнего ключа. Некоторые системы баз данных имеют отсроченные проверки, а НЕТ ДЕЙСТВИЙ - отсроченная проверка.
В MySQL ограничения внешних ключей проверяются немедленно, поэтому НЕТ ДЕЙСТВИЙ - это то же самое, что и RESTRICT.

Пример одной связи:
АудитЛог Индекс Столбец ID Пользователя Таблица Пользователи Столбец ID Пользователя
ИЗМЕНИТЬ ТАБЛИЦУ `auditlog` ДОБАВИТЬ ОГРАНИЧЕНИЕ `c_auditlog_1` ВНЕШНИЙ КЛЮЧ (`userid`) взаимосвязи вне


Получили список таблиц к очистке:
auditlog_details
event_recovery
event_suppress
event_tag
problem

Стоит учесть что на эти таблицы так же возможны связи и где то что то подчищается!, но если удалять командой связи не нарушены все остальное должно подтянутся к удалению.

Получили список таблиц к очистке:
auditlog_details - нет даты, Журнал аудита детали (Было стало изменения)
event_recovery - нет даты, восстановление событий
event_suppress - нет даты, подавление событий
event_tag - нет даты, тег события
problem - Проблемы
sessions - сессии пользователей

Так же дополним его возможным к очистке таблицам
applications - Группы элементов данных (на потерянные группы)
autoreg_host - нет даты, Авто регистрация хоста(узла) ?
corr_condition_tagpair - нет даты, изменения тегов в корреляции ?
dhosts - Хост(Узел) (связи) обнаружения Доступен/Недоступен (даты lastup lastdown)?

Примечание возможно кто то решит оставить эти данные
problem - тут у нас текущий дашборт и раздел Проблемы текущие и история сколько раз срабатывал триггер, при очистке у нас заново будет открываться триггер и меняться дата его открытия, но не всегда все зависит как составлен триггер!!!
Если мы хотим оставить полную статистику нужно оставлять данную таблицу, если нет нужно ее чистить.

auditlog_details - кто что менял изменения
sessions - Сессий подключений отключений пользователей она так же не очищается.

В процессе

Показать

На потерянные данные ?
applications
conditions
functions
graphs_items
hostmacro
items
items_applications
httpstep
maintenances_groups
maintenances_hosts
maintenances_windows
mappings
media
rights
screens_items
trigger_depends
escalations
problem_tag



Проверка потерянных данных
-- Строки, потерянных записей таблицы приложения applications, которые больше не сопоставляются с хостом
-- Сравниваем applications (Группы элементов данных) с таблицей hosts (Узлы) по столбцу hostid
SELECT * FROM applications WHERE hostid NOT IN (SELECT hostid FROM hosts WHERE hostid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных записей приложения applications, которые больше не сопоставляются с хостом
SELECT COUNT(*) AS applications_missing_host FROM applications WHERE NOT hostid IN (SELECT hostid FROM hosts);

-- Сравниваем auditlog_details (аудитлог детали - кто что изменял) с auditlog (аудитлог) по столбцу auditid
SELECT * FROM auditlog_details WHERE auditid NOT IN (SELECT auditid FROM auditlog WHERE auditid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных сведений журнала аудита auditlog_details (таких как учетные данные входа)
SELECT COUNT(*) AS auditlog_missing_details FROM auditlog_details WHERE NOT auditid IN (SELECT auditid FROM auditlog); -- Это занимает экспоненциальное время, отключено
-- Удаление потерянных значанией
DELETE FROM auditlog_details WHERE auditid NOT IN (SELECT auditid FROM auditlog WHERE auditid is NOT NULL);

-- Сравниваем conditions (условия) с actions (Действия) по столбцу actionid
SELECT * FROM conditions WHERE actionid NOT IN (SELECT actionid FROM actions WHERE actionid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных условий conditions
SELECT COUNT(*) AS conditions_missing_acction FROM conditions WHERE NOT actionid IN (SELECT actionid FROM actions);

-- Сравниваем functions (функции) с items (Элементы данных) по столбцу itemid
SELECT * FROM functions WHERE itemid NOT IN (SELECT itemid FROM items WHERE itemid is NOT NULL);
-- Сравниваем functions (функции) с triggers (триггеры) по столбцу triggerid
SELECT * FROM functions WHERE triggerid NOT IN (SELECT triggerid FROM triggers WHERE triggerid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных функций functions
SELECT COUNT(*) AS functions_missing_item FROM functions WHERE NOT itemid IN (SELECT itemid FROM items);
SELECT COUNT(*) AS functions_missing_trigger FROM functions WHERE NOT triggerid IN (SELECT triggerid FROM triggers);

-- Сравниваем graphs_items (элементов графика) с graphs (Графики) по столбцу graphid
SELECT * FROM graphs_items WHERE graphid NOT IN (SELECT graphid FROM graphs WHERE graphid is NOT NULL);
-- Сравниваем graphs_items (элементов графика) с items (Элементы данных) по столбцу itemid
SELECT * FROM graphs_items WHERE itemid NOT IN (SELECT itemid FROM items WHERE itemid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных элементов графика graphs_items
SELECT COUNT(*) AS graph_items_missing_graphs FROM graphs_items WHERE NOT graphid IN (SELECT graphid FROM graphs);
SELECT COUNT(*) AS graph_items_missing_items FROM graphs_items WHERE NOT itemid IN (SELECT itemid FROM items);

-- Сравниваем hostmacro (макросы узлов) с hosts (узлы) по столбцу hostid
SELECT * FROM hostmacro WHERE hostid NOT IN (SELECT hostid FROM hosts WHERE hostid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для макроса потерянного хоста hostmacro
SELECT COUNT(*) AS host_macros_missing_hosts FROM hostmacro WHERE NOT hostid IN (SELECT hostid FROM hosts);

-- Сравниваем items (Элементы данных) с hosts (узлы) по столбцу hostid
SELECT * FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts WHERE hostid is NOT NULL);
-- Сравниваем items_applications (Элементы данных связи) с applications (Группы элементов данных) по столбцу applicationid
SELECT * FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications WHERE applicationid is NOT NULL);
-- Сравниваем items_applications (Элементы данных связи) с items (Элементы данных) по столбцу itemid
SELECT * FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items WHERE itemid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных данных элемента items
SELECT COUNT(itemid) AS items_missing_hosts FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts);
SELECT COUNT(*) AS item_apps_missing_apps FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications);
SELECT COUNT(*) AS item_apps_missing_items FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items);

-- Сравниваем httpstep (шаги веб проверки в узле) с httptest (веб тест) по столбцу httptestid
SELECT * FROM httpstep WHERE httptestid NOT IN (SELECT httptestid FROM httptest WHERE httptestid is NOT NULL);
-- Сравниваем httptestitem (веб-тест-элемент) с httpstep (шаги веб проверки в узле) по столбцу httpstepid
SELECT * FROM httpstepitem WHERE httpstepid NOT IN (SELECT httpstepid FROM httpstep WHERE httpstepid is NOT NULL);
-- Сравниваем httptestitem (веб-тест-элемент) с items (Элементы данных) по столбцу itemid
SELECT * FROM httpstepitem WHERE itemid NOT IN (SELECT itemid FROM items WHERE itemid is NOT NULL);
-- Сравниваем httpstep (шаги веб проверки в узле) с applications (группы элементов данных) по столбцу applicationid
SELECT * FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications WHERE applicationid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных данных проверки HTTP httpstep
SELECT COUNT(*) AS http_step_missing_tests FROM httpstep WHERE NOT httptestid IN (SELECT httptestid FROM httptest);
SELECT COUNT(*) AS http_step_items_missing_steps FROM httpstepitem WHERE NOT httpstepid IN (SELECT httpstepid FROM httpstep);
SELECT COUNT(*) AS http_step_items_missing_items FROM httpstepitem WHERE NOT itemid IN (SELECT itemid FROM items);
SELECT COUNT(*) AS http_test_missing_apps FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications);

-- Сравниваем maintenances_groups (группы обслуживания) с maintenances (Периоды обслуживания) по столбцу maintenanceid
SELECT * FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances WHERE maintenanceid is NOT NULL);
-- Сравниваем maintenances_hosts (узлы обслуживания) с maintenances (Периоды обслуживания) по столбцу maintenanceid
SELECT * FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances WHERE maintenanceid is NOT NULL);
-- Сравниваем maintenances_hosts (узлы обслуживания) с hosts (узлы) по столбцу hostid
SELECT * FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts WHERE hostid is NOT NULL);
-- Сравниваем maintenances_windows (Обслуживания windows) с maintenances (Периоды обслуживания) по столбцу maintenanceid
SELECT * FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances WHERE maintenanceid is NOT NULL);
-- Сравниваем maintenances_windows (Обслуживания windows) с timeperiods (периодичность) по столбцу timeperiodid
SELECT * FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods WHERE timeperiodid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных данных обслуживания maintenances
SELECT COUNT(*) AS maintenance_group_missing_maintenances FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
-SELECT COUNT(*) AS maintenance_group_missing_groups FROM maintenances_groups WHERE groupid NOT IN (SELECT groupid FROM groups);
SELECT COUNT(*) AS maintenances_hosts_missing_maintenances FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
SELECT COUNT(*) AS maintenances_hosts_missing_hosts FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts);
SELECT COUNT(*) AS maintenances_windows_missing_maintenances FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
SELECT COUNT(*) AS maintenances_windows_missing_windows FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods);

-- Сравниваем mappings (Преобразование значений) с valuemaps (значений название) по столбцу valuemapid
SELECT * FROM mappings WHERE valuemapid NOT IN (SELECT valuemapid FROM valuemaps WHERE valuemapid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных сопоставлений mappings
SELECT COUNT(*) AS mappings_missing_valuemap FROM mappings WHERE NOT valuemapid IN (SELECT valuemapid FROM valuemaps);

-- Сравниваем media (способы оповещения) с users (пользователи) по столбцу userid
SELECT * FROM media WHERE userid NOT IN (SELECT userid FROM users WHERE userid is NOT NULL);
-- Сравниваем media (способы оповещения) с media_type (тип оповещения) по столбцу mediatypeid
SELECT * FROM media WHERE mediatypeid NOT IN (SELECT mediatypeid FROM media_type WHERE mediatypeid is NOT NULL);
-- Сравниваем rights (права) с usrgrp (группы) по столбцу usrgrpid
SELECT * FROM rights WHERE groupid NOT IN (SELECT usrgrpid FROM usrgrp WHERE usrgrpid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных носителей / пользовательских элементов media / user items
SELECT COUNT(*) AS media_missing_user FROM media WHERE NOT userid IN (SELECT userid FROM users);
SELECT COUNT(*) AS media_missing_type FROM media WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
SELECT COUNT(*) AS rights_missing_user FROM rights WHERE NOT groupid IN (SELECT usrgrpid FROM usrgrp);

-- Сравниваем screens_items (элементы экраны) с screens (экраны) по столбцу screenid
SELECT * FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens WHERE screenid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных экранов screens
SELECT COUNT(*) AS screens_items_missing_screen FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens);

-- Сравниваем sessions (сессии пользователей) с users (пользователи) по столбцу userid
SELECT * FROM sessions WHERE userid NOT IN (SELECT userid FROM users WHERE userid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных сессий не существующих пользователей
SELECT COUNT(*) AS sessions_missing_user FROM sessions WHERE NOT userid IN (SELECT userid FROM users);
-- Выбираем сессии пользователей Все что старше 3х месяцев
SELECT * FROM `zabbixdb`.`sessions` WHERE `lastaccess` < (UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)));
-- Удалить сессии пользователей все что старше 3х месяцев
DELETE FROM `zabbixdb`.`sessions` WHERE `lastaccess` < (UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)));

-- Сравниваем trigger_depends (триггер зависит) с triggers (триггеры) по столбцам triggerid_down и triggerid
SELECT * FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers WHERE triggerid is NOT NULL);
-- Сравниваем trigger_depends (триггер зависит) с triggers (триггеры) по столбцам triggerid_up и triggerid
SELECT * FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers WHERE triggerid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Подсчитайте количество строк, которые будут удалены для потерянных событий и триггеров triggers
SELECT COUNT(*) AS trigger_down_dependency_missing_trigger FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers);
SELECT COUNT(*) AS trigger_up_dependency_missing_trigger FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers);

-- Строки, потерянных таблицы восстановленных событий event_recovery (если из всех 3х не совпало можно считать потерянными)
-- Сравниваем event_recovery (восстановленные события) c events (события проблемы) по столбцу eventid
SELECT * FROM event_recovery WHERE eventid NOT IN (SELECT eventid FROM events WHERE eventid is NOT NULL);
-- Сравниваем event_recovery (восстановленные события) c events (события проблемы) по столбцам eventid и r_eventid
SELECT * FROM event_recovery WHERE r_eventid NOT IN (SELECT eventid FROM events WHERE r_eventid is NOT NULL);
-- Сравниваем event_recovery (восстановленные события) c events (события проблемы) по столбцам eventid и c_eventid
SELECT * FROM event_recovery WHERE c_eventid NOT IN (SELECT eventid FROM events WHERE c_eventid is NOT NULL);
-- Совпадение не найдено не в одном из 3х столбцов
SELECT * FROM event_recovery WHERE NOT eventid IN (SELECT eventid FROM events) AND NOT r_eventid IN (SELECT eventid FROM events) AND NOT c_eventid IN (SELECT eventid FROM events);
--------------------------------------------------------------------------------------------------------------------------------------------
Создать условие к 3 столбцам "или"
-- Подсчитайте количество строк, которые будут удалены для потерянных восстановленных событий (если из всех 3х не совпало можно считать потерянными)
SELECT COUNT(*) AS event_recovery_missing_events FROM event_recovery WHERE NOT eventid IN (SELECT eventid FROM events);
SELECT COUNT(*) AS event_recovery_missing_events FROM event_recovery WHERE NOT r_eventid IN (SELECT eventid FROM events);
SELECT COUNT(*) AS event_recovery_missing_events FROM event_recovery WHERE NOT c_eventid IN (SELECT eventid FROM events);
-- Совпадение не найдено не в одном из 3х столбцов
SELECT COUNT(*) AS event_recovery_missing_events FROM event_recovery WHERE NOT eventid IN (SELECT eventid FROM events) AND NOT r_eventid IN (SELECT eventid FROM events) AND NOT c_eventid IN (SELECT eventid FROM events);

-- Строки, потерянных таблицы подавленных событий event_suppress
-- Сравниваем event_suppress (подавленные события) c events (события проблемы) по столбцу eventid
SELECT * FROM event_suppress WHERE eventid NOT IN (SELECT eventid FROM events WHERE eventid is NOT NULL);
-- Строки, потерянных таблицы тегов событий
--------------------------------------------------------------------------------------------------------------------------------------------
--Подсчитайте количество строк, которые будут удалены для потерянных подавленных событий
SELECT COUNT(*) AS event_suppress_missing_events FROM event_suppress WHERE NOT eventid IN (SELECT eventid FROM events);

-- Сравниваем event_tag (тег события) c events (события проблемы) по столбцу eventid
SELECT * FROM event_tag WHERE eventid NOT IN (SELECT eventid FROM events WHERE eventid is NOT NULL);
--------------------------------------------------------------------------------------------------------------------------------------------
--Подсчитайте количество строк, которые будут удалены для потерянных тегов событий
SELECT COUNT(*) AS event_tag_missing_events FROM event_tag WHERE NOT eventid IN (SELECT eventid FROM events);

-- Строки, потерянных таблицы проблем problem (возможно есть проблемы которые не относятся к событиям?)
-- Сравниваем problem (проблемы) с events (события проблемы) по столбцу eventid
SELECT * FROM problem WHERE eventid NOT IN (SELECT eventid FROM events WHERE eventid is NOT NULL);
-- Сравниваем problem (проблемы) с event_recovery (восстановленные события) по столбцам eventid и r_eventid
SELECT * FROM problem WHERE eventid NOT IN (SELECT r_eventid FROM event_recovery WHERE r_eventid is NOT NULL);
-- Проверим сразу для 2х столбцов
SELECT * FROM problem WHERE NOT eventid IN (SELECT eventid FROM events) AND NOT eventid IN (SELECT r_eventid FROM event_recovery);
--------------------------------------------------------------------------------------------------------------------------------------------
-- Строки, потерянных проблем (возможно есть проблемы которые не относятся к событиям?)
SELECT COUNT(*) AS problem_missing_events FROM problem WHERE NOT eventid IN (SELECT eventid FROM events);
SELECT COUNT(*) AS problem_missing_events FROM problem WHERE NOT eventid IN (SELECT r_eventid FROM event_recovery);
-- Проверим сразу для 2х столбцов
SELECT COUNT(*) AS problem_missing_events FROM problem WHERE NOT eventid IN (SELECT eventid FROM events) AND NOT eventid IN (SELECT r_eventid FROM event_recovery);

Просмотр и Удаление данных все что старше 3 месяца таблица problem
SELECT * FROM `zabbixdb`.`problem` WHERE `clock` < (UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)));
DELETE FROM `zabbixdb`.`problem` WHERE `clock` < (UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)));

Примерно пока получаем

USE `zabbixdb`;

DELIMITER $$
--
-- Процедуры
--

DROP PROCEDURE IF EXISTS `DELETE_3_MONTH`$$
CREATE PROCEDURE `DELETE_3_MONTH`()
COMMENT 'Удаление истририи более 3х месяцев и потерянных данных'
BEGIN

DELETE FROM `auditlog_details` WHERE `auditid` NOT IN (SELECT `auditid` FROM `auditlog` WHERE `auditid` is NOT NULL);
DELETE FROM `sessions` WHERE `lastaccess` < (UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)));
DELETE FROM `problem` WHERE `clock` < (UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)));
UPDATE `triggers` SET value=0, lastchange=UNIX_TIMESTAMP(NOW()), error='' WHERE `lastchange` < (UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 3 MONTH))) AND `value` = 1;

END

END$$

DELIMITER ;


DELIMITER $$
--
-- События
--
CREATE EVENT `DELETE_3_MONTH`
ON SCHEDULE EVERY 1 DAY
STARTS '2021-10-18 00:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Удаление данных старше 3 месяцев'
DO BEGIN
CALL `DELETE_3_MONTH`();
END$$

DELIMITER ;

[Артём Мамзиков]

Для переноса базы данных на другой сервер без истории исключаем следующие таблицы с историей
acknowledges
alerts
auditlog
events
history
history_log
history_str
history_text
history_uint
trends
trends_uint
service_alarms
housekeeper
problem - содержит ключи связанные с eventid
problem_tag - содержит ключи связанные с eventid
auditlog_details
escalations
history_str_sync
history_sync

На примере phpmyadmin
так как у нас сделано партиционирование базы выгрузим вначале пустые таблицы, иначе потом будет ругаться что таких таблиц нет в базе.
В Экспорте базы данных zabbixdb выбираем структуру следующих таблиц
acknowledges
alerts
auditlog
events
history
history_log
history_str
history_text
history_uint
housekeeper
problem
problem_tag
service_alarms
trends
trends_uint

Ставим галки
V Добавить выражение CREATE DATABASE / USE
V Добавить выражение DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER
V Добавить выражение CREATE PROCEDURE / FUNCTION / EVENT
Использовать оператор при сохранении данных: REPLACE - INSERT может конфликтовать при одинаковых данных
Остальное оставляем по умолчанию Нажимаем Вперед

Выгрузка пустых таблиц истории

Теперь выгружаем оставшиеся таблицы с данными со всеми настройками шаблонами узлами дашбортами и т.д.

Таблицы с данными

Все остальные настройки аналогичны. Делаем выгрузку.
Открываем базу с данными notepad++ у кого mariadb новой версии
так как у нас много столбцов в таблице host_inventory выходит ошибка Подробнее
Находим строку CREATE TABLE `host_inventory` и перед ней вставляем 2 строки
SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=OFF;
Сохраняем.

Далее на другом сервер делаем загрузку Импорт
базы данных zabbixdb не существует на данном сервере при импорте она создастся при первом импорте.
Сперва загружаем Пустые таблицы, после таблицы с данными. Процесс не быстрый (200мб минут-20-30).
При первой загрузке если выйдет ошибка про e_part_manage ничего страшного потом просто проверьте ее все ли ок (событие)

Импорт базы с данными 2 этап

Получаем что все ОК

Все проверяем, и переносим свои скрипты параметры крона и тд что осталось.


Пример Выгрузки через командную строку
mysqldump -uzabbix -pПароль zabbixdb \
–ignore-table=zabbix.history \
–ignore-table=zabbix.history_uint \
–ignore-table=zabbix.history_text \
–ignore-table=zabbix.trends \
–ignore-table=zabbix.trends_uint \
| > zabbix_backup.sql

Только конфигурация:
Бэкап конфигурации Zabbix (содержит данные о хостах, элементах, шаблонах и тд).
Данные мониторинга (история, тренды, события) не включены.
mysqldump -uzabbix -pПароль zabbixdb \
–ignore-table=zabbixdb.acknowledges \
–ignore-table=zabbixdb.alerts \
–ignore-table=zabbixdb.auditlog \
–ignore-table=zabbixdb.auditlog_details \
–ignore-table=zabbixdb.escalations \
–ignore-table=zabbixdb.events \
–ignore-table=zabbixdb.history \
–ignore-table=zabbixdb.history_log \
–ignore-table=zabbixdb.history_str \
–ignore-table=zabbixdb.history_str_sync \
–ignore-table=zabbixdb.history_sync \
–ignore-table=zabbixdb.history_text \
–ignore-table=zabbixdb.history_uint \
–ignore-table=zabbixdb.history_uint_sync \
–ignore-table=zabbixdb.trends \
–ignore-table=zabbixdb.trends_uint \
| > zabbixdb.sql

Если необходимо отключить авторизацию LDAP
mysql -u root -p
update zabbixdb.config set authentication_type='0' where configid='1';

Сброс пароля пользователю Admin.
UPDATE users SET passwd=md5('zabbix') WHERE alias='Admin';

[Артём Мамзиков]

Еще о База данных Заббикс