Learn how to - Install MariaDB 10.5 on FreeBSD 12 / FreeBSD 13

Felix Williams Databases How to Servers February 10, 2023

Learn how to – Install MariaDB 10.5 on FreeBSD 12 / FreeBSD 13.

How do I install MariaDB 10.5 on FreeBSD 12/FreeBSD 13?, How to Install MariaDB 10.5 Database Server on FreeBSD 12/FreeBSD 13?. MariaDB 10.5 is a multi-user, multi-threaded SQL database server. It is a community-developed fork of the MySQL relational database management system.

MariaDB 10.5 Features:

Ignored Indexes – These are indexes that are visible and maintained but not used by the optimizer
sys schema supported– This is a “system” database containing views and procedures for investigating performance problems.
SKIP LOCKED – Locked tables are skipped from being updated or selected.
JSON_TABLE() – can create a JSON table that can be used as a subquery from a JSON document.
OFFSET…FETCH…[WITH TIES] – WITH TIES is an optional clause that adds extra functionality. Example as used
Oracle compatibility – There are ongoing works in making MariaDB compatible with OracleDB with some Oracle Syntaxes and functions already added.

In my last article, I covered:

How to Install PHP 7.2 on FreeBSD 12

Below are the steps to install and configure MariaDB 10.5 on FreeBSD 12 / FreeBSD 13

Step 1: Update ports tree

Start by updating your FreeBSD server Ports tree using the portsnap command:

$ jmutai@freebsd:~ % su -
 Password:
 root@freebsd:~# portsnap fetch update
 Looking up portsnap.FreeBSD.org mirrors... 4 mirrors found.
Fetching public key from ipv4.aws.portsnap.freebsd.org... done.
Fetching snapshot tag from ipv4.aws.portsnap.freebsd.org... done.
Fetching snapshot metadata... done.
Fetching snapshot generated at Thu Dec  2 00:04:27 UTC 2021:
2e7ecd7558a20055d792bbfe1624d0c3af9da2d3f1aea3          92 MB   84 MBps    01s
Extracting snapshot... done.
Verifying snapshot integrity... done.
Fetching snapshot tag from ipv4.aws.portsnap.freebsd.org... done.
Fetching snapshot metadata... done.
Updating from Thu Dec  2 00:04:27 UTC 2021 to Thu Dec  2 07:27:20 UTC 2021.
Fetching 5 metadata patches... done.
Applying metadata patches... done.
Fetching 0 metadata files... done.
Fetching 30 patches.
(30/30) 100.00%  done.
done.
Applying patches...
done.
Fetching 0 new ports or files... done.
/usr/ports was not created by portsnap.
 You must run 'portsnap extract' before running 'portsnap update'.

When running Portsnap for the first time, you need to extract the snapshot into /usr/ports:

sudo portsnap extract

You should get the message below at the end of the output.

Building new INDEX files... done.

Confirm that everything is up to date.

$ sudo portsnap fetch update
Looking up portsnap.FreeBSD.org mirrors... 4 mirrors found.
Fetching snapshot tag from ipv4.aws.portsnap.freebsd.org... done.
Ports tree hasn't changed since last snapshot.
No updates needed.
Ports tree is already up to date.

Step 2: Install MariaDB 10.5 Database Server

MariaDB 10.5 can be installed using binary or ports method. This installation is from the binary package using the pkgpackage manager.

Check if MariaDB 10.5 is available from your package cache:

$ pkg search mariadb | grep 10.5
mariadb105-client-10.5.13      Multithreaded SQL database (client)
mariadb105-server-10.5.13      Multithreaded SQL database (server)

From the output, we can see there is version available. Install both the server and client using the command below:

$ sudo pkg install mariadb105-server mariadb105-client
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
New version of pkg detected; it needs to be installed first.
The following 1 package(s) will be affected (of 0 checked):

Installed packages to be UPGRADED:
	pkg: 1.16.1 -> 1.17.5

Number of packages to be upgraded: 1

The process will require 3 MiB more space.
7 MiB to be downloaded.

Proceed with this action? [y/N]: y
[1/1] Fetching pkg-1.17.5.pkg: 100%    7 MiB   7.7MB/s    00:01
Checking integrity... done (0 conflicting)
[1/1] Upgrading pkg from 1.16.1 to 1.17.5...
[1/1] Extracting pkg-1.17.5: 100%
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 10 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	bash: 5.1.8
	boost-libs: 1.72.0_5
	galera26: 26.4.9
	icu: 69.1,1
	libedit: 3.1.20210216,1
	libxml2: 2.9.12
	mariadb105-client: 10.5.13
	mariadb105-server: 10.5.13
	pcre2: 10.39
	unixODBC: 2.3.9

Number of packages to be installed: 10

The process will require 477 MiB more space.
57 MiB to be downloaded.

Proceed with this action? [y/N]: y

Step 3: Start and Enable mysql-server service

After installing MariaDB Database server on FreeBSD 12/13 server, you’ll need to start the service before you can configure it.

Run the following command in your terminal to enable themysql-server service to start on system boot.

$ sudo sysrc mysql_enable="YES"
mysql_enable:  -> YES

Then start the service.

sudo service mysql-server start

Step 4: Harden MariaDB server installation

Run the command mysql_secure_installation to harden MariaDB database server.

$ sudo mysql_secure_installation
 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
       SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
 In order to log into MariaDB to secure it, we'll need the current
 password for the root user.  If you've just installed MariaDB, and
 you haven't set the root password yet, the password will be blank,
 so you should just press enter here.
 Enter current password for root (enter for none): 
 OK, successfully used password, moving on…
 Setting the root password ensures that nobody can log into the MariaDB
 root user without the proper authorisation.
 Set root password? [Y/n] y
 New password: 
 Re-enter new password: 
 Password updated successfully!
 Reloading privilege tables..
  … Success!
 By default, a MariaDB installation has an anonymous user, allowing anyone
 to log into MariaDB without having to have a user account created for
 them.  This is intended only for testing, and to make the installation
 go a bit smoother.  You should remove them before moving into a
 production environment.
 Remove anonymous users? [Y/n] Y
  … Success!
 Normally, root should only be allowed to connect from 'localhost'.  This
 ensures that someone cannot guess at the root password from the network.
 Disallow root login remotely? [Y/n] Y
  … Success!
 By default, MariaDB comes with a database named 'test' that anyone can
 access.  This is also intended only for testing, and should be removed
 before moving into a production environment.
 Remove test database and access to it? [Y/n] Y
 Dropping test database…
 … Success!
 Removing privileges on test database…
 … Success! 
 Reloading the privilege tables will ensure that all changes made so far
 will take effect immediately.
 Reload privilege tables now? [Y/n] Y
  … Success!
 Cleaning up…
 All done!  If you've completed all of the above steps, your MariaDB
 installation should now be secure.
 Thanks for using MariaDB!

Ensure you:

Set Database root user password
Remove anonymous users
Disallow root user remote logins
Remove test database and access to it

When done, test access using the root user

$ mysql -u root -p
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 13
Server version: 10.5.13-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.5.13-MariaDB |
+-----------------+
 1 row in set (0.001 sec)
 MariaDB [(none)]> QUIT
 Bye

Step 5: Bind MariaDB Server service to localhost

By default, MariaDB service binds to all available network interfaces as can be seen from:

$ sockstat -4 -6 | grep 3306
mysql mysqld 1350 21 tcp46 *:3306 *:*

If you don’t have services connecting to your database server from the network, you can limit this to localhost.

# sysrc mysql_args="--bind-address=127.0.0.1"
mysql_args: -> --bind-address=127.0.0.1

You need to restart mysql-server after making this change

# service mysql-server restart
Stopping mysql.
Waiting for PIDS: 1350, 1350.
Starting mysql.

Verify the change:

# sockstat -4 -6 | grep mysql
mysql mysqld 1614 21 tcp4 127.0.0.1:3306 *:*

Your installation of MariaDB 10.6 on FreeBSD 12 | FreeBSD 13 has been successful. We went ahead and enabled the service to start on boot and hardened the server. Until next time, thanks for visiting our website. Follow us on our social media pages for instant updates.

Best MySQL Learning Courses on Udemy:

If you’re interested in PostgreSQL Database server, check:

How to Install PostgreSQL 11 on FreeBSD 12

Cart

Learn how to – Install MariaDB 10.5 on FreeBSD 12 / FreeBSD 13