Databases Azure

MariaDB ColumnStore on Ubuntu 24.04 on Azure User Guide

| Product: MariaDB ColumnStore on Ubuntu 24.04 LTS on Azure

Overview

This guide covers the deployment and operation of MariaDB ColumnStore on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

MariaDB ColumnStore is the columnar storage engine for MariaDB Server, built for analytics: data is stored by column, compressed and scanned in parallel, so aggregates over millions of rows (reporting, dashboards, data warehouse queries) run far faster than on a row store. You use it through normal MariaDB SQL by creating tables with ENGINE=ColumnStore, and you can keep ordinary InnoDB tables in the same server. A typical shape is InnoDB for the small dimension tables you update constantly and ColumnStore for the large, append mostly fact tables you aggregate over.

ColumnStore is an engine, not a server, so it cannot run on its own. This image runs ColumnStore 25.10.6 with MariaDB Server 11.8.9, the vendor's paired release from the MariaDB 11.8 long term support line, as a single node. Both come from MariaDB's official repository as a matched pair (the engine package depends on that exact server version), so they never drift apart and neither is compiled from source.

What the cloudimg image adds:

  • No shared password, anywhere. The image ships with both data stores empty and no password account at all. First boot initialises this VM's own MariaDB data directory and ColumnStore system catalog, then creates dbadmin, a full MariaDB administrator, with a password unique to your VM, and writes it to /root/mariadb-columnstore-credentials.txt (root only). An empty cloudimg database is ready for your schema.
  • Root only from the VM. MariaDB's root account has no password and no network access: it signs in only through the local socket as the operating system's root user (sudo mariadb).
  • TLS from the first boot, and required. A TLS key and certificate are generated on your VM before MariaDB first starts (MariaDB will not start without them), and every TCP connection must use TLS 1.2 or 1.3: plain text connections are refused.
  • ColumnStore on localhost only. A stock ColumnStore install listens on seven unauthenticated internal ports (8601 to 8700) on every network interface. In this image every ColumnStore service is bound to 127.0.0.1, MariaDB listens on 127.0.0.1 only, and the Cluster Management API (cmapi, a multi node tool with its own API key) is not installed.
  • A hardened MariaDB and ColumnStore. No anonymous accounts, no test database, LOAD DATA LOCAL off, server side file access confined to /var/lib/mysql-files, host name lookups off, the usage feedback plugin off, no binary or general query log, the stock cross engine join identity (root with an empty password) removed, and the world writable log and temporary directories that the ColumnStore installer leaves behind closed.
  • A memory budget that fits the VM. ColumnStore's block cache and join memory are set as a share of the VM's memory (20% and 50%), below the 75% ceiling MariaDB documents, so the same image works from 4 GiB up and grows with larger sizes.
  • Signed, pinned packages. MariaDB Server and the ColumnStore engine come from the official MariaDB 11.8 repository, checked against the MariaDB signing key and pinned to that repository.
  • Metadata access blocked. MariaDB, every ColumnStore service and the first boot services cannot reach the Azure instance metadata service or the Azure wire server (the Azure Linux Agent keeps its own access).
  • Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on. The firmware update timer (fwupd-refresh) is masked, because Azure VMs have no firmware it can update.

What is on the image: MariaDB ColumnStore 25.10.6 (package mariadb-plugin-columnstore 1:11.8.9-25.10.6+maria~ubu2404), MariaDB Server, client and Mariabackup 11.8.9 (package 1:11.8.9+maria~ubu2404), all from the MariaDB repository, the cpimport bulk loader, Ubuntu 24.04 LTS, cloud-init and the Azure Linux Agent.

Prerequisites

  • An active Azure subscription

  • A subscription to the MariaDB ColumnStore on Ubuntu 24.04 listing on Azure Marketplace

  • An SSH public key (or a password) for virtual machine authentication

  • A virtual network and subnet in the target region

Choosing a virtual machine size

ColumnStore refuses to start on a machine with less than 3 GB of memory, so the smallest supported size has 4 GiB. Beyond that, its appetite for RAM and cores scales with the data you put in it rather than with the software itself. MariaDB's ColumnStore Hardware Guide asks for 8 CPU cores and 32 GB of RAM for development work and far more for production analytics.

Workload Suggested size Notes
Evaluation, functional testing, small marts Standard_D4s_v5 (4 vCPU, 16 GB) Comfortable for getting to know the engine
Development and departmental analytics Standard_D8s_v5 (8 vCPU, 32 GB) Matches MariaDB's documented development minimum
Production analytics Standard_E16s_v5 or larger Memory optimised; size to your working set

The image itself idles at well under 1 GB. Query capacity, not startup, is what larger sizes buy you. ColumnStore returns a clear error when a join or aggregate exceeds its memory budget; the budget is a share of the VM's memory, so it grows when you resize the VM (Step 11).

ColumnStore keeps its data under /var/lib/columnstore on the OS disk. Before loading a large dataset, choose a larger OS disk at deployment or attach a data disk and mount it there before first use.

Step 1: Deploy from the Azure Portal

Navigate to Marketplace in the Azure Portal, search for MariaDB ColumnStore, and select the cloudimg publisher entry. Click Create.

On the Networking tab, attach a network security group that allows inbound TCP 22 from your management IP range only. No database port needs to be opened. MariaDB and every ColumnStore service listen on the loopback address, and the documented remote access path is an SSH tunnel (Step 8).

Click Review + create, wait for validation to pass, then Create. Deployment takes around two minutes, and first boot takes about a minute more.

To deploy from the Azure CLI instead, replace the placeholders and run:

az vm image terms accept --urn cloudimg1647283583153:mariadb-columnstore-ubuntu-24-04:default:latest

az vm create \
  --resource-group <your-resource-group> \
  --name mariadb-columnstore-01 \
  --image cloudimg1647283583153:mariadb-columnstore-ubuntu-24-04:default:latest \
  --size Standard_D8s_v5 \
  --admin-username azureuser \
  --ssh-key-values ~/.ssh/id_rsa.pub \
  --public-ip-sku Standard

Step 2: Connect over SSH

Connect as the administrator user you chose at deployment (this guide uses azureuser):

ssh azureuser@<public-ip>

Step 3: Check the services

First boot mints the certificate, initialises this VM's own data stores, starts MariaDB and the ColumnStore engine, and creates the administrator account:

systemctl is-active mariadb-tls-init mariadb mariadb-columnstore-firstboot mariadb-columnstore
systemctl is-active mcs-workernode@1 mcs-controllernode mcs-primproc mcs-writeengineserver mcs-dmlproc mcs-ddlproc
sudo test -f /var/lib/cloudimg/mariadb-columnstore-firstboot.done && echo FIRSTBOOT_DONE

Every unit is active and the first boot marker is present:

active
active
active
active
active
active
active
active
active
active
FIRSTBOOT_DONE

Only SSH listens on a network port; MariaDB and every ColumnStore service listen on the loopback address:

sudo ss -lntp | awk 'NR>1 {print $4, $6}' | sed -E 's/,pid=[0-9]+,fd=[0-9]+//g' | grep -v -E '^127\.0\.0\.5[34]' | sort

0.0.0.0:22 users:(("sshd"),("systemd"))
127.0.0.1:3306 users:(("mariadbd"))
127.0.0.1:8601 users:(("PrimProc"))
127.0.0.1:8612 users:(("DDLProc"))
127.0.0.1:8614 users:(("DMLProc"))
127.0.0.1:8616 users:(("controllernode"))
127.0.0.1:8620 users:(("PrimProc"))
127.0.0.1:8630 users:(("WriteEngineServ"))
127.0.0.1:8700 users:(("workernode"))
[::]:22 users:(("sshd"),("systemd"))

First boot complete: every service active and only SSH on a network port

If first boot has not finished yet, wait a minute and run the checks again. If it failed, its log says why:

sudo journalctl -u mariadb-columnstore-firstboot.service --no-pager -n 50

Step 4: Get your administrator password

sudo cat /root/mariadb-columnstore-credentials.txt

The file is readable by root only:

# MariaDB ColumnStore 25.10.6 (MariaDB Server 11.8.9) on Ubuntu 24.04 - generated on first boot by mariadb-columnstore-firstboot.service
# This password is unique to this VM; none was baked into the image. Keep this file secret.

MARIADB_ADMIN_USER=dbadmin
MARIADB_ADMIN_PASSWORD=<MARIADB_ADMIN_PASSWORD>
MARIADB_HOST=127.0.0.1
MARIADB_PORT=3306
MARIADB_DATABASE=cloudimg
MARIADB_TLS_CA=/etc/mysql/ssl/server-cert.pem

