How to Set Up MySQL/MariaDB on FreeBSD

MySQL and MariaDB are the most widely used open-source relational databases. MariaDB is a fork of MySQL that maintains protocol compatibility while adding features and performance improvements. On FreeBSD, both are available as packages and work identically for most applications. This guide covers installation, initial security, configuration tuning, user management, backup strategies, and replication setup.

For most new deployments on FreeBSD, MariaDB is the recommended choice. It is the default MySQL-compatible database in the FreeBSD ports tree, receives more frequent updates, and includes features like Aria storage engine, thread pool, and improved optimizer that MySQL Community Edition lacks.

Prerequisites

FreeBSD 14.0 or later
Root access
At least 1 GB RAM (2+ GB recommended for production)

Step 1: Install MariaDB

Install MariaDB server and client:

sh
pkg install mariadb1011-server mariadb1011-client

Or if you specifically need MySQL:

sh
pkg install mysql84-server mysql84-client

The rest of this guide uses MariaDB commands, but the configuration and concepts apply to both. The command-line tools (mysql, mysqldump, mysqladmin) are compatible between the two.

Enable at Boot

sh
sysrc mysql_enable="YES"

Start the Service

sh
service mysql-server start

Verify it is running:

sh
service mysql-server status
mysqladmin version

Step 2: Secure the Installation

The default installation has no root password and includes test databases. Run the security script immediately:

sh
mysql_secure_installation

This interactive script will:

Set the root password
Remove anonymous users
Disable remote root login
Remove the test database
Reload privilege tables

Answer "Y" to all prompts for a secure baseline.

Verify Access

sh
mysql -u root -p

Enter the password you set. You should see the MariaDB (or MySQL) prompt:

sh
MariaDB [(none)]> SELECT VERSION();
MariaDB [(none)]> SHOW DATABASES;
MariaDB [(none)]> \q

Step 3: Configure my.cnf

