DEV Community: budiantoip

Configure Email Clients

budiantoip — Tue, 28 May 2024 09:50:35 +0000

This part will guide you on how to configure email clients, such as Mozilla Thunderbird, Apple Mail, and Microsoft Outlook. Further details will be explained in each section.

IMAP or POP

Before we begin, since we will be presented with the option to choose between IMAP and POP when configuring mail clients, let us review the differences between the two.

IMAP

Server-Based Storage

Emails are stored on the mail server and synchronized across all devices.
Actions like reading, deleting, or organizing emails are mirrored on all devices.

Accessibility:

Allows access to email from multiple devices (computers, smartphones, tablets) while keeping everything synchronized.
Ideal for users who check their email from various locations or devices.

Real-Time Updates:

Changes made in the mail client are reflected immediately on the server and other devices.
Supports folder organization and maintains the structure across devices. #### Offline Access:
You can choose to download emails for offline access, but changes made offline will be updated on the server once reconnected.

Space Management:

Email is stored on the server, potentially occupying server space if not managed properly.
Typically used by those with large mail quotas or those who manage emails regularly.

POP

Local Storage:

Emails are downloaded from the server to a single device and typically deleted from the server (though some settings allow copies to remain on the server).
Actions on one device (like deleting or moving emails) do not affect other devices.

Single-Device Focus:

Best suited for users who access their email from one device.
Less suitable for multi-device access since synchronization across devices is not inherently supported.

Offline Access:

Since emails are downloaded and stored locally, they are accessible offline.
Changes made (like organizing or deleting emails) are local to that device only.

Space Management:

Frees up server space since emails are typically removed from the server after download.
Useful for users with limited server storage or who prefer keeping a local archive.

Which One to Choose?

IMAP is generally recommended if you:
- Access your email from multiple devices.
- Want real-time synchronization of your email and folders across all devices.
- Prefer server-based storage and management of your emails.
POP might be suitable if you:
- Mainly access your email from a single device.
- Prefer to download and store emails locally.
- Want to minimize server storage usage.

Mozilla Thunderbird

Mozilla Thunderbird is free and open-source email client software that also functions as a full personal information manager with a calendar and contact book and an RSS feed reader, chat client, and news client.

To download the application file, head over to this page and then click the Download button at the top right. Once downloaded, install the application.

To configure email on Mozilla Thunderbird, follow these steps:

Open Mozilla Thunderbird.
Open the Account Settings, and add a Mail Account.
You will be presented with this form:

Enter your full name, email address, and password.
Ensure the Remember password option is checked.
Click continue and the application will try to get the email server configuration automatically.
If the process runs successfully, you will see a message like this:
You will be presented with two configurations, IMAP and POP3.
Once you choose the config you want, click Done.
After that you will be presented with the "Account successfully created" message.
Click Finish, and you will be redirected to your email inbox. From there you can send and receive emails.

Apple Mail

Apple Mail is available on MacOS.

To configure email on apple mail, follow these steps:

Open the apple mail application.
Click the Mail menu on top left, near the apple icon. Then click Accounts.
An "Internet Accounts" window will be opened.
Click the Add Account button at the bottom, then choose Add Other Account...
Choose Mail account.
You will be presented with a form like this:
Enter your name, email address, and password. Then click Sign in
After that, the form will look like this:
Pick an Account Type, IMAP or POP
Enter mail.domain.com on both Incoming and Outgoing Mail Servers.
Once done, click Sign in again.
It might take a while for it to process. Once done, open up your Apple Mail application, and you will see your mailbox there. From there you can send and receive emails.

Microsoft Outlook

Microsoft Outlook can be obtained by purchasing a Microsoft 365 subscription. For more information, refer to this page.

To configure email on Microsoft Outlook, follow these steps:

Open Microsoft Outlook.
Go to Settings.
Choose Accounts.
Add an account.
Enter your email address.
Choose IMAP/POP as the provider.
The form will look like this:
Important! Remove @domain.com from Username.
Enter your email password.
Type mail.domain.com as both Incoming and Outgoing Servers.
Click Add Account.
You will then see a message like this:

email-test@domain.com has been added
Close the window and go back to your Microsoft Outlook.
You should now be able to send and receive emails.

How to Build Email Server with Exim on Alma Linux 9

budiantoip — Sat, 20 Apr 2024 07:59:22 +0000

Cloud Provider Consideration

Before we continue, be mindful when choosing the cloud provider in which your email server will be launched. Not all cloud providers open the email-sending port, e.g., 25, hence blocking your email-sending capabilities. You will need to request them to open the port, and they will ask for some information before they can approve your request. Some providers I have known like these close their email-sending port:

Digital Ocean
Vultr
Linode
AWS

You can host your email server at Contabo or Kamatera or even build it on your on-premise server.

Server Consideration

Installing from a new server is highly recommended, as it will avoid any unnecessary dependency conflict.

Screen Installation

First, we will install the screen package. The package is helpful if we get disconnected during the build session, we can reconnect to the session and continue what we left off.