# root has no password: on the VM use  sudo mariadb  (unix socket only, never over the network).
# Every TCP connection must use TLS (require_secure_transport=ON); MariaDB listens on 127.0.0.1:3306 only and every
# ColumnStore daemon on 127.0.0.1 only. Create ColumnStore tables with ENGINE=ColumnStore.
# Re-issue the TLS certificate for your own DNS name:  sudo mariadb-tls-cert --renew <your-dns-name>
# User guide: https://www.cloudimg.co.uk/guides/mariadb-columnstore-on-ubuntu-24-04-azure/

The per VM credentials file, root only, and the hardened server settings

Step 5: Sign in

As the operating system's root user, MariaDB's root account signs in through the local socket with no password:

sudo mariadb -e "SELECT CURRENT_USER(), VERSION(), mcsgetversion();"

CURRENT_USER()  VERSION()   mcsgetversion()
root@localhost  11.8.9-MariaDB-ubu2404  25.10.6

MariaDB Server 11.8.9 with the ColumnStore 25.10.6 engine

To work as dbadmin over TCP, keep its password in a client option file that only you can read, so it never appears on a command line. This writes ~/.mariadb-dbadmin.cnf, pointing the client at the VM's own certificate and verifying it:

( umask 077; sudo awk -F= '/^MARIADB_ADMIN_PASSWORD=/{print "[client]\nuser=dbadmin\npassword=" $2 "\nhost=127.0.0.1\nssl-ca=/etc/mysql/ssl/server-cert.pem\nssl-verify-server-cert"}' /root/mariadb-columnstore-credentials.txt > ~/.mariadb-dbadmin.cnf )
ls -l ~/.mariadb-dbadmin.cnf | awk '{print $1, $NF}'
mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" -e "SELECT CURRENT_USER(); SHOW SESSION STATUS LIKE 'Ssl_version';"

The session is dbadmin over a verified TLS connection:

-rw------- /home/azureuser/.mariadb-dbadmin.cnf
CURRENT_USER()
dbadmin@127.0.0.1
Variable_name   Value
Ssl_version TLSv1.3

The image starts with only the system databases (calpontsys, columnstore_info, infinidb_querystats and queryacc belong to ColumnStore) and the empty cloudimg database; nothing from the build is left behind:

mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" -e "SHOW DATABASES;"

Database
calpontsys
cloudimg
columnstore_info
infinidb_querystats
information_schema
mysql
performance_schema
queryacc
sys

Step 6: Create and query a ColumnStore table

Create tables with ENGINE=ColumnStore. This example creates a sales table, loads 100,000 generated rows with one INSERT ... SELECT from MariaDB's built in number sequence (ColumnStore turns it into a bulk load) and runs an aggregate:

mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" <<'SQL'
DROP DATABASE IF EXISTS analytics;
CREATE DATABASE analytics;
USE analytics;
CREATE TABLE analytics.sales (
  sale_id INT,
  sale_date DATE,
  region VARCHAR(16),
  amount DECIMAL(12,2)
) ENGINE=ColumnStore;
INSERT INTO analytics.sales
  SELECT seq, DATE_ADD('2026-01-01', INTERVAL seq % 365 DAY),
         ELT(1 + seq % 4, 'emea', 'apac', 'amer', 'latam'), (seq % 1000) + 0.99
  FROM seq_1_to_100000;
SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='analytics' AND TABLE_NAME='sales';
SELECT COUNT(*) AS loaded FROM analytics.sales;
SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue FROM analytics.sales GROUP BY region ORDER BY region;
SQL

ENGINE
Columnstore
loaded
100000
region  orders  revenue
amer    25000   12524750.00
apac    25000   12499750.00
emea    25000   12474750.00
latam   25000   12549750.00

A ColumnStore table and an analytic aggregate over it

For large files, cpimport loads a CSV straight into a ColumnStore table, much faster than INSERT. Run it as root on the VM:

printf '200001,2026-06-30,emea,10.50\n200002,2026-06-30,apac,20.25\n' | sudo tee /var/lib/mysql-files/sales-extra.csv >/dev/null
sudo cpimport -s ',' analytics sales /var/lib/mysql-files/sales-extra.csv 2>&1 | grep -E 'Table analytics.sales|rows processed'
sudo rm -f /var/lib/mysql-files/sales-extra.csv
mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" -e "SELECT COUNT(*) FROM analytics.sales;"

<date> <time> (<pid>) INFO : For table analytics.sales: 2 rows processed and 2 rows inserted.
COUNT(*)
100002

