Home > Others > Zabbix Database Backup Script for `postgres` and `mysql`.

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

from Blogger http://ift.tt/1OZc8aH
via IFTTT

Advertisements
Categories: Others Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: