Automating MariaDB server Deployment and Database configuration with Ansible

Hello Folks and welcome again,

Today we’re going to explore databases, In this article we’ll dive into the operation of SQL databases specifically MariaDB which is a fork of MySQL. But before we delve in, let’s start by a simple question which is :

Why do we need database ?

Well, databases are essential for efficiently storing and accessing data. By having a robust database behind your project, you will be able to increase the functionality of your app by adding multiple items that can be saved and accessed by these products , for example shopping carts or member information , all of this can be saved using a database.

In our case, we’ll use MariaDB, a relational database that excels in storing and accessing key-value pairs using MySQL commands. This ability to save and access information is crucial for platforms like forums for example , where usernames and passwords need to be correlated together for authentication . Databases also play a vital role in inventory tracking across businesses of all sizes, which makes them a necessity that every company relies upon.

I hope this provides a brief overview of the importance and the pivotal role of databases. Remember, the more you work with relational databases, the more you will understand their importance when it comes to business strategies.

Before we begin, I must clarify that I’m not a database specialist. This isn’t my primary area of expertise. For in-depth database insights, consult a database administrator or expert.

Okay , let’s proceed to install and configure MariaDB on a Linux RedHat based system. Since deploying multiple databases can be repetitive, we’ll automate everything using Ansible (the latest version).

Let’s start by creating the Ansible configuration file and the inventory. There’s a lot of work to do, I’ll just copy my SSH key to the root user and perform the tasks directly as root ( This is not the most elegant approach, you can refer to one of my previous articles for a safer method ).

I’ve changed the color of my terminal (it’s different from before, I hope it’s clear). Now, let’s begin creating our playbook ( this will be long one ), so get ready :

For clarity, I’ll use my Kate editor ( I know my terminal’s colors aren’t clear enough, what can I say, I’m terrible with colors 😄).

So first, I need to define the collections in the header because I don’t want to write the long module names ( collections are a way to organize modules and I’ve explained them before) :

The first three tasks will be to install the mariadb server, the mariadb client and the PyMySQL packages. Then start the mariadb service and open the port on the firewall. But first I need to execute the “ dnf module list mariadb “ command to verify which version we have on the remote server ( which is Rocky 9.2 ) :

Looks like the default version is 10.11 on Rocky 9.2 , we’ll use that. Let’s back to our playbook and create the three tasks

For the firewall task, since we need to reload firewalld, I used a handler. The notify directive will trigger it when the task succeeds. We will declare the handler at the end of the playbook.

Now, before we move on to securing MariaDB and start using the mysql module, I need to install the community.mysql collection first. This is because all the modules that are not related to the system are not installed by default.

Okay , let’s start by configuring the root password ( the database root, not the system root ). We’ll use a vault variable ( the password will be inside an encrypted vault file ) and without forgetting to declare the vault file in the playbook header variable ( var_files) :

Now you may wonder why I’m using the login_unix_socket option. If you’ve been following, we’re installing both the client and the DB server on the same machine ( green2). This means the connection will be local and we’ll manage the database directly from the same machine ( there are actually different approaches but it’s better from a security prescriptive to disable remote logins to the database, that’s why I installed both the client and the DB server on the same machine ).

Alright , let’s continue and create the vault file that contains the root password :

As you can see, the file is now encrypted. Of course, “strongroot” is not a good password. In a prod environment, you should use a more secure one . Also, we’ll pass the password of the vault file when we run the playbook ( we could use a file, but I didn’t want to add more complexity ).

Next, we are going to use a template to copy a configuration file “my.cnf” to the root user directory on the server, so that we can log in and make changes to the database.

The template contains the credentials that the MariaDB client is going to use to log in to the MariaDB server (again, the password should match on both sides, which is why we’ll use the same vault file).

After that, we will remove the anonymous user so that only authenticated users can access the databases.

Now, I’m going to speed up the process a bit. First, I’ll create a database called “companies”. Then, I’ll create an “insert.sql” file containing SQL statements to insert a simple table with two columns into the database. After that, we’ll copy the insert file to the remote server and import it into the database. And finally, I will create a user called “Meher” (that’s my name 😁) and grant him some privileges (not full privileges).

And here’s the insert file below :

Let me quickly explain these SQL commands. We will create a table called “recruiters” with the storage engine set to “MYISAM” and the default character set to “utf8”. Inside the table, we will insert two columns: names and status, and we will populate it with three rows (I chose funny names from a famous anime 😄).

Finally , we’ll create our user “Meher” and we’ll give him the INSERT, UPDATE, SELECT privileges ( I know I used a straightforward syntax here, the * means all tables inside the database , although I could have specified the table by its name ) and of course without forgetting the handler .

And that’s it! Let’s run the playbook (I’ll use the — ask-vault-pass option to pass the password of the vault file to the Ansible playbook). The playbook will take some time to finish. Let’s wait :

Et voilà! As you can see, the playbook ran successfully.

Let’s do some verification to ensure we have the expected results :

Great, Now let’s verify the privileges of the user :

And as you can see, our database is fully created and operational.

Thank you for taking the time to read my article. Please like and share if you found it helpful, and I’ll see you in the next one. Au revoir .