Configuring and Securing MariaDB

Libraries: If you want this lab, consider one of these libraries.
Cloud

Configuring and Securing MariaDB

Before we can start building our world-changing website or application on LEMP, we have to lay the foundation: the stack. In this hands-on lab, we will walk through configuring and securing MariaDB on Ubuntu Linux. When you have completed this lab, you will have a secure MariaDB installation on Ubuntu Linux.

Get started Contact sales

Lab Info

Level

Intermediate

Last updated

Oct 28, 2025

Duration

1h 0m

Challenge

Confirm that MariaDB is Installed, Enabled, and Running
Become root:
```
sudo su -
```
Check the status of the MariaDB installation:
```
systemctl status mariadb
```
The service should be enabled and running.

Check the installed version of MariaDB using mysql:
```
mysql -V
```
We should see that there's an instance of MariaDB running. The exact version isn't important.
Challenge

Secure the Database Using the mysql_secure_installation Script
Launch the mysql_secure_installation script. Set the MariaDB root password to 123456. This is important, as successful completion of this lab will require this:
```
mysql_secure_installation
```
We will be prompted for answers by the mysql_secure_installation script.

Try logging in as the MariaDB root user using the mysql client:
```
mysql -u root -p=123456
```
Exit the MariaDB client:
```
quit
```
Challenge

Configure the Network
In order to make MariaDB available outside of the localhost 127.0.0.1, we need to set the bind-address variable to the private IP address of our server. We can find this in the /home/cloud_user/server_info.txt file.

Edit the MariaDB server configuration file:
```
vi /etc/mysql/mariadb.conf.d/50-server.cnf
```
Change the bind-address to our private IP.
Challenge

Configure the Second Port
We'd also like to configure an "extra port."

Add the following lines to the [mariadb] section in /etc/mysql/mariadb.conf.d/50-server.cnf:
```
# Second Admin Port
extra_port = 8385
extra_max_connections = 10
```
Save and exit.

Restart the mariadb service and check the status:
```
systemctl restart mariadb
```
```
systemctl status mariadb
```
The service should be enabled and running.
Challenge

Validate the Network and the Second Port
The mariadb service should be enabled and active. Check that the MariaDB service is listening on ports 3306 and 8385:
```
netstat -anp | egrep "3306|8385"
```
Challenge

Configure the Firewall to Support MariaDB
Check the firewall configuration:
```
ufw status
```
Open port 3306 to allow inbound MariaDB traffic:
```
ufw allow 3306
```
Open port 8385 to allow inbound MariaDB traffic on the "extra port":
```
ufw allow 8385
```
Recheck the status of the firewall:
```
ufw status
```
MariaDB traffic is now allowed through the firewall on both port 3306 and 8385.
Challenge

Create a Certificate Authority Private Key and Certificate
Create a directory to store our certificates:
```
mkdir -p /etc/mysql/certificates
```
```
cd /etc/mysql/certificates
```
Generate a private key:
```
openssl genrsa 2048 > ca-key.pem
```
Generate the X509 certificate. Hit Enter and use the default answers, except for CN, use 'ca.bigstatecollege.edu' for the CN field. THIS IS IMPORTANT!
```
openssl req -new -x509 -nodes -days 365000 
      -key ca-key.pem -out ca-cert.pem
```
Challenge

Create a Private Key for the MariaDB Server
Generate a private key for the server:
```
openssl req -newkey rsa:2048 -days 365000 
      -nodes -keyout server-key.pem -out server-req.pem
```
We will have to answer some questions. Hit Enter and use the default answers, except for CN, use 'server.bigstatecollege.edu' for the CN field. THIS IS IMPORTANT!

Process the key to remove the passphrase:
```
openssl rsa -in server-key.pem -out server-key.pem
```
We should see:

writing RSA key
Challenge

Create a Self-Signed Certificate for the MariaDB Server
Generate a self-signed X509 certificate for the MariaDB server from the certificate request:
```
openssl x509 -req -in server-req.pem -days 365000 
      -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 
      -out server-cert.pem
```
We now have a self-signed X509 certificate for the MariaDB server in the /etc/mysql/certificates/ directory.
Challenge

