Major Release Upgrades for MariaDB Enterprise ColumnStore

Compatibility

• Enterprise ColumnStore 5

• Enterprise ColumnStore 6

• Enterprise ColumnStore 22.08

Prerequisites

This procedure assumes that the new Enterprise ColumnStore version will be installed onto new servers.

To reuse existing servers for the new Enterprise ColumnStore version, you must adapt the procedure detailed below. After step 1, confirm all data has been backed-up and verify backups. The old version of Enterprise ColumnStore should then be uninstalled, and all Enterprise ColumnStore files should be deleted before continuing with step 2.

Step 1: Backup/Export Schemas and Data

On the old ColumnStore cluster, perform a full backup.

MariaDB recommends backing up the table schemas to a single SQL file and backing up the table data to table-specific CSV files.

1. For each table, obtain the table's schema by executing the SHOW CREATE TABLE statement:

   SHOW CREATE TABLE DATABASE_NAME.TABLE_NAME\G
   

   Backup the table schemas by copying the output to an SQL file. This procedure assumes that the SQL file is named schema-backup.sql.

2. For each table, backup the table data to a CSV file using the SELECT .. INTO OUTFILE statement:

   SELECT * INTO OUTFILE '/path/to/DATABASE_NAME-TABLE_NAME.csv'
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   FROM DATABASE_NAME.TABLE_NAME;
   

3. Copy the SQL file containing the table schemas and the CSV files containing the table data to the primary node of the new ColumnStore cluster.

Step 2: Install New Major Release

On the new ColumnStore cluster, follow the deployment instructions of the desired topology for the new ColumnStore version.

For deployment instructions, see "MariaDB Deployment".

Step 3: Restore/Import Data

On the new ColumnStore cluster, restore the table schemas and data.

1. Restore the schema backup using mariadb client:

   $ mariadb --host HOST --port PORT --user USER --password < schema-backup.sql
   

   • HOST and PORT should refer to the following:

     • If you are connecting with MaxScale as a proxy, they should refer to the host and port of the MaxScale listener

     • If you are connecting directly to a multi-node ColumnStore cluster, they should refer to the host and port of the primary ColumnStore node

     • If you are connecting directly to single-node ColumnStore, they should refer to the host and port of the ColumnStore node

   • When the command is executed, mariadb client prompts for the user password

   For additional information about mariadb client, see "MariaDB Client".

2. For each table, restore the data from the table's CSV file by executing the cpimport utility on the primary ColumnStore node:

   $ sudo cpimport -s ',' \
      DATABASE_NAME \
      TABLE_NAME \
      /path/to/DATABASE_NAME-TABLE_NAME.csv
   

   For additional information about the cpimport utility, see "MariaDB Enterprise ColumnStore Data Loading with cpimport".

Step 4: Test

On the new ColumnStore cluster, verify that the table schemas and data have been restored.

1. For each table, verify the table's definition by executing the SHOW CREATE TABLE statement:

   SHOW CREATE TABLE DATABASE_NAME.TABLE_NAME\G
   

2. For each table, verify the number of rows in the table by executing SELECT COUNT(*):

   SELECT COUNT(*) FROM DATABASE_NAME.TABLE_NAME;
   

3. For each table, verify the data in the table executing the SELECT statement.

   If the table is very large, you can limit the number of rows in the result set by adding a LIMIT clause:

   SELECT * FROM DATABASE_NAME.TABLE_NAME LIMIT 100;