Backup and restore · WorkFusion Documentation

Restore from backup media

At predetermined checkpoints (after key events or time periods) a solution may backup (store) a snapshot of its operational state and the information it has processed. Restoring the solution state and information from backup media enables past information to be reconstructed and the solution to resume operation with a minimum of lost data and time. The Restore from Backup Media section identifies solution checkpoints and the procedures for using backup solution status information to recover from solution failures or degradation.

Primary purpose of the backup is to recover data after its loss. Secondary is to recover data from an earlier time. Backups represent a simple form of Disaster Recovery, and should be included in Disaster Recovery plan. At rare occasions the backups alone are considered as the only procedure of the Disaster Recovery plan.

Steps on defining backup approach

Select an appropriate strategy, tool or method to back up the data.
Ensure there is an appropriate retention policy defined for the backup data.
Ensure there are appropriate security measures in place for the backup data, including encryption and access policies.
Regularly test the recovery of the backup data and the restoration of the system.

Applicability

The Restore from Backup Media approach is used:

As a fallback method in case of a major failure of the main DC with data integrity affected due to the main replication strategy failure.
As a fallback method in case of a major failure of the main HA replication strategy failure.
As a main HA strategy in absence of other higher level HA options or strategies.
As a main DR strategy in absence of other higher level DR options or strategies.

Backup and recovery strategies

Snapshot backup

A copy of the file system as if it were frozen at a specific point in time.

Backup software packages as a rule may operate on the following level:

Host snapshot

Typically is performed on file system of logical volume level. The backup software resides on on the operating system and makes a point in time snapshot of data to disk

Disk snapshot

Typically is tightly coupled with hardware., for example, hardware-based with or without specialized software to do point in time disk snapshot

Virtual Machine or Virtual Host snapshot

Typically is performed by Hypervisor or Virtual Machine management tooling. Point in time copy is performed of the whole Virtual Machine image or host

Database backup

As an addition to snapshot backup strategy databases data backup may be performed.

Cold database backup

During a cold backup database is stopped or locked and not available to users. It allows taking consistent backup of the data set while it is not being modified.

Hot database backup

A way to make a database backup while it's active and is available to users. As a rule it includes an inconsistent image of the data plus log of changes performed during the hot backup procedure was running.

MySQL backup

Backup strategies

Main operations

All scenarios assume we need to minimize time-to-recovery.

Utility xtrabackup can fulfill next actions:

Create a full backup.
Apply logs with commited only transactions to the full backup.
Create an incremental backup.
Apply incremental logs with commited only transactions to the full backup.
Prepare the full backup (applying rollbacks to a full backup).

note

Only a prepared full backup is suitable for recovering.
An incremental backup can't be applied to a prepared full backup.
Logs applying and backup preparation may be done without connection to MySQL.
A full backup must be recreated after database recovery.

Scenario #1: Daily full backup

Useful for a daily full backup.

Backup steps:

Create a full backup.
Prepare the backup.

Recovery steps:

Restore database from a prepared backup.

Pros:

Simple backup procedure.
Fastest recovery.
A backup may be compressed.

Cons:

A full backup may take a lot of time.

Scenario #2: Single incremental backup

Backup steps:

Once create a full backup.
Periodically create an incremental backup and apply it to the full backup.

Recovery steps:

Prepare a backup.
Restore database from a backup.

Pros:

An incremental backup creating is faster then a full backup.

Cons:

A full backup can't be compressed. It must be available during incremental backup.

Scenario #3: Multiple incremental backups

Note: This case is not implemented in script backup-mysql.sh.

Backup steps:

Create full backup.
Daily create new incremental backup without log applying.

Recovery steps:

Apply consequently all incremental backups to full backup.
Prepare full backup.
Recovery database from backup.

Pros:

Full and incremental backups may be compressed.
Fastest backup time (no log applying).

Cons:

Long backup preparation time.

Backup script

backup-mysql.sh:

