HotDB For Zabbix(Standard) Installation

Introduction

HotDB for Zabbix (Standard) is a distributed database solution especially used under Zabbix 2.2/3.0/3.2.

It can provide centralized database experience under distributed database environment.

Server Parameter Recommendation

Installation

1. Packet Decompression

You need to download and extract two packages below:

tar zxvf hotdb-for-zabbix-rpm.tar.gz 
tar zxvf hotdb-for-zabbix-v2.5.8_user.tar.gz

Then move rpm folder to hotdb-for-zabbix-v2.5.8 folder:

mv rpm hotdb-for-zabbix-v2.5.8

Enter hotdb-for-zabbix-v2.5.8 folder to start installation:

conf : yum source

hotdb : HotDB program files

include : script files

rpm: JDK rpm file and MySQL rpm

install_hotdbforzabbix.sh : installation script

2. Install MySQL

Execute installation script below on four MySQL databases:

sh install_hotdb_for_zabbix.sh

Enter one then continue

2.1 Judgement of successful installation

If you have installed correctly, you will get results below:

a. Check port 3306

netstat -tnlp | grep mysql

b. Login MySQL and check user tables and database tables

select user,host from mysql.user;

show databases;

Otherwise, you have failed to install MySQL databases.

3.Install HotDB

3.1 Modify IP address of MySQL

Find datasource_list file under include file fold,change 4 MySQL databases’IP information in format bellow:

From left to the right we can see: IP, port and database name. And you can only change the IP part.

INCORRECT IP ADDRESS WILL LEAD TO FAILURE OF HOTDB INSTALLATION.

3.2 Install HotDB

sh install_hotdb_for_zabbix.sh

Enter two then choose your Zabbix version ( Default: v3.2 )

If there's already a MySQL process that port is 3306,then you need to choose whether you want to use it as a repository.

There a logic database called Zabbix in HotDB for Zabbix, which can provide centralized database experience under distributed database environment. You need to choose whether to initialize it or you can create one by yourself.

If you choose n, you will need to import Zabbix initial table and data, or migrate existed data in Zabbix database by yourself.

3.3 Judgement of successful installation

a. Check port 3323

netstat -tnlp | grep 3323

b. Log in

mysql -uroot -proot -P3323 -h127.0.0.1

3.4 HotDB Management (Web)

You can visit website to manage HotDB : http://192.168.220.104:3324 (IP address here is HotDB's IP and we don't have English version)

Both username and password are "admin". Please use chrome or firefox.

HotDB for Zabbix has divided all the tables Zabbix used into 4 pieces, which we called datanode.

You can find all the tables here. MySQL databases are under datanodes. And you can install two MySQL databases under one datanode to keep high availability.You can set master-slave relationship in failover setting (the tab after datasource/MySQL database setting).

After you changed things in management, you need to press dynamic loading button to load information at HotDB-server.

Zabbix Data Migration

1. Statement

When exporting data from Zabbix, it is not recommended to modify Zabbix’s configuration, template, and so on. If you want data from migration process to be retained in the new environment, then you need to open binlog in Zabbix’s MySQL server, whose format is STATEMENT or MIXED (MIXED requires the database isolation level to be REPEATABLE-READ). After data import is complete, you need to perform optional data catch up steps.

As Zabbix use foreign key, and HotDB for Zabbix has not yet fully supported foreign key. To avoid such error while importing data, mysqldump order will be executed in several times, so the data is not derived in one transaction. Therefore, if there is an error about foreign key, you can ignore it or restart exporting data, you can also stop Zabbix service to export data. You may also notice that there will be a primary key conflict when catching up data, which also caused by data not derived in a transaction reasons, which can also be ignored.

2. Data Export

This section explains situation when Zabbix database use MySQL 5.6, any other MySQL version may require more or less necessary parameters when using the mysqldump command in the script.

Find script in hotdb-server/conf/tool,execute it and you will see parameters required:

[root@vm1 tmp]# sh zabbix_dump.sh 
invalid input invalid mysqldumpcmd client command

Example