Columnar table design rules

  • No indexes or primary keys. ColumnStore does not use them; a CREATE TABLE that declares one fails with Too many keys specified; max 0 keys allowed.
  • Load in bulk. Use cpimport or INSERT ... SELECT for large loads; many single row inserts are slow on a columnar engine.
  • Create the table first. CREATE TABLE ... AS SELECT falls back to another engine; create the table with ENGINE=ColumnStore, then INSERT ... SELECT into it.
  • Use your own database. Do not create ColumnStore tables in mysql, information_schema or calpontsys.

Step 7: Create an application user

Give each application its own database and an account with rights on that database only. Run this as root through the socket (choose your own strong password; the statements go to MariaDB on standard input):

sudo mariadb <<'SQL'
CREATE USER 'appuser'@'localhost' IDENTIFIED BY '<app-password>';
CREATE USER 'appuser'@'127.0.0.1' IDENTIFIED BY '<app-password>';
GRANT ALL PRIVILEGES ON analytics.* TO 'appuser'@'localhost', 'appuser'@'127.0.0.1';
SQL

An application or BI tool on the same VM connects to 127.0.0.1:3306 with TLS (most MySQL and MariaDB drivers have a "require SSL" or ssl-mode=REQUIRED option; give them /etc/mysql/ssl/server-cert.pem as the CA file to verify the server too), or to the local socket /run/mysqld/mysqld.sock.

Step 8: Connect from your workstation over an SSH tunnel

MariaDB is not exposed to the network. From your workstation, copy the VM's certificate so your client can verify the server, open an SSH tunnel and connect through it:

scp azureuser@<public-ip>:/etc/mysql/ssl/server-cert.pem ./mariadb-ca.pem
ssh -N -L 3307:127.0.0.1:3306 azureuser@<public-ip>
mariadb -h 127.0.0.1 -P 3307 -u dbadmin -p --ssl-ca=./mariadb-ca.pem --ssl-verify-server-cert

Business intelligence tools that speak the MySQL wire protocol (Metabase, Superset, Tableau, Power BI through the MySQL connector, DBeaver) offer the same SSH tunnel and "require SSL" settings.

Step 9: Allow connections from your virtual network

To let application servers in your virtual network connect directly to MariaDB, change the listening address, allow 3306 in the network security group from your application subnet only, and create accounts for that subnet that require TLS:

sudo sed -i 's/^bind-address = 127.0.0.1/bind-address = 0.0.0.0/' /etc/mysql/mariadb.conf.d/99-cloudimg.cnf
sudo systemctl restart mariadb
sudo mariadb -e "CREATE USER 'appuser'@'10.0.1.%' IDENTIFIED BY '<app-password>' REQUIRE SSL; GRANT ALL PRIVILEGES ON analytics.* TO 'appuser'@'10.0.1.%';"

Then add an inbound rule for TCP 3306 from the application subnet (for example 10.0.1.0/24) to the VM's network security group. Never open 3306 to the internet, and never open the ColumnStore ports (8600 to 8800): they carry an unauthenticated internal protocol and stay on 127.0.0.1 for a single node.

Step 10: Your own DNS name and certificate

The certificate generated on first boot names localhost, 127.0.0.1 and the VM's host name, private address and public address. If clients reach the database by a DNS name of your own, re-issue the certificate with that name added. MariaDB loads the new certificate at once, without a restart (FLUSH SSL); give clients the new /etc/mysql/ssl/server-cert.pem:

sudo mariadb-tls-cert --renew db.example.internal

To use a certificate from your own certificate authority, replace server-cert.pem and server-key.pem in /etc/mysql/ssl/ (keep the key 0600 and owned by mysql; if your certificate is issued by a CA, point ssl_ca in /etc/mysql/mariadb.conf.d/99-cloudimg.cnf at your CA bundle), then run sudo mariadb -e "FLUSH SSL;".

Step 11: ColumnStore memory and settings

ColumnStore's settings live in /etc/columnstore/Columnstore.xml and are read and changed with mcsGetConfig and mcsSetConfig. The image sets the block cache to 20% of the VM's memory and join memory to 50%, so they follow the VM size when you resize it:

for s in "DBBC NumBlocksPct" "HashJoin TotalUmMemory" "HashJoin PmMaxMemorySmallSide"; do set -- $s; echo "$1/$2 = $(sudo mcsGetConfig $1 $2)"; done

