Install MySQL 8.4 LTS on FreeBSD 15

The FreeBSD package for MySQL 8.4 leaves root with no password at all. Not a temporary one written to the error log, which is what the Oracle packages on Linux do, and not a prompt during install. The account simply has an empty credential until you change it, because the rc script initialises the data directory with --initialize-insecure. That is the first of three things on FreeBSD that will not match a MySQL guide written for Linux.

Original content from computingforgeeks.com - post 166413

The second is the config file. The package prints a message telling you to replace the default my.cnf, then ships a sample that will refuse to start on a data directory the package itself created. The third is the socket, which lives in /tmp rather than under /var/run, so every connection string copied from a Linux tutorial points at nothing.

This walks through the install in the order that avoids all three, then measures what the tuning knobs are actually worth on a ZFS root.

Benchmarked August 2026 on MySQL 8.4.11 (FreeBSD 15).

Which MySQL release belongs on a FreeBSD server

Three series are packaged. Look at all of them before picking, because a search anchored to one major version will hide the others:

pkg search -q '^mysql[0-9]'

Each series ships as a client and a server package:

mysql80-client-8.0.46
mysql80-server-8.0.46
mysql84-client-8.4.11
mysql84-server-8.4.11
mysql97-client-9.7.2
mysql97-server-9.7.2

Rule out 8.0 first. Oracle’s end of life notice moved it to Sustaining Support on 21 April 2026, which means no new fixes, only access to existing ones. It is still in the ports tree and it should not be your choice for a new deployment.

That leaves two long term support releases, not one. Oracle names both 8.4 and 9.7 as current LTS targets, so this is a choice between two supported lines rather than a choice between stable and bleeding edge. 8.4 has been generally available since April 2024, which is the practical argument for it: more mileage in third party tooling, ORMs, backup agents and managed replicas, and a support window that runs to April 2029 on premier support under Oracle’s lifetime support policy, with paid extended support carrying it to April 2032. Plan against the 2029 date unless you are buying the extension. 9.7 is the newer LTS and the better pick if you want the longer runway and your stack has been tested against it. Everything below targets 8.4; the package names are the only part that changes for 9.7.

Prerequisites

A FreeBSD 15 host with 4 GB of RAM and 2 vCPU is a workable starting point for a small application database, and that is the configuration everything here was measured on. Size from the working set rather than from a table in a tutorial. Note that the config this guide installs asks for a 1 GB buffer pool and 512 MB of redo capacity on its own, and on ZFS the ARC competes for whatever is left, so a 2 GB host needs those numbers reduced before it will behave.

Every command here runs as root. FreeBSD 15 ships no sudo in the base system, so if you are used to prefixing commands you will want to set up sudo or doas on FreeBSD first, or just use a root shell. The base system also has no vim. It has vi and ee, and the examples below use ee because it needs no prior knowledge to exit.

Step 1: Install the server and client packages

The server package pulls the client as a dependency, but naming both makes the intent explicit and gives you mysqladmin and mysqldump without a second pass:

pkg install -y mysql84-server mysql84-client

Read the post-install message rather than scrolling past it, because one line is accurate and one is not:

There is no initial password for first time use of MySQL.
Keep in mind to reset it to a secure password.

MySQL 8.4 has a default /usr/local/etc/mysql/my.cnf,
remember to replace it with your own
or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf.

The first statement is true and matters. The second is wrong: no my.cnf is installed. What the package drops is a sample, and the difference decides whether the server starts later.

ls -la /usr/local/etc/mysql/

Only the sample and an empty keyring directory are present:

drwxr-xr-x   3 root  wheel    4 Aug 12 22:23 .
drwxr-xr-x  10 root  wheel   16 Aug 12 22:28 ..
drwxr-x---   2 mysql mysql    2 Aug  8 05:17 keyring
-rw-r--r--   1 root  wheel 2039 Aug  8 05:17 my.cnf.sample

Step 2: Put the config in place before the first start

This is the ordering that matters, and it is the opposite of what most install guides do. The rc script initialises the data directory the first time you start the service, and it passes whatever config file exists at that moment. Two settings in the shipped sample can only be applied at initialisation. Start the service first and you have locked yourself out of both.

