How to add logger tables: Difference between revisions
(Created page with "=How to add logger tables= {{KB|{{ZC}}|{{ZCS 8.8}}|{{ZCS 8.7}}||}} {{WIP}} ==Problem== Zimbra includes the logger service, that collects data from different servers for the...") |
|||
Line 6: | Line 6: | ||
==Problem== | ==Problem== | ||
Zimbra includes the logger service, that collects data from different servers for the state of the different services. If multiserver installation is in place, there must be only one logger server, and the rest of the server are viewed as clients. Each client and the logger server keep a database for the known servers in the setup, placed in SQLite3 database. | Zimbra includes the logger service, that collects data from different servers for the state of the different services. If multiserver installation is in place, there must be only one logger server, and the rest of the server are viewed as clients. Each client and the logger server keep a database for the known servers in the setup, placed in SQLite3 database. Sometimes, due to power outage or different reasons, the table that keeps the data for the other servers [Hosts], might get deleted. In this wiki we will see how to recreate this and the rest of the tables from the logger DB. | ||
==Resolution== | ==Resolution== | ||
1. Enter | 1. Enter the SQLite3database: | ||
$ | $ sqlite3 /opt/zimbra/logger/db/data/logger.sqlitedb | ||
2. Once inside confirm | 2. Once inside confirm which tables exist: | ||
sqlite> .tables | |||
3. | 3. If tables are missing, recreate them with the following lines. For each table, just copy and paste the text to the sqlite command line: | ||
4. | CREATE TABLE hosts ( | ||
id INTEGER PRIMARY KEY, | |||
dns_hostname VARCHAR(255) NOT NULL UNIQUE, | |||
zm_hostname VARCHAR(255) | |||
); | |||
CREATE TABLE config ( | |||
version INTEGER NOT NULL UNIQUE | |||
); | |||
CREATE TABLE rrds ( | |||
id INTEGER PRIMARY KEY, | |||
host_id INTEGER NOT NULL REFERENCES hosts(id) ON DELETE CASCADE, | |||
col_name VARCHAR(255) NOT NULL, | |||
col_name_19 VARCHAR(20) NOT NULL, -- due to rrd 19 char limit | |||
col_num INTEGER NOT NULL, | |||
csv_file VARCHAR(255) NOT NULL, | |||
rrd_file INTEGER NOT NULL DEFAULT 0 | |||
); | |||
CREATE TABLE rrd_column_type ( | |||
csv_file varchar(255) not null, | |||
col_name varchar(255) not null, | |||
-- G-AUGE, C-OUNTER, A-BSOLUTE or D-ERIVED | |||
col_type char(1) not null, | |||
col_interval integer, col_unit varchar(64), | |||
CONSTRAINT unqcoltype UNIQUE (csv_file, col_name) | |||
); | |||
4. Once recreated you can populate them with the /opt/zimbra/bin/zmloggerhostmap command line tool. | |||
To add mapping use the -a switch at the end as follows: | |||
/opt/zimbra/bin/zmloggerhostmap -a server1. domain.com server1. domain.com | |||
To delete use -d switch: | |||
/opt/zimbra/bin/zmloggerhostmap -d server1. domain.com server1. domain.com | |||
The hostmap rely on the correct values set in the hosts file, DNS and the zimbra hostname. You need to make sure all is correct for proper functioning. | |||
3. Check if the hosts table is populated: | |||
sqlite> select * from hosts; | |||
4. | |||
$ mysql.server stop | $ mysql.server stop | ||
$ mysql.server start | $ mysql.server start |
Revision as of 12:50, 1 October 2019
How to add logger tables
Problem
Zimbra includes the logger service, that collects data from different servers for the state of the different services. If multiserver installation is in place, there must be only one logger server, and the rest of the server are viewed as clients. Each client and the logger server keep a database for the known servers in the setup, placed in SQLite3 database. Sometimes, due to power outage or different reasons, the table that keeps the data for the other servers [Hosts], might get deleted. In this wiki we will see how to recreate this and the rest of the tables from the logger DB.
Resolution
1. Enter the SQLite3database:
$ sqlite3 /opt/zimbra/logger/db/data/logger.sqlitedb
2. Once inside confirm which tables exist:
sqlite> .tables
3. If tables are missing, recreate them with the following lines. For each table, just copy and paste the text to the sqlite command line:
CREATE TABLE hosts ( id INTEGER PRIMARY KEY, dns_hostname VARCHAR(255) NOT NULL UNIQUE, zm_hostname VARCHAR(255) );
CREATE TABLE config ( version INTEGER NOT NULL UNIQUE );
CREATE TABLE rrds ( id INTEGER PRIMARY KEY, host_id INTEGER NOT NULL REFERENCES hosts(id) ON DELETE CASCADE, col_name VARCHAR(255) NOT NULL, col_name_19 VARCHAR(20) NOT NULL, -- due to rrd 19 char limit col_num INTEGER NOT NULL, csv_file VARCHAR(255) NOT NULL, rrd_file INTEGER NOT NULL DEFAULT 0 );
CREATE TABLE rrd_column_type ( csv_file varchar(255) not null, col_name varchar(255) not null, -- G-AUGE, C-OUNTER, A-BSOLUTE or D-ERIVED col_type char(1) not null, col_interval integer, col_unit varchar(64), CONSTRAINT unqcoltype UNIQUE (csv_file, col_name) );
4. Once recreated you can populate them with the /opt/zimbra/bin/zmloggerhostmap command line tool.
To add mapping use the -a switch at the end as follows:
/opt/zimbra/bin/zmloggerhostmap -a server1. domain.com server1. domain.com
To delete use -d switch:
/opt/zimbra/bin/zmloggerhostmap -d server1. domain.com server1. domain.com
The hostmap rely on the correct values set in the hosts file, DNS and the zimbra hostname. You need to make sure all is correct for proper functioning.
3. Check if the hosts table is populated:
sqlite> select * from hosts;
4.
$ mysql.server stop $ mysql.server start
However after server restart these settings will be gone.
To make the change permanent:
1. Edit my.cnf file and under [mysqld] section change the max_connections value.
2. Restart:
$ mysql.server stop $ mysql.server start
IMPORTANT: Increasing the connections will also increase the memory usage on the server. Make sure you have enough resources before applying these changes.