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

Ready to streamline your complex Zabbix operations?

I’ve distilled the most valuable insights from this blog into one essential guide. Take full control of your environment with the Zabbix 7 Enterprise Optimization Handbook [Mastering Hybrid Infrastructure Monitoring with a Kubernetes First Approach].

👉 Get your PDF copy here: https://jikimy.gumroad.com/l/zabbixmaster

— 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)  : The initial root password is not set.
Just press Enter without typing anything.

# 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;
mysql> GRANT FILE ON *.* TO 'zabbix'@'localhost';
       => Grant privileges for partitioning

# Note: The partition automation script connects via TCP (127.0.0.1), so you must also create a 'zabbix'@'127.0.0.1' account with the same privileges.


# 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.12.12