How to Install and Use MariaDB Server on FreeBSD 14

To install the MariaDB server on FreeBSD, follow this guide. In this guide, I’ll cover how to install MariaDB on FreeBSD 14, set up the MariaDB root password, and secure MariaDB deployment.

Not only that, but I will also show you the basic usage of MariaDB for creating users, databases, and tables. Last but not least, I’ll teach you the basic CRUD (Create, Read, Update, and Delete) operations on MariaDB.

So if you are here to install a MariaDB server for your application, or you want to learn basic usage of MariaDB, you’re in the right place. Let’s dive right in.

Prerequisites

To get started, confirm that you have got the following:

• A FreeBSD 14 server.
  Read more: FreeBSD 14: Step-by-Step Installation (With Screenshot)
• A root user or non-root user with sudo or administrator privileges.

Installing MariaDB Server on FreeBSD

By default, the FreeBSD repository provides multiple versions of MariaDB servers. To install MariaDB on your FreeBSD server, complete the following tasks:

1. First, run the command below to update the FreeBSD repository and get the latest package information.

pkg update
pkg search mariadb

As seen in the following, the FreeBSD repository provides MariaDB servers 10.11, 10.6, and 10.5.

2. Run the command below to install the MariaDB 10.11 to the FreeBSD server.

pkg install mariadb1011-server mariadb1011-client

Input y to proceed with the installation.

3. Once installation is complete, run the command below to enable and verify MariaDB service.

sysrc mysql_enable="YES"
sysrc -a | grep mysql

4. Lastly, run the mariadb command below to check the MariaDB server version.

mariadb --version

You will see that MariaDB 10.11 is installed.

Managing MariaDB Service on FreeBSD

Now that you have installed and enabled the MariaDB server, take a look at how to manage the MariaDB server via the service command. Learn how to start, verify, stop, and restart MariaDB using these steps:

1. Run the command below to start the MariaDB server on your FreeBSD system. The first time you run this, you will initialize the MariaDB data directory to the /var/db/mysql directory.

service mysql-server start

2. Once the MariaDB server starts, run the following command to verify it.

service mysql-server status

In the following output, you can see the MariaDB server is running on a process ID (PID) 2639.

3. If you need to stop the MariaDB server, run the command below.

service mysql-server stop

4. Lastly, run the following command to restart the MariaDB server when needed.

service mysql-server restart

Securing MariaDB Server via mariadb-secure-installation

With the MySQL/MariaDB up and running, move on to set up the MariaDB root password to secure the deployment using the mariadb-secure-installation utility. Proceed with these steps to secure your MariaDB server installation:

1. Execute the mariadb-secure-installation command below to start the configuration.

mariadb-secure-installation

Press ENTER when prompted for the root password. The MariaDB root user comes without a password.

2. Input Y to enable unix_socket authentication for the root user.

3. Input Y again to set up the root password. Then, input your password and repeat.

4. Input Y to delete the default anonymous user from your MariaDB server.

5. Then, input Y when asked to disable the remote authentication for the root user.

6. Now input Y to remove the default database test and its privileges.

7. Lastly, input Y again to reload table privileges and apply your modification to the MariaDB server.

Once the process is complete, you will see an output All done! …. your MariaDB installation should now be secure.

How to Create a Database and User on MariaDB?

So far, you have installed and secured the MariaDB server. Let’s explore how to create a database and user on MariaDB with the following steps:

1. Execute the mariadb command below to log in to the MariaDB as user root. Input your password when prompted.

mariadb -u root -p

Once logged in, you will see the MariaDB shell with a prompt like root@localhost [none]>. This is an indicator where you’ve connected to MariaDB as user root@localhost.

2. Now run the following queries to create a new database testapp and verify the list of available databases on MariaDB.

# creating database testapp
CREATE DATABASE testapp;

# lisiting database on MariaDB
SHOW DATABASES;

You will see the testapp database on the database list:

3. After you’ve created the database, move on to create a new MariaDB user.

Run the following query to create a new user testuser with the password TestUserPassw0rd. Please update the password in this query.

CREATE USER testuser@localhost IDENTIFIED BY 'TestUserPassw0rd';

4. Next, run the GRANT ALL query below to allow full control of database testapp to user testuser@localhost. Then, reload table privileges to apply your changes.

# grant access all privileges of database testapp to user testuser
GRANT ALL PRIVILEGES ON testapp.* TO testuser@localhost;

# reload table privileges
FLUSH PRIVILEGES;

5. Lastly, run the query below to verify the list privileges for user testuser@localhost.

SHOW GRANTS FOR testuser@localhost;

In the following output, you can see the user testuser@localhost with privileges to access the database testapp.

Connecting to MariaDB via Specific User

After creating the MariaDB database and user, let’s verify your user and database by connecting to it via the mariadb client utility.

Follow these steps to connect to your MariaDB database and user:

1. Run the mariadb command below to connect to the MariaDB server via user testuser to the database testapp.

mariadb -h localhost -u testuser -D testapp -p

When prompted, input the password for testuser.