sh zabbixdump.sh 
"mysqldump -h 127.0.0.1 -P 3306 -uroot -proot" zabbix /data

Parameter 1 : Require one mysqldump command that can connect Zabbix Database, which need MySQL connect information, such as IP/port or SOCKET address, username ,password. You need to use " "

Parameter 2 : The name of Zabbix database, default as Zabbix

Parameter 3 : File path of export data, default as /data

Since data export may take a long time, we suggest user use screen, then you will get result like below:

-rw-r--r-- 1 root root   48301 Dec  5 11:20 Zabbixdatadump20161205.sql
-rw-r--r-- 1 root root   10361 Nov 18 18:25 Zabbix_dump.sh
-rw-r--r-- 1 root root   90960 Dec  5 11:20 Zabbixschemadump20161205.sql
-rw-r--r-- 1 root root 2196863 Dec  5 11:20 Zabbixtemplatedump20161205.sql

3. Data Import

You can login HotDB for Zabbix like the way you login MySQL.

For example

mysql -h 127.0.0.1 -P 3323 -u root -proot

Import data like the way below:(Zabbix should be configured and loaded successfully)

mysql -h 127.0.0.1 -P 3323 -u root -proot < Zabbixschemadump20161205.sql
mysql -h 127.0.0.1 -P 3323 -u root -proot < Zabbixtemplatedump20161205.sql

Since Zabbixdatadump20161205.sql has large volume so that we need to import it concurrently, execute scripts below and you will see sample command :

[root@vm1 tmp]# sh splitimport.sh 

Example

sh splitimport.sh 
"mysql -h 127.0.0.1 -P 3323 -uhotdb -photdb hotdb" /tmp/data.sql

Parameter 1 : The command to connect target MySQL to import data, which require MySQL connect information, such as IP/port or SOCKET path, username ,password and database name. These content needs " " symbol.

Parameter 2 : The name of exported SQL file

Drop Table: If you need to drop tables, please find the SQL file under hotdb-server/conf/inisqlza*. The number after za is present for zabbix verssion.

For example

util.sql under ini_sql_za32 is to drop tables of zabbix 3.2

util.sql under ini_sql_za30 is to drop tables of zabbix 3.0

4. Data Catch Up

HotDB for Zabbix cannot execute binlog in row format so that binlog’s format will be the main reason whether data can catch up successfully.

Data catch up will use mysqlbinglog command to parse binglog into SQL statements using literals, and HotDB for Zabbix will execute them.

4.1 Find binlog in Zabbixtemplatedump20161205.sql.

For example

[root@vm1 tmp]# grep "CHANGE MASTER TO" Zabbixtemplatedump20161205.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000029',
MASTER_LOG_POS=120;

4.2 Binlog Parsing and Execute in HotDB for Zabbix

 mysqlbinlog -R -h 127.0.0.1 -P 3306 -u root -proot --base64-output=decode-rows --skip-gtids --short-form --to-last-log --stop-never --start-position=120 mysql-bin.000029 |sed -u 's/\/\*!\*\/;/;/g'|egrep -v 'SET @@SESSION.PSEUDO_SLAVE_MODE=|SET @@session.max_insert_delayed_threads=|SET @OLD_COMPLETION_TYPE=|^DELIMITER|^SET TIMESTAMP=1.........;|^SET @@session|^use'|mysql -f -h 192.168.220.104 -P 3323 -u root -proot zabbix

You need to replace 192.168.220.104 with the real IP address of HotDB.

--start-position=120 mysql-bin.000029

The binlog location here should be replaced with the real address, too.

FAQ

1. Why does this happen?

If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

If you have this problem. Please set lower_casetablenames as 1.

The solution below only suit for three situations here:

Solution

  1. Change or add lowercasetable_names=1 in every datasource, including my.cnf file
  2. Stop Zabbix_Server to prevent partly submit(HotDB for Zabbix Standard version doesn't support strong consistency, partly submit may occur when database failover)
  3. Restart all MySQL databases
  4. Restart Zabbix_Server
  5. Check whether problem has been solved