DBBC/NumBlocksPct = 20
HashJoin/TotalUmMemory = 50%
HashJoin/PmMaxMemorySmallSide = 512M

To change a setting, set it and restart the engine. Keep the two percentages together at or below 75%, and do not set join memory much lower than this on a small VM: ColumnStore's own system catalog queries need it. If a query outgrows the budget you see a clear error such as MCS-2001: Join or subselect exceeds memory limit; raise the budget or move to a larger VM.

sudo mcsSetConfig HashJoin TotalUmMemory '60%'
sudo systemctl restart mariadb-columnstore

Cross engine joins (a ColumnStore table joined with an InnoDB table in one query) need a MariaDB account that ColumnStore uses to read the InnoDB side. The image ships that setting empty rather than the stock root with an empty password. To turn it on, create a least privilege account that requires TLS and set it, keeping Columnstore.xml readable by the mysql user only:

sudo mariadb -e "CREATE USER 'cross_engine'@'127.0.0.1' IDENTIFIED BY '<cross-engine-password>' REQUIRE SSL; GRANT SELECT ON *.* TO 'cross_engine'@'127.0.0.1';"
sudo mcsSetConfig CrossEngineSupport User cross_engine
sudo mcsSetConfig CrossEngineSupport Password '<cross-engine-password>'
sudo mcsSetConfig CrossEngineSupport TLSCA /etc/mysql/ssl/server-cert.pem
sudo chmod 0640 /etc/columnstore/Columnstore.xml /etc/columnstore/Columnstore.xml.columnstoreSave
sudo systemctl restart mariadb-columnstore

Step 12: Security checks

MariaDB runs with the cloudimg settings: loopback only, TLS required, LOAD DATA LOCAL and the binary log off, file access confined:

sudo mariadb -e "SELECT @@bind_address, @@require_secure_transport, @@tls_version, @@local_infile, @@log_bin, @@secure_file_priv;"

@@bind_address  @@require_secure_transport  @@tls_version   @@local_infile  @@log_bin   @@secure_file_priv
127.0.0.1   1   TLSv1.2,TLSv1.3 0   0   /var/lib/mysql-files/

Every ColumnStore service is configured to listen on 127.0.0.1, and the cross engine identity is empty:

for s in ExeMgr1 PMS1 pm1_WriteEngineServer DDLProc DMLProc DBRM_Controller DBRM_Worker1; do echo "$s ListenAddr=$(sudo mcsGetConfig $s ListenAddr) Port=$(sudo mcsGetConfig $s Port)"; done
echo "CrossEngineSupport User='$(sudo mcsGetConfig CrossEngineSupport User)' Password='$(sudo mcsGetConfig CrossEngineSupport Password)'"

ExeMgr1 ListenAddr=127.0.0.1 Port=8601
PMS1 ListenAddr=127.0.0.1 Port=8620
pm1_WriteEngineServer ListenAddr=127.0.0.1 Port=8630
DDLProc ListenAddr=127.0.0.1 Port=8612
DMLProc ListenAddr=127.0.0.1 Port=8614
DBRM_Controller ListenAddr=127.0.0.1 Port=8616
DBRM_Worker1 ListenAddr=127.0.0.1 Port=8700
CrossEngineSupport User='' Password=''

A plain text connection is refused, root cannot sign in over the network with any password, and a wrong dbadmin password is refused:

mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" --skip-ssl -e 'SELECT 1' 2>&1 || true
mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" -u root --password=anything -e 'SELECT 1' 2>&1 || true
mariadb --defaults-file="$HOME/.mariadb-dbadmin.cnf" --password=wrong-password -e 'SELECT 1' 2>&1 || true

ERROR 3159 (08004): Connections using insecure transport are prohibited while --require_secure_transport=ON.
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
ERROR 1045 (28000): Access denied for user 'dbadmin'@'127.0.0.1' (using password: YES)

root has no usable password and authenticates only through the socket, the internal mariadb.sys account is locked, and dbadmin is the only password account:

sudo mariadb -e "SHOW CREATE USER 'root'@'localhost'; SHOW CREATE USER 'mariadb.sys'@'localhost';"
sudo mariadb -N -e "SELECT CONCAT(User,'@',Host) FROM mysql.global_priv ORDER BY 1;"

