Scheduled Database Backup

This procedure shows how to configure a periodic database backup using a bash script (tbdbbackup) executed from a Tmedia host unix cron jobs. Cron allows Linux and Unix users to run commands or scripts at a given date and time.

Overview

The tbdbbackup script support the following features:

• Backup the TMG-CONTROL configuration database into a backup file
• Backup file name formatted as tbdbbackup_<hostname>_toolpack_<version>_<date>_<time>.sql.gz

 tbdbbackup_TB014369_toolpack_2_8_2017_01_11_15h52m.sql.gz

• Keep N days of local backup files in /lib/tb/toolpack/pkg/database_backups. Default is 7 days.
• Backup files are listed on the Web portal in Backups->Manual Database Backup
• Optional copy to a remote server supporting SSH Key-Based Authentication
  • Copy the backup file on a remote server (using scp) - optional
  • Keep M days of backup files on the remote server (using ssh) - optional

On latest product version, the script should be available from the /usr/bin. To validate if you have the script on your Tmedia:

• Connect to the Tmedia using SSH as root

[root@TB014369 ~]# which tbdbbackup
/usr/bin/tbdbbackup

Install

From repository

The tbdbbackup script is included in the tb_tools packages 1.2-32.el7 (CentOS7) and 1.2-32.el5 (CentOS5).

#yum install tb_tools
#yum list tb_tools
tb_tools.noarch        1.2-32.el7.tb

Manually

If you don't have access to the repository from the Tmedia or the tb_tools package not yet updated on TB repository, you can manual install the script.
1. Get the script here: tbdbbackup.gz [click to download]

2. Copy the scrips to Tmedia (using a sftp client)

gunzip tbdbbackup.gz
tar xf tbdbbackup.tar
cp tbdbbackup/tbdbbackup /usr/bin/.
cp tbdbbackup/tbtoolpackenv /usr/bin/.
chmod +x /usr/bin/tbdbbackup
rm -fr tbdbbackup

Configuration

The script is configured with user variables.

• CONFIG_FILE: Configuration file name and location. By default, it is set to /root/tbdbbackup.cfg.

CONFIG_FILE=/root/tbdbbackup.cfg

tbdbbackup.cfg file example:

LOCAL_BACKUP_MAX_DAYS=1
REMOTE_BACKUP_MAX_DAYS=1
REMOTE_HOST=192.168.101.170
REMOTE_PORT=22
REMOTE_DIR=/root/backup_test
REMOTE_USER=root

The configuration file can redefine any of the following user variables. To see the default variables:

head -n 50 /usr/bin/tbdbbackup

• LOCAL_DIR : Set this variable to change the local directory. By default the script uses /lib/tb/toolpack/pkg/database_backups.

For example to change the local backup dir to /home/tbdbbackup:

LOCAL_DIR=/home/tbdbbackup

• LOCAL_BACKUP_MAX_DAYS : Max number of days to keep local backup files. Set to 7 days by default.

# Assign no value to disable the local cleanup
LOCAL_BACKUP_MAX_DAYS=7

• REMOTE_BACKUP_MAX_DAYS : Max number of days to keep remote backup files (if REMOTE_HOST is set). Set to 7 days by default.

# Assign no value to disable the remote cleanup
REMOTE_BACKUP_MAX_DAYS=7

• REMOTE_HOST : Remote host name or Ip address. Remote host copy is disabled by default.

# Remote host IP or hostname for the remote copy
REMOTE_HOST=10.10.10.10

By default, no value is assigned. This disable the remote copy.

REMOTE_HOST=

• REMOTE_PORT : Remote host ssh port.

REMOTE_PORT=22

• REMOTE_DIR : Remote directory where to copy the backup files (/root/backup by default)

REMOTE_DIR=/root/backup

• REMOTE_USER : Remote host login user.

REMOTE_USER=root

• CONNECT_TIMEOUT : scp connection timeout

CONNECT_TIMEOUT=10