The MariaDB configuration file on FreeBSD is /usr/local/etc/mysql/my.cnf. If it does not exist, create it. MariaDB also reads /usr/local/etc/mysql/conf.d/*.cnf for drop-in configurations.

Base Configuration

Create or edit /usr/local/etc/mysql/my.cnf:

sh
[mysqld]
# Networking
bind-address            = 127.0.0.1
port                    = 3306
socket                  = /var/run/mysql/mysql.sock

# Character set
character-set-server    = utf8mb4
collation-server        = utf8mb4_unicode_ci

# InnoDB settings
innodb_buffer_pool_size = 512M
innodb_log_file_size    = 128M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method     = O_DIRECT
innodb_file_per_table   = ON

# Query cache (MariaDB; deprecated in MySQL 8)
query_cache_type        = 0
query_cache_size        = 0

# 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

# Connections
max_connections         = 150
wait_timeout            = 600
interactive_timeout     = 600

# Temporary tables
tmp_table_size          = 64M
max_heap_table_size     = 64M

# Thread handling
thread_cache_size       = 16

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

Create the Log Directory

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

Apply the Configuration

sh
service mysql-server restart

Verify the settings took effect:

sh
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'character_set_server';"

Step 4: Tuning InnoDB

InnoDB is the default storage engine and the one you should use for all production tables. The key tuning parameters:

Buffer Pool Size

The buffer pool is where InnoDB caches data and indexes. Set it to 50-70% of available RAM on a dedicated database server:

sh
# For a server with 4 GB RAM
innodb_buffer_pool_size = 2G

# For a server with 16 GB RAM
innodb_buffer_pool_size = 10G

Buffer Pool Instances

For buffer pools larger than 1 GB, use multiple instances to reduce contention:

sh
innodb_buffer_pool_instances = 4

Log File Size

Larger log files improve write performance but increase crash recovery time:

sh
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M

Flush Settings

For maximum durability (ACID compliance):

sh
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

For better performance with slight risk of losing the last second of transactions on crash:

sh
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

Monitor InnoDB Performance

sh
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" | head -100

Key metrics to watch:

sh
# Check buffer pool hit ratio
mysql -u root -p -e "
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_ratio
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a, (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;
"

A hit ratio below 99% indicates the buffer pool is too small.

Step 5: User Management

Create a Database and User

sh
mysql -u root -p

sh
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'strong_password_here';

GRANT ALL PRIVILEGES ON myapp.* TO 'myapp_user'@'localhost';

FLUSH PRIVILEGES;

Create a Read-Only User

For applications that only need read access (reporting, analytics):

sh
CREATE USER 'myapp_reader'@'localhost' IDENTIFIED BY 'reader_password_here';
GRANT SELECT ON myapp.* TO 'myapp_reader'@'localhost';
FLUSH PRIVILEGES;

Allow Remote Access

By default, MariaDB listens on localhost only. To allow remote connections:

Change bind-address in my.cnf:

sh
bind-address = 0.0.0.0

Create a user with remote access:

sh
CREATE USER 'remote_user'@'10.0.0.%' IDENTIFIED BY 'password_here';
GRANT ALL PRIVILEGES ON myapp.* TO 'remote_user'@'10.0.0.%';
FLUSH PRIVILEGES;

Open the firewall port (if using ipfw or pf):

sh
# ipfw example
ipfw add allow tcp from 10.0.0.0/24 to any 3306

Restart MariaDB:

sh
service mysql-server restart

View Existing Users

sh
SELECT user, host, plugin FROM mysql.user;

Change a User Password

sh
ALTER USER 'myapp_user'@'localhost' IDENTIFIED BY 'new_password_here';
FLUSH PRIVILEGES;

Revoke Privileges

sh
REVOKE ALL PRIVILEGES ON myapp.* FROM 'myapp_user'@'localhost';
FLUSH PRIVILEGES;

Drop a User

sh
DROP USER 'myapp_user'@'localhost';

Step 6: Backup Strategies

Logical Backups with mysqldump

mysqldump produces SQL output that can recreate the database. It is the simplest backup method:

sh
# Backup a single database
mysqldump -u root -p myapp > /backup/myapp-$(date +%Y%m%d).sql

# Backup all databases
mysqldump -u root -p --all-databases > /backup/all-databases-$(date +%Y%m%d).sql

# Backup with compression
mysqldump -u root -p myapp | gzip > /backup/myapp-$(date +%Y%m%d).sql.gz

# Backup with consistent snapshot (InnoDB)
mysqldump -u root -p --single-transaction --routines --triggers myapp > /backup/myapp-consistent.sql

The --single-transaction flag is critical for InnoDB tables. It provides a consistent backup without locking tables.

Restore from mysqldump

sh
# Restore a database
mysql -u root -p myapp < /backup/myapp-20260409.sql

# Restore from compressed backup
gunzip < /backup/myapp-20260409.sql.gz | mysql -u root -p myapp

Physical Backups with mariabackup

For large databases, mariabackup (or MySQL's xtrabackup) creates physical copies of the data files. This is faster than mysqldump for databases over 10 GB:

sh
pkg install mariadb1011-server
# mariabackup is included with the server package

# Full backup
mariabackup --backup --target-dir=/backup/full \
    --user=root --password=your_password

# Prepare the backup (apply log)
mariabackup --prepare --target-dir=/backup/full

Restore from Physical Backup

sh
# Stop MariaDB
service mysql-server stop

# Remove old data (careful!)
rm -rf /var/db/mysql/*

# Copy backup files
mariabackup --copy-back --target-dir=/backup/full

# Fix permissions
chown -R mysql:mysql /var/db/mysql

# Start MariaDB
service mysql-server start

Automated Backup Script

Create /usr/local/sbin/mysql-backup.sh:

sh
#!/bin/sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d-%H%M)
RETENTION_DAYS=14

mkdir -p "${BACKUP_DIR}"

# Dump all databases
mysqldump -u root --single-transaction --routines --triggers \
    --all-databases | gzip > "${BACKUP_DIR}/all-${DATE}.sql.gz"

# Prune old backups
find "${BACKUP_DIR}" -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete

# Log completion
logger "MySQL backup completed: all-${DATE}.sql.gz"

sh
chmod +x /usr/local/sbin/mysql-backup.sh

Add to crontab:

sh
# Daily backup at 2 AM
0 2 * * * /usr/local/sbin/mysql-backup.sh

Step 7: Set Up Replication

Replication copies data from a primary (master) server to one or more replicas (slaves). This provides read scaling and disaster recovery.

Configure the Primary Server

Edit /usr/local/etc/mysql/my.cnf on the primary:

sh
[mysqld]
server-id               = 1
log_bin                 = /var/log/mysql/mariadb-bin
binlog_format           = ROW
binlog_expire_logs_days = 14
max_binlog_size         = 256M

Restart MariaDB:

sh
service mysql-server restart

Create a replication user:

sh
CREATE USER 'repl_user'@'10.0.0.%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.0.%';
FLUSH PRIVILEGES;

Get the current binary log position:

sh
SHOW MASTER STATUS;

Note the File and Position values. You will need them on the replica.

Take a Consistent Snapshot

sh
mysqldump -u root -p --all-databases --single-transaction \
    --master-data=2 > /tmp/primary-dump.sql

The --master-data=2 flag writes the binary log position as a comment in the dump file.

Transfer the dump to the replica:

sh
scp /tmp/primary-dump.sql replica-server:/tmp/

Configure the Replica Server

Edit /usr/local/etc/mysql/my.cnf on the replica:

sh
[mysqld]
server-id               = 2
relay_log               = /var/log/mysql/relay-bin
read_only               = ON
log_bin                 = /var/log/mysql/mariadb-bin
binlog_format           = ROW

Restart MariaDB on the replica:

sh
service mysql-server restart

Import the primary's data:

sh
mysql -u root -p < /tmp/primary-dump.sql

Configure replication (use the File and Position from the primary):

sh
CHANGE MASTER TO
    MASTER_HOST='10.0.0.1',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='mariadb-bin.000001',
    MASTER_LOG_POS=12345;

START SLAVE;

Verify Replication

sh
SHOW SLAVE STATUS\G

Key fields to check:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0 (or a low number)

If either thread is not running, check Last_IO_Error and Last_SQL_Error for details.

Monitor Replication Health

sh
# Quick check from shell
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "Running|Behind|Error"

GTID-Based Replication

For MariaDB, GTID (Global Transaction ID) simplifies replication management:

sh
# On the primary
[mysqld]
server-id               = 1
log_bin                 = /var/log/mysql/mariadb-bin
binlog_format           = ROW

sh
# On the replica, use GTID
CHANGE MASTER TO
    MASTER_HOST='10.0.0.1',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='replication_password',
    MASTER_USE_GTID=slave_pos;

START SLAVE;

GTID eliminates the need to track binary log file names and positions manually.

Monitoring and Maintenance

Check Database Sizes

sh
mysql -u root -p -e "
SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
"

Check Active Connections

sh
mysql -u root -p -e "SHOW PROCESSLIST;"

Check Slow Queries

sh
# View slow query log
tail -50 /var/log/mysql/slow.log

# Find queries slower than 5 seconds
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Optimize Tables

After large deletes or updates, reclaim space:

sh
mysqlcheck -u root -p --optimize myapp

Check and Repair Tables

sh
mysqlcheck -u root -p --check --all-databases
mysqlcheck -u root -p --repair --all-databases

Frequently Asked Questions

Should I use MySQL or MariaDB on FreeBSD?

MariaDB is recommended for most use cases on FreeBSD. It is the better-maintained option in the ports tree, provides more features in the community edition, and maintains full MySQL compatibility. Use MySQL if your application specifically requires MySQL-only features (e.g., MySQL Group Replication, MySQL Shell).

How do I check if MariaDB is using InnoDB?

sh
mysql -u root -p -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'myapp';"

All tables should show InnoDB as the engine.

How much RAM should I allocate to the buffer pool?

On a dedicated database server, allocate 50-70% of total RAM. On a shared server (web + database), allocate 25-40%. Start conservative and increase based on buffer pool hit ratio monitoring.

Can I run MariaDB in a FreeBSD jail?

Yes. MariaDB runs well in jails. Ensure the data directory (/var/db/mysql) is on persistent storage and that the jail has sufficient resource limits (memory, file descriptors).

How do I upgrade MariaDB to a new major version?

Back up all databases with mysqldump
Stop the current MariaDB service
Install the new version package
Start the new MariaDB
Run mysql_upgrade to update system tables

sh
mysqldump -u root -p --all-databases > /backup/pre-upgrade.sql
service mysql-server stop
pkg install mariadb1011-server
service mysql-server start
mysql_upgrade -u root -p

How do I reset the root password if I forgot it?

sh
# Stop MariaDB
service mysql-server stop

# Start with skip-grant-tables
mysqld_safe --skip-grant-tables &

# Connect and reset
mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
\q

# Restart normally
service mysql-server restart

What is the difference between ROW and STATEMENT binlog format?

ROW format logs the actual row changes. STATEMENT format logs the SQL statements. ROW is safer and recommended for replication because it avoids issues with non-deterministic functions. MIXED format uses STATEMENT when safe and ROW when needed.

How do I enable SSL connections to MariaDB?

Add to my.cnf:

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

Generate certificates using OpenSSL or Let's Encrypt, then restart MariaDB.