1. Zabbix server setup:
a. To have running zabbix serve on the same host where is running proxy tcp port on which is listening server needs to be changed from standard tcp/10051 to ie. tcp/10100 by:
echo “ListenPort=10100” >/etc/zabbix/zabbix_server.d/ListenPort
2. Zabbix proxy DB setup:
a. Create zabbix proxy database
mysql -e “create database zabbix_proxy character set utf8 collate utf8_bin”
b. Import zabbix proxy database scheme
zcat /usr/share/doc/zabbix-proxy-mysql-*/schema.sql.gz |mysql zabbix_proxy
c. Create zabbix proxy user and grant privs
mysql -e “CREATE USER ‘zabbix-proxy’@’localhost’ IDENTIFIED BY ‘XXXXXXXXXXX’;”
mysql -e “GRANT SELECT, INSERT, UPDATE, DELETE on zabbix_proxy.* to ‘zabbix-proxy’@’localhost’;”
mysql -e “FLUSH PRIVILEGES;”
3. Zabbix proxy setup:
a. In /etc/zabbix/zabbix_proxy.conf added line
Include=/etc/zabbix/zabbix_proxy.d/
b. Overwrite default settings:
echo “DBHost=localhost” > /etc/zabbix/zabbix_proxy.d/DBHost
echo “ProxyMode=0” > /etc/zabbix/zabbix_proxy.d/ProxyMode
echo “ProxyOfflineBuffer=6” > /etc/zabbix/zabbix_proxy.d/ProxyOfflineBuffer
echo “DBName=zabbix_proxy” > /etc/zabbix/zabbix_proxy.d/DBName
echo “DBUser=zabbix_proxy” > /etc/zabbix/zabbix_proxy.d/DBUser
echo “DBPassword=crow7tree4” > /etc/zabbix/zabbix_proxy.d/DBPassword
echo “ServerPort=10100” > /etc/zabbix/zabbix_proxy.d/ServerPort
echo “ConfigFrequency=60” > /etc/zabbix/zabbix_proxy.d/ConfigFrequency
echo “Server=localhost” > /etc/zabbix/zabbix_proxy.d/Server
4. Zabbix agent setup:
a. Enable zabbix agent remote commands
- echo EnableRemoteCommands=1 >/etc/zabbix/zabbix_agentd.d/EnableRemoteCommands
b. ServerActive=10.102.83.60
c. StartAgents=0
d. HostMetadataItem=system.uname
5. Autoregistration actions
a. HostMetadataItem content can be used on automatic assignment of templates to host.
b. For example on my laptop I have two autoregistration actions: one for add MySQL template and second used to add OS Linux template. Both Actions are using HostMetadataItem content like below:
c. # cat /etc/zabbix/zabbix_agentd/HostMetadata
e. With above I was able to define on my laptop two actions:
Add template OS Linux Host metadata like OS::Linux
Proxy = my-proxy Link to templates: OS Linux
Add template Service MySQL Host metadata like SVC::MySQL
Proxy = my-proxy Link to templates: Service MySQL
6. MySQL database monitoring demonstration (monitoring zabbix DB backend)
a. Create monitoring account
mysql -e “CREATE USER ‘monitoring’@’localhost’ IDENTIFIED BY ‘monitoring’;”
mysql -e “GRANT SELECT, INDEX, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ‘monitoring’@’localhost’;”
mysql -e “FLUSH PRIVILEGES;”
7. MySQL server DB settings changes:
log-bin = mysql-bin
Expire_logs_days = 1
Transaction-isolation = READ-COMMITTED
Binlog_format = MIXED
Event_scheduler = ON
Innodb_boofer_pool = 4G
Innodb_file_per_table = ON
Innodb_flush_logs_at_trx_commit = 2
Innodb_doublewrite = false
Innodb_flush_method = O_DIRECT
Innodb_max_ditry_pages = 0
Innodb_support_xa = false
Max_allowed_packets = 64M
8. Add MySQL history tables partitioning
Maintenance of partitions should be done from root mysql account so zabbix@ account does not need to have granted permissions allowing create and drop partitions.
To add partitioning need to be done:
a. Importing partitioning.sql file containing SQL procedures used on create and maintainance partitions. This file needs to be imported to root account by:
- cat partitioning.sql mysql -uroot
Short description imported procedures:
i. partition_create - create a partition on the given table in the given <schema>.partition_drop - This will drop partitions older than the given timestamp on the given table in the given schema.
ii. partition_maintenance - it is responsible for parsing the given parameters and then creating/dropping partitions as needed
iii. partition_verify - checks if partitioning is enabled on the given table in the given schema. If it is not enable, it creates a single partition.
b. Add partitioning can be added by execute in mysql command queries:
CALL partition_maintenance(‘zabbix’, ‘history’, 14, 24, 7);
CALL partition_maintenance(‘zabbix’, ‘history_uint’, 14, 24, 7);
ALTER TABLE history_log drop PRIMARY KEY, ADD INDEX (id), DROP INDEX history_log_2, ADD INDEX history_log_2 (itemid, id);
CALL partition_maintenance(‘zabbix’, ‘history_log’, 14, 24, 7);
CALL partition_maintenance(‘zabbix’, ‘history_str’, 14, 24, 7);
ALTER TABLE history_text DROP PRIMARY KEY, ADD INDEX (id), DROP INDEX history_text_2, ADD INDEX history_text_2 (itemid, id);
CALL partition_maintenance(‘zabbix’, ‘history_text’, 14, 24, 7);
Above:
i. Changes setting of primary keys on history_log and hoistory_text tables and adds initial partitioning
ii. Adds maintaining 14 daily partitions, each one with 24h data and creates 7 new partitions for next 7 days
c. Additionally event maintaining partitioning needs to be created by
- cat partitioning_event.sql | mysql -uroot
This file contains:
DELIMITER $$
CREATE EVENT IF NOT EXISTS `part_manage`
ON SCHEDULE EVERY 1 DAY
STARTS ‘2016-08-02 00:05:00’
ON COMPLETION PRESERVE
ENABLE
COMMENT ‘Zabbix partitioning’
DO BEGIN
CALL partition_maintenance(‘zabbix’, ‘history’, 14, 24, 7);
CALL partition_maintenance(‘zabbix’, ‘history_uint’, 14, 24, 7);
CALL partition_maintenance(‘zabbix’, ‘history_log’, 14, 24, 7);
CALL partition_maintenance(‘zabbix’, ‘history_str’, 14, 24, 7);
CALL partition_maintenance(‘zabbix’, ‘history_text’, 14, 24, 7);
END$$
DELIMITER ;
If it will be any needs to change how long raw history data needs to be kept above zabbix_part_manager event needs to be dropped, changed settings in this event and event needs to be created again.
d. Disable over zabbix web frontend housekeeping history tables
Administration->General->Housekeeping-> History -> uncheck “Enable internal housekeeping” -> Update
Other things done:
1. Changed global settings of the inventory mode to “auto”
Administration->General-->Other-> change “Default host inventory mode” to “Automatic” -> Update
2. Fixed problem with unknown MySQL root password. Reset DB backend root password
a. Stop mysqld service
b. Start manually
/usr/libexec/mysqld basedir=/usr datadir=/var/lib/mysql user=mysql log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid socket=/var/lib/mysql/mysql.sock --skip-grant-tables
c. Set root password
mysql> update user set password=PASSWORD(“XXXXXXX”) where user=’root’;
d. killall mysqld
e. Start mysqld service