How to Install MariaDB on FreeBSD 14.0

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

Before you begin, you need to:

• Deploy a FreeBSD 14.0 server instance.
• 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.

1. Update the pkg package repository catalog.

   CONSOLE

   Copy

   $ sudo pkg update

   Explain Code

   Powered by Vultr AgentBeta

2. Search for all available MariaDB packages.

   CONSOLE

   Copy

   $ sudo pkg search mariadb

   Explain Code

   Powered by Vultr AgentBeta

   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)

3. Install the latest stable MariaDB database server package. For example, mariadb106.

   CONSOLE

   Copy

   $ sudo pkg install -y mariadb106-server

   Explain Code

   Powered by Vultr AgentBeta

4. View the installed MariaDB version on your server.

   CONSOLE

   Copy

   $ mariadb --version

   Explain Code

   Powered by Vultr AgentBeta

   Output:

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

5. Temporarily start the MariaDB server to test access to the database console.

   CONSOLE

   Copy

   $ sudo service mysql-server onestart

   Explain Code

   Powered by Vultr AgentBeta

6. Log in to the MariaDB database server console.

   CONSOLE

   Copy

   $ sudo mariadb

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   SQL

   Copy

   root@localhost [(none)]> 

   Explain Code

   Powered by Vultr AgentBeta

   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

   Copy

   $ sudo pkg remove -y mariadb106-server && sudo pkg install -y mariadb105-server

   Explain Code

   Powered by Vultr AgentBeta

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.

1. Enable MariaDB to automatically start at boot time.

   CONSOLE

   Copy

   $ sudo sysrc mysql_enable="YES"

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   mysql_enable:  -> YES

2. Start the MariaDB database server.

   CONSOLE

   Copy

   $ sudo service mysql-server start

   Explain Code

   Powered by Vultr AgentBeta

   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.

3. View the MariaDB service status and verify that it's running.

   CONSOLE

   Copy

   $ sudo service mysql-server status

   Explain Code

   Powered by Vultr AgentBeta

   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.

1. Start the MariaDB secure installation script.

   CONSOLE

   Copy

   $ sudo mysql_secure_installation

   Explain Code

   Powered by Vultr AgentBeta
   • 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.

1. Log in to the MariaDB database server as the root database user.

   CONSOLE

   Copy

   $ sudo mariadb -u root -p

   Explain Code

   Powered by Vultr AgentBeta

   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)]> 

2. Create a new sample database demodb.

   SQL

   Copy

   [(none)]> CREATE DATABASE demodb;

   Explain Code

   Powered by Vultr AgentBeta

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

   SQL

   Copy

   [(none)]> SHOW DATABASES;

   Explain Code

   Powered by Vultr AgentBeta

   Output:

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

4. Switch to the database.

   SQL

   Copy

   [(none)]> use demodb;

   Explain Code

   Powered by Vultr AgentBeta

5. Create a new sample database user db_admin with a strong password. Replace STRONG-PASSWORD with your desired password.

   SQL

   Copy

   [demodb]> CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'STRONG-PASSWORD';

   Explain Code

   Powered by Vultr AgentBeta

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

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

7. Reload the MariaDB privileges tables to apply changes.

   SQL

   Copy

   [demodb]> FLUSH PRIVILEGES;

   Explain Code

   Powered by Vultr AgentBeta

8. Exit the MariaDB database console.

   SQL

   Copy

   [demodb]> EXIT

   Explain Code

   Powered by Vultr AgentBeta

9. Log in to the MariaDB database server using the new user db_admin to test access to the demodb database.

   CONSOLE

   Copy

   $ sudo mysql -u db_admin -p

   Explain Code

   Powered by Vultr AgentBeta

   Enter the db_admin user password when prompted and press Enter to access the database console.

10. View all databases available to the user.

    SQL

    Copy

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

    Explain Code

    Powered by Vultr AgentBeta

    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 to 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 VPC network address. In addition, you can integrate MariaDB with existing applications to work as the database backend to enable the creation of application records.