MariaDB ColumnStore

In the Azure Portal, go to Marketplace, search for MariaDB ColumnStore, and select the SecureAnyCloud publisher entry. Choose 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).

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

To deploy from the Azure CLI instead, replace the values in angle brackets with your own and run the following.

az vm image terms accept --urn sac1647283583153:mariadb-columnstore-ubuntu-24-04:default:latest
az vm create \
  --resource-group <your-resource-group> \
  --name mariadb-columnstore-01 \
  --image sac1647283583153: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

Connect as the administrator user you chose at deployment. This guide uses azureuser.

ssh azureuser@<public-ip>

First boot mints the certificate, initialises this VM's own data stores, starts MariaDB and the ColumnStore engine, and creates the administrator account. Check that every service is running.

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/sac/data/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"))

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

Read the generated administrator password from the credentials file.

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=sac 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://secureanycloud.com/stacks/mariadb-columnstore/

As the operating system's root user, you sign in to MariaDB's root account 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

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. The following writes ~/.mariadb-dbadmin.cnf, which points the client at the VM's own certificate and verifies 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 and the empty SecureAnyCloud database. The databases calpontsys, columnstore_info, infinidb_querystats and queryacc belong to ColumnStore. Nothing from the build is left behind.

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

Database calpontsys sac columnstore_info infinidb_querystats information_schema mysql performance_schema queryacc sys

Create tables with ENGINE=ColumnStore. This example creates a sales table and loads 100,000 generated rows with one INSERT ... SELECT from MariaDB's built-in number sequence. ColumnStore turns that statement into a bulk load. The example then 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

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.

Give each application its own database and an account with rights on that database only. Run the following 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, or to the local socket /run/mysqld/mysqld.sock. 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 so they verify the server too.

MariaDB is not exposed to the network. From your workstation, copy the VM's certificate so your client can verify the server. Then 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 offer the same SSH tunnel and "require SSL" settings. Examples are Metabase, Superset, Tableau, Power BI through the MySQL connector, and DBeaver.

To let application servers in your virtual network connect directly to MariaDB, change the listening address and allow 3306 in the network security group from your application subnet only. Then 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-sac.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. Never open the ColumnStore ports (8600 to 8800) either. They carry an unauthenticated internal protocol and stay on 127.0.0.1 for a single node.

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 a CA issues your certificate, point ssl_ca in /etc/mysql/mariadb.conf.d/99-sac.cnf at your CA bundle. Then run sudo mariadb -e "FLUSH SSL;".

ColumnStore's settings live in /etc/columnstore/Columnstore.xml. Read and change them 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%. Do not set join memory much lower than this on a small VM, because 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 join a ColumnStore table with an InnoDB table in one query. They 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 cross-engine joins on, create a least-privilege account that requires TLS and set it. Keep 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

MariaDB runs with the SecureAnyCloud settings. It listens on loopback only, requires TLS, has LOAD DATA LOCAL and the binary log off, and confines file access. Verify these settings.

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=''

MariaDB refuses a plain-text connection. 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/sac-columnstore-perms --check stat -c '%a %U:%G %n' /var/lib/columnstore /etc/columnstore/Columnstore.xml /var/log/mariadb/columnstore /dev/shm

sac-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 and round-trips a ColumnStore table over verified TLS. It also 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/sac/mariadb-columnstore-release.txt apt-cache policy mariadb-plugin-columnstore | sed -n '1,3p'

sac: 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

Change the password 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

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, use a logical dump. It 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/sac-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).

Use this table of paths and services as a reference.

• 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-sac.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/sac-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/sac-mariadb.pref

• Allow port 22 from your management network only. Keep 3306 closed, or open it to your application subnet only (Step 9). Never open it 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.