sudo dnf install -y epel-release
sudo dnf install -y screen

Now, start a screen session.



screen -U -S email-server-build

Later, when you get disconnected from the ssh session, run this command to resume the session:



screen -R

To list active screen sessions, run this command:



screen -ls

To terminate current screen session, run:



exit

Setup Hostname

We will set up a hostname to identify the server uniquely.



hostnamectl set-hostname <server_name>

For example:



hostnamectl set-hostname mail.domain.com

Then, verify the change by running this command:



hostname

The output should look like this:



mail.domain.com

Next, update the hosts file:



vim /etc/hosts

Change an entry like this:



x.x.x.x vmi123456.server.net vmi123456

Note that, x.x.x.x is the server's IP address.
To this:



x.x.x.x vmi123456.server.net vmi123456 mail.domain.com

Reboot the server to apply the changes.



shutdown -r now

System Update and Dependencies Installation

We need to update the existing packages.



sudo dnf update

Then, install vim as our text editor. You can install your own preferred text editor, e.g. nano. And, install tar and socat as they are required by acme.sh.



sudo dnf install vim tar socat -y

Acme.sh Installation

Next, we will install acme.sh, a command-line tool for managing SSL/TLS certificates. I prefer acme.sh over certbot, as it does not depend on the OS version. For more details about acme.sh, check its GitHub repo here.

Next, install acme.sh.



curl https://get.acme.sh | sh -s email=my@example.com

Note that change my@example.com accordingly.
Create an alias for the acme.sh command so we can run acme.sh directly without specifying its full path.



echo 'alias acme.sh="/root/.acme.sh/acme.sh"' >> ~/.bash_aliases
source ~/.bash_aliases

Before we issue an SSL certificate, we must configure the DNS record properly. I will use mail.domain.com in this tutorial, and its A and MX records has already been configured. Refer to the DNS Record Configuration section at the end of this article to get more details.

After that, let us start issuing a staging SSL certificate. This certificate cannot be used on production servers and is used primarily to test certificate generation. Note that generating a live LetsEncrypt certificate is limited to 50 per week, for more info check this documentation. If it works, we can continue issuing a live SSL certificate.

Before we issue the SSL certificate, let us create a folder to store the generated certificates.



mkdir -p /etc/pki/tls/certs/staging
mkdir -p /etc/pki/tls/certs/live

To issue a staging SSL certificate, run this command:



acme.sh --issue \
    -d "mail.domain.com" \
    --cert-home /etc/pki/tls/certs/staging \
    --standalone \
    --debug \
    --staging

Some notes:

-d: the domain name we want to issue the certificate for
--cert-home: the folder path where we want the generated certificate files to be stored.
--standalone: this assumes we do not have a web server capable of serving web files, e.g. apache or nginx
debug: display all information. Useful for tracing issues
staging: generate a staging certificate.

If things go well, you will see an output like this:



[Sun Mar 24 04:08:19 UTC 2024] Your cert is in: /etc/pki/tls/certs/staging/mail.domain.com_ecc/mail.domain.com.cer
[Sun Mar 24 04:08:19 UTC 2024] Your cert key is in: /etc/pki/tls/certs/staging/mail.domain.com_ecc/mail.domain.com.key
[Sun Mar 24 04:08:19 UTC 2024] The intermediate CA cert is in: /etc/pki/tls/certs/staging/mail.domain.com_ecc/ca.cer
[Sun Mar 24 04:08:19 UTC 2024] And the full chain certs is there: /etc/pki/tls/certs/staging/mail.domain.com_ecc/fullchain.cer
[Sun Mar 24 04:08:19 UTC 2024] _on_issue_success

Now, let us issue a live certificate:



acme.sh --issue \
    -d "mail.domain.com" \
    --cert-home /etc/pki/tls/certs/live \
    --standalone \
    --debug

This time we removed the --staging parameter, and changed the certificate home folder.

The output will look like this:



[Sun Mar 24 04:35:12 UTC 2024] Your cert is in: /etc/pki/tls/certs/live/mail.mail.domain.com_ecc/mail.mail.domain.com.cer
[Sun Mar 24 04:35:12 UTC 2024] Your cert key is in: /etc/pki/tls/certs/live/mail.mail.domain.com_ecc/mail.mail.domain.com.key
[Sun Mar 24 04:35:12 UTC 2024] The intermediate CA cert is in: /etc/pki/tls/certs/live/mail.mail.domain.com_ecc/ca.cer
[Sun Mar 24 04:35:12 UTC 2024] And the full chain certs is there: /etc/pki/tls/certs/live/mail.mail.domain.com_ecc/fullchain.cer
[Sun Mar 24 04:35:12 UTC 2024] _on_issue_success

Now, we need to change the future SSL generation configured via crontab. Run this command:



EDITOR=vim crontab -e

Replace the following cron:



11 16 * * * "/root/.acme.sh"/acme.sh --cron --home "/root/.acme.sh" > /dev/null

With:



11 16 * * * /root/.acme.sh/acme.sh --cron --home /root/.acme.sh --cert-home /etc/pki/tls/certs/live --standalone > /dev/null

Configure Firewall

We will use firewalld to protect our email server. First, install the firewalld package.



sudo dnf install -y firewalld

Then, enable and start firewalld on server startup.



sudo systemctl enable firewalld
sudo systemctl start firewalld

Now, configure firewall rules.



sudo firewall-cmd --zone=public --permanent --add-service=http
sudo firewall-cmd --zone=public --permanent --add-service=https
sudo firewall-cmd --zone=public --add-service=pop3 --permanent
sudo firewall-cmd --zone=public --add-service=pop3s --permanent
sudo firewall-cmd --zone=public --add-service=smtp --permanent
sudo firewall-cmd --zone=public --add-service=smtps --permanent
sudo firewall-cmd --zone=public --add-service=imap --permanent
sudo firewall-cmd --zone=public --add-service=imaps --permanent
sudo firewall-cmd --reload

Validate the result by checking the configured firewall rules.



sudo firewall-cmd --zone=public --list-all

The output should look like this:

Exim Installation and Configuration

We will install Exim, a mail transfer agent used to deliver and receive emails.



sudo dnf install -y exim

Before configuring exim, we need to back up the existing config file.



cp -p /etc/exim/exim.conf{,.orig}

This will copy exim.conf to exim.conf.orig.

After that, we can safely configure exim.



vim /etc/exim/exim.conf

Adjust the existing exim configuration so they look like these:



primary_hostname = mail.domain.com
domainlist local_domains = @ : domain.com
tls_advertise_hosts = *
tls_certificate = /etc/pki/tls/certs/live/mail.domain.com_ecc/mail.domain.com.cer
tls_privatekey = /etc/pki/tls/certs/live/mail.domain.com_ecc/mail.domain.com.key
auth_advertise_hosts = *

Next, find ROUTERS CONFIGURATION section, and then find localuser: block.

Adjust the whole block so it will look like these:



localuser:
  driver = redirect
  check_local_user
  file_transport = local_delivery
  reply_transport = local_delivery_autoreply
  data = /home/${local_part_data}/Maildir

Next, find the TRANSPORTS CONFIGURATION section and then find local_delivery: block.

Adjust the whole block so it will look like this:



local_delivery:
  driver = appendfile
  directory = $home/Maildir
  maildir_format
  maildir_use_size_file
  delivery_date_add
  envelope_to_add
  return_path_add

And add this block right after the local_delivery: block.



local_delivery_autoreply:
   driver = autoreply
   headers = Content-Type: text/plain; charset=utf-8\nContent-Transfer-Encoding: base64
   to = ${sender_address}
   from = ${$local_part}@${$domain}
   debug_print = "T: auto reply for $local_part@$domain"
   once_file_size = 500K
   log = /var/log/exim/sieve_autoreply.log

The autoreply transport will generate a new email message as an automatic reply to the incoming message. It is useful to generate an automatic email when we are on a vacation or out-of-office. We will utilize this later in Roundcube. For more details on autoreply, refer to the doc here.

Next, find AUTHENTICATION CONFIGURATION section.

Then, add these blocks at the end of the section.



dovecot_login:
  driver = dovecot
  public_name = LOGIN
  server_socket = /var/run/dovecot/auth-client
  server_set_id = $auth1

dovecot_plain:
  driver = dovecot
  public_name = PLAIN
  server_socket = /var/run/dovecot/auth-client
  server_set_id = $auth1

Save all the changes and then quit.

Start exim service, and automatically start the service on server startup.



sudo systemctl start exim
sudo systemctl enable exim
sudo systemctl status exim

Test the local_delivery transport and ensure there is no warning or error message.



exim -d+all -bP transport local_delivery

Next, change the certificate folder's ownership so that Exim will be able to access the certificates later.



chown -R exim:exim /etc/pki/tls/certs/staging
chown -R exim:exim /etc/pki/tls/certs/live

Dovecot Installation and Configuration

We will use dovecot, an open-source IMAP and POP3 server. It takes responsibility for connecting your email client (Thunderbird, etc.) to your mail box.

Run this command to install dovecot.



sudo dnf install -y dovecot

SSL Configuration

Configure SSL in dovecot.



vim /etc/dovecot/conf.d/10-ssl.conf

Then adjust the existing config to this:



ssl = yes
ssl_cert = </etc/pki/tls/certs/live/mail.domain.com_ecc/fullchain.cer
ssl_key = </etc/pki/tls/certs/live/mail.domain.com_ecc/mail.domain.com.key

Note that, ensure the file paths are prefixed by <. Otherwise, it will trigger an error.

Plain Authentication

Allow plain authentication in dovecot.



vim /etc/dovecot/conf.d/10-auth.conf

Adjust the existing config.



disable_plaintext_auth = no
auth_mechanisms = plain login

Mailbox Location

Configure mailbox location.



vim /etc/dovecot/conf.d/10-mail.conf