Create a Private Key for the MariaDB Client
Generate a private key for the MariaDB client. Hit Enter and use the default answers, except for CN, use 'client.bigstatecollege.edu' for the CN field. THIS IS IMPORTANT!
```
openssl req -newkey rsa:2048 -days 365000 
      -nodes -keyout client-key.pem -out client-req.pem
```
Next, process the key to remove the passphrase.
```
openssl rsa -in client-key.pem -out client-key.pem
```
You should see the following: writing RSA key
Challenge

Create a Self-Signed Certificate for the MariaDB Client
Generate a self-signed X509 certificate for the MariaDB client from the certificate request:
```
openssl x509 -req -in client-req.pem -days 365000 
      -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 
      -out client-cert.pem
```
Allow the mysql user access to the certificates, and add read permission for group and other:
```
chmod 644 *
```
Challenge

Verify the Self-Signed Certificates for the MariaDB Client and Server
Verify that the X509 certificate was correctly generated using the openssl verify command:
```
openssl verify -CAfile ca-cert.pem client-cert.pem server-cert.pem
```
We should see the following:

client-cert.pem: OK

server-cert.pem: OK
Challenge

Enable TLS for the MariaDB Server
Add the server configuration in the [mariadb] configuration block in /etc/mysql/mariadb.conf.d/50-server.cnf:
```
vi /etc/mysql/mariadb.conf.d/50-server.cnf
```
For the server, in the [mariadb] configuration block, add this:
```
# SSL Configuration
ssl_cert = /etc/mysql/certificates/server-cert.pem
ssl_key = /etc/mysql/certificates/server-key.pem
ssl_ca = /etc/mysql/certificates/ca-cert.pem
```
Save and exit the server configuration file.

Challenge

Enable TLS for the MariaDB Client

Edit the file:

vi /etc/mysql/mariadb.conf.d/50-client.cnf

In the [client-mariadb] configuration block, add the following:

# SSL Configuration
ssl_cert = /etc/mysql/certificates/client-cert.pem
ssl_key = /etc/mysql/certificates/client-key.pem
ssl_ca = /etc/mysql/certificates/ca-cert.pem

Save and exit the client configuration file.

Challenge

Activate TLS for the MariaDB Client and Server
Restart the MariaDB server:
```
systemctl restart mariadb
```
Once the MariaDB server is restarted, verify it's running:
```
systemctl status mariadb
```
The service should be enabled and running.
Challenge

Check that TLS is Enabled on the MariaDB Server
Check that TLS is now enabled on the MariaDB server:
```
mysql -u root -p=123456 -e "SHOW VARIABLES LIKE 'have_ssl';"
```
We should see that have_ssl returns YES.

TLS is enabled on the MariaDB server.
Challenge

Verify the Connection of the MariaDB Client is Using TLS
In order to verify that the connection from the MariaDB client to the server is using TLS, we can check the value of the connection's Ssl_cipher status variable. If the value is non-null, then the connection is using TLS:
```
mysql -u root -p=123456 -e "SHOW SESSION STATUS LIKE 'Ssl_cipher';"
```
We should see a non-null value as the result.
Challenge

Examine the SSL Configuration of the MariaDB Server
Let's check out the SSL configuration.
```
mysql -u root -p=123456 -e "SHOW VARIABLES LIKE '%ssl%';"
```
Check for TLS v 1/1.1/1.2 - Substitute our private IP address for YOUR_PRIVATE_IP here!

TLSv1.0:

openssl s_client -connect YOUR_PRIVATE_IP:3306 -tls1

TLSv1.1:

openssl s_client -connect YOUR_PRIVATE_IP:3306 -tls1_1

TLSv1.2:

openssl s_client -connect YOUR_PRIVATE_IP:3306 -tls1_2

Challenge

Create the Encryption Key File

Let's create some directories to store our certificates for both the client and server:

mkdir -p /etc/mysql/encryption

cd /etc/mysql/encryption

Create an encryption key file:

for i in `seq 1 10` ; do echo $i";"`openssl rand -hex 32` >> /etc/mysql/encryption/keyfile ; done

cat /etc/mysql/encryption/keyfile

Challenge

Encrypt the Encryption Key File

Generate a random encryption password:

openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key

Encrypt the key file:

