The Ultimate Guide to High Availability with MariaDB

ColumnStore Backup and Recovery

You are viewing an old version of this article. View the current version here.

Contents

  1. Backup Overview
  2. Backup and Restore for MariaDB CoulmnStore 1.1.0 onwards
    1. columnstoreBackup
      1. Setup
      2. Running the tool
      3. LOGGING
      4. Return Codes
      5. Operation Notes
    2. columnstoreRestore
      1. SETUP
      2. Running
      3. LOGGING
      4. Return Codes
      5. Operation Notes
  3. Backup/Restore Process for MariaDB ColumnStore 1.0.x
    1. Suspend Write Activity
    2. Backup the MariaDB Server data files
      1. Using mysqldump
      2. Server Data File Directory Backup
    3. Backup ColumnStore Data Files
    4. Resume Write Activity
  4. Restore Overview
    1. Restoring the MariaDB Instance
      1. mysqldump
      2. Restoring the Server Data Files
    2. Restoring the ColumnStore Data Files

Backup Overview

The high level steps involved in performing a full backup of MariaDB ColumnStore are:

  • Suspend write activity on the system.
  • Backup the MariaDB Server data files.
  • Backup the ColumnStore data files.
  • Resume write activity on the system.

Backup and Restore for MariaDB CoulmnStore 1.1.0 onwards

columnstoreBackup

In MariaDB ColumnStore 1.1.0 a tool - columnstoreBackup to automate the backup/restore across the MariaDB ColumnStore nodes is available. It is available for download at https://mariadb.com/downloads/mariadb-tx/tools

Note: columnstoreBackup tool is only for ColumnStore data backups. Other engines may not be fully backed up and data could be lost when restoring.

Setup

To run columnstoreBackup you'll need to setup a backup server with passwordless ssh login available for the user account that installed MariaDB ColumnStore. (Default: root) Copy the executable columnstoreBackup onto the backup server. Create a target directory on the backup server to store the files. This directory will need to have enough space to store all ColumnStore data files. Example:

Backup Executable: /home/user/columnstoreBackup
Backup Data Directory: /home/user/columnstoreBackupData/

There is an optional columnstoreBackup.config file that when placed in the same directory as the columnstoreBackup executable will allow you to configure an incremental backup option that uses the rsync link-dest option to enable incremental backups. These are stored in backup.1 thru backup.[n-1] from newest to oldest. The columnstoreBackup.config file should only contain a single line:

NUMBER_BACKUPS=[n]

Where "n" is the number of incremental backups to store. (Default: 3)

Running the tool

columnstoreBackup must be run as root user either logging in as root or via the sudo command.

Usage: [sudo] ./columnstoreBackup [options] activeParentOAM backupServerLocation

activeParentOAM           IP address of ColumnStore server
                             (Active parent OAM module on multi-node install)
backupServerLocation      Path to the directory for storing backup files.

OPTIONS:
-h,--help         Prints help and exits.
-v,--verbose      Print more verbose execution details.
-d,--dry-run      Dry run and executes rsync dry run with stats.
-z,--compress     Utilize the compression option for rsync.
-n [value]        Maximum number parallel rsync commands. (Default: 5)
--user=[user]     Change the user performing remote sessions. (Default: root)

--install-dir=[PATH]  Change the install directory of ColumnStore.
                          Default: /usr/local/mariadb/columnstore

Example:

Running from the directory /home/user/:

sudo ./columnstoreBackup -zv 192.168.1.2 home/user/columnstoreBackupData

This will execute a backup for the system with a parent OAM module located at 192.168.1.2 and store all backup files inside the directory located at home/user/columnstoreBackupData. Option v will print out a more verbose logging of commands executed and option z will let rsync utilize the compression option for file transfers.

LOGGING

Logging is output to the console as well as to a columnstoreBackup.log that is located in the directory columnstoreBackup is executed. This will contain some extra details on some issues. Log rotation is left to the user for handling.

Return Codes