Adjust the existing config.



mail_location = maildir:~/Maildir

Allow Exim to Authenticate

Set up dovecot to allow Exim to use its authentication system.



vim /etc/dovecot/conf.d/10-master.conf

Find the unix_listener auth-userdb block.

Then, fully comment the block, and add this block beneath it.



unix_listener auth-client {
    mode = 0660
    user = exim
}

The final changes should look like this:

Start dovecot service, and automatically start the service on server startup.



sudo systemctl start dovecot
sudo systemctl enable dovecot
sudo systemctl status dovecot

Create an Email Address

We will create an email address, say email-test@domain.com.



useradd -m email-test

Set its password.



passwd email-test

Create info user (optional).



useradd -m info
passwd info

Note that, when sending an email to info@domain.com, you might encounter a problem, this is because info@domain.com is considered a root user. To fix the issue, remove it from the aliases list.



vim /etc/aliases

Then, comment this line by prefixing the line with #.



# info:          postmaster

Test the email user routing, and ensure there is no error or warning message.



exim -bt email-test@domain.com

The output should look like this:



email-test@domain.com -> /home/email-test/Maildir
  transport = local_delivery

And the other user.



exim -bt info@domain.com

The output should look like this:



info@domain.com -> /home/info/Maildir

  transport = local_delivery

DNS Record Configuration

Before we can send and receive emails, ensure to have these records on your DNS Control Panel:
A record:

Change 1.2.3.4 with the IP Address of your email server.

MX record:

The MX record indicates it will point to your email server.

Introduction

budiantoip — Sat, 20 Apr 2024 07:58:18 +0000

This workshop will guide you through building an email server, powered with Exim and Dovecot on top of AlmaLinux 9.

The workshop will be divided into parts, and they are:

Part 1: Build the email server

We will build the email server from scratch here.
Part 2: Configure email clients

We will configure email clients to send and receive emails. We will configure Mozilla Thunderbird, Apple Mail, and Microsoft Outlook.
Part 3: Configure webmails

We will configure several webmails. We will install and configure RoundCube.
Part 4: Configure spam protection

We will install and configure SpamAssassin.
Part 5: Configure Email Deliverability

We will configure SPF, DKIM, and DMARC records to increase email deliverability.

Some parts require some time to write since I have to work on them in my spare time. But hopefully, I can finish all of them soon.

How to Setup TLS connection in SQL Anywhere 11

budiantoip — Thu, 21 Dec 2023 09:20:41 +0000

In this article, we will learn how to setup a TLS connection in SQL Anywhere 11.
The connection will be like this:

A client device, e.g. laptop, connects to a server. Communications between the two will be encrypted and sent over a TLS protocol.

Requirements

Both client and server use Windows Operating System
The SQL Anywhere 11 is installed on both
A telnet utility is installed on the client device

Boundary

The client device should be able to connect to the server. We will test it using the telnet utility
I use SQL Anywhere 11 here, but the steps described here should be applicable to version 9+
The server should allow inbound connections to port 2638
The scenario has been tested on Windows 10

Server Setup

We will now setup the TLS connection on the server. We will do these:

Create a TLS certificate
Run DB server

1. Create a TLS certificate

Before going further, make sure the SQL Anywhere 11 has been installed on the server already. We will use the createcert utility here.

Also, we first need to create a certs folder. The certificate files created will be stored in this folder. To create the folder, follow these steps:

Open up Command Prompt
Type: mkdir c:\Users\Public\Documents\certs, and hit enter
Type: dir c:\Users\Public\Documents\certs, and hit enter. There should be no error message, and the folder contents will be displayed.

To create the TLS certificate, simply login to your server, and follow these steps:

Open up Powershell
Type cd "C:\Program Files (x86)\SQL Anywhere 11\Bin32" and then press enter
Type createcert.exe and then press enter. Note that, you may adjust the values accordingly.
1. Enter RSA key length (512-16384): 4096
2. Country Code: ID
3. State/Province: DKI Jakarta
4. Locality: Indonesian
5. Organization: My Organization
6. Organizational Unit: Engineering
7. Common name: my-organization.com
8. Enter file path of signer's certificate: (leave this empty, and hit enter)
9. Serial number [generate GUID]: (leave this empty, and hit enter)
10. Certificate valid for how many years (1-100): 1
11. Certificate Authority (Y/N) [N]: N
  1. Digital Signature
  2. Nonrepudiation
  3. Key Encipherment
  4. Data Encipherment
  5. Key Agreement
  6. Certificate Signing
  7. CRL Signing
  8. Encipher Only
  9. Decipher Only
12. Key Usage [3,4,5]: (leave this empty, and hit enter)
13. Enter file path to save certificate: C:\Users\Public\Documents\certs\my-organization.com.pem
14. Enter file path to save private key: C:\Users\Public\Documents\certs\my-organization.com.key
15. Enter password to protect private key: my-password
16. Enter file path to save identity: C:\Users\Public\Documents\certs\my-organization.com.id.pem