• NAME_PREFIX : Change this variable to customize the file backup prefix file name

# By default take the Tmedia hostname for the backup 'hostname'.
NAME_PREFIX=tbdbbackup_$(hostname)

Remote Server Copy

You must create an SSH key pair and copy it to the remote server, if the remote server copy is configured. This will enable the Tmedia to copy the backup files to the remote host without asking for a password:

• Connect to the Tmedia using SSH as root

• Create Tmedia public/private dsa key pair

ssh-keygen -t rsa

Note: Do not add a pass phrase, simply press enter until the key pair is generate.

• Append the content of the public key to the remote host authorized keys

Tmedia public key location

/root/.ssh/id_rsa.pub

Remote Host authorized keys location

~/.ssh/authorized_keys

More details here: Password-less SSH

Troubleshooting

1.Manually run the script

/usr/bin/tbdbbackup

2.Check if the backup is locally created:

ls -alt /lib/tb/toolpack/pkg/database_backups/

3.If the remote copy is configured, make sure the file was copied on the remote server

4.To test the "LOCAL_BACKUP_MAX_DAYS" (if configured), you can perform the following:

• run the script a few times to create multiple files
• use "touch" to change the date of a file to a date older then "LOCAL_BACKUP_MAX_DAYS" days ago. For example, if "LOCAL_BACKUP_MAX_DAYS" is set to 15 days, set the date of the file as "16 days ago".

[root@TB014369]# ls -alt /lib/tb/toolpack/pkg/database_backups/
total 820
-rw-r--r--  1 root  root  206638 Jan 11 16:40 tbdbbackup_TB014369_toolpack_2_8_2017_01_11_16h40m.sql.gz
-rw-r--r--  1 root  root  206612 Jan 11 15:52 tbdbbackup_TB014369_toolpack_2_8_2017_01_11_15h52m.sql.gz

[root@TB014369 database_backups]# touch -d "16 days ago" /lib/tb/toolpack/pkg/database_backups/tbdbbackup_TB014369_toolpack_2_8_2017_01_11_16h40m.sql.gz

[root@TB014369]# ls -alt /lib/tb/toolpack/pkg/database_backups/
total 820
-rw-r--r--  1 root  root  206612 Jan 11 15:52 tbdbbackup_TB014369_toolpack_2_8_2017_01_11_15h52m.sql.gz
-rw-r--r--  1 root  root  206638 Dec 26 16:43 tbdbbackup_TB014369_toolpack_2_8_2017_01_11_16h40m.sql.gz

• run the script again; the file tbdbbackup_TB014369_toolpack_2_8_2017_01_11_16h40m.sql.gz should have been removed from /lib/tb/toolpack/pkg/database_backups/

5. Look at the script log file in /lib/tb/toolpack/pkg/database_backups/tbdbbackup.log

# cat /lib/tb/toolpack/pkg/database_backups/tbdbbackup.log 
Start scheduled backup: Thu Jan 12 11:53:12 EST 2017
+ mysqldump -u tbdb -ptbdbpw --opt toolpack_2_10 > tbdbbackup_TB014369_toolpack_2_10_2017_01_12_11h53m.sql
  => OK
+ gzip -f tbdbbackup_TB014369_toolpack_2_10_2017_01_12_11h53m.sql
  => OK
+ Delete local files older then 7 days:
  Nothing to delete
Stop scheduled backup: Thu Jan 12 11:53:21 EST 2017

Schedule the Script

Modify the system crontab to execute the backup on a regular bases.

• Create or modify the crontab on the Tmedia

crontab -e

• Add new line with a cron configuration

Frequency	Cron Configuration
hourly (top of the hour)	0 * * * * /usr/bin/tbdbbackup > /dev/null 2>&1
daily (midnight)	0 0 * * * /usr/bin/tbdbbackup > /dev/null 2>&1

Example:

0 0 * * * /usr/bin/tbdbbackup > /dev/null 2>&1