Going MySQL 5.7 partitioning, CentOS 7, Zabbix

prerequsites for db server:

yum -y install bzip2 perl-Sys-Syslog perl-DateTime

stop incomming data:

systemctl stop {zabbix-server,httpd}
systemctl status {zabbix-server,httpd}

backup configuration

sudo mysqldump \
--flush-logs \
--single-transaction \
--create-options \
--ignore-table=zabbix.acknowledges \
--ignore-table=zabbix.alerts \
--ignore-table=zabbix.auditlog \
--ignore-table=zabbix.auditlog_details \
--ignore-table=zabbix.escalations \
--ignore-table=zabbix.events \
--ignore-table=zabbix.history \
--ignore-table=zabbix.history_log \
--ignore-table=zabbix.history_str \
--ignore-table=zabbix.history_str_sync \
--ignore-table=zabbix.history_sync \
--ignore-table=zabbix.history_text \
--ignore-table=zabbix.history_uint \
--ignore-table=zabbix.history_uint_sync \
--ignore-table=zabbix.profiles \
--ignore-table=zabbix.service_alarms \
--ignore-table=zabbix.sessions \
--ignore-table=zabbix.trends \
--ignore-table=zabbix.trends_uint \
--ignore-table=zabbix.user_history \
--ignore-table=zabbix.node_cksum zabbix | gzip > /root/conf.zbx.gz

backup all 7 biggest tables:

cd
time=$(date +%Y%m%d%H%M)
mysqldump -uroot -p --flush-logs --single-transaction --no-create-db --no-create-info zabbix history_uint | bzip2 -9 > $time.history_uint.bz2
mysqldump -uroot -p --flush-logs --single-transaction --no-create-db --no-create-info zabbix history_log | bzip2 -9 > $time.history_log.bz2
mysqldump -uroot -p --flush-logs --single-transaction --no-create-db --no-create-info zabbix history_str | bzip2 -9 > $time.history_str.bz2
mysqldump -uroot -p --flush-logs --single-transaction --no-create-db --no-create-info zabbix history_text | bzip2 -9 > $time.history_text.bz2
mysqldump -uroot -p --flush-logs --single-transaction --no-create-db --no-create-info zabbix history | bzip2 -9 > $time.history.bz2
mysqldump -uroot -p --flush-logs --single-transaction --no-create-db --no-create-info zabbix trends | bzip2 -9 > $time.trends.bz2
mysqldump -uroot -p --flush-logs --single-transaction --no-create-db --no-create-info zabbix trends_uint | bzip2 -9 > $time.trends_uint.bz2
ls -l $time*

cold backup:

systemctl stop mysqld
systemctl status mysqld
mkdir -p /root/original
cd /var/lib
time cp -R mysql /home/original
systemctl start mysqld

bare minimum mysql configuration:

# this is tested and works on mysql  Ver 15.1 Distrib 5.5.56-MariaDB
systemctl stop mysqld

vim /etc/my.cnf.d/server.cnf

[mysqld]
innodb_buffer_pool_size = 256M # ~75% of total available memory, for example if you have 8GB of RAM then set it to 6G
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
query_cache_type = 0
max_connections = 1000
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1

rm /var/lib/mysql/ib_logfile*
systemctl start mysqld

clear the tables, recreate with same structure:

mysql -uroot -p
use zabbix;

SHOW ENGINE INNODB STATUS\G

show create table history\G
drop table history;
CREATE TABLE `history` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `value` double(16,4) NOT NULL DEFAULT 0.0000, `ns` int(11) NOT NULL DEFAULT 0, KEY `history_1` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

show create table history_log\G
drop table history_log;
CREATE TABLE `history_log` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `timestamp` int(11) NOT NULL DEFAULT 0, `source` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '', `severity` int(11) NOT NULL DEFAULT 0, `value` text COLLATE utf8_bin NOT NULL, `logeventid` int(11) NOT NULL DEFAULT 0, `ns` int(11) NOT NULL DEFAULT 0, KEY `history_log_1` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

