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 the backup approach are as follows:

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

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

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 than a full backup.

Cons:

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

Scenario 3: multiple incremental backups

note

The case is not implemented in the backup-mysql.sh script.

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

See the example backup script: cold-backup.zip

Main use cases

Use case: create a full backup

You may use script from cold-backup.zip archive for full backup.

Use case: create an incremental backup

You need to use external tools to go with incremental backups. Workfusion does not provide such scripts.

Use case: recovery from a backup

(Re)install MySQL server software if needed.

Stop MySQL server if running.

Unpack files from archive that was created previously and put them into data folder.

Backup integrity

To check integrity of a backup:

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 does not have the OK status, the database is broken.

Sample of output for a broken database backup

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

PostgreSQL backup

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

You may use your own tools or examples provided below.

The cold-backup.zip script
Your own scripts and tools

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.

Set up 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 the postgreSQL software if needed.
Stop the database (user: root).
```
service postgresql-9.3 stop
```
caution

Ensure that $PGDATA is defined.
Clear old data (user: root).
```
rm -rf $PGDATA/*
```
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, clear transaction logs.
```
pg_resetxlog -f /var/lib/pgsql/9.3/data"
pg_ctl start
```
In other case, continue with the next steps.
1. 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)
```
1. Copy all WAL files started with 000000010000000000000040 to $PGDATA/pg_xlog.
2. Create $PGDATA/recovery.conf if needed (user: postgres).
```
standby_mode = 'on'
primary_conninfo = 'port=5432 host=DBHOSTNAME_MASTER user=repmgr'
recovery_target_timeline = 'latest'
```
1. Replace DBHOSTNAME_MASTER with a proper value.
Start the 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.4/data

echo Stop PostgreSQL...
service postgresql-9.4 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.4/bin/pg_resetxlog $PGSQL_DATA_DIR/ -f'

echo Start PostgreSQL...
service postgresql-9.4 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.

pg_basebackup may issue the following 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 is recommended to perform the backup 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

The 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 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 (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 tarcan be used, as well as any other (including previously mentioned) backup strategies.

Refer to the Riak KV instructions for the detailed steps.

You can also cold-backup.zip to provide this functionality for you.

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 took 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, located here.
    C:\Program Files\Tableau\Tableau Server\\bin

Where  is your version of Tableau Server.
For example, go to the Tableau Server 10.1 bin directory by typing the following:

     cd C:\Program Files\Tableau\Tableau Server\10.1\bin

Type tabadmin backup  -v and press Enter.
For example, type this:
    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

On the Tableau Server machine, open a command prompt as Administrator.

Click Start or press the Windows key.
Type cmd. Results will be listed, including cmd.exe.
Right-click cmd.exe and select Run as administrator.
Go to the Tableau Server bin folder, located here: 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

For more details, see the Tableau Online help.

Mongo backup

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

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/disk backup if that's in place

Vault backup

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

Restore from backup media

Applicability

Snapshot backup

Host snapshot

Disk snapshot

Virtual Machine or Virtual Host snapshot

Database backup

Cold database backup

Hot database backup

MySQL backup

Main operations

note

Scenario 1: daily full backup

Scenario 2: single incremental backup

Scenario 3: multiple incremental backups

note

Backup script

Main use cases

Use case: create a full backup

Use case: create an incremental backup

Use case: recovery from a backup

Backup integrity

PostgreSQL backup

Preface

Cold full backup

Hot full backup

Incremental backup

Recovery from a hot or incremental backup

caution

Recovery from cold backup

Backup integrity

S3 backup

Hot backup

Backup

Restore

Cold backup

Analytics backup

Back up Tableau data

Restore Tableau Server

Mongo backup

Solr backup

Artifactory backup

Vault backup