Databases AWS

MariaDB ColumnStore 25.10.6 on AWS User Guide

Last updated: 2026-10-22 | Product: MariaDB ColumnStore 25.10.6 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and using the MariaDB ColumnStore 25.10.6 on Ubuntu 24.04 AMI from cloudimg on Amazon EC2. It is one version of the cloudimg MySQL, MariaDB and Percona Server listing on AWS Marketplace; you pick it as the software version when you subscribe and launch.

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. 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.

What the cloudimg image adds:

No shared password, anywhere. The image ships freshly initialised data stores with no password account at all. First boot creates dbadmin, a full MariaDB administrator, with a password unique to your instance, and writes it to /root/mariadb-columnstore-credentials.txt (root only).
Root only from the instance. 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 instance 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) 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.
Your analytics data on its own disk. The ColumnStore data directory /var/lib/columnstore is a separate 20 GiB EBS volume you can grow or snapshot on its own.
A memory budget that fits the instance. ColumnStore's block cache and join memory are set as a share of the instance's memory (20% and 50%), below the 75% ceiling MariaDB documents, so the same image works from 4 GiB up.
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.
Hardened defaults. MariaDB, every ColumnStore service and the first boot services are blocked from the instance metadata service, and the AMI requires IMDSv2.
Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on. The firmware update timer (fwupd-refresh) is masked, because EC2 instances 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, Ubuntu 24.04 LTS, cloud-init and the AWS Systems Manager agent.

Default user: ubuntu · Architecture: x86_64 · Recommended instance type: m5.large

Prerequisites

Memory: at least 4 GiB. ColumnStore refuses to start on an instance with less than 3 GB of memory, so the smallest supported size is a 4 GiB instance such as t3.medium. m5.large (2 vCPU, 8 GiB) is the recommended starting point; for production analytics MariaDB suggests far more (for example 8 vCPU and 32 GiB for development, and larger memory optimised instances such as the r6i family for production data sets), because ColumnStore uses memory for its block cache and joins.
An AWS account subscribed to the listing, an EC2 key pair and a VPC subnet with a route to the internet (or a private route you use for management).
A security group that allows TCP 22 (SSH) from your admin network. MariaDB and ColumnStore listen on the loopback address only, so port 3306 stays closed unless you choose to open it to your application subnet (Step 9).

Step 1: Launch the instance

In the AWS Marketplace listing choose Continue to Subscribe, then Continue to Configuration, pick MariaDB ColumnStore 25.10.6 on Ubuntu 24.04 as the software version and your Region, then Launch through EC2. Choose m5.large (or larger), your key pair and a security group as above. The AMI brings two volumes: the 8 GiB root volume and a 20 GiB data volume (/dev/sdf) for /var/lib/columnstore. Make the data volume as large as your analytics data needs at launch.

With the AWS CLI (replace the values in angle brackets; this example grows the data volume to 200 GiB):

aws ec2 run-instances --region <region> --image-id <ami-id-from-the-listing> \
  --instance-type m5.large --key-name <your-key-pair> \
  --security-group-ids <your-security-group> --subnet-id <your-subnet> \
  --block-device-mappings 'DeviceName=/dev/sdf,Ebs={VolumeSize=200,VolumeType=gp3}' \
  --metadata-options HttpTokens=required,HttpEndpoint=enabled \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=mariadb-columnstore}]'

If you make the data volume larger at launch, grow the file system once after first boot with sudo resize2fs "$(findmnt -no SOURCE /var/lib/columnstore)". First boot takes about a minute after the instance is running.

Step 2: Connect to your instance

Version	SSH user
MariaDB ColumnStore 25.10.6 on Ubuntu 24.04	`ubuntu`

ssh -i <your-key.pem> ubuntu@<public-ip>

Step 3: Check the services

First boot creates the certificate, starts MariaDB, creates the administrator account and starts the ColumnStore engine:

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"))

The ColumnStore data directory is the separate data volume:

findmnt -no SOURCE,FSTYPE /var/lib/columnstore
df -h --output=size,used,avail,target /var/lib/columnstore

/dev/nvme1n1 ext4
 Size  Used Avail Mounted on
  20G   63M   19G /var/lib/columnstore

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 instance; 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_TLS_CA=/etc/mysql/ssl/server-cert.pem

# root has no password: on the instance 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>
# ColumnStore data (/var/lib/columnstore) is its own EBS volume.
# User guide: https://www.cloudimg.co.uk/guides/mariadb-columnstore-25-10-6-on-ubuntu-24-04-aws/

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

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 instance'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/ubuntu/.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); nothing from the build is left behind:

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

Database
calpontsys
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

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

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

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 instance 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 own computer

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

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

Graphical and BI tools such as DBeaver, Metabase, Superset or Tableau offer the same SSH tunnel and "require SSL" settings.

Step 9: Allow connections from your VPC

To let application servers in your VPC connect directly to MariaDB, change the listening address, open 3306 to 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 instance's 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 instance's private and public EC2 names and addresses. 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 instance's memory and join memory to 50%, so they follow the instance 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 instance: ColumnStore's own system catalog queries need it.

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

ColumnStore can also keep its data in Amazon S3 through its StorageManager (/etc/columnstore/storagemanager.cnf). Every ColumnStore service is blocked from the instance metadata service, so S3 storage needs an access key in that file; to use an instance role instead, remove /etc/systemd/system/mcs-storagemanager.service.d/50-cloudimg-imds-deny.conf and run sudo systemctl daemon-reload.

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 instance and names its own addresses; the key is readable by MariaDB only:

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

subject=CN = ip-<private-ip-dashed>.ec2.internal, O = cloudimg MariaDB
X509v3 Subject Alternative Name:
    DNS:ip-<private-ip-dashed>.ec2.internal, IP Address:<private-ip>, DNS:ec2-<public-ip-dashed>.compute-1.amazonaws.com, 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 instance metadata service:

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 '%-24s %s\n' "$u" "$(systemctl show -p IPAddressDeny --value "$u.service")"; done

mariadb                  fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mariadb-tls-init         fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mariadb-columnstore-firstboot        fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mariadb-columnstore      fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mcs-workernode@1         fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mcs-controllernode       fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mcs-primproc             fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mcs-writeengineserver    fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mcs-dmlproc              fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mcs-ddlproc              fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mcs-loadbrm              fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
mcs-storagemanager       fd00:ec2::254/128 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 on the data volume; MariaDB's own system tables, any InnoDB tables and the table definitions are in /var/lib/mysql on the root volume. Mariabackup does not copy ColumnStore data. For a consistent backup, stop the engine and MariaDB, snapshot both volumes, and start them again:

sudo systemctl stop mariadb-columnstore mariadb
aws ec2 create-snapshots --instance-specification InstanceId=<instance-id> --description "columnstore backup"
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).

Component	Path
ColumnStore data directory	`/var/lib/columnstore` (separate EBS volume)
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 admin 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 instance'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 snapshot the data volume regularly.

Support and licensing

MariaDB Server and MariaDB ColumnStore are open source under the GNU General Public License version 2; there is no per user or per CPU licence fee. The AMI price covers cloudimg's hardening, first boot setup and support.

Email: support@cloudimg.co.uk
Website: https://www.cloudimg.co.uk
Support: 24/7, with a 24 hour response SLA