How to Install MariaDB on FreeBSD 14.0

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.

1. Update the pkg packages catalog.

   console Copy

   $ sudo pkg update
   

   Explain Code

2. Search for all available MariaDB packages.

   console Copy

   $ sudo pkg search mariadb
   

   Explain Code

   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

4. View the installed MariaDB version on your server.

   console Copy

   $ mariadb --version
   

   Explain Code

   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

6. Log in to the MariaDB database server console.

   console Copy

   $ sudo mariadb
   

   Explain Code

   Output:

   sql Copy

   root@localhost [(none)]>
   

   Explain Code

   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

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

   Output:

   mysql_enable:  -> YES

2. Start the MariaDB database server.

   console Copy

   $ sudo service mysql-server start
   

   Explain Code

   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

   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
   • 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

   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

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

   sql Copy

   [(none)]> SHOW DATABASES;
   

   Explain Code

   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

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

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

7. Reload the MariaDB privileges tables to apply changes.

   sql Copy

   [demodb]> FLUSH PRIVILEGES;
   

   Explain Code

8. Exit the MariaDB database console.

   sql Copy

   [demodb]> EXIT
   

   Explain Code

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

   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

    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.