show create table history_str\G
drop table history_str;
CREATE TABLE `history_str` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `value` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `ns` int(11) NOT NULL DEFAULT 0, KEY `history_str_1` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

show create table history_text\G
drop table history_text;
CREATE TABLE `history_text` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `value` text COLLATE utf8_bin NOT NULL, `ns` int(11) NOT NULL DEFAULT 0, KEY `history_text_1` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

show create table history_uint\G
drop table history_uint;
CREATE TABLE `history_uint` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `value` bigint(20) unsigned NOT NULL DEFAULT 0, `ns` int(11) NOT NULL DEFAULT 0, KEY `history_uint_1` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


show create table trends\G
drop table trends;
CREATE TABLE `trends` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `num` int(11) NOT NULL DEFAULT 0, `value_min` double(16,4) NOT NULL DEFAULT 0.0000, `value_avg` double(16,4) NOT NULL DEFAULT 0.0000, `value_max` double(16,4) NOT NULL DEFAULT 0.0000, PRIMARY KEY (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

show create table trends_uint\G
drop table trends_uint;
CREATE TABLE `trends_uint` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT 0, `num` int(11) NOT NULL DEFAULT 0, `value_min` bigint(20) unsigned NOT NULL DEFAULT 0, `value_avg` bigint(20) unsigned NOT NULL DEFAULT 0, `value_max` bigint(20) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

create partitions for trends:

ALTER TABLE `trends` PARTITION BY RANGE(clock)
(
PARTITION p2018_01 VALUES LESS THAN (UNIX_TIMESTAMP("2018-02-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_02 VALUES LESS THAN (UNIX_TIMESTAMP("2018-03-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_03 VALUES LESS THAN (UNIX_TIMESTAMP("2018-04-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_04 VALUES LESS THAN (UNIX_TIMESTAMP("2018-05-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_05 VALUES LESS THAN (UNIX_TIMESTAMP("2018-06-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_06 VALUES LESS THAN (UNIX_TIMESTAMP("2018-07-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_07 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_08 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_09 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_10 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-01 00:00:00")) ENGINE=InnoDB
);
show create table trends\G

ALTER TABLE `trends_uint` PARTITION BY RANGE(clock)
(
PARTITION p2018_01 VALUES LESS THAN (UNIX_TIMESTAMP("2018-02-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_02 VALUES LESS THAN (UNIX_TIMESTAMP("2018-03-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_03 VALUES LESS THAN (UNIX_TIMESTAMP("2018-04-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_04 VALUES LESS THAN (UNIX_TIMESTAMP("2018-05-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_05 VALUES LESS THAN (UNIX_TIMESTAMP("2018-06-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_06 VALUES LESS THAN (UNIX_TIMESTAMP("2018-07-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_07 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_08 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_09 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-01 00:00:00")) ENGINE=InnoDB,
PARTITION p2018_10 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-01 00:00:00")) ENGINE=InnoDB
);
show create table trends_uint\G

restore content:

echo $time
ls -l $time*
bzcat $time.history_log.bz2 | mysql -uroot -p zabbix
bzcat $time.history_str.bz2 | mysql -uroot -p zabbix
bzcat $time.history_text.bz2 | mysql -uroot -p zabbix
bzcat $time.history_uint.bz2 | mysql -uroot -p zabbix
bzcat $time.history.bz2 | mysql -uroot -p zabbix
bzcat $time.trends.bz2 | mysql -uroot -p zabbix
bzcat $time.trends_uint.bz2 | mysql -uroot -p zabbix

starting back instance:

systemctl start zabbix-server
tail -100f /var/log/zabbix/zabbix_server.log
systemctl start httpd

allow perl script to manage the zabbix database:

GRANT SELECT, UPDATE, DELETE, INSERT ON zabbix.* TO 'zabbix_part'@'localhost' identified by 'wasJPdQnVUTwJvFx';

schedule to execute:

Going MySQL 5.7 partitioning, CentOS 7, Zabbix

No comments:

Post a Comment

solve, automate, speed up

Zabbix MySQL Partitioning

Going MySQL 5.7 partitioning, CentOS 7, Zabbix

No comments:

Post a Comment