openssl enc -aes-256-cbc -md sha1 
   -pass file:/etc/mysql/encryption/keyfile.key 
   -in /etc/mysql/encryption/keyfile 
   -out /etc/mysql/encryption/keyfile.enc

We will see a warning. We can ignore it.

*** WARNING : deprecated key derivation used.

Using -iter or -pbkdf2 would be better.

Challenge

Configure MariaDB to Use the Encrypted Key File - Part 1
Edit the MariaDB server configuration file:
```
vi /etc/mysql/mariadb.conf.d/50-server.cnf
```

Challenge

Configure MariaDB to Use the Encrypted Key File - Part 2

Add the following to the [mariadb] configuration block:

# Encryption at Rest
plugin-load-add=file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CTR
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads = 4
innodb-tablespaces-encryption
innodb_default_encryption_key_id=7

Challenge

Activate Data-at-Rest Encryption
Save the configuration file, exit the editor, and restart the MariaDB server:
```
systemctl restart mariadb
```
Once the MariaDB server is restarted, verify it's running:
```
systemctl status mariadb
```
Challenge

Create an Encrypted Database Table
Test encryption by creating a database table with encryption:
```
mysql -u root -p=123456
```
We will need to create and use a new database. We will call this database encryption_test:
```
CREATE DATABASE encryption_test;
```
Now we need to USE the encryption_test database:
```
USE encryption_test;
```
Let's create a table, tab1, in the encryption_test database:
```
CREATE TABLE tab1 (
   id int PRIMARY KEY,
   str varchar(50)
);
```
Challenge

Examining the Encrypted Database Table
Let's take a look at the new table, tab1:
```
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME='encryption_test/tab1';
```
We now have a database table, tab1, in the encryption_test database that is encrypted!
Challenge

Creating a Non-Encrypted Database Table
Let's try creating a database table that's not encrypted:
```
CREATE TABLE tab2 (
   id int PRIMARY KEY,
   str varchar(50)
) ENCRYPTED=NO;
```
Let's take a look at the new table, tab2:
```
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME='encryption_test/tab2';
```
We've created a table with no encryption called tab2. Note that the ENCRYPTION_SCHEME is set to 0.
Challenge

Forcing Encryption
What if we want to force our DBAs to use encryption? We can do that by setting the innodb_encrypt_tables variable to FORCE. We can do this in the 50-server.cnf configuration file to make it permanent, but let's set it in the MariaDB client so we can try creating an unencrypted table with encryption forced without having to cycle the server:
```
SET GLOBAL innodb_encrypt_tables='FORCE';
```
This will force encryption.
Challenge

Displaying the InnoDB Configuration
If we want to see all the innodb variables, we can use the following in the MariaDB client:
```
SHOW VARIABLES LIKE '%innodb%';
```
This should show all of the variables and their values. If we'd like to see the variables that relate to encryption, use:
```
SHOW VARIABLES LIKE '%innodb_enc%';
```
Challenge

Attempt to Create a Non-Encrypted Table
Let's attempt to create an unencrypted table:
```
CREATE TABLE tab3 (
   id int PRIMARY KEY,
   str varchar(50)
) ENCRYPTED=NO;
```
We aren't allowed to create an unencrypted table.

Challenge

Attempt to Create an Encrypted Table

Let's try the same command again, with encryption:

CREATE TABLE tab3 (
   id int PRIMARY KEY,
   str varchar(50)
) ENCRYPTED=YES;

Now we can check out the details on the tab3 table:

SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE NAME='encryption_test/tab3';

Exit the MariaDB client:

quit

About the author

Pluralsight Skills

Pluralsight Skills gives leaders confidence they have the skills needed to execute technology strategy. Technology teams can benchmark expertise across roles, speed up release cycles and build reliable, secure products. By leveraging our expert content, skill assessments and one-of-a-kind analytics, keep up with the pace of change, put the right people on the right projects and boost productivity. It's the most effective path to developing tech skills at scale.

Real skill practice before real-world application

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Learn by doing

Engage hands-on with the tools and technologies you’re learning. You pick the skill, we provide the credentials and environment.

Follow your guide

All labs have detailed instructions and objectives, guiding you through the learning process and ensuring you understand every step.

Turn time into mastery

On average, you retain 75% more of your learning if you take time to practice. Hands-on labs set you up for success to make those skills stick.