2. Once logged in to MariaDB, run the status query below to verify your current connections.

status

In this output, notice the current database is testapp, with the user testuser, and the MariaDB server 10.11.

Basic CRUD Operations in MariaDB

let’s leverage your MySQL/MariaDB knowledge with these best practices:

• Creating table via CREATE TABLE query.
• Insert data using the INSERT query.
• Retrieving or showing data via SELECT query.
• Updating existing data in MySQL/MariaDB via UPDATE query.
• Deleting specific row data via the DELETE query.

Let’s get this done.

Creating Table in MySQL/MariaDB

In this section, you will utilize the CREATE TABLE query for creating a table in MySQL. In addition to that, you will use the SHOW TABLES query to list available tables and the DESCRIBE query to check the list columns.

Execute these actions to create a table in MySQL/MariaDB:

1. To create a new table, run the CREATE TABLE query below. In this case, you will create table players with columns id, name, team, and number.

CREATE TABLE players (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
team VARCHAR(50),
number INT(100)
);

2. Now run the following query to verify the list table on your database, and check the available columns on the table players.

# listing available tables
SHOW TABLES;

# check columns on table players
DESCRIBE players;

You will see 4 rows available on the table players.

Insert Data to MySQL/MariaDB

With the table players created, let’s add/insert new data Into it. Learn now how to add data to MySQL/MariaDB via the INSERT query below:

1. Execute the INSERT INTO query below to add new data to your database.

INSERT INTO players (name, team, number)
VALUES ('Sthephen Curry', 'Golden State Warriors', 30);

2. In addition, run the query below to add multiple data at once. Notice in the following, you can use commas to separate each of your data within the VALUES.

INSERT INTO players (name, team, number)
VALUES ('Klay Thompson', 'Golden State Warriors', 11),
('Nikola Jokic', 'Denver Nuggets', 15),
('Jamal Murray', 'Denver Nuggets', 29),
('Keegan Murray', 'Sacramento Kings', 13);

When you insert multiple data at once, you will see the Records: 4 output indicates how many data you’ve added.

Retrieve or Show Data in MySQL/MariaDB

To retrieve r show data in MySQL/MariaDB, use the SELECT query like the following:

1. Run the SELECT query below to retrieve data from table players on your MariaDB server. The symbol * is used to retrieve data from all columns within the players table.

SELECT * FROM players;

2. You can also specify a column using the query below. In this case, you will only retrieve data from columns name and team.

SELECT name, team FROM players;

Updating Data in MySQL/MariaDB

To update existing data on MySQL/MariaDB, you can use the UPDATE query followed by WHERE filter. Here’s how you do it:

1. Run the UPDATE query below to update existing data on the MariaDB server.

In this example, you will update the column number with the new value 27 via the SET query for specific row name = Jamal Murray using the WHERE filter.

UPDATE players
SET number = 27
WHERE name = 'Jamal Murray';

When successful, you should see an output such as Row matched: 1 Changed:1.

2. Now run the SELECT query below to verify your updated data.

SELECT * FROM players
WHERE name = 'Jamal Murray';

Deleting Data from MySQL/MariaDB

The DELETE query is used to delete existing entries from MySQL/MariaDB. Combined with WHERE filter, you can delete data from specific columns.

1. To delete data from the MariaDB server, use the DELETE FROM query below. In this case, you will delete the data from table players for id = 5 via the WHERE filter.

DELETE FROM players
WHERE id = 5;

2. Now run the SELECT query below to verify the list data on table players.

SELECT * FROM players;

Notice the data for id number 5 is deleted from the table.

Uninstalling MariaDB Server from FreeBSD

In the last, if you need to delete the MariaDB server, execute the following actions:

1. First, run the command below to stop and disable the MariaDB server.

service mysql-server stop
sysrc -x mysql_enable

2. Now remove the MariaDB server and remove orphaned packages using the command below.

pkg remove mariadb1011-server mariadb1011-client
pkg autoremove

3. Lastly, remove the mysql user and group using the command below.

rmuser mysql
pw groupdel -n mysql

Conclusion

By following this guide, you’ve installed and secured the MariaDB 10.11 server on FreeBSD 14. In addition to that, you have also learned the following about the MariaDB server:

• Basic usage of the mariadb client command for connecting to the MariaDB server.
• How to create a user and database on MariaDB.
• How to create tables on MariaDB.
• Lastly, basic MySQL/MariaDB queries for CRUD (Create, Read, Update, and Delete) operations.

Empowering with this knowledge, you’re ready to install your applications. You can integrate your MariaDB server with PHP or PHP-FPM, Nginx, or Apache web server to run your applications.

ArvidL

ArvidL

System administrator and devops enthusiast, leveraging over 10+ years of Linux expertise to optimize operations. Proficient in FreeBSD, VMWare, KVM, Proxmox, PfSense, Ansible, Docker, and Kubernetes.

Read Also:

A Complete Guide to Install PowerDNS on FreeBSD 14

How to Install BIND DNS Server on FreeBSD 14