Migrating from InfiniDB to ColumnStore
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.