MariaDB on FreeBSD: MySQL Fork Review

MariaDB started as a drop-in replacement for MySQL after Oracle's acquisition of Sun Microsystems raised concerns about MySQL's future as a truly open-source project. A decade later, MariaDB has diverged significantly -- it ships its own storage engines, its own optimizer, and its own replication technology while maintaining wire-level compatibility with MySQL clients. On FreeBSD, MariaDB benefits from ZFS integration for consistent snapshots, a mature threading model, and the same Unix heritage that MySQL was originally developed on. This review covers MariaDB's architecture, FreeBSD-specific installation and tuning, storage engine options, Galera Cluster for high availability, and how it compares with both MySQL and PostgreSQL for FreeBSD deployments.

What MariaDB Does

MariaDB is a relational database management system that speaks the MySQL protocol. Applications, libraries, and tools written for MySQL work with MariaDB without modification in the vast majority of cases. But MariaDB is not just "MySQL with a different name" -- it has accumulated substantial technical differences.

Key capabilities:

MySQL protocol compatibility -- any MySQL client library, ORM, or tool connects to MariaDB without changes. This includes PHP's mysqli, Python's mysql-connector, and the mysql command-line client.
Additional storage engines -- Aria (crash-safe MyISAM replacement), ColumnStore (columnar analytics), Spider (sharding), and CONNECT (heterogeneous data sources) ship with MariaDB but not MySQL.
Galera Cluster -- synchronous multi-master replication built into MariaDB Server. All nodes are writable. No separate product or license required.
Optimizer improvements -- MariaDB's query optimizer includes subquery optimizations, derived table merge, and condition pushdown that were developed independently from MySQL.
Temporal tables -- system-versioned tables that automatically maintain a history of all row changes, useful for auditing and regulatory compliance.
Sequence engine -- generates sequences of integers on-the-fly without creating physical tables.
Oracle compatibility mode -- partial PL/SQL compatibility for migrations from Oracle databases.

MariaDB is not a NoSQL database, a document store, or an analytics platform (though ColumnStore moves it in that direction). Its core strength remains OLTP workloads: web applications, CMS platforms, e-commerce, and transactional systems.

Installation on FreeBSD

MariaDB is available through both the package system and the ports tree. Multiple major versions are packaged simultaneously.

Binary Package Installation

sh
pkg install mariadb1011-server mariadb1011-client

As of early 2026, FreeBSD packages MariaDB 10.11 (LTS) and 11.x. The 10.11 LTS release is recommended for production use as it receives maintenance updates through 2028.

Enable MariaDB at boot:

sh
sysrc mysql_enable="YES"

Note that the rc.d script is named mysql for compatibility, not mariadb.

Initial Secure Configuration

After installation, run the security script:

sh
service mysql-server start
mysql_secure_installation

This script sets the root password, removes anonymous users, disables remote root login, and drops the test database. Always run it on new installations.

Ports Installation

For custom build options (specific storage engines, embedded server, debug builds):

sh
cd /usr/ports/databases/mariadb1011-server
make config
make install clean

The ports build lets you enable or disable ColumnStore, Spider, Mroonga (full-text search), OQGRAPH (graph computation), and other optional engines.

Verify Installation

sh
mysql --version
mysqladmin -u root -p status

Configuration on FreeBSD

The main configuration file is /usr/local/etc/mysql/my.cnf. MariaDB also reads files from /usr/local/etc/mysql/conf.d/ if that directory exists.

Production Configuration Template

sh
cat > /usr/local/etc/mysql/my.cnf << 'EOF'
[mysqld]
datadir = /var/db/mysql
socket = /var/run/mysql/mysql.sock
pid-file = /var/run/mysql/mysqld.pid
port = 3306
bind-address = 127.0.0.1

# InnoDB Settings
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_buffer_pool_instances = 4

# Connection Handling
max_connections = 200
thread_cache_size = 50
table_open_cache = 4000
table_definition_cache = 2000

# Query Cache (disabled in 10.11+, use ProxySQL for caching)
query_cache_type = OFF

# Logging
log_error = /var/log/mysql/error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Character Set
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

[client]
socket = /var/run/mysql/mysql.sock
default-character-set = utf8mb4
EOF

Create the log directory:

sh
mkdir -p /var/log/mysql
chown mysql:mysql /var/log/mysql

InnoDB Buffer Pool Sizing

The single most impactful tuning parameter is innodb_buffer_pool_size. Set it to 60-70% of available RAM on a dedicated database server. On a server with 8 GB RAM:

sh
innodb_buffer_pool_size = 5G

On a shared server running other services, reduce this to 25-40% of total RAM. Monitor the buffer pool hit rate:

sh
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

A hit rate below 99% means the buffer pool is too small for your working set.

Storage Engines

MariaDB's multi-engine architecture lets you choose the right storage engine for each table. This is a significant architectural advantage over PostgreSQL, which uses a single storage engine.

InnoDB