# ----------
# CHANGELOG v.7.4.0.2
# 2016-06-06 apouckatch added function _backup_archivate(): is called during full backuping
# ----------
# USAGE info
# ----------
_backup_usage()
{
echo "
NAME
       $0 - create and recovery backups of MySQL database
SYNOPSYS
       $0 -c <config-file-name> -m <backup-mode>
       $0 --config=<config-file-name> --mode=<backup-mode>
DESCRIPTION
       Create full and incremental backups of MySQL databases, restore database from backup
       -c, --config
               path to configuration file. This file has to include declaration of next constants:
               MY_CNF_FILE    : path to MySQL config file (e.g.: /etc/my.cnf)
               MYSQL_USER     : user name with backup privileges (e.g.: root)
               MYSQL_PASSWORD : password of MYSQL_USER
               MYSQL_DATA_DIR : path to MySQL database (e.g.: /var/lib/mysql)
               BACKUP_ROOT_DIR: path to MySQL backups' home directory (e.g.: /backups/mysql)
               FULL_BACKUP_DIR: path to full backup directory (e.g.: /backups/mysql/full)
               INCR_BACKUP_DIR: path to incremental backup directory (e.g.: /backups/mysql/incr)
               ARCH_BACKUP_DIR: path to full backup archives directory (e.g.: /backups/mysql/arch)
       -m, --mode
               full        - create and apply logs for full backup
               incremental - create and apply logs for incremental backup
               prepare     - prepare full backup
               recovery    - recovery full backu
AUTHOR
        Written by Andrey Pouckatch
COPYRIGHT
        WorkFusion, 2016 workfusion.com
"
}
 
# --------------------
# FULL mode procedures
# --------------------
_backup_full_create()
{
xtrabackup \
  --defaults-file=$MY_CNF_FILE  \
  --backup                      \
  --target-dir=$FULL_BACKUP_DIR \
  --datadir=$MYSQL_DATA_DIR     \
  --user=$MYSQL_USER            \
  --password=$MYSQL_PASSWORD \
  --socket=/var/lib/mysql/mysql.sock
}
# -----------------
# APPLY binary logs
# -----------------
_backup_full_apply_logs()
{
xtrabackup --prepare \
  --apply-log-only   \
  --target-dir=$FULL_BACKUP_DIR
}
# --------------
# PREPARE backup
# --------------
_backup_prepare()
{
xtrabackup --prepare --target-dir=$FULL_BACKUP_DIR
}

# --------------------------------
# PREPARE full backup for recovery
# --------------------------------
_backup_full_prepare()
{
# initial preparation:
_backup_prepare
# log cleaning (second call):
_backup_prepare
}
 