After this, 3 certificate files will be created in C:\Users\Public\Documents\certs, and they are:

my-organization.com.pem (we will use this on the client device later)
my-organization.com.id.pem (we will use this on the server later)
my-organization.com.key

2. Run DB server

Before we run the DB server, copy the SQL Anywhere database, e.g. DEMO2022.db to C:\Users\Public\Documents.
To run the DB server, follow these steps:

Open up Windows Explorer, then go to C:\Program Files (x86)\SQL Anywhere 11\Bin32
Find dbsrv11.exe, and double click it.
Once an application loaded, fill in the form as follow:
1. Database: C:\Users\Public\Documents\Demo2022.db
2. Server name: DEMO2022
3. Options: -ec TLS(TLS_TYPE=RSA;IDENTITY=c:\Users\Public\Documents\certs\my-organization.com.id.pem;IDENTITY_PASSWORD=my-password) -x tcpip
4. Hit the OK button The database server will now run at port 2638. If no error message is shown, the database server runs correctly.

Setup Client

Before going further, make sure the SQL Anywhere 11 has been installed on the server already.

Also, we will use telnet to test the client connection to the server. If the telnet has not been installed yet, open up Powershell (run it as Administrator), and then run this command:
Enable-WindowsOptionalFeature -Online -FeatureName TelnetClient

If you receive an error message when running the command, make sure you are connected to the internet, and then re-run the command.

Now let's assume each of the device's ip addresses are:

Client: 192.168.0.9
Server: 192.168.0.10

To test the client-to-server connection, Open up Command Prompt, and then run this command:
telnet 192.168.0.10 2638
Hit enter, and then you will see a blank output, it means the connection goes through and the client can connect to the server successfully. Otherwise, if you see an error message, fix it first until you can connect to the server successfully.

Now, we need to copy the certificate files we created on the server to the client device, specifically at C:\Users\Public\Documents\certs (create the certs folder first).

Now, we will setup the ODBC connection. Open up Start Menu, and then type "ODBC", and then click "ODBC Data Sources (64-bit)". Make sure you choose the 64-bit option. Once it is opened, follow these steps:

Hit the "System DSN" tab. We will create a system-wide ODBC configuration
Hit the "Add" button
Choose "SQL Anywhere 11". If the option does not appear, you need to install the SQL Anywhere 11 first
1. At the ODBC tab, fill in the following:
  1. Data source name: Demo
2. At the Login tab, if your database requires a login:
  1. Choose "Supply user ID and password"
  2. Fill in User ID and Password
  3. Tick the "Encrypt password" option
3. At the Database tab, fill in the following:
  1. Server name: Demo2022 (we specify this name when running the DB server before)
  2. Untick "Stop database after last disconnect". This will make sure the DB server will always run on the server
4. At the Network tab, fill in the following:
  1. Untick the "Shared memory" option. This option is enabled when we run the DB server locally
  2. Tick the "TCP/IP" option, and set the value to this: "HOST=192.168.0.10;PORT=2638"
  3. Click the "TLS" option, and then click the Edit button
    1. For the "Trusted certificates", click the Browse button and then point it to the my-organization.com.pem. If you cannot see the certificate files, choose the "All Files" option, and then you should be able to see them. Or, directly set the value to this: C:\Users\Public\Documents\certs\my-organization.com.pem
    2. TLS Type: RSA
5. Lastly, go back to the ODBC tab, and hit the "Test Connection" button. And you should see a "Connection successful" message.

Now, you can use the created ODBC Data Source in your application. Happy coding!

References

How to Enable Slow Query Logging on MariaDB

budiantoip — Mon, 20 Nov 2023 22:32:07 +0000

MariaDB is one of the most popular open source relational databases. It’s made by the original developers of MySQL and guaranteed to stay open source. It is part of most cloud offerings and the default in most Linux distributions.

Many websites use MariaDB to store its data. When a website is launched, it may run fast. But the data grows from time to time, especially when it's getting more traffic from day to day. There are times when websites become slow. Many factors can be involved, but one of them is because of slow queries. Slow queries happen when many, e.g. millions of records are in one or multiple tables.

Slow queries

Slow queries are queries that run more than a specific time limit, e.g. 5 seconds. Imagine if a webpage executes 10 queries, and each of them runs for at least 5 seconds. In addition to how many asset files are loaded, plus other processing, including but not limited to executing a third-party API call, sending email notifications, and lastly, imagine if you have thousands of people that are using your website, that 5 seconds will surely make your website look slow.

How to identify slow queries

MariaDB provides a way for us to log slow queries. In the previous example, we specified queries run at least 5 seconds are considered slow. MariaDB will log those slow queries to a log file or table. More instructions will be provided later in this article.

Before we begin

The instructions have been tested on MariaDB 10.7.8. The DDL file in this article uses the UUID data type, which is only available starting from this MariaDB version.

Prerequisites