CREATE USER for root@localhost
CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket
CREATE USER for mariadb.sys@localhost
CREATE USER `mariadb.sys`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
dbadmin@127.0.0.1
dbadmin@::1
dbadmin@localhost
mariadb.sys@localhost
mysql@localhost
root@localhost

The certificate was generated on this VM and names its own addresses; the key is readable by MariaDB only:

openssl x509 -in /etc/mysql/ssl/server-cert.pem -noout -ext subjectAltName
ls -l /etc/mysql/ssl | awk 'NR>1{print $1, $3, $4, $NF}'

X509v3 Subject Alternative Name:
    DNS:<vm-name>.<internal-dns-suffix>.internal.cloudapp.net, DNS:<vm-name>, IP Address:<private-ip>, IP Address:<public-ip>, DNS:localhost, IP Address:127.0.0.1, IP Address:0:0:0:0:0:0:0:1
-rw-r--r-- root root server-cert.pem
-rw------- mysql mysql server-key.pem

MariaDB, the first boot services and every ColumnStore service cannot reach the Azure instance metadata service (169.254.169.254) or the wire server (168.63.129.16):

for u in mariadb mariadb-tls-init mariadb-columnstore-firstboot mariadb-columnstore mcs-workernode@1 mcs-controllernode mcs-primproc mcs-writeengineserver mcs-dmlproc mcs-ddlproc mcs-loadbrm mcs-storagemanager; do printf '%-30s %s\n' "$u" "$(systemctl show -p IPAddressDeny --value "$u.service")"; done

mariadb                        168.63.129.16/32 169.254.169.254/32
mariadb-tls-init               168.63.129.16/32 169.254.169.254/32
mariadb-columnstore-firstboot  168.63.129.16/32 169.254.169.254/32
mariadb-columnstore            168.63.129.16/32 169.254.169.254/32
mcs-workernode@1               168.63.129.16/32 169.254.169.254/32
mcs-controllernode             168.63.129.16/32 169.254.169.254/32
mcs-primproc                   168.63.129.16/32 169.254.169.254/32
mcs-writeengineserver          168.63.129.16/32 169.254.169.254/32
mcs-dmlproc                    168.63.129.16/32 169.254.169.254/32
mcs-ddlproc                    168.63.129.16/32 169.254.169.254/32
mcs-loadbrm                    168.63.129.16/32 169.254.169.254/32
mcs-storagemanager             168.63.129.16/32 169.254.169.254/32

ColumnStore's data, logs and temporary files are not world writable, and its data directory is closed to other users:

sudo /usr/local/sbin/cloudimg-columnstore-perms --check
stat -c '%a %U:%G %n' /var/lib/columnstore /etc/columnstore/Columnstore.xml /var/log/mariadb/columnstore /dev/shm

cloudimg-columnstore-perms: OK
750 mysql:mysql /var/lib/columnstore
640 mysql:mysql /etc/columnstore/Columnstore.xml
775 mysql:syslog /var/log/mariadb/columnstore
1777 root:root /dev/shm

The image's own self test signs in as dbadmin, round trips a ColumnStore table over verified TLS, and proves the refusals:

sudo bash /usr/local/sbin/mariadb-cred-roundtrip.sh

OK dbadmin signs in over verified TLS and round-trips a ColumnStore table (ColumnStore 25.10.6); wrong password, plaintext TCP and root over TCP are refused; root works only via the unix socket

The packages come from the signed MariaDB 11.8 repository:

cat /etc/cloudimg/mariadb-columnstore-release.txt
apt-cache policy mariadb-plugin-columnstore | sed -n '1,3p'

cloudimg: MariaDB Server 1:11.8.9+maria~ubu2404 with the ColumnStore engine 1:11.8.9-25.10.6+maria~ubu2404 (both GPL-2.0-only) from the official MariaDB 11.8 repository (dlm.mariadb.com), signing key 177F4010FE56CA3336300305F1656F24C74CD1D8.
mariadb-plugin-columnstore:
  Installed: 1:11.8.9-25.10.6+maria~ubu2404
  Candidate: 1:11.8.9-25.10.6+maria~ubu2404

Step 13: Changing the administrator password

Change it for each of the three dbadmin host entries (localhost, 127.0.0.1, ::1), then update ~/.mariadb-dbadmin.cnf and update or delete MARIADB_ADMIN_PASSWORD in /root/mariadb-columnstore-credentials.txt:

sudo mariadb <<'SQL'
ALTER USER 'dbadmin'@'localhost' IDENTIFIED BY '<new-password>';
ALTER USER 'dbadmin'@'127.0.0.1' IDENTIFIED BY '<new-password>';
ALTER USER 'dbadmin'@'::1' IDENTIFIED BY '<new-password>';
SQL

Step 14: Backup, updates and maintenance

ColumnStore data lives in /var/lib/columnstore; MariaDB's own system tables, any InnoDB tables and the table definitions are in /var/lib/mysql. Mariabackup does not copy ColumnStore data. For a consistent backup, stop the engine and MariaDB, snapshot the VM's disks (for example with Azure Backup or az snapshot create), and start them again:

sudo systemctl stop mariadb-columnstore mariadb
az snapshot create --resource-group <your-resource-group> --name columnstore-backup --source <os-disk-id>
sudo systemctl start mariadb mariadb-columnstore

To export a table while everything runs, a logical dump works for small and medium tables:

sudo mariadb-dump --single-transaction analytics | gzip > <backup-dir>/analytics-$(date +%F).sql.gz

Ubuntu security updates install automatically. MariaDB Server and the ColumnStore engine come from the MariaDB 11.8 repository as a matched pair, so upgrade them together when you choose (a database upgrade restarts the server and the engine):

sudo apt-get update && sudo apt-get install --only-upgrade mariadb-server mariadb-client mariadb-backup mariadb-plugin-columnstore
sudo /usr/local/sbin/cloudimg-columnstore-perms

ColumnStore's own logs are in /var/log/mariadb/columnstore/ (err.log, warning.log, info.log, debug.log, and crit.log for the highest severity).

Component Path
ColumnStore data directory /var/lib/columnstore
MariaDB data directory /var/lib/mysql
ColumnStore settings /etc/columnstore/Columnstore.xml (mcsGetConfig, mcsSetConfig)
MariaDB settings /etc/mysql/mariadb.conf.d/99-cloudimg.cnf
Certificate and key /etc/mysql/ssl/server-cert.pem, /etc/mysql/ssl/server-key.pem
Certificate helper /usr/local/sbin/mariadb-tls-cert
ColumnStore permission helper /usr/local/sbin/cloudimg-columnstore-perms
First boot services mariadb-tls-init.service, mariadb-columnstore-firstboot.service
ColumnStore services mariadb-columnstore.service and the mcs-* units
ColumnStore logs /var/log/mariadb/columnstore/
Credentials /root/mariadb-columnstore-credentials.txt (mode 0600)
Repository and pin /etc/apt/sources.list.d/mariadb.sources, /etc/apt/preferences.d/cloudimg-mariadb.pref

Step 15: Security recommendations

  • Allow port 22 from your management network only. Keep 3306 closed, or open it to your application subnet only (Step 9); never to the internet, and never open the ColumnStore ports.
  • Create a MariaDB user per application with rights on its own database only, and keep dbadmin for administration.
  • Give clients the VM's certificate as their CA file and turn on server certificate verification, or issue a certificate from your own CA (Step 10).
  • Change the administrator password if it may have been shared (Step 13), upgrade MariaDB and ColumnStore together, and back up regularly.

Troubleshooting

ERROR 1815 (HY000): Internal error: System is not ready yet: the engine services are still starting (right after boot), or one has stopped. Check them, and restart the engine if one is not active:

systemctl is-active mcs-primproc mcs-controllernode mcs-dmlproc mcs-ddlproc
sudo systemctl restart mariadb-columnstore

Lost connection to DDLProc: the DDL service exited, most often because the join memory budget was set too low for the engine's own catalog queries. Raise HashJoin TotalUmMemory (Step 11), then restart the engine.

A query fails with MCS-2001 or ERR_AGGREGATION_TOO_BIG: the query needs more memory than the budget allows. Raise the budget or use a larger VM.

Too many keys specified; max 0 keys allowed: the CREATE TABLE statement declares an index or primary key. Remove it; ColumnStore does not use indexes.

Support and licensing

MariaDB Server and MariaDB ColumnStore are open source under the GNU General Public License version 2; there is no licence fee. The cloudimg charge covers packaging, hardening, first boot setup, security patching, image maintenance and support.

cloudimg provides 24/7 support with a guaranteed 24 hour response SLA for all Azure Marketplace images.

  • Email: support@cloudimg.co.uk
  • Website: https://www.cloudimg.co.uk

Upstream MariaDB ColumnStore documentation is at mariadb.com/docs.