# ---------------------
# ARCHIVATE full backup
# ---------------------
_backup_full_archivate()
{
echo Create a new directory for archive...
NEW_ARCH_DIR=$ARCH_BACKUP_DIR/full.`date +"%Y-%m%d-%H%M%S"`
mkdir $NEW_ARCH_DIR
echo Move full backup files:
echo Source  directory: $FULL_BACKUP_DIR
echo Archive directory: $NEW_ARCH_DIR
mv $FULL_BACKUP_DIR/* $NEW_ARCH_DIR
}

# ---------------------------
# INCREMENTAL mode procedures
# ---------------------------
_backup_inc_create()
{
xtrabackup \
  --defaults-file=$MY_CNF_FILE \
  --backup                     \
  --target-dir=$INCR_BACKUP_DIR \
  --incremental-basedir=$FULL_BACKUP_DIR \
  --datadir=$MYSQL_DATA_DIR    \
  --user=$MYSQL_USER           \
  --password=$MYSQL_PASSWORD
}
# ------------------------------------
# APPLY incremental backup binary logs
# ------------------------------------
_backup_inc_apply_logs()
{
xtrabackup --prepare \
  --apply-log-only \
  --target-dir=$FULL_BACKUP_DIR \
  --incremental-dir=$INCR_BACKUP_DIR
}
 
# --------
# RECOVERY
# --------
_backup_recovery()
{
service mysql stop
echo Clear old data...
rm -rf $MYSQL_DATA_DIR/*
echo Copy data from the backup...
cp -R $FULL_BACKUP_DIR/* $MYSQL_DATA_DIR
echo Restore file permissions..
chown -R mysql:mysql $MYSQL_DATA_DIR
service mysql start
}

# ----------------------------------
# Parse arguments and call functions
# ----------------------------------
_process_command_line()
{
OPTS=`getopt -o m:c: --long mode:,config: -n 'parse-options' -- "$@"`
if [ $? != 0 ] ; then echo "Failed parsing options." >&2 ; exit 1; fi
eval set -- "$OPTS"
while true; do
  case "$1" in
    -m | --mode      ) PRM_MODE="$2"  ; shift; shift ;;
    -c | --config    ) PRM_CONFIG="$2"; shift; shift ;;
    -- ) shift; break ;;
    *  ) break ;;
  esac
done
# load parameters
source ./$PRM_CONFIG
case "$PRM_MODE" in
  full        ) _backup_full_archivate; _backup_full_create; _backup_full_apply_logs ;;
  incremental ) _backup_inc_create ; _backup_inc_apply_logs ;;
  prepare     ) _backup_full_prepare ;;
  recovery    ) _backup_recovery ;;
  archivate   ) _backup_archivate ;;
  * ) echo ERROR: Bad mode "$PRM_MODE"; _backup_usage ;;
esac
}
 
# ----
# MAIN
# ----
_process_command_line "$@"

db2-backup.cfg.sh:

MY_CNF_FILE=/etc/my.cnf
MYSQL_USER=root
MYSQL_PASSWORD=rootpswd
MYSQL_DATA_DIR=/var/lib/mysql
BACKUP_ROOT_DIR=/backups/mysql/db2
FULL_BACKUP_DIR=$BACKUP_ROOT_DIR/full
INCR_BACKUP_DIR=$BACKUP_ROOT_DIR/incr
ARCH_BACKUP_DIR=$BACKUP_ROOT_DIR/arch

Main use cases

Use case: create a full backup

./backup-mysql.sh --config=db2-backup.cfg.sh --mode=full

Console output sample

160303 06:56:05  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'root'  (using password: YES).
... 
160303 06:56:08 completed OK!

Use case: create an incremental backup

./backup-mysql.sh --config=db2-backup.cfg.sh --mode=incremental

Console output sample

160303 07:05:54  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'root'  (using password: YES).
...
160303 07:05:59 completed OK!

Use case: recovery from a backup

(Re)install MySQL server software if needed.

./backup-mysql.sh --config=db2-backup.cfg.sh --mode=recovery

Console output sample

Shutting down MySQL (Percona XtraDB Cluster)...            [  OK  ]
Clear old data...
Copy data from the backup...
Restore file permissions..
Starting MySQL (Percona XtraDB Cluster)..                  [  OK  ]

Backup integrity

To check integrity of a backup:

Recovery database from a full backup:

./backup-mysql.sh --config=db2-backup.cfg.sh --mode=recovery

Run check utility:

mysqlcheck --all-databases -u root -p

Console output sample

mysql.columns_priv                                 OK              
mysql.db                                           OK              
mysql.event                                        OK              
mysql.func                                         OK              


....
 
test_repl.t4i2                                     OK              
test_repl.t5i3                                     OK

If all tables have status 'OK' then database integrity check is passed.
If at least one table hasn't 'OK' status, then database is broken:

**Sample of output for a broken database backup**

```java
...
trepl2.t2                                          OK
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '
```

PostgreSQL backup

Backup strategies

Preface

Backup utility pg_basebackup provides full backup mode. For incremental backup - archive logs may be applied to the last full backup. If archive_mode in on, postgres runs archive_command for every completed archive log.

As pg_basebackup doesn't include all required WALs into stream

postgresql.conf need to be configured to copy WAL files to the Backup server.

Cold full backup

The Reserve DB server maybe used to create a cold full backup.

host: Reserve DB, user: postgres

$ pg_ctl stop
$ tar -xcvf /backups/pgsql/arch/postgres-$(date +"%Y-%m%d-%H%M).tar.gz $PGDATA/*
$ pg_ctl start

Hot full backup

host: Main

BACKUP_POSTGRES_DIR=/backups/pgsql/arch
 
pg_basebackup -D - -Ft -P | gzip > $BACKUP_POSTGRES_DIR/postgres-$(date +"%Y-%m%d-%H%M").tar.gz

Incremental backup

Increment backup requires a preliminary created full backup.

Setup WAL copying to the Backup server.

host: Main, file: postgresql.conf

archive_mode = on
### archive_command = 'cd .'
archive_command = 'test ! -f /backups/pgsql/xlog/%f && cp %p /backups/pgsql/xlog/%f'

Recovery from a hot or incremental backup

(Re)install postgreSQL software if needed.

Stop database | user: root

service postgresql-9.3 stop

caution

Ensure that $PGDATA is defined.

Clear old data | user: root

rm -rf $PGDATA/*

note

Specify a correct filename for BACKUP_POSTGRES_FILE.

Extract files from a full backup archive | user: postgres

BACKUP_POSTGRES_DIR=/backups/pgsql/arch
BACKUP_POSTGRES_FILE=postgres-<backup-date>.tar.gz
tar -zxvf $BACKUP_POSTGRES_DIR/$BACKUP_POSTGRES_FILE -C $PGDATA

Restore file permissions | user: root

chown postgres:postgres $PGDATA -R
chmod go-rwx $PGDATA -R

If there're not WAL files but a full backup only, run:

Clear transaction logs

pg_resetxlog -f /var/lib/pgsql/9.3/data"
pg_ctl start

In other case continue with the next steps.

Find out the last WAL file name in the backup_label file:

$ cat $PGDATA/backup_label | grep file
START WAL LOCATION: 0/40000028 (file 000000010000000000000040)

Copy all WAL files started with 000000010000000000000040 to $PGDATA/pg_xlog.

Create $PGDATA/recovery.conf if needed (see section III. Recovery scenarios) | user: postgres

standby_mode = 'on'
primary_conninfo = 'port=5432 host=DBHOSTNAME_MASTER user=repmgr'
recovery_target_timeline = 'latest'

note

Replace DBHOSTNAME_MASTER with a proper value.

Start database | user: postgres

pg_ctl start

Recovery from cold backup

user: postgres

$ pg_ctl stop
$ tar -zxvf /backups/pgsql/arch/postgres-2016-0311-1456.tar.gz -C $PGDATA
$ pg_ctl start

Backup integrity

To check integrity of a backup just recovery database from this backup and create a full hot backup of the recovered database with pg_basebackup utility.

Assumed that backups/pgsql/last contains an unpacked full cold backup, /backups/pgsql/test is empty, and PostgreSQL is installed and ran.

user: root, file: check_backup.sh

#!/bin/bash
export BACKUP_FILE_DIR=/backups/pgsql/last
export BACKUP_TEST_DIR=/backups/pgsql/test
export PGSQL_DATA_DIR=/var/lib/pgsql/9.3/data

echo Stop PostgreSQL...
service postgresql-9.3 stop

echo Clear pgsql data...
rm -rf $PGSQL_DATA_DIR/*

echo Copy backup files...
cp -R $BACKUP_FILE_DIR/* $PGSQL_DATA_DIR/

echo Restore permissions...
chown postgres:postgres $PGSQL_DATA_DIR -R

echo Remove backup label...
rm -f $PGSQL_DATA_DIR/backup_label

echo Switch to postgres to reset transaction log...
su postgres -c '/usr/pgsql-9.3/bin/pg_resetxlog $PGSQL_DATA_DIR/ -f'

echo Start PostgreSQL...
service postgresql-9.3 start

echo Wait 2 secs...
sleep 2

echo Switch to postgres to create test backup...
su postgres -c 'pg_basebackup --pgdata=$BACKUP_TEST_DIR -P'

If pg_basebackup is completed successfully, the source backup is valid.

Mind that pg_basebackup may issue the next message:

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

S3 backup

Hot backup

This approach is to traverse the S3 resources and download them as regular files.

As a result it allows restoring individual files.

Backup

It's recommended to perform this back up during no or low S3 load hours

Check how much space your S3 storage takes

s3cmd -c /etc/s3cfgfasttrack du -H | grep Total

Create a backup folder:

# Please make sure you have enough free space on backup disk, see S3 storage size above
mkdir -p /backups/wf-s3-content

Perform the backup of the S3 storage:

BUCKET_LIST=$(s3cmd -c /etc/s3cfgfasttrack ls | cut -d'/' -f3); \
for i in $BUCKET_LIST; do mkdir -p /backup/wf-s3-content/$i && \
s3cmd -c /etc/s3cfgfasttrack get -f -r s3://$i /backup/wf-s3-content/$i; done

This procedure may take some time (overall progress is displayed), it depends on how many files you have in your S3 storage.

Create an archive with the S3 content:

cd /backup
tar czvf wf-s3-content.tar.gz wf-s3-content

Restore

Copy and replace the archive to the package installation directory:

cp /tmp/wf-s3-content.tar.gz PATH_TO/app-all/
# where PATH_TO is the path where 'app-all' package installation directory is located.

Restore the S3 content backup:

cd PATH_TO/app-all/
# where PATH_TO is the path where 'app-all' package installation directory is located.
./workfusion_setup_full.sh -i wf_s3_content

The procedure may take some time (the process progress will be indicated in the console), it depends on how many files you have in your S3 storage.

Cold backup

Backups can be accomplished using standard system utilities such cp, rsync, and tar can be used, as well as any other (including previously mentioned) backup strategies.

Refer to the Riak KV instructions for the detailed steps.

Analytics backup

Back up Tableau data

A backup of your Tableau Server installation saves all configuration information, user information, and content in a single file. This can use this file to restore your server to the same condition it was in when you performed the backup.

To create a backup of your Tableau Server configuration and data:

Click Start or press the Windows key.
Type cmd. Results will be listed, including cmd.exe, the command prompt.
Right-click cmd.exe and select Run as administrator.
Go to the Tableau Server bin folder: C:\Program Files\Tableau\Tableau Server\<version>\bin, where <version> is your version of Tableau Server.

For example, go to the Tableau Server 9.3 bin directory by typing the following: cd C:\Program Files\Tableau\Tableau Server\9.3\bin
Type tabadmin backup <filename> -v and press Enter. For example, type: tabadmin backup tabserver -d -v

The example creates a backup file in the bin folder named tabserver-2016-02-10.tsbak. The -d option adds the datestamp, and the -v switch verifies the state of the database for backup and restore.

Restore Tableau Server from a backup file

On the Tableau Server computer, open a command prompt as administrator.

Click Start or press the Windows key.
Type cmd. Results will be listed, including cmd.exe, the command prompt.
Right-click cmd.exe and select Run as administrator.
Go to the Tableau Server bin folder: C:/Program/Files/Tableau/Tableau Server/<version>/bin, where <version> is your version of Tableau Server.

For example, go to the Tableau Server 10.3 bin directory by typing the following: cd C:/Program/Files/Tableau/Tableau Server/10.3/bin
Stop the server: tabadmin\ stop
Restore from a backup file: tabadmin\ restore\ <filename>
In the above line, replace <filename> with the name of the backup file you want to restore from.
Start the server: tabadmin\ start

See further details at the Tableau Online help.

Mongo backup

Mongo DB keeps only temporal inflight data. It does not require a backup.

Solr backup

Solr is a deprecated component and should not be used as a data storage solution.

Artifactory backup

Nexus backup is done by simple file copy of the data directory. It would have handled by host or disk backup if that is in place.

Vault backup

Vault backup is done by simple file copy of the data directory. It would have handled by host or disk backup if that is in place.