Docker Engine or Docker Desktop installed
A sample of a large dataset is used to experiment with the slow queries. You can get it by cloning my repository here. Note that the dataset file size is 644 MB. It may take a while for the git clone to finish.
Once the repository is cloned, run this command on your terminal at the project level to initialize the container:
```
docker-compose up -d
```
It will take a while for the container to initialize as it has to extract the compressed dataset file, but you can monitor the process by running this command:
```
docker logs -f mariadb_slow_query
```
Wait until you read this line: Finished initializing the container
Once the container is fully initialized, you can run all the commands in this article inside the container. To get inside the container, run this command:
```
docker exec -it mariadb_slow_query /bin/bash
```

Slow query logging

There are 2 ways to enable slow query logging, and they are:

By modifying the MariaDB configuration file

You can use this approach to enable slow query logging permanently. Note that this approach requires us to restart the MariaDB service. Restarting MariaDB service can be dangerous. Imagine if visitors to your website are trying to make payments, and the payment processes are suddenly terminated. This may cause corrupted data stored in your database. And, slow query logging should be enabled temporarily, as it can make your disk space full in no time. When the disk space is fully occupied, it can corrupt your MariaDB, rendering it completely inoperable. So, be very super careful when taking this approach.
By modifying some of the MariaDB system variables

I prefer this approach because it does not require us to restart the MariaDB service. We will use this approach in this article.

Logging Type

There are two slow query logging types, and they are:

File logging

To enable slow query logging and store the result in a log file, follow these steps:
a. Login to MySQL console, and select the appropriate database

b. Run this query to find out which filename the slow query log will be written to:
```
SELECT @@slow_query_log_file;
```
If the output is a filename, not a fullpath, e.g. showing folder location, it means it will be written to a file, and the file by default is stored to /var/lib/mysql.

If we want to store the file to a new filename, we can run this query:
```
SET GLOBAL slow_query_log_file='slow.log';
```
The old file is preserved, and a new file will be created.

If we want to store the file in a specific folder, for example /var/log/mysql, we can run this query:
```
SET GLOBAL slow_query_log_file='/var/log/mysql/slow.log';
```
b. Next, we need to change the time limit. We can change the time limit to 1 second, which means any queries run at least 1 second will be considered slow queries. By default, it's configured to 10 seconds. We can change it by running this query:
```
SET global long_query_time = 1;
```
c. Lastly, we need to see whether the slow query logging has been enabled or not by running this command:
```
SELECT @@slow_query_log;
```
if it returns 0, it means it's disabled. We can enable it by running this query:
```
SET global slow_query_log = 1; 
```
To disable it back, run this query:
```
SET global slow_query_log = 0; 
```
Table logging
The slow queries can be recorded in a table by following these steps:
a. Login to MySQL console, and select the appropriate database

b. First, let's check the current logging type by running this query:
```
SELECT @@log_output;
```
By default, it will return FILE. We can change it to table logging, by running this query:
```
SET global log_output = 'table';
```
Note that the above query will be applied to slow and general query logging. General query logging will log all types of queries. By default, general query logging is disabled.
The slow query logging will be recorded to slow_query table within the mysql database. Meanwhile, the general query logging will be recorded to general_log table within the mysql database.
c. Change the time limit, e.g. 1 second. Any query that runs at least 1 second will be considered a slow query. We can change the time limit by running this command:
```
SET global long_query_time = 1;
```
d. Enable the slow query logging by running this query:
```
SET global slow_query_log = 1; 
```
To disable it back, run this query:
```
SET global slow_query_log = 0; 
```
e. Check the slow query log by running this query:
```
SELECT * FROM mysql.slow_log;
```

Run some tests

Now, we need to generate the slow query logs, either by running the website or by running some queries that are believed to be slow.
For example, using the above large dataset, run these queries:

SELECT u.DisplayName, c.* from askubuntu.comments c INNER JOIN askubuntu.posts p ON c.PostId = p.id INNER JOIN askubuntu.users u ON c.UserId = u.id Where Text Like '%aptitude%';

SELECT p.Title, u.DisplayName, p.ViewCount, p.AnswerCount, p.CommentCount FROM posts p INNER JOIN users u ON p.OwnerUserId = u.Id WHERE p.Title LIKE '%ubuntu%';

Now, let's check the slow query logs.
If you choose file logging, run this command:

tail -f LOG_FILE_LOCATION

Note that, change LOG_FILE_LOCATION accordingly, e.g. /var/log/mysql/slow.log.

If you choose table logging, run this query:

SELECT query_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log ORDER BY query_time DESC;

References

How to Reboot AWS Lightsail Instance for a Beginner (With Images)

budiantoip — Tue, 14 Nov 2023 13:00:19 +0000

This article is intended for non-technical people who want to know how to reboot an AWS Lightsail instance via the AWS Console.

AWS Lightsail is a Virtual Private Server (VPS) provider. Lightsail includes everything you need to launch your project quickly – virtual machines, containers, databases, CDN, load balancers, DNS management, etc. People love Lightsail because of its simplicity.

AWS Lightsail is a lightweight version of EC2, with limited features compared to EC2. It has a low and relatively stable monthly price. And it is a good entrance for beginners who want to use VPS on AWS.

