The Ultimate Guide to High Availability with MariaDB

Migrating from InfiniDB to ColumnStore

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

Overview

The columnar disk storage format is unchanged between InfiniDB 4 and ColumnStore allowing for relatively straightforward data migration utilizing backup and restore logic. This document outlines an approach to perform the migration that can be adapted to your particular needs.

Backup Data in InfiniDB

Suspend writes if this is a live system:

# cc suspendDatabaseWrites y

Use mysqldump to create schema files from appropriate databases:

/usr/local/Calpont/mysql/bin/mysqldump --defaults-file=/usr/local/Calpont/mysql/my.cnf --skip-lock-tables --no-data loans > loans_schema.sql

Update schema file to utilize correct engine and add schema sync only comment:

# sed "s/ENGINE=InfiniDB/ENGINE=columnstore COMMENT='schema sync only'/gI" loans_schema.sql > loans_schema_columnstore.sql

Take a backup of the columnar data files from each PM which are stored in the data<N> directories of each PM server. The exact folder list can be confirmed by looking at the SystemConfig section of the configuration file /usr/local/Calpont/etc/Calpont.xml. Each data<n> directory corresponds to a specific DBRoot containing the actual columnar data in the 000.dir and system metadata under systemFiles/dbrm. In addition you may also want to take a copy of the data directory if this contains custom scripts for bulk loading:

cp -r /usr/local/Calpont/data? .

Restoring Backup into ColumnStore:

First install a new fresh install of ColumnStore then create the schema using the mysqldump file:

# mcsmysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.19-MariaDB Columnstore 1.0.5-1

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

MariaDB [(none)]> use loans
Database changed
MariaDB [loans]> source loans_schema_columnstore.sql
Query OK, 0 rows affected (0.00 sec)

...

MariaDB [loans]> exit

Now replace the data<N> directories with the backup on each PM as appropriate:

# mcsadmin shutdownSystem y
# rm -rf /usr/local/mariadb/columnstore/data?
# cp -r data? /usr/local/mariadb/columnstore/
# mcsadmin startSystem

The system should start cleanly and the data should now be accessible in the database.

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.