MatrixOne HTAP Database on AWS User Guide

Overview

MatrixOne is an open source hyperconverged database that serves transactional and analytical workloads from a single engine. The same system that records rows can also answer aggregate queries over them, so a typical HTAP deployment needs neither a separate warehouse nor a pipeline to copy data between the two. MatrixOne speaks the MySQL network protocol, which means existing MySQL clients, drivers, BI tools and application code connect to it unchanged and familiar SQL works as written.

Alongside ordinary relational tables it provides native vector columns with distance functions for similarity search, and fulltext indexing with MATCH ... AGAINST queries for keyword search. One database can therefore back semantic retrieval, keyword lookup and conventional reporting at the same time.

The cloudimg image installs MatrixOne 4.1.2 from the official upstream release archive, SHA-256 verified during the build, and runs it as a ready to use standalone single node deployment: one mo-service process that hosts the Log Service, the Transaction Node and one Compute Node together, started by systemd and restarted on failure. Backed by 24/7 cloudimg support.

What is included:

• MatrixOne 4.1.2 (Apache-2.0), installed from the official upstream release archive and SHA-256 verified
• A standalone single node deployment run by systemd, listening on the MySQL protocol port 6001
• Native vector columns (VECF32) with distance functions, and fulltext indexing with MATCH ... AGAINST
• A unique per instance database password generated on first boot, with no upstream default credential ever present
• Database storage on a dedicated 40 GiB EBS volume at /var/lib/matrixone
• Memory limits that adapt automatically to the size of the instance you run
• 24/7 cloudimg support

This is a database product. MatrixOne serves the MySQL protocol on port 6001, but the security group opens port 22 only. The database port is not exposed to the internet by default. Reach it over an SSH tunnel, or open it in your own security group for trusted source ranges.

A note on default credentials

Upstream MatrixOne bootstraps two built in accounts, root and dump, both using a well known published password. This image never ships that credential. The database store is empty in the image, and on first boot the catalogue is created with a password generated uniquely for your instance, so the published default never exists on your instance. First boot deliberately fails rather than completing if either account would still accept the upstream default.

Connecting to your instance

Connect over SSH on port 22 as the default login user for your operating system variant. MatrixOne itself is administered over the MySQL protocol as root with the per instance password (below), not over SSH.

Prerequisites

An AWS account, an EC2 key pair in the target region, and a VPC with a subnet. m5.large (2 vCPU / 8 GiB RAM) is the recommended starting size; provision a larger memory optimized instance for heavier analytical workloads. Security group inbound: allow 22/tcp from your management network only. No inbound application ports are needed because the database is reached over an SSH tunnel.

Have a MySQL command line client on the machine you connect from. MatrixOne implements the mysql_native_password authentication plugin, so use a MySQL 8.0.30+ client or a MariaDB client. MySQL 9.0 and later clients removed that plugin and cannot connect. On macOS in particular, prefer mariadb-client over a 9.x mysql-client.

Step 1 - Launch from the AWS Marketplace

Find the MatrixOne listing by cloudimg in the AWS Marketplace and choose Continue to Subscribe, then Continue to Configuration and Continue to Launch. Select your region, the m5.large instance type (or larger), your VPC and subnet, and a security group that allows SSH (22) from your management network only. Choose your EC2 key pair and launch.

First boot initialisation generates your per instance database password, starts MatrixOne and bootstraps the catalogue with that password. Allow a minute or so after the instance is running before the database accepts queries.

Step 2 - Launch from the AWS CLI

aws ec2 run-instances \
  --image-id <matrixone-ami-id> \
  --instance-type m5.large \
  --key-name <your-key-pair> \
  --security-group-ids <sg-with-ssh-22> \
  --subnet-id <your-subnet> \
  --region <your-region> \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=matrixone}]'

The AMI id is shown on the Marketplace launch page for your chosen region.

Step 3 - Connect to your instance

ssh ubuntu@<public-ip>

A welcome banner prints the most useful commands. MatrixOne runs as the system service matrixone.

Step 4 - Confirm the database is running

systemctl is-active matrixone

You should see the service active:

active

The dedicated data volume holds the entire database store at /var/lib/matrixone:

findmnt -no SOURCE,TARGET,FSTYPE,SIZE /var/lib/matrixone

/dev/nvme1n1 /var/lib/matrixone ext4   39.1G

MatrixOne is listening for the MySQL protocol on port 6001, and exposes a Prometheus metrics endpoint on port 7001:

ss -tlnH '( sport = :6001 or sport = :7001 )'

LISTEN 0      4096   *:6001 *:*
LISTEN 0      4096   *:7001 *:*

Step 5 - Retrieve your per instance database password

Each instance generates its own unique database password on first boot and writes it to a root only credentials file:

sudo cat /root/matrixone-credentials.txt

The file lists the host and port, the root user and its generated password, and the dump user and its own separate generated password. It is mode 0600 and owned by root. Store the passwords in your secrets manager. In the commands below, <MATRIXONE_ROOT_PASSWORD> stands for the value of matrixone.root.password.

Step 6 - Connect over the MySQL protocol

MatrixOne speaks the MySQL network protocol, so any compatible MySQL client connects unchanged. Connect on port 6001 as root with the per instance password and check the version:

mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -t -e "SELECT VERSION();"

+-------------------------+
| VERSION()               |
+-------------------------+
| 8.0.30-MatrixOne-v4.1.2 |
+-------------------------+

The version string reports MySQL 8.0.30 compatibility followed by the MatrixOne release, which is how MySQL tooling recognises the server. A wrong password is rejected:

ERROR 1045 (28000): Access denied for user root. internal error: check password failed

Step 7 - Run SQL

MatrixOne uses standard SQL over the MySQL protocol. Create a database and a table, insert rows, and run an aggregation:

mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -e "CREATE DATABASE IF NOT EXISTS demo;"
mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -e "USE demo; CREATE TABLE IF NOT EXISTS events (id INT PRIMARY KEY, kind VARCHAR(32), ts DATETIME);"
mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -e "USE demo; INSERT INTO events VALUES (1,'login',NOW()),(2,'click',NOW()),(3,'click',NOW());"
mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -t -e "SELECT kind, COUNT(*) AS n FROM demo.events GROUP BY kind ORDER BY kind;"

+-------+------+
| kind  | n    |
+-------+------+
| click |    2 |
| login |    1 |
+-------+------+

The rows were written and aggregated by the same engine, which is what the hyperconverged design is for: no extract step and no second system to keep in sync.

Step 8 - Vector similarity search

MatrixOne provides native vector columns and distance functions, so similarity search runs inside the database next to your relational data. Create a table with a VECF32 column, insert embeddings, and order by distance to find nearest neighbours:

mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -e "USE demo; CREATE TABLE IF NOT EXISTS items (id INT PRIMARY KEY, name VARCHAR(32), embedding VECF32(3));"
mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -e "USE demo; INSERT INTO items VALUES (1,'apple','[1,2,3]'),(2,'banana','[10,20,30]'),(3,'apricot','[1,2,4]');"
mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -t -e "SELECT name, l2_distance(embedding, '[1,2,3]') AS distance FROM demo.items ORDER BY distance LIMIT 3;"

+---------+------------------+
| name    | distance         |
+---------+------------------+
| apple   |                0 |
| apricot |                1 |
| banana  | 33.6749153137207 |
+---------+------------------+

The query vector matches apple exactly (distance 0), ranks the near neighbour apricot next, and places the distant banana last. In a real application these vectors would be embeddings from a model, and this is the retrieval step behind semantic search and recommendation features. l2_distance computes Euclidean distance; cosine and inner product functions are also available.

Step 9 - Fulltext search

MatrixOne also supports fulltext indexes with MySQL compatible MATCH ... AGAINST syntax, so keyword search lives in the same database as the rows it searches:

mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -e "USE demo; CREATE TABLE IF NOT EXISTS notes (id INT PRIMARY KEY, body TEXT, FULLTEXT(body));"
mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -e "USE demo; INSERT INTO notes VALUES (1,'matrixone unifies transactional and analytical work'),(2,'a separate warehouse needs a copy pipeline');"
mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -t -e "SELECT id, body FROM demo.notes WHERE MATCH(body) AGAINST('analytical');"

+------+-----------------------------------------------------+
| id   | body                                                |
+------+-----------------------------------------------------+
|    1 | matrixone unifies transactional and analytical work |
+------+-----------------------------------------------------+

Only the row containing the term is returned. Clean up the demo objects when you are finished:

mysql -h 127.0.0.1 -P 6001 -uroot -p<MATRIXONE_ROOT_PASSWORD> -e "DROP DATABASE demo;"

Step 10 - Connect remotely over an SSH tunnel

Port 6001 is bound on the instance but not exposed publicly, which is the secure default. To reach the database from your workstation, open an SSH tunnel and point a local MySQL client at localhost:6001:

# On your workstation:
ssh -L 6001:127.0.0.1:6001 ubuntu@<public-ip>

# In another terminal, with a local mysql client:
mysql -h 127.0.0.1 -P 6001 -uroot -p

For BI tools and applications, point any MySQL driver at the tunnelled localhost:6001. Note that MatrixOne does not advertise TLS by default, so a client configured with --ssl-mode=REQUIRED will fail; the SSH tunnel is what encrypts the connection in this configuration. For production remote access without a tunnel, open port 6001 in your security group only for trusted source ranges, or place the database behind a private subnet. Never expose the database port to the open internet.

Maintenance

• Persistence: the entire database store lives on the dedicated EBS data volume at /var/lib/matrixone, so your data survives reboots and the volume is independently resizable and snapshot-able.
• Password: rotate a password from a mysql session with ALTER USER 'root' IDENTIFIED BY '<new-password>';. MatrixOne accepts SET PASSWORD syntactically but does not apply it, so always use ALTER USER.
• Service control: sudo systemctl status matrixone, sudo systemctl restart matrixone. Logs are available with sudo journalctl -u matrixone.
• Tuning: the launch configuration is /opt/matrixone/etc/launch/, with cn.toml, tn.toml and log.toml for the Compute Node, Transaction Node and Log Service. Fileservice cache sizes are capped there for a small instance; raise them on a larger instance and restart. The process memory limit is computed automatically at start from total RAM, so it scales with the instance size without editing anything.
• Metrics: MatrixOne exposes Prometheus metrics on port 7001 at /metrics, which you can scrape over the tunnel or from a private network.
• Scaling: this image runs the standalone single node configuration. MatrixOne also supports a distributed multi node cluster; contact cloudimg support for multi node topologies.
• Security patches: unattended-upgrades remains enabled so the OS continues to receive security updates automatically.

Support

cloudimg provides 24/7 expert support for this image. Contact support@cloudimg.co.uk.

MatrixOne is a trademark of Matrix Origin. MySQL is a trademark of Oracle Corporation. This image is provided by cloudimg and is not affiliated with or endorsed by Matrix Origin or Oracle Corporation.