AWS Console is a web application to navigate and manage AWS services.

To reboot your AWS Lightsail instance, follow these steps:

Login to the AWS console (https://console.aws.amazon.com/). After that, you will be redirected to the sign-in page. From here, you have two options, and they are:
1. Login as root. Enter your email, and then enter your password on the next page. Lastly, you might be asked to enter an MFA code.
2. Login as an IAM user. You can enter your Account ID here and then your IAM username and password on the next page. Lastly, you might be asked to enter an MFA code.
Once logged in, you will be redirected to the home page. There are two important sections here. The first is the "Search Bar". You can search for anything from here. The other is the "Recently Visited" section, listing any recently visited AWS services.
You can hit the Lightsail link in the "Recently Visited" section or type lightsail in the "Search Bar" and then hit the Lightsail result under the Services section.
Now, you will be redirected to the AWS Lightsail page. The page will show how many instances you have. In my case, it shows 2 instances, Staging and Production. We will reboot the Production instance, so click the "Production" instance and we will be redirected to the instance details page.
The instance details page shows detailed information regarding your instance. To reboot the instance, click the Reboot button on the top right.
It will take some time for the instance to finish rebooting. Wait about 3-5 minutes, then use this online tool to test your website reachability. The tool will try to load your website from different server locations.
Enter your website URL, and then hit the "Check Reachability" button. It may take some time for it to return results. If the result shows a green-colored message like this, it means your server has finished rebooting and your website is fully operational:

[Your website URL] is looking good! See below for more details.

References:

Feel free to connect with me on LinkedIn:
https://www.linkedin.com/in/budiantoip/

How to migrate a Mongo Database with Ansible Playbook

budiantoip — Tue, 31 Oct 2023 03:15:19 +0000

Recently, a client tasked me with migrating MongoDB from MongoDB Atlas to Digital Ocean managed MongoDB service. He had 2 reasons, one was to manage everything in Digital Ocean since the application server was in Digital Ocean, and two was to minimize the network latency, the client wanted to have the MongoDB in the same VPC as the application server.

Prerequisites

Database Tools (The package includes mongodump and mongorestore)
Ansible

Migration Concept

To migrate the mongo database, we will use the backup (mongodump) and restore (mongorestore) utilities. In my case, I ran both commands from my application server in Digital Ocean. We will then use the Ansible Playbook to create a template to run it as many times as we need. In my case, the client wanted to run it twice. First, he wanted me to migrate the DB to a staging environment, and once everything worked correctly, he asked me to migrate the DB to a production environment. Ansible Playbook also provides time calculation, giving you an idea of how long the live Mongodb migration will take.

Backup

We will back up the MongoDB from MongoDB Atlas using the mongodump utility.

Syntax and Example

To backup your existing database, the mongodump syntax will be like this:

mongodump --username DB_USERNAME --out=TARGET_FOLDER --config=mongodump.cfg

Note that:

username is the username we use to authenticate to our mongo DB.
out is the target folder to store our mongo DB backup.
config is the config file that will be used by mongodump.

The mongodump.cfg contains these configurations:

password: PASSWORD
uri: mongodb+srv://HOSTNAME.mongodb.net/DATABASE_NAME?retryWrites=true&w=majority

Change PASSWORD, HOSTNAME, and DATABASE_NAME accordingly. retryWrites=true&w=majority are connection parameters used by mongo DB Atlas, so leave them as they are.

Restore

We will restore the MongoDB to Digital Ocean managed mongo DB service using the mongorestore utility.

Syntax and Example

To restore to a database, the mongorestore syntax will be like this:

mongorestore --ssl --tlsInsecure --username USERNAME --config=mongorestore.cfg --nsInclude=DATABASE_NAME TARGET_FOLDER/DATABASE_NAME/

Note that:

ssl means we will connect to mongo DB via TLS/SSL.
tlsInsecure will disable the certificate validations.
config is the config file that will be used by mongorestore.
nsInclude specifies the namespace pattern. Since we will restore the whole database, we will use DATABASE_NAME (change this with your database name).

The mongorestore.cfg contains these configurations:

uri: mongodb+srv://HOSTNAME.mongo.ondigitalocean.com/DATABASE_NAME?authSource=admin
password: PASSWORD

Change PASSWORD, HOSTNAME, and DATABASE_NAME accordingly.
authSource=admin is a connection parameter used by Digital Ocean managed mongo DB service, so leave it as it is.

Ansible Playbook

We will now use Ansible Playbook. First create a file named playbook.yml, and then put these in the file:

# How to run: ansible-playbook playbook.yaml
-
  name: Migrate mongodb
  hosts: localhost
  tasks:
    - name: Remove backup folder if exists
      ansible.builtin.file:
        path: /root/mongorestore/backup
        state: absent
    - name: Mongodump from mongodb atlas
      shell: mongodump --username USERNAME --out=backup/mongodb --config=mongodump.cfg
    - name: Mongorestore to Digital Ocean managed database - mongodb
      shell: mongorestore --ssl --tlsInsecure --username USERNAME --config=mongorestore.cfg --nsInclude=DATABASE_NAME backup/mongodb/DATABASE_NAME/

Change USERNAME and DATABASE_NAME accordingly.
The ansible playbook contains 3 tasks, and they are:

The first task will remove the backup folder if it exists. Since we will run the ansible playbook many times, we need to do this to ensure there is no trace of the previous backup
The 2nd task will dump mongo DB
The last task will restore mongo DB

Next, we need to create a file named mongodump.cfg, and then put these in the file:

password: PASSWORD
uri: mongodb+srv://HOSTNAME.mongodb.net/DATABASE_NAME?retryWrites=true&w=majority

Remember to change PASSWORD, HOSTNAME, and DATABASE_NAME accordingly.

Then create a file named mongorestore.cfg, and then put these in the file:

uri: mongodb+srv://HOSTNAME.mongo.ondigitalocean.com/DATABASE_NAME?authSource=admin
password: PASSWORD

Lastly, we will configure Ansible to display the time calculation to know how much time we need to run all the 3 tasks. According to the doc here, there are 4 ways that we can configure (pick one), and they are:

The ANSIBLE_CONFIG environment variable
Create a file named ansible.cfg and put it in the same folder as the playbook.yml resides
~/.ansible.cfg, if you are logged in as root, the file will be stored in /root. If you are logged in another user, e.g. ubuntu, it will be stored in /home/ubuntu
Modify the file in /etc/ansible/ansible.cfg

Let's say we create a file named ansible.cfg. Once created, put these in the file:

[defaults]
callback_whitelist = profile_tasks, profile_roles, timer

We use callback_whitelist to allow some plugins to run during the playbook execution. For ansible version older than 2.0, refer to here to find out how to display the time calculation. profile_tasks, profile_roles, and timer will display a detailed information of the executed tasks.

Before we run the ansible playbook, we need to create a new terminal session so that when our SSH connection is accidentally closed, the command will continue to run in the background. Run this command:

screen -S mongodb_migration

To connect to the terminated terminal session, run this command:

screen -R

Once everything is fully setup, run the ansible playbook with this command:

ansible-playbook playbook.yml

The output will look like this:

root@docker-ubuntu:~/mongodb-migration# ansible-playbook playbook.yml
[WARNING]: provided hosts list is empty, only localhost is available. Note that the implicit localhost does not match 'all'

PLAY [Migrate mongodb] ****************************************************************************************************************************************************

TASK [Gathering Facts] ****************************************************************************************************************************************************
Tuesday 31 October 2023  03:00:31 +0000 (0:00:00.042)       0:00:00.042 *******
Tuesday 31 October 2023  03:00:31 +0000 (0:00:00.041)       0:00:00.041 *******
ok: [localhost]

TASK [Remove backup folder if exists] *************************************************************************************************************************************
Tuesday 31 October 2023  03:00:32 +0000 (0:00:01.770)       0:00:01.812 *******
Tuesday 31 October 2023  03:00:32 +0000 (0:00:01.770)       0:00:01.812 *******
changed: [localhost]

TASK [Mongodump from mongodb atlas] ***************************************************************************************************************************************
Tuesday 31 October 2023  03:00:33 +0000 (0:00:00.608)       0:00:02.420 *******
Tuesday 31 October 2023  03:00:33 +0000 (0:00:00.607)       0:00:02.420 *******
changed: [localhost]

TASK [Mongorestore to Digital Ocean managed database - mongodb] ***********************************************************************************************************
Tuesday 31 October 2023  03:04:34 +0000 (0:04:01.372)       0:04:03.793 *******
Tuesday 31 October 2023  03:04:34 +0000 (0:04:01.373)       0:04:03.793 *******
changed: [localhost]

PLAY RECAP ****************************************************************************************************************************************************************
localhost                  : ok=4    changed=3    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Tuesday 31 October 2023  03:09:43 +0000 (0:05:08.507)       0:09:12.300 *******
===============================================================================
shell ----------------------------------------------------------------- 549.88s
gather_facts ------------------------------------------------------------ 1.77s
ansible.builtin.file ---------------------------------------------------- 0.61s
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
total ----------------------------------------------------------------- 552.26s
Tuesday 31 October 2023  03:09:43 +0000 (0:05:08.508)       0:09:12.301 *******
===============================================================================
Mongorestore to Digital Ocean managed database - mongodb --------------------------------------------------------------------------------------------------------- 308.51s
Mongodump from mongodb atlas ------------------------------------------------------------------------------------------------------------------------------------- 241.37s
Gathering Facts ---------------------------------------------------------------------------------------------------------------------------------------------------- 1.77s
Remove backup folder if exists ------------------------------------------------------------------------------------------------------------------------------------- 0.61s

I have uploaded the ansible files to a repository here.

Feel free to connect with me on LinkedIn:
https://www.linkedin.com/in/budiantoip/