Zabbix Database Backup Script for `postgres` and `mysql`.

After the zabbix setup is done, we would like to take backup of the database. Here we are doing a complete backup. But if we are looking for only configuration backup then we can ignore the history* tables from the database, but for now we are taking complete backup. Backups are taken everyday and last 3 days backups are stored and rest for removed.

Backup Script Location.

What does the script do.

1. Take backup of the database zabbix, given by variable BASE='zabbix'
2. Archive the backup and Remove old backups (older than 3 days).

Here is the script flow.

Create a directory if it does not exist, this will run only once for the first time.

# Check if directory exsists.
if [ ! -d $BACKUP_DIR ];
then
    mkdir -p $BACKUP_DIR
fi

If the directory was created successfully, then take backup.

# Check if directory exsists.
if [ -d $BACKUP_DIR ];
then
    # Take a DB backup
    backup_postgres_db

    # Creating Archives.
    create_archive
fi

Setting up the scripts in postgres.

Logon to the server and su to postgres user. $HOME for postgres user is /var/lib/pgsql.

1. Create/Copy the postgres script to this location /var/lib/pgsql.
2. Change permission to execute chmod 777 /var/lib/pgsql/zabbix_db_backup_postgres.sh
3. Create a directory for the backup. Similar as /zabbix_db_backup/postgres_complete_backup/.
4. Set owner to postgres which will be running the script for backup location in step 3.
5. Update script with new path BACKUP_DIR=/zabbix_db_backup/postgres_complete_backup/
6. Update script with below information.
   1. Database username : USERDB='zabbix',
   2. Database password : PASSWD='zabbix',
   3. Database : BASE='zabbix'
7. Create a file called .pgpass. To have the script run without the password, we need to have the .pgpass file in the $HOME of the postgres user. Format of the file is a below. (: seperated values)
8. Set permission to .pgpass file as 600

Here is a template for the .pgpass file.

::::

Example: one database in one line. (Set file permissions to 0600)

localhost:5432:zabbix_db_name:zabbix_user_name:zabbix_password

Here are the command output.

[Zubair.ahmed@nms2 /]$ sudo su postgres
[sudo] password for Zubair.ahmed:
bash-4.1$ pwd
/
bash-4.1$ cd ~
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 1690 Jan  6 00:27 .bash_history
-rwx------.  1 postgres postgres  267 Nov 30 16:12 .bash_profile
-rw-------   1 postgres postgres   38 Dec 17 00:01 .pgpass
-rw-------.  1 postgres postgres  547 Dec 17 00:23 .psql_history
-rw-------   1 postgres postgres 3100 Jan  6 00:27 .viminfo
-rwxrwxrwx   1 postgres postgres 2186 Jan  5 23:19 zabbix_db_backup_postgres.sh
bash-4.1$

Here is how the .pgpass file looks like.

bash-4.1$ cat .pgpass
localhost:5432:zabbix:zabbix:VeryD!f1cultPassw0rd
bash-4.1$

Update crontab postgres

Updating the crontab with below command.

crontab -e

Update the crontab with below contents.

# Crontab information on how it works.
# +----------------> 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 backuo of Zabbix Database Every Week.
0 0 * * * /var/lib/pgsql/zabbix_db_backup_postgres.sh

Script will run everyday at 00:00 hrs, and keeps backup for 3days.

Setting up the scripts in mysql.

Log on to the user which will be used for taking backups.

1. Create/Copy the postgres script to user home location $HOME.
2. Change permission to execute chmod 777 $HOME/zabbix_db_backup_postgres.sh
3. Create a directory for the backup. Similar as /zabbix_db_backup/postgres_complete_backup/.
4. Set owner to the user which will be running the script for backup location in step 3.
5. Update script with new path BACKUP_DIR=/zabbix_db_backup/postgres_complete_backup/
6. Update script with
   1. Database username : USERDB='zabbix',
   2. Database password : PASSWD='zabbix',
   3. Database : BASE='zabbix'

Update crontab mysql

Updating the crontab with below command.

crontab -e

Update the crontab with below contents.

# Crontab information on how it works.
# +----------------> 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 backuo of Zabbix Database Every Week.
0 0 * * * /var/lib/pgsql/zabbix_db_backup_mysql.sh

Script will run everyday at 00:00 hrs, and keeps backup for 3days.

​