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.
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:
sqlroot@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 defaultroot
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!
- Press Enter to select
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.
Log in to the MariaDB database server as the
root
database user.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. Replacestrong-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 thedemodb
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 thedemodb
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.
sqldb_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 databasedemodb
.
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.