Upgrade a Multi-Node MariaDB Enterprise ColumnStore Deployment from 1.2 to 1.4 with MariaDB Enterprise Server 10.4 on Debian 10¶
These instructions detail the upgrade from MariaDB ColumnStore 1.2 to MariaDB Enterprise ColumnStore 1.4 with MariaDB Enterprise Server 10.4 on Debian 10 in a Multi-node ColumnStore Deployment configuration.
In this upgrade process, the system is upgraded from MariaDB ColumnStore 1.2, which is bundled with a patched version of MariaDB Server, to MariaDB Enterprise ColumnStore 1.4, which integrates with MariaDB Enterprise Server 10.4 as a storage engine plugin.
When MariaDB ColumnStore is upgraded, the old version must be uninstalled and the new version installed. Additionally, the configuration of MariaDB ColumnStore 1.2 and earlier is not compatible with MariaDB Enterprise ColumnStore 1.4. System variables and plugins have been renamed, which requires that you edit your configuration files to the new standard in addition to upgrading your software.
Occasionally, issues can be encountered during upgrades. These issues can even potentially corrupt the database's data files, preventing you from easily reverting to the old installation. It is generally best to perform a backup prior to upgrading. If an issue is encountered during the upgrade, you can use the backup to restore your MariaDB ColumnStore database to the old version. If the upgrade finishes without issue, the backup can be deleted.
columnstoreBackup for each Performance Module:
$ columnstoreBackup -zv 192.0.2.1 /data/backups/pm1
Confirm the successful completion of the backup operation.
Backups should be tested before they are trusted.
Additional information on
columnstoreBackup is available on the MariaDB Knowledge Base.
Uninstall Old Version¶
When upgrading MariaDB ColumnStore, ColumnStore plugins as well as the previous installation of MariaDB server must be uninstalled before attempting to install the new release.
Remove ColumnStore Plugins¶
MariaDB ColumnStore 1.4 includes several changes to the names of the plugins used by the storage engine. These changes make the plugin loads from MariaDB ColumnStore 1.2 incompatible with a 1.4 installation.
Before removing the previous installation, connect to the Server through the ColumnStore 1.2 Client,
mcsmysql, and use UNINSTALL PLUGIN to remove the MariaDB ColumnStore plugins:
UNINSTALL PLUGIN infinidb; UNINSTALL PLUGIN columnstore_tables; UNINSTALL PLUGIN columnstore_columns; UNINSTALL PLUGIN columnstore_extents; UNINSTALL PLUGIN columnstore_files; UNINSTALL PLUGIN columnstore;
Removing these plugins ensures that when the upgraded Server starts, it doesn't raise errors attempting to load plugins under their old names.
The current MariaDB ColumnStore processes must be stopped before uninstalling the installation. To stop the ColumnStore Instance, use mcsadmin shutdownSystem:
$ mcsadmin shutdownSystem y
Uninstall via APT (Debian/Ubuntu)¶
Uninstall all MariaDB ColumnStore packages. Note that a wildcard character used in the command to ensure that all MariaDB ColumnStore packages are uninstalled. Check that this wildcard does not unintentionally refer to any of your custom applications.
To remove all MariaDB ColumnStore packages, run the following command:
$ sudo apt remove "mariadb-columnstore-*"
Before proceeding, verify that all MariaDB ColumnStore packages are uninstalled. The following command should not return any results, if it does, remove those packages individually:
$ apt list --installed | grep -i -E "mariadb-columnstore"
MariaDB Corporation provides a APT package repository for Debian 10.
MariaDB Enterprise Server 10.4 does not require additional software to operate as an analytics database with MariaDB ColumnStore.
Install via APT (Debian/Ubuntu)¶
Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for
customer_download_tokenin the following directions.
Configure the APT package repository.
To configure APT package repositories:
$ sudo apt install wget $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup $ echo "957bc29576e8fd320fa18e35fa49b5733f3c8eeb4ca06792fb1f05e089c810ff mariadb_es_repo_setup" \ | sha256sum -c - $ chmod +x mariadb_es_repo_setup $ sudo ./mariadb_es_repo_setup --token="customer_download_token" --apply \ --mariadb-server-version="10.4" $ sudo apt update
Install MariaDB ColumnStore and package dependencies:
$ sudo apt install mariadb-server \ mariadb-columnstore-platform mariadb-plugin-columnstore
Stop the Server and disable the service after installing the packages:
$ sudo systemctl stop mariadb $ sudo systemctl disable mariadb
MariaDB ColumnStore post-installation scripts fail if they find MariaDB Enterprise Server running on the system.
Configure MariaDB ColumnStore.
Installation only loads MariaDB ColumnStore to the system. MariaDB ColumnStore requires configuration before the database server is ready for use.
MariaDB ColumnStore 1.4 updates the prefix used in system variables. The
infinidb_ prefix used in MariaDB ColumnStore 1.2 has been replaced with the
MariaDB ColumnStore 1.2 configuration files are not compatible with MariaDB ColumnStore 1.4.
Cross Engine Joins¶
Cross Engine Joins use a TCP connection from the ExeMgr to the Server, using the
root user with no password by default. MariaDB Enterprise Server 10.4 does not accept the default configuration. If you plan to use Cross Engine Joins, you need to create a new user for this purpose and update the MariaDB
Columnstore.xml configuration file.
Columnstore.xml is dangerous and can have unexpected results. Do not edit this configuration unless you specifically need support for Cross Engine Joins.
/etc/columnstore/Columnstore.xml file, modifying the
<CrossEngineSupport> element to use the configured
cross_engine user on Cross Engine Joins:
<CrossEngineSupport> <Host>127.0.0.1</Host> <Port>3306</Port> <User>cross_engine</User> <Password>cross_engine_passwd</Password> </CrossEngineSupport>
The configuration sets the user and password MariaDB ColumnStore uses for Cross Engine Joins. In order for this user to work, you must also create the user and grant it the appropriate privileges. Create the relevant user once you have the upgraded instance online.
MariaDB Enterprise Server with MariaDB ColumnStore 1.4 uses a different directory structure from MariaDB ColumnStore 1.2. In order to restore your data, you need to reconfigure the Server to use the 1.2 directory locations.
Stop the Server¶
Packages managers may start the service for MariaDB Enterprise Server during installation. When the Server operates as a ColumnStore Instance, it uses a separate conflicting service. Stop and disable the default service before proceeding:
$ sudo systemctl stop mariadb $ sudo systemctl disable mariadb
Restoring Server Data¶
In MariaDB ColumnStore 1.2, MariaDB Server writes ColumnStore data to DBRoots and data from all other storage engines in a non-standard data directory located in root installations at
/usr/local/mariadb/columnstore/mysql/db/. To restore the Server data from the 1.2 to the MariaDB Enterprise Server 10.4 installation, you need to move the old data directory to the new location.
On each MariaDB Enterprise Server, move the default data directory to an alternate location:
$ sudo mv /var/lib/mysql /var/lib/mysql_default
Then, move the data directory from MariaDB ColumnStore 1.2 to the new location:
$ sudo mv /usr/local/mariadb/columnstore/mysql/db /var/lib/mysql
When you start the ColumnStore Instance, it starts MariaDB Enterprise Server on the data directory from the old Server.
Restoring ColumnStore Data¶
In MariaDB ColumnStore 1.2, DBRoot files containing ColumnStore data are stored on Performance Modules (PM), in the ColumnStore home directory. The convention for the file names is
On each PM, identify the local DBRoots:
$ sudo ls /usr/local/mariadb/columnstore | grep "data[0-9]+" data1 data2 data3
For each DBRoot, move it from the directory used by the ColumnStore 1.2 installation to the one used by the ColumnStore 1.4 installation:
$ sudo mv /usr/local/mariadb/columnstore/data1 /var/lib/columnstore/data1
Installing the MariaDB Enterprise Server and MariaDB ColumnStore packages provides you with necessary packages to run the Server as a ColumnStore Instance, but only configures the Server as a Server. A few additional steps are needed to configure the MariaDB ColumnStore storage back-end.
Update the Bash Profile¶
MariaDB ColumnStore 1.2 shipped with a
columnstoreAlias.sh script, which created a series of aliases for various commands.
Remove reference to this file from your
.bash_profile and any copies in
/etc/profile.d/ to ensure that the commands below call the MariaDB ColumnStore 1.4 binaries in
/usr/bin and not the MariaDB ColumnStore 1.2 aliases.
Note, that to restore the environment variables, you will need to open a new shell after the above change.
columnstore-post-install script on each ColumnStore Instance to provision the system to host the storage back-end:
$ sudo columnstore-post-install
MariaDB ColumnStore provides a post-configuration script to configure the ColumnStore Instance. Post-configuration is only required on the first Server in your deployment (called
pm1 by convention). The postConfigure script connects over SSH to initialize Performance Modules on the other Servers.
Run the postConfigure script on the Server hosting the initial Performance Module (that is,
$ sudo /usr/bin/postConfigure
The postConfigure script performs the following configuration:
When prompted to configure the ColumnStore deployment for single- or multi-node, select multi-node.
Set the system name.
Configure the storage mount to store data internally on the local file system.
Once postConfigure has the information it needs, it connects to each Server and sets up the Performance Module to match the configuration, then starts MariaDB ColumnStore.
Restart the System¶
postConfigure script is complete, use mcsadmin restartSystem to restart MariaDB ColumnStore to clear the cache:
$ sudo mcsadmin restartSystem y
Upgrade the Data Directory¶
When the Server is running with MariaDB ColumnStore, run mariadb-upgrade to update the data directory:
$ sudo mariadb-upgrade
Create the Cross Engine User¶
Configuring MariaDB ColumnStore to perform Cross Engine Joins sets the user and password the system uses when reading data for the operation, it does not create the user or grant it the privileges to access the necessary data.
If you have configured your system to perform Cross Engine Joins, you must create a user with sufficient privileges to access that data. To create this user, use the CREATE USER and GRANT statements with the relevant credentials:
CREATE USER 'cross_engine'@'127.0.0.1' IDENTIFIED BY "cross_engine_passwd"; GRANT SELECT ON *.* TO 'cross_engine'@'127.0.0.1';
MariaDB ColumnStore includes an administrative utility called mcsadmin, which you can use to start and stop the ColumnStore processes:
When you have MariaDB ColumnStore up and running, you should test it to ensure that it is in working order and that there were not any issues during startup.
Checking Server Status¶
Connect to the server using MariaDB Client using the
$ sudo mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 38 Server version: 10.4.13-7-MariaDB-Enterprise MariaDB Enterprise Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Checking System Status¶
Execute the mcsadmin getSystemStatus command:
$ sudo mcsadmin getSystemStatus getsystemstatus Wed Jan 8 23:44:55 2020 System columnstore-1 System and Module statuses Component Status Last Status Change ------------ -------------------------- ------------------------ System ACTIVE Wed Jan 8 23:14:14 2020 Module pm1 ACTIVE Wed Jan 8 23:14:11 2020 Module pm2 ACTIVE Wed Jan 8 23:14:11 2020