Fixing Slow Zabbix DB: Practical MySQL Partition Setup (Part 1)

— Installing Zabbix 7.4 and Loading the Schema (History / Trends Partition Prep)

This guide explains how to install Zabbix 7.4 on Ubuntu 22.04 and prepare its MySQL schema for partitioning.
It’s a continuation of the practical performance-tuning series below:

1) Scope and Prerequisites

⚠️ If your environment requires long-term security certification or audits, avoid short-lifecycle releases.
Stick to 7.0 LTS in EOL-sensitive organizations.

2) Add Zabbix Repository

- Repository setup
# wget https://repo.zabbix.com/zabbix/7.4/release/ubuntu/pool/main/z/zabbix-release/zabbix-release_latest_7.4+ubuntu22.04_all.deb
# dpkg -i zabbix-release_latest_7.4+ubuntu22.04_all.deb
# apt update

✅ Checkpoint

• /etc/apt/sources.list.d/zabbix.list should exist.
• apt update must download indexes from repo.zabbix.com.

3) Install Zabbix Server, Frontend and Agent 2

- Zabbix server / frontend / agent2
# apt install -y zabbix-server-mysql zabbix-frontend-php zabbix-apache-conf zabbix-sql-scripts zabbix-agent2

- Optional Agent 2 plugins (install only what you need)
# apt install -y zabbix-agent2-plugin-mongodb zabbix-agent2-plugin-mssql zabbix-agent2-plugin-postgresql

✅ Checkpoint

• Config files appear under /etc/zabbix/ (notably zabbix_server.conf).
• Apache module and zabbix.conf symlink under /etc/apache2/conf-enabled/.

4) Install MySQL

# apt install -y mysql-server

✅ Checkpoint

• systemctl status mysql → should be active (running)
• Ubuntu 22.04 may enable auth_socket authentication:
  only the OS root user can log in as MySQL root.

5) Initialize the Database (User / Privileges / Function Trust)

mysql -uroot -p
# (enter password)

# Create DB, user, and privileges
mysql>create database zabbix character set utf8mb4 collate utf8mb4_bin;
mysql>create user zabbix@localhost identified by 'password';         # replace with real password
mysql>grant all privileges on zabbix.* to zabbix@localhost;

# Temporarily allow stored functions
mysql>set global log_bin_trust_function_creators = 1;
quit;

🧩 Explanation

log_bin_trust_function_creators=1 must be enabled temporarily.

Zabbix’s schema contains several stored functions (e.g., get_host_agent()),
and when binary logging is active, a non-SUPER user cannot create functions by default.
Turning this flag on lets the import succeed.

6) Import Zabbix Schema

- Load Zabbix SQL schema
#zcat /usr/share/zabbix/sql-scripts/mysql/server.sql.gz | 
mysql --default-character-set=utf8mb4 -uzabbix -p zabbix

7) Restore Function Trust Setting

mysql -uroot -p
# (enter password)
mysql>set global log_bin_trust_function_creators = 0;
quit;

🔐 Security Note

The trust flag should only be enabled during schema creation.
Always revert it afterward.

8) Register DB Credentials in Zabbix Server

# vi /etc/zabbix/zabbix_server.conf

Uncomment and set:

DBName=zabbix
DBUser=zabbix
DBPassword=your_actual_password

9) Start Services

# systemctl restart zabbix-server zabbix-agent2 apache2
# systemctl enable zabbix-server zabbix-agent2 apache2

✅ Checkpoint

• systemctl status zabbix-server → active (running)
• Access web UI: http://<server_ip>/zabbix

⏱ Time-zone handling:
Zabbix UI (PHP) uses your local zone (KST etc.) for display,
but database partitions use UTC epoch internally.
The time-zone difference affects display only, not data retention or partition boundaries.

10) Next Parts Preview

Troubleshooting Checklist

✅ End of Part 1 — Next:
Partition data directories, AppArmor rules, and tablespace configuration (Part 2).

🛠 마지막 수정일: 2025.10.28