AHMED ZBYR

AHMED ZBYR

Biker, EV Enthusiast

Regular database backups are essential after deploying a Zabbix monitoring server. Depending on your recovery requirements and disk capacity, you can take either a full backup (including historical metrics and trends) or a configuration-only backup (excluding history*, trends*, and acknowledges/events tables to reduce backup size).

This guide walks through setting up automated daily full backups for Zabbix running on PostgreSQL or MySQL / MariaDB, compressing the dumps, and retaining the last 3 days of archives via cron.

Security & Operational Best Practices:

  • Avoid passing database passwords directly on the command line (where they are visible in ps output) or embedding plain-text credentials in world-readable scripts. Use .pgpass (0600) for PostgreSQL and .my.cnf (0600) for MySQL.
  • Set script permissions to 0700 (owner-executable only) rather than 0777.
  • Store backups on a separate volume or replicate them off-host, and periodically test restoring a dump into a staging database.

How the Backup Script Works

Each daily run performs three steps:

  1. Ensure the target directory exists: Creates $BACKUP_DIR on first run if it is missing.
  2. Dump the Zabbix database: Runs pg_dump (PostgreSQL) or mysqldump (MySQL) for the database specified by BASE='zabbix'.
  3. Compress and rotate old backups: Archives the dump with a timestamp and deletes backup archives older than the retention window (3 days).

Script Control Flow

Ensure the backup directory exists before running the backup:

# Check if backup directory exists; create it on first run
if [ ! -d "$BACKUP_DIR" ]; then
    mkdir -p "$BACKUP_DIR"
fi

# Run the database dump and archive rotation
if [ -d "$BACKUP_DIR" ]; then
    backup_postgres_db
    create_archive
fi

To prune archives older than 3 days safely inside $BACKUP_DIR, use find:

find "$BACKUP_DIR" -type f -name "*.gz" -mtime +3 -delete

Option 1: PostgreSQL Backup Setup (pg_dump)

On RHEL/CentOS systems, the postgres system account typically uses /var/lib/pgsql as its $HOME directory.

1. Prepare the Backup Directory and Script

  1. Switch to the postgres user (whose $HOME is /var/lib/pgsql): 
    sudo su - postgres
  2. Copy zabbix_db_backup_postgres.sh into /var/lib/pgsql/ and restrict permissions so only postgres can read or execute it: 
    chmod 0700 /var/lib/pgsql/zabbix_db_backup_postgres.sh
  3. Create the destination directory for backups and assign ownership to postgres: 
    sudo mkdir -p /zabbix_db_backup/postgres_complete_backup
sudo chown -R postgres:postgres /zabbix_db_backup
sudo chmod 0750 /zabbix_db_backup/postgres_complete_backup
  4. Update the variables at the top of zabbix_db_backup_postgres.sh: 
    BACKUP_DIR="/zabbix_db_backup/postgres_complete_backup"
USERDB="zabbix"
BASE="zabbix"

2. Configure Passwordless Authentication with .pgpass

Instead of hardcoding PASSWD in the script, create /var/lib/pgsql/.pgpass so pg_dump can authenticate non-interactively. PostgreSQL ignores .pgpass unless its permissions are 0600.

The .pgpass format uses colon-separated fields:

hostname:port:database:username:password

Create and secure the file as the postgres user:

cat << 'EOF' > ~/.pgpass
localhost:5432:zabbix:zabbix:REPLACE_WITH_DB_PASSWORD
EOF
chmod 0600 ~/.pgpass

Verify the directory layout and permissions:

bash-4.1$ pwd
/var/lib/pgsql
bash-4.1$ ls -la
total 36
drwx------.  3 postgres postgres 4096 Jan  6 00:27 .
drwxr-xr-x. 43 root     root     4096 Jan  5 03:09 ..
drwx------.  4 postgres postgres 4096 Oct  6 20:42 9.4
-rw-------   1 postgres postgres   49 Dec 17 00:01 .pgpass
-rwx------   1 postgres postgres 2186 Jan  5 23:19 zabbix_db_backup_postgres.sh

Test a manual run before scheduling:

/var/lib/pgsql/zabbix_db_backup_postgres.sh
ls -lh /zabbix_db_backup/postgres_complete_backup/

3. Schedule Daily Backups in crontab

Edit the postgres user’s crontab:

crontab -e

Add the following entry to run the backup daily at midnight (00:00):

# Crontab field reference:
# +---------------- minute (0 - 59)
# |  +------------- hour (0 - 23)
# |  |  +---------- day of month (1 - 31)
# |  |  |  +------- month (1 - 12)
# |  |  |  |  +---- day of week (0 - 6) (Sunday=0 or 7)
# |  |  |  |  |
# *  *  *  *  *  command to be executed

# Execute daily backup of Zabbix PostgreSQL database at 00:00
0 0 * * * /var/lib/pgsql/zabbix_db_backup_postgres.sh

Option 2: MySQL / MariaDB Backup Setup (mysqldump)

1. Prepare the Backup Directory and Script

  1. Log in as the dedicated backup user and copy zabbix_db_backup_mysql.sh into $HOME.
  2. Restrict script permissions to the owner: 
    chmod 0700 "$HOME/zabbix_db_backup_mysql.sh"
  3. Create the MySQL backup directory and grant ownership to the backup user: 
    sudo mkdir -p /zabbix_db_backup/mysql_complete_backup
sudo chown -R "$(whoami)":"$(whoami)" /zabbix_db_backup/mysql_complete_backup
sudo chmod 0750 /zabbix_db_backup/mysql_complete_backup
  4. Configure the script variables: 
    BACKUP_DIR="/zabbix_db_backup/mysql_complete_backup"
USERDB="zabbix"
BASE="zabbix"

2. Configure Credentials Safely with ~/.my.cnf

To avoid passing -p"$PASSWD" on the mysqldump command line, store the client credentials in ~/.my.cnf with 0600 permissions:

[client]
host=localhost
user=zabbix
password="REPLACE_WITH_DB_PASSWORD"

Secure the file:

chmod 0600 ~/.my.cnf

When running mysqldump on an InnoDB-backed Zabbix database, include --single-transaction to capture a consistent snapshot without locking tables:

mysqldump --defaults-file="$HOME/.my.cnf" \
  --single-transaction --quick --routines --triggers \
  "$BASE" | gzip > "$BACKUP_DIR/zabbix_$(date +%F_%H%M%S).sql.gz"

3. Schedule Daily Backups in crontab

Open the backup user’s crontab:

crontab -e

Add the daily midnight schedule:

# Execute daily backup of Zabbix MySQL database at 00:00
0 0 * * * /home/backupuser/zabbix_db_backup_mysql.sh

Verifying and Restoring a Backup

Always verify that generated archives are non-empty and passes gzip -t integrity checks:

gzip -t /zabbix_db_backup/postgres_complete_backup/*.gz
  • Restore PostgreSQL dump: 
    gunzip -c backup_file.sql.gz | psql -h localhost -U zabbix -d zabbix
  • Restore MySQL dump: 
    gunzip -c backup_file.sql.gz | mysql --defaults-file=~/.my.cnf zabbix

References

You May Also Enjoy