Easily Install MariaDB 10.5 on FreeBSD 12.1

In this tutorial, you will how to install MariaDB 10.5 on FreeBSD 12.1. “MariaDB 10.5 is the current stable series of MariaDB. It is an evolution of MariaDB 10.4 with several entirely new features not found anywhere else and with backported and reimplemented features from MySQL”.

Installing MariaDB 10.5 on FreeBSD 12.1

Run System Update

Assuming that you are installing MariaDB 10.5 on a newly installed FreeBSD 12.1, update and FreeBSD package catalogue.

pkg update

pkg upgrade

Install MariaDB 10.5

The FreeBSD 12.1 package catalogue provides the latest stable release versions of MariaDB.

pkg search mariadb

mariadb-connector-c-3.1.9      MariaDB database connector for C
mariadb-connector-odbc-3.1.9   MariaDB database connector for odbc
mariadb103-client-10.3.24      Multithreaded SQL database (client)
mariadb103-server-10.3.24      Multithreaded SQL database (server)
mariadb104-client-10.4.14      Multithreaded SQL database (client)
mariadb104-server-10.4.14      Multithreaded SQL database (server)
mariadb105-client-10.5.5       Multithreaded SQL database (client)
mariadb105-server-10.5.5       Multithreaded SQL database (server)

Therefore, to install MariaDB, simply execute the command below;

pkg install mariadb105-server mariadb105-client

The following 7 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	boost-libs: 1.72.0_2
	galera26: 26.4.5
	mariadb105-client: 10.5.5
	mariadb105-server: 10.5.5
	rsync: 3.2.3
	unixODBC: 2.3.7
	xxhash: 0.8.0

Number of packages to be installed: 7

The process will require 409 MiB more space.
43 MiB to be downloaded.

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

Running MariaDB 10.5 on FreeBSD 12.1

Once the installation is installed, you can start MariaDB on FreeBSD 12.1;

service mysql-server onestart

Checking the status of MariaDB service on FreeBSD 12.1

service mysql-server onestatus

mysql is running as pid 1543.

You can enable MariaDB to run on system boot using the command below;

sysrc mysql_enable=yes

Or simply run;

service mysql-server enable

You would then be able to manage MariaDB service as follows;

To start MariaDB service;

service mysql-server start

To stop the service;

service mysql-server stop

Disable the service from running on system boot;

service mysql-server disable

Restart MariaDB 10.5 on FreeBSD 12.1

service mysql-server restart

Check Status;

service mysql-server status

Securing MariaDB 10.5 on FreeBSD 12.1

MariaDB comes with a default security script, mysql_secure_installation that is used to improve the security of MariaDB installation by:

• Setting the password for root accounts (if need be).
• Disabling remote root login to the databases.
• Removing anonymous-user accounts.
• Removing the test database, which by default can be accessed by anonymous users.

Simply run the command below to launch the script.

mysql_secure_installation

MariaDB 10.5 Authentication

The new installations of MariaDB have two secure accounts are created during the installation. The accounts are root@localhost and mysql@localhost. Both accounts uses either of the unix_socket and the mysql_native_password authentication plugins.

unix_socket authentication plugin allows a system root user or a user with sudo rights to login as root@locahost to MariaDB database without a password. You can even login as mysql user.

With unix_socket authentication plugin, while being a root user, you can simply login by running either of the commands below;

mysql

or

mysql -u root

Even if you run, mysql -u root -p, and press ENTER for blank password, you will still login.

As a user with sudo rights, prefix the commands above with sudo.

Read more about MariaDB authentication plugins on MariaDB Knowledge base.

Enable MariaDB password Authentication on FreeBSD 12.1

The mysql_native_password plugin is used as a failover for the unix_socket plugin. However, the account has an invalid password. To enable password authentication, you need to login to MariaDB as root user as shown above and set the password.

mysql

set password = password("P@sSw0Rd123");

flush privileges;
quit

This re-enables the MariaDB password authentication and hence, you can now login as non root or non sudo user.

mysql -u root -p

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.5.5-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.

root@localhost [(none)]>

Similarly, you can login as mysql user;

sudo -u mysql mysql

Set Native Password Authentication Method as Default

To completely disable unix_socket authentication plugin and instead use the msqyl_native_password authentication method, simply login to MariaDB and change the authentication plugin for root user.

mysql

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("MyPQQSSword");

flush privileges;
quit

Next time you try to login without specifying the password, login will fail.

sudo mysql -u root

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

You can go ahead and perform your database tasks using MariaDB 10.5 on FreeBSD 12.1

You can check our other guides on MariaDB/MySQL installations by following the links below;

Install MariaDB 10 on Debian 10 Buster

Install MariaDB 10.4 on Ubuntu 18.04/Debian 9

Install MariaDB 10.3 on CentOS 7

Install MariaDB 10.3 on Fedora 30

Install MySQL 8 on CentOS 8

Install MySQL 8 on Debian 10 Buster