Copy the sample into place now:

cp /usr/local/etc/mysql/my.cnf.sample /usr/local/etc/mysql/my.cnf

Open it and review the lines that are about to become permanent:

ee /usr/local/etc/mysql/my.cnf

The full file is around 2 KB and also sets basedir, tmpdir, secure-file-priv, binlog expiry and a MyISAM key buffer. These are the entries that decide the rest of this guide:

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
datadir                         = /var/db/mysql
log-bin                         = mysql-bin
server-id                       = 1
enforce-gtid-consistency        = 1
gtid-mode                       = ON
lower_case_table_names          = 1
innodb_buffer_pool_size         = 1G
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_redo_log_capacity        = 512M

lower_case_table_names and innodb_data_file_path are the two that are fixed at initialisation. Decide on them now. The buffer pool and redo capacity can be changed later with a restart, and the tuning section has measured numbers for the buffer pool.

Step 3: Enable and start the service

Register the service so it survives a reboot, then start it:

sysrc mysql_enable=YES
service mysql-server start

First start does the initialisation, so it takes appreciably longer than later restarts. Confirm the daemon came up:

service mysql-server status

A running server reports its pid:

mysql is running as pid 12680.

If it reports that mysql is not running, skip to the two initialisation errors further down. Both produce that exact message and neither prints anything useful to the terminal.

Step 4: Set the root password immediately

There is no temporary password to hunt for. Searching the error log the way you would on Rocky or Ubuntu returns nothing:

grep -i 'temporary password' /var/db/mysql/*.err

The reason is in the rc script, which hands mysqld the insecure initialisation flag:

mysqld_init_args="${mysql_extra} --initialize-insecure --basedir=/usr/local --datadir=${mysql_dbdir} --user=${mysql_user}"

So root connects with nothing at all. Worth understanding precisely how exposed that is: the account is root@localhost only, and the FreeBSD package creates no anonymous users, so a blank password is a local risk rather than a remote one. The port, however, is another matter, which Steps 6 and 7 deal with.

The standard hardening script handles the password and the rest:

mysql_secure_installation

It opens by confirming what the package left behind, then walks four prompts:

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: n
Please set the password for root here.

New password:
Re-enter new password:

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Verify the blank password is gone by trying to use it:

mysql -u root -e 'SELECT 1'

Access denied is the correct answer here:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Step 5: The two errors that hit a config applied too late

If you started the service before copying the sample, the server will now refuse to start. Both failures land in the error log and neither surfaces at the terminal, which is why the service status message is so unhelpful.

Error: “The Auto-extending innodb_system data file is of a different size”

The rc script initialises with MySQL’s built in default of a 12 MB ibdata1, which is 768 pages at the 16 KB page size. The sample asks for 128 MB, or 8192 pages. InnoDB will not reconcile the two:

[ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system data file '/var/db/mysql/ibdata1' is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!
[ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
[ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.

On an existing database the fix is to make the config match reality rather than the other way round:

innodb_data_file_path            = ibdata1:12M:autoextend

Error: “Different lower_case_table_names settings for server and data dictionary”

Fixing the tablespace exposes the next one immediately. This setting is stamped into the data dictionary at creation and cannot be changed afterwards:

[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[ERROR] [MY-010119] [Server] Aborting

Comment the line out to keep the existing database, or accept that a case-insensitive schema means starting the data directory over. Starting over destroys every database on the host, so it is only an option on a server you have not put anything into yet. If that applies, stop the service and clear the directory:

service mysql-server stop
rm -rf /var/db/mysql
service mysql-server start

Leave /var/db/mysql_tmpdir and /var/db/mysql_secure alone while doing that. The package creates both, the sample config points at them, and initialisation aborts without them. The rc script discards the output that would have told you so, which turns a missing directory into a silent failure to start.

Step 6: Open remote access deliberately

With no config file at all, mysqld binds every interface. The sample sets bind-address = 127.0.0.1, which is the safer default and another reason Step 2 matters for more than tablespace sizing. Left alone, that setting means nothing outside the host can connect, so accepting application traffic takes a deliberate edit:

ee /usr/local/etc/mysql/my.cnf

Bind to the LAN address the application will reach, or to all interfaces if you are relying on the firewall in Step 7 to scope access:

bind-address                    = 0.0.0.0

bind_address is read only at runtime, so trying SET GLOBAL earns you ERROR 1238 (HY000): Variable 'bind_address' is a read only variable. It takes a restart instead:

service mysql-server restart

On a box with a public address, do Step 7 before this restart rather than after. Binding all interfaces with no firewall loaded leaves the port open to the internet for however long it takes you to read the next section.

Check what is listening afterwards:

sockstat -4l -p 3306

A wildcard in the local address column means every interface is answering, which is what you get both from the edit above and from running with no config file at all:

USER  COMMAND     PID FD PROTO LOCAL ADDRESS         FOREIGN ADDRESS
mysql mysqld     8604 33 tcp4  *:3306                *:*

Now create a user scoped to the subnet that needs it. MySQL 8.4 defaults to caching_sha2_password, so name it rather than relying on a default that shifted between releases. Run this from an interactive mysql session if you would rather not leave the application password in shell history:

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'appuser'@'10.0.1.%' IDENTIFIED WITH caching_sha2_password BY 'StrongPassword';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'10.0.1.%';

That authentication plugin has a behaviour worth knowing before it surprises you in a deployment. The first authentication a client makes must happen over TLS or with the server’s public key; only afterwards does the server cache the digest and allow the fast path.

Connecting normally, the server negotiates TLS with its self-signed certificate and the session reports a modern cipher:

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+

Error 2061: “Authentication requires secure connection”

Disable TLS on a cold cache and the handshake has no safe way to carry the password:

ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

Adding --get-server-public-key fetches the RSA key and the login succeeds without TLS. This is the trap in testing: run the same command twice and the second attempt works, because the digest is now cached. A restart or a FLUSH PRIVILEGES clears that cache and the error returns. Test the failure path on a cold cache or you will ship a client that only authenticates against a warm server.

Step 7: Filter port 3306 with pf

FreeBSD ships pf but does not load it. On a fresh install the firewall is simply absent:

service pf status

Which reports the kernel module is not even loaded:

pf.ko is not loaded

Combined with a wildcard bind, that means an unfiltered database port on every interface from the moment the service starts. Before writing any rules, find the real name of your external interface, because a ruleset naming an interface that does not exist loads without complaint and then matches nothing:

route -n get default | grep interface

On this virtio guest it is vtnet0. On physical hardware expect em0, igb0, ix0, re0 or bge0. Substitute yours into the ruleset:

ee /etc/pf.conf

A table keeps the client list editable without rewriting the ruleset, and the ICMP rule keeps ping and path MTU discovery working:

ext_if = "vtnet0"
set skip on lo0

table <mysql_clients> persist { 10.0.1.0/24 }

block in all
pass out all keep state
pass in inet proto icmp icmp-type { echoreq, unreach }
pass in on $ext_if proto tcp to port 22 keep state
pass in on $ext_if proto tcp from <mysql_clients> to port 3306 keep state

Parse the file before loading it. This catches syntax errors only. It will not tell you the interface name is wrong, because a nonexistent interface parses perfectly happily, so check your ext_if against the route output above by eye as well:

pfctl -n -f /etc/pf.conf

Now the part that catches people out. Starting pf runs pfctl -F all, which flushes the state table, and your current SSH session is a state. That session will drop. It reconnects fine if the ruleset permits port 22, and it does not if you got the interface name wrong, so give yourself a way back before you commit:

nohup sh -c 'sleep 600; pfctl -d' >/dev/null 2>&1 & echo $! > /tmp/pf-timer.pid
sysrc pf_enable=YES
service pf start

That background timer disables pf after ten minutes no matter what happens to your shell, and recording its pid matters: killing the wrapper stops pfctl -d from ever running, whereas a pattern kill on the sleep can leave the wrapper alive to fall straight through and disable pf anyway. Reconnect, confirm the rules are right, then cancel it:

kill "$(cat /tmp/pf-timer.pid)"

If the ten minutes elapsed while you were reconnecting, pf is already off and pfctl -e turns it back on. Console access through the hypervisor works instead of the timer if you have it.

Once you are back in, verify the loaded rules match what you wrote:

pfctl -s rules

The block-by-default line comes first, with the passes beneath it:

block drop in all
pass in on vtnet0 proto tcp from any to any port = ssh flags S/SA keep state
pass in on vtnet0 proto tcp from <mysql_clients> to any port = mysql flags S/SA keep state
pass in inet proto icmp all icmp-type echoreq keep state
pass in inet proto icmp all icmp-type unreach keep state
pass out all flags S/SA keep state

Tested from a host outside the table, the connection hangs until it times out rather than being refused, which is what a silent drop looks like from the client side. Adding that host opens it immediately with no reload:

pfctl -t mysql_clients -T add 10.0.1.60

That change lives in memory only. The next pfctl -f /etc/pf.conf or reboot resets the table to whatever pf.conf lists, so put permanent entries in the file. Longer ruleset patterns, including NAT and traffic shaping, are covered in the pf firewall guide for FreeBSD 15.

Step 8: Verify the install

Two commands confirm the pieces that differ from Linux. The first reports the server version and, importantly, the socket path:

mysqladmin -u root -p version

Uptime, thread count and the connection method all print together:

Note /tmp/mysql.sock in that output. The port compiles in that default and creates nothing under /var/run/mysql, so any application config, ORM setting or monitoring check carried over from a Linux host needs the path corrected. Query it directly if you are scripting against it:

mysql -u root -p -e "SELECT @@version, @@socket, @@datadir;"

Data lives under /var/db rather than /var/lib, which is the other path that trips up transplanted backup scripts:

+-----------+-----------------+-----------------+
| @@version | @@socket        | @@datadir       |
+-----------+-----------------+-----------------+
| 8.4.11    | /tmp/mysql.sock | /var/db/mysql/  |
+-----------+-----------------+-----------------+

If you would rather run Valkey or PostgreSQL alongside this, the Valkey install on FreeBSD 15 and the PostgreSQL setup for FreeBSD follow the same pkg and rc pattern. The MySQL 8.4 install on Amazon Linux is a useful comparison for how much of this is FreeBSD specific rather than version specific.

Step 9: Give the database its own ZFS dataset

The default installer layout puts /var/db inside zroot/ROOT/default, so by default the database shares a dataset with the entire root filesystem. That blocks every per-dataset tuning knob, because anything you set applies to the whole OS. Splitting it out is worth doing before the database has much in it.

Create the dataset somewhere temporary first, then move the data with the service stopped:

service mysql-server stop
zfs create -o mountpoint=/var/db/mysql-new zroot/mysql
cp -a /var/db/mysql/. /var/db/mysql-new/

Leaving recordsize at the pool default here is deliberate, and the tuning section has the measurements behind that choice. Swap the old directory out of the way and mount the new dataset in its place. The old copy stays on disk until you are satisfied, which is the point of doing it this way rather than moving in one step. Check that no mysql-old exists from an earlier attempt first, because mv puts a directory inside an existing target of the same name instead of replacing it:

mv /var/db/mysql /var/db/mysql-old
zfs set mountpoint=/var/db/mysql zroot/mysql
chown -R mysql:mysql /var/db/mysql
chmod 750 /var/db/mysql
service mysql-server start

Confirm the datadir is now its own dataset before deleting anything:

df /var/db/mysql

The dataset name in the first column is what you want to see, rather than zroot/ROOT/default:

Filesystem  1K-blocks    Used    Avail Capacity  Mounted on
zroot/mysql  14036464 3169892 10866572    23%    /var/db/mysql

Once the server is running against the new dataset and you have checked the data, reclaim the space. The permissions check matters because the dataset root is created 0755 and the datadir wants 0750:

ls -ld /var/db/mysql

That must read drwxr-x--- with mysql mysql as owner and group. Once it does, and only then, drop the old copy and the temporary mountpoint:

rm -rf /var/db/mysql-old
rmdir /var/db/mysql-new

Pool layout choices underneath this are covered in the ZFS pool design guide for FreeBSD 15, and ZFS snapshots and send/recv are worth wiring up now that the database has a dataset boundary to snapshot on.

Tuning numbers from the test box

Everything below was measured with sysbench on the same 2 vCPU, 4 GB host, ZFS root, against ten tables of 500,000 rows (roughly 1 GB of data). Single-box numbers on virtualised storage, so treat the direction as the finding and the magnitude as approximate.

The buffer pool is the lever that pays. Holding everything else constant and changing only innodb_buffer_pool_size, a read-only workload gained 17.9% throughput and dropped its 95th percentile latency by more than a quarter:

innodb_buffer_pool_size	Transactions/sec	p95 latency
256M	1495.37	4.91 ms
1G	1763.10	3.55 ms

The advice you will find repeated about ZFS did not reproduce, which is why Step 9 left recordsize alone. Setting recordsize=16K to match the InnoDB page size is supposed to be a straightforward win. Measured over three paired passes of a read-write workload, alternating the datadir between the 128K root dataset and a second dataset created at 16K, it was slower every single time:

Pass	128K tps	16K tps	Difference
1	117.78	92.29	-21.6%
2	107.26	89.50	-16.6%
3	85.93	76.92	-10.5%

Three for three in the same direction, so it is not noise, though the absolute numbers drift downward across passes as the dataset grows. On a read-only workload the same comparison was a dead heat, because ARC absorbed the reads and recordsize stopped mattering. The zfsprops(7) page is less dogmatic than the folklore, noting that a recordsize “greater than or equal to the record size of the database can result in significant performance gains”.

None of this proves 128K is universally better. It proves the 16K recommendation is not free, and that it is worth measuring on your own storage before adopting it. If you do decide to test 16K, note that the change is not retroactive: zfsprops(7) states that changing recordsize “affects only files created afterward; existing files are unaffected”, so setting it on a dataset that already holds your tablespaces does nothing until those files are rewritten. Testing it properly means a second dataset and another migration, which is the other reason Step 9 does not set it speculatively.

Which points at the setting that genuinely is FreeBSD specific. ARC and the InnoDB buffer pool cache the same pages twice, and ARC has no cap by default:

sysctl -n kstat.zfs.misc.arcstats.size vfs.zfs.arc.max

On the 4 GB test box ARC had taken 2.41 GB while InnoDB held a further gigabyte, on a dataset of about one. A max of zero means no limit. Cap it at runtime, then make it survive reboots. Note that sysrc refuses tunable names containing dots, so the loader.conf entry is a plain appended line rather than a sysrc call:

sysctl vfs.zfs.arc.max=1073741824
echo 'vfs.zfs.arc.max="1073741824"' >> /boot/loader.conf

Lowering the cap does not hand the memory back straight away. The zfs(4) page is explicit that reducing it below the current ARC size “will not cause the ARC to shrink without memory pressure to induce shrinking”, so re-reading arcstats.size immediately afterwards still shows the old figure. The cap governs where ARC settles, not where it is right now.

Then stop ZFS caching the same data pages InnoDB already holds. Because the database now has its own dataset, this applies to the database alone. Setting it on zroot/ROOT/default instead would strip data caching from the whole root filesystem, which is not what you want:

zfs set primarycache=metadata zroot/mysql

That change alone measured 1785.79 transactions per second against 1750.68 with full caching, a 2% gain that is small enough to ignore on its own. The memory it frees is not, because handing it to the buffer pool is the change that measured 17.9%.

One last note on innodb_flush_method. Started with no config file the server runs fsync, because 8.4’s switch to O_DIRECT as the default is scoped to Linux. The sample sets O_DIRECT explicitly, and older ZFS guidance says to avoid that because OpenZFS had no direct I/O support. Direct I/O landed in OpenZFS 2.3 and FreeBSD 15 ships 2.4, and the server started clean on it. Worth knowing that a clean start is not proof the direct path is being taken, since ZFS accepted and ignored the flag for years before 2.3, and writes still have to be recordsize aligned to bypass the ARC. Leave the sample’s value alone and judge it on the numbers rather than the setting.