0 - success
1 - command line parameter or config file issue detected
2 - missing rsync or xmllint
3 - detected issue with disk space
4 - detected bad configuration file settings
5 - rsync command failed with an error
255 - could not connect via passwordless ssh

Operation Notes

columnstoreBackup will create the following directories inside the Backup Data Directory:

backup.[1-n] (n incremental backups)
cnf (my.cnf and my.cnf.d)
pm[moduleID]dbroot[DBRootID] (pm1dbroot1 contains PM data from dbroot 1 on pm 1)
um[moduleID] (NOTE: When UM/PM are combined on nodes UM1 is the mysql/db directory for PM1)

These directories are created if they do not exist and can be created prior to execution by the user.

The columnstoreBackup option -n [value] limits the number parallel rsync commands executed at a given time. The default 5 means up to 5 DBRoots will kick off rysnc commands to various PMs and the backup system will wait until all are complete and verified successful. At this time it will kick off another 5 DBRoots. The progress indicator should reflect the percentage of total completion and not individual rysnc commands. This value can be set higher via the -n command but if the number of DBRoots present in the system is large enough there may be a performance hit on system processing or network bandwidth limitations.

columnstoreRestore

The tool is designed to be run on the system storing the backups. This will automate restoring from backups created by the columnstoreBackup tool.

SETUP

To run columnstoreRestore you'll need to setup a backup server with passwordless ssh login available for the user account that installed MariaDB ColumnStore. (Default: root)

columnstoreRestore must be run as root or with sudo.

columnstoreRestore expects MariaDB Columnstore to be shutdown in a fresh install state.

Take the following steps to prepare system for columnstoreRestore:

  • On the active parent OAM module execute the command 
mcsadmin shutdownsystem y
  • Run on all PM modules: 
rm -rf [INSTALL_DIR]/data*/000.dir
  • Run on all UM or combo PM front-end nodes 
rm -rf [INSTALL_DIR]/data1/systemFiles/dbrm/*
  • Run on all PMs 
cd [INSTALL_DIR]/mysql/db 
delete all directories except:
calpontsys
infinidb_Qquerystats
infinidb_infinidb_vtable
mysql
performance_schema
test
  • On the active parent OAM module execute the command 
[INSTALL_DIR]/bin/clearShm
  • On the backup system run columnstoreRestore script

Running

columnstoreRestore must be run as root user either logging in as root or via the sudo command.

Usage: ./columnstoreRestore [options] backupServerLocation restoreServerPM1

restoreServerPM1          IP address of ColumnStore server
                             (Assumes PM1 = Active Parent OAM Module)
backupServerLocation      Path to the directory for storing backup files.

OPTIONS:
-h,--help         Print this message and exit.
-v,--verbose      Print more verbose execution details.
-d,--dry-run      Dry run and executes rsync dry run with stats.
-z,--compress     Utilize the compression option for rsync.
-n [value]        Maximum number parallel rsync commands. (Default: 5).
--user=[user]     Change the user performing remote sessions. (Default: root)

--install-dir=[PATH]  Change the install directory of ColumnStore.
                          Default: /usr/local/mariadb/columnstore

EXAMPLE: Running from the directory /home/user/ with the columnstoreBackupData directory created in the columnstoreBackup example above:

sudo ./columnstoreRestore -zv home/user/columnstoreBackupData 192.168.1.100

This will execute a restore for the MariaDB ColumnStore system with a parent OAM module located at 192.168.1.100 from the directory located at home/user/columnstoreBackupData. Option v will print out a more verbose logging of commands executed and option z will let rsync utilize the compression option for file transfers.

LOGGING

Logging is output to the console as well as to a columnstoreRestore.log that is located in the directory columnstoreBackup is executed. This will contain some extra details on some issues. Log rotation is left to the user for handling.

Return Codes

0 - success
1 - command line parameter or config file issue detected
2 - missing rsync or xmllint
3 - detected issue with disk space
4 - detected bad configuration file settings
5 - rsync command failed with an error
255 - could not connect via passwordless ssh

Operation Notes

columnstoreRestore will create a restoreConfig directory inside the backupServerLocation defined at command line. This is just meant to store a copy of the restored systems version and configuration file for verification the restore is possible.

The columnstoreRestore option -n [value] limits the number parallel rsync commands executed at a given time. The default 5 means up to 5 DBRoots will kick off rysnc commands to various PMs and the backup system will wait until all are complete and verified successful. At this time it will kick off another 5 DBRoots. The progress indicator should reflect the percentage of total completion and not individual rysnc commands. This value can be set higher via the -n command but if the number of DBRoots present in the system is large enough there may be a performance hit on system processing or network bandwidth limitations.

Backup/Restore Process for MariaDB ColumnStore 1.0.x

Suspend Write Activity

To suspend data writes to column store the following command can be issued the admin console:

mcsadmin> suspendDatabaseWrites
suspenddatabasewrites   Thu Oct 13 13:18:40 2016

This command suspends the DDL/DML writes to the MariaDB Columnstore Database
           Do you want to proceed: (y or n) [n]: y

Suspend Calpont Database Writes Request successfully completed

Optionally y can be appended as an argument to suspendDatabaseWrites to avoid the confirmation prompt.

Backup the MariaDB Server data files

The MariaDB Server should be backed up using one of the available backup methods described in the server backup and restore overview. Since the column store data is not stored within the MariaDB Server backup should run very quickly. Utilizing either mysqldump or just backing up the directory are straightforward options.

Using mysqldump

For example:

> /usr/local/mariadb/columnstore/mysql/bin/mysqldump --skip-lock-tables --no-data loansdb > mariadb_bkp.sql

Note the --no-data option since only the ddl is required for column store tables. The next step will backup the data files. If tables exist using other storage engines then this is likely not appropriate for these.

Server Data File Directory Backup

Backup can be achieved by simply copying the Server data directories under /usr/local/mariadb/columnstore/.

> cp -rp /usr/local/mariadb/columnstore/mysql/db .

Backup ColumnStore Data Files

Backup can be achieved by simply copying the data directories or using vendor supplied backup or snapshot utilities for those directories. A files and directories in the data<N> directories where N represents a unique directory such as data1, data2, etc for each PM server.

> cp -rp /usr/local/mariadb/columnstore/data? .

Resume Write Activity

To resume data writes to column store the following command can be issued the admin console:

mcsadmin> resumeDatabaseWrites
resumedatabasewrites   Thu Oct 13 13:58:55 2016

This command resumes the DDL/DML writes to the MariaDB Columnstore Database
           Do you want to proceed: (y or n) [n]: y

Resume MariaDB Columnstore Database Writes Request successfully completed

Optionally y can be appended as an argument to resumeDatabaseWrites to avoid the confirmation prompt.

Restore Overview

The high level steps involved in restoring a backup are:

  • Restore the MariaDB Instance
  • Restore the ColumnStore data files.

Restoring the MariaDB Instance

The appropriate restoration method corresponding to the backup utility used should be performed first to restore the MariaDB server instance.

mysqldump

If mysqldump was utilized then the backup script is run:

> mcsmysql

MariaDB [(none)]> create database loansdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use loansdb;
Database changed
MariaDB [loansdb]> source mariadb_bkp.sql
Query OK, 0 rows affected (0.00 sec)
...
MariaDB [loansdb]> exit

Restoring the Server Data Files

Backup can be achieved by simply copying the Server data directories under /usr/local/mariadb/columnstore/.

> rm -rf /usr/local/mariadb/columnstore/mysql/db
> cp -rpf db /usr/local/mariadb/columnstore/mysql

Restoring the ColumnStore Data Files

The data<N> directories should simply be copied from the backup location or restored via an appropriate backup or snapshot utility. For example:

> rm -rf /usr/local/mariadb/columnstore/data?
> cp -rpf data? /usr/local/mariadb/columnstore
> mcsadmin startSystem

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.