MariaDB ColumnStore 25.10.6

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 choose your Region. Then choose Launch through EC2.

Choose m5.large (or larger), your key pair and a security group as described in Prerequisites. The AMI has two volumes: the 8 GiB root volume and a 20 GiB data volume (/dev/sdf) for /var/lib/columnstore. At launch, make the data volume as large as your analytics data needs.

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

Connect over SSH. For MariaDB ColumnStore 25.10.6 on Ubuntu 24.04, the SSH user is ubuntu.

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

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

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

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

Read the credentials file as root:

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

Only root can read the file:

# 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://secureanycloud.com/stacks/mariadb-columnstore-25-10-6/

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 that the password never appears on a command line. This command writes ~/.mariadb-dbadmin.cnf, which points the client at the instance'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/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

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 it into a bulk load). It 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 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

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, 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 to verify the server too.

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 that 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 and Tableau offer the same SSH tunnel and "require SSL" settings.

To let application servers in your VPC connect directly to MariaDB, change the listening address and open 3306 to your application subnet only. 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 instance's security group. Never open 3306 to the internet. Never open the ColumnStore ports (8600 to 8800) either, because 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 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 a CA issued 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 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%. On a small instance, do not set join memory much lower than this, because 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, instead of the stock root with an empty password. To turn it 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

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-sac-imds-deny.conf and run sudo systemctl daemon-reload.

MariaDB runs with the SecureAnyCloud settings: loopback only, TLS required, LOAD DATA LOCAL and the binary log off, and 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. Only MariaDB can read the key:

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 = sac 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/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, 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/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 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, 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).

These are the key components and their paths:

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