How to Install MariaDB on FreeBSD 14.0

Updated on July 19, 2024

Introduction

MariaDB is an open-source relational database management system (RDBMS) that works as a drop-in alternative to MySQL with improved performance and support for multiple storage engine features. MariaDB uses the SQL (Structured Query Language) syntax to perform database operations and integrate with existing server applications such as web server stacks using modular extensions.

This article explains how to install MariaDB on FreeBSD 14.0, secure the database server, and enable database users to create and manage SQL records.

Prerequisites

Deploy a FreeBSD 14.0 instance on Vultr.
Access the server using SSH.
Create a non-root user with sudo privileges and switch to the user.

Install MariaDB

MariaDB is available in the default FreeBSD 14.0 package repositories. Follow the steps below to install MariaDB using the default pkg package manager and enable the database server to automatically start at boot time.

Update the pkg packages catalog.
console
```
$ sudo pkg update
```

Search for all available MariaDB packages.

                            console
                            
$ sudo pkg search mariadb

Output:

mariadb1011-client-10.11.8_1   Multithreaded SQL database (client)
mariadb1011-server-10.11.8_1   Multithreaded SQL database (server)
mariadb105-client-10.5.24      Multithreaded SQL database (client)
mariadb105-server-10.5.24      Multithreaded SQL database (server)
mariadb106-client-10.6.18_1    Multithreaded SQL database (client)
mariadb106-server-10.6.18_1    Multithreaded SQL database (server)
mariadb114-client-11.4.2       Multithreaded SQL database (client)
mariadb114-server-11.4.2       Multithreaded SQL database (server)

Install the latest stable MariaDB database server package. For example, mariadb106.
console
```
$ sudo pkg install -y mariadb106-server
```

View the installed MariaDB version on your server.

console

$ mariadb --version

Output:

mariadb  Ver 15.1 Distrib 10.6.18-MariaDB, for FreeBSD14.0 (amd64) using  EditLine wrapper

Temporarily start the MariaDB server to test access to the database console.
console
```
$ sudo service mysql-server onestart
```
Log in to the MariaDB database server console.
console
```
$ sudo mariadb
```
Output:
sql
```
root@localhost [(none)]>
```
If you receive the following error:
```
ERROR 2002 (HY000): Can't connect to local server through socket '/var/run/mysql/mysql.sock' (2)
```
Downgrade your MariaDB version to the previous stable package. For example, mariadb105 based on your installed version.
console
```
$ sudo pkg remove -y mariadb106-server && sudo pkg install -y mariadb105-server
```

Manage the MariaDB System Service

MariaDB uses the mysql-server system service profile to manage the database server process on your FreeBSD 14.0 server. Follow the steps below to manage the MariaDB system service and enable the database server to automatically start at boot time.

Enable MariaDB to automatically start at boot time.
console
```
$ sudo sysrc mysql_enable="YES"
```
Output:
```
mysql_enable:  -> YES
```

Start the MariaDB database server.

                            console
                            
$ sudo service mysql-server start

Output:

You can start the MariaDB daemon with:
cd '/usr/local' ; /usr/local/bin/mariadbd-safe --datadir='/var/db/mysql'

You can test the MariaDB daemon with mariadb-test-run.pl
cd '/usr/local/' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

Starting mysql.

View the MariaDB service status and verify that it's running.
console
```
$ sudo service mysql-server status
```
Output:
```
mysql is running on pid 1324
```

Secure MariaDB

MariaDB allows unauthenticated access to the database server console by default using the root database user profile. Follow the steps below to secure the MariaDB database server and enable password authentication for all database users.

Start the MariaDB secure installation script.
console
```
$ sudo mysql_secure_installation
```
- Press Enter to select none as the default root user password.
```
Enter current password for root (enter for none):
```
- Enter N and press Enter to run MariaDB without UNIX socket authentication.
```
Switch to unix_socket authentication [Y/n]
```
- Enter Y and press Enter to set up a new strong password for the root database user.
```
Change the root password? [Y/n]
```
- Enter your desired root database user password and press Enter to save changes.
```
New password:
```
- Enter the password again to verify that it matches and press Enter to apply the new user password.
```
Re-enter new password:
```
- Enter Y and press Enter to remove all anonymous users access on your database server.
```
Remove anonymous users? [Y/n]
```
- Enter Y and press Enter to disable remote access to the root database user.
```
Disallow root login remotely? [Y/n]
```
- Enter Y and press Enter to remove the test database and disable access to it.
```
Remove test database and access to it? [Y/n]
```
- Enter Y and press Enter to reload the MariaDB privileges table to apply changes.
```
Reload privilege tables now? [Y/n]
```
Output:
```
... 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!
```

Access the MariaDB Console

MariaDB uses the mysql or mariadb client utilities to connect to the database server console. Follow the steps below to access the MariaDB console and create sample records to test your database server functionalities.

                            console
                            
$ sudo mariadb -u root -p

Enter the root database user password you created earlier when prompted and press Enter to access the database server console. Your output should look like the one below when successful:

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

Create a new sample database demodb.
sql
```
[(none)]> CREATE DATABASE demodb;
```

View all databases and verify that the new database is available.

                            sql
                            
[(none)]> SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+       
| information_schema |
| mysql              |
| performance_schema |
| demodb             |
+--------------------+
4 rows in set (0.015 sec)

Switch to the database.
sql
```
[(none)]> use demodb;
```
Create a new sample database user db_admin with a strong password. Replace strong-password with your desired password.
sql
```
[demodb]> CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'strong-password';
```

Grant the new user db_admin full privileges to the demodb database.

                            sql
                            
[demodb]> GRANT ALL PRIVILEGES ON demodb.* TO 'db_admin'@'localhost';

Reload the MariaDB privileges tables to apply changes.
sql
```
[demodb]> FLUSH PRIVILEGES;
```
Exit the MariaDB database console.
sql
```
[demodb]> EXIT
```
Log in to the MariaDB database server using the new user db_admin to test access to the demodb database.
console
```
$ sudo mysql -u db_admin -p
```
Enter the db_admin user password when prompted and press Enter to access the database console.

View all databases available to the user.

                            sql
                            
db_admin@localhost [(none)]> show databases;

Output:

+--------------------+
| Database           |
+--------------------+
| demodb             |
| information_schema |
+--------------------+
2 rows in set (0.001 sec)

Based on the above output, the db_admin database user has access to the sample database demodb.

Conclusion

You have installed MariaDB on a FreeBSD 14.0 server and configured the database server with secure authentication to enable database users create and manage SQL records. You can use the MariaDB database server as a dedicated database backend by modifying the default listening address from localhost to 0.0.0.0 or a Vultr VPC 2.0 address. In addition, you can integrate MariaDB with existing applications to work as the database backend to enable the creation of application records.

How to Install MariaDB on FreeBSD 14.0

Introduction

Prerequisites

Install MariaDB

Manage the MariaDB System Service

Secure MariaDB

Access the MariaDB Console

Conclusion

Products

Features

Marketplace

Resources

Company