The default and recommended engine for most workloads. InnoDB provides ACID transactions, row-level locking, foreign keys, and crash recovery. MariaDB 10.11 ships a maintained fork of InnoDB that diverges from Oracle's version.

sh
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB;

Aria

Aria is MariaDB's crash-safe replacement for MyISAM. It is used internally for temporary tables and is suitable for read-heavy workloads where transactions are not required.

sh
CREATE TABLE sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    data BLOB,
    expires_at INT
) ENGINE=Aria TRANSACTIONAL=1;

ColumnStore

ColumnStore is a columnar storage engine designed for analytical queries over large datasets. It excels at full-table scans, aggregations, and joins across billions of rows.

sh
pkg install mariadb-columnstore-engine

ColumnStore is appropriate for data warehousing and reporting but not for OLTP workloads. It does not support individual row updates efficiently.

MyRocks

MyRocks uses Facebook's RocksDB as a storage backend. It provides better write amplification and compression ratios than InnoDB, making it suitable for write-heavy workloads on SSD storage.

Galera Cluster

Galera Cluster is MariaDB's synchronous multi-master replication solution. Every node in the cluster accepts reads and writes, and transactions are certified across all nodes before being committed. This provides:

Synchronous replication -- no replication lag. When a commit returns success, the data is on all nodes.
Multi-master -- any node can accept writes. No failover required when a node goes down.
Automatic node provisioning -- new nodes joining the cluster receive a full state transfer automatically.

Galera Setup on FreeBSD

Install Galera support:

sh
pkg install galera26

Configure the first node in /usr/local/etc/mysql/conf.d/galera.cnf:

sh
cat > /usr/local/etc/mysql/conf.d/galera.cnf << 'EOF'
[mysqld]
binlog_format = ROW
innodb_autoinc_lock_mode = 2
innodb_flush_log_at_trx_commit = 2

# Galera Configuration
wsrep_on = ON
wsrep_provider = /usr/local/lib/libgalera_smm.so
wsrep_cluster_name = "freebsd_cluster"
wsrep_cluster_address = "gcomm://node1_ip,node2_ip,node3_ip"
wsrep_node_name = "node1"
wsrep_node_address = "node1_ip"
wsrep_sst_method = mariabackup
wsrep_sst_auth = "sst_user:sst_password"
EOF

Bootstrap the first node:

sh
mysqld_safe --wsrep-new-cluster &

Start subsequent nodes normally with service mysql-server start. They will discover the cluster via the wsrep_cluster_address and perform an automatic state transfer.

Monitoring Galera Status

sh
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_%';"

Key metrics to monitor:

wsrep_cluster_size -- number of nodes in the cluster.
wsrep_local_state_comment -- should be "Synced" on all nodes.
wsrep_flow_control_paused -- fraction of time the node was paused due to flow control. Should be near 0.
wsrep_local_recv_queue_avg -- average receive queue length. Values above 0.5 indicate the node cannot apply writesets fast enough.

FreeBSD-Specific Tuning

ZFS Integration

FreeBSD's ZFS provides snapshots that are invaluable for database backups. Take a consistent snapshot of the MariaDB data directory:

sh
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"
zfs snapshot zroot/var/db/mysql@backup-$(date +%Y%m%d)
mysql -u root -p -e "UNLOCK TABLES;"

For InnoDB-only workloads, you can skip the table lock if you use innodb_flush_log_at_trx_commit = 1 -- ZFS atomic snapshots combined with InnoDB's crash recovery produce consistent backups.

Set ZFS recordsize to match InnoDB's page size:

sh
zfs set recordsize=16k zroot/var/db/mysql

Disable ZFS compression for the data directory if the data is already encrypted or compressed at the application level:

sh
zfs set compression=off zroot/var/db/mysql

For unencrypted data, lz4 compression can reduce disk usage by 30-50% with negligible CPU overhead.

Kernel Tuning

Increase shared memory and semaphore limits for large buffer pools:

sh
sysctl kern.ipc.shmmax=4294967296
sysctl kern.ipc.shmall=1048576

Make these persistent in /etc/sysctl.conf:

sh
echo 'kern.ipc.shmmax=4294967296' >> /etc/sysctl.conf
echo 'kern.ipc.shmall=1048576' >> /etc/sysctl.conf

Increase the maximum open file descriptors for the mysql user by editing /etc/login.conf and rebuilding:

sh
cap_mkdb /etc/login.conf

MariaDB vs MySQL on FreeBSD

Since MariaDB forked from MySQL 5.5, both projects have evolved independently. The differences are now substantial:

MariaDB advantages:

Built-in Galera Cluster (MySQL requires Group Replication, which has different semantics).
More storage engines (Aria, ColumnStore, Spider, MyRocks).
Oracle PL/SQL compatibility mode.
System-versioned (temporal) tables.
Community-governed development. No CLA required for contributions.
Generally faster optimizer for complex queries with subqueries and derived tables.

MySQL advantages:

Document store (X Protocol) for JSON-native access.
MySQL Shell -- an advanced administration tool with Python and JavaScript scripting.
Group Replication and InnoDB Cluster provide a tightly integrated HA solution.
MySQL HeatWave for cloud-native analytics (not relevant for FreeBSD self-hosting).
Larger commercial ecosystem with Oracle support contracts.

On FreeBSD specifically, MariaDB has better pkg support with multiple concurrent versions available. MySQL packages are less consistently maintained on FreeBSD.

MariaDB vs PostgreSQL

This comparison comes up frequently for FreeBSD users choosing a database:

PostgreSQL has richer SQL compliance, better JSON support, more advanced indexing (GIN, GiST, BRIN), and a more sophisticated query planner. It is the better choice for complex queries, geospatial data (PostGIS), and applications that need advanced SQL features.
MariaDB is faster for simple key-value lookups, has lower connection overhead, and is the default database for WordPress, Drupal, Magento, and most PHP applications. If your application was built for MySQL, use MariaDB.

The practical answer: if your application documentation says "requires MySQL," use MariaDB. If you are starting a new project and have database choice freedom, evaluate PostgreSQL first.

Backup Strategies

mariabackup (Physical Backup)

sh
pkg install mariadb-backup
mariabackup --backup --target-dir=/backup/mariadb/full --user=root --password=yourpass
mariabackup --prepare --target-dir=/backup/mariadb/full

Physical backups are faster than mysqldump for databases over 10 GB and support incremental backups.

mysqldump (Logical Backup)

sh
mysqldump --all-databases --single-transaction --routines --triggers --events -u root -p > /backup/mariadb/all-databases.sql

The --single-transaction flag ensures a consistent snapshot for InnoDB tables without locking.

ZFS Snapshots

As discussed above, ZFS snapshots provide the fastest backup method on FreeBSD:

sh
zfs snapshot zroot/var/db/mysql@daily-$(date +%Y%m%d)
zfs send zroot/var/db/mysql@daily-$(date +%Y%m%d) | gzip > /backup/mysql-snapshot.zfs.gz

Monitoring

Key metrics to monitor in production:

sh
mysql -u root -p -e "
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
"

For continuous monitoring, deploy Prometheus with the mysqld_exporter:

sh
pkg install mysqld_exporter
sysrc mysqld_exporter_enable="YES"
sysrc mysqld_exporter_args='--config.my-cnf=/usr/local/etc/mysql/.my.cnf'
service mysqld_exporter start

Verdict

MariaDB is the right choice for FreeBSD deployments that need MySQL protocol compatibility. It is the database behind the majority of the world's PHP applications, and its Galera Cluster provides genuine multi-master high availability without commercial licensing. On FreeBSD, the combination of ZFS snapshots for backups, kernel-level performance tuning, and rock-solid package support makes MariaDB a strong production choice.

For new projects without MySQL compatibility requirements, evaluate PostgreSQL. For existing MySQL-based applications, MariaDB is the natural fit on FreeBSD -- it offers more features, better community governance, and equivalent or better performance than Oracle's MySQL.

Frequently Asked Questions

Can I migrate from MySQL to MariaDB on FreeBSD?

Yes. For MySQL 5.7 and earlier, MariaDB is a drop-in replacement. Stop MySQL, install MariaDB, and start it against the same data directory. For MySQL 8.0+, a mysqldump and reimport is safer due to data dictionary format changes.

Which MariaDB version should I use on FreeBSD?

Use the latest LTS release (10.11 as of 2026). LTS releases receive security and bug fixes for 5 years. Short-term releases (11.x) are suitable for testing new features but have shorter support windows.

Does Galera Cluster work in FreeBSD jails?

Yes, but each jail needs its own network address and the ability to communicate with other nodes on ports 3306, 4567, 4568, and 4444. VNET jails simplify this configuration.

How do I convert MyISAM tables to InnoDB?

Run ALTER TABLE tablename ENGINE=InnoDB; for each table. For bulk conversion, use a script that queries information_schema.TABLES and generates ALTER statements. Test on a replica first -- some MyISAM-specific features (full-text indexes with custom parsers) may require adjustments.

Is MariaDB MaxScale needed for Galera Cluster?

MaxScale is a database proxy that handles read/write splitting, query routing, and connection pooling. It is not required for Galera but improves application-level transparency. ProxySQL is a popular open-source alternative that also works with MariaDB on FreeBSD.

How do I enable TLS for client connections?

Generate certificates, then add to my.cnf:

sh
[mysqld]
ssl_cert = /usr/local/etc/mysql/ssl/server-cert.pem
ssl_key = /usr/local/etc/mysql/ssl/server-key.pem
ssl_ca = /usr/local/etc/mysql/ssl/ca-cert.pem
require_secure_transport = ON

Restart MariaDB and verify with SHOW VARIABLES LIKE '%ssl%';.

MariaDB on FreeBSD: MySQL Fork Review