---

Datadog

---

VI - Database monitoring

a - What is a database (Reminder)

A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it contains.

Other types of data stores can also be used, such as files on the file system or large in-memory hash tables, but retrieving and writing data would not be as fast and easy with these types of systems.

Nowadays, we use relational database management systems (RDBMS) to store and manage huge volumes of data. This is called a relational database because all the data is stored in different tables and relationships are established using primary keys or other keys called foreign keys.

A relational database management system (RDBMS) is software that -

• Allows you to implement a database with tables, columns and indexes.
• Guarantees referential integrity between rows in different tables.
• Updates indexes automatically.
• Interprets a SQL query and combines information from different tables.

b - RDBMS terminology (Reminder)

Before continuing to explain the MySQL database system, let's review a few database-related definitions.

• Database - A database is a collection of tables, with related data.

• Table - A table is a matrix with data. A table in a database looks like a simple spreadsheet.

• Column - A column (data element) contains data of one and the same type, for example the zip code column.

• Row - A row (= tuple, entry or record) is a group of related data, for example subscription data.

• Redundancy - Storing data twice, redundantly to make the system faster.

• Primary key - A primary key is unique. A key value cannot appear twice in a table. With one key, you can only find one row.

• Foreign key - A foreign key is the linking pin between two tables.

• Compound key - A compound key (composite key) is a key composed of several columns, as one column is not sufficiently unique.

• Index - An index in a database is like an index at the back of a book.

• Referential integrity - Referential integrity ensures that a foreign key value always points to an existing row.

c - MySQL database (Reminder)

MySQL is a fast, easy-to-use RDBMS used by many small and large companies. MySQL is developed, marketed and supported by MySQL AB, a Swedish company. MySQL is becoming so popular for many good reasons -

• MySQL is released under an open-source license. So you don't have to pay anything to use it.

• MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.

• MySQL uses a standard form of the well-known SQL data language.

• MySQL runs on many operating systems and with many languages, including PHP, PERL, C, C++, JAVA, etc.

• MySQL works very fast and performs well even with large data sets.

• MySQL is very user-friendly with PHP, the most popular language for Web development.

• MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4 GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).

• MySQL is customizable. The open source GPL license allows programmers to modify MySQL software to suit their own specific environments.

d - Installing MYSQL

The version of MySQL available in the Ubuntu repository by default is version 8.0.27. We're going to install Mysql on our server using the apt package manager. To install it, let's update the list of packages on our server:

sudo apt update

Next, let's install the mysql-server packages:

sudo apt install mysql-server -y

We can now configure the service so that it can launch and start by passing it the --now flag, which means that we want it started right away:

sudo systemctl enable --now mysql.service

We need to strengthen our Mysql instance. To improve the security of the Mysql database engine, we need to run the shell script mysql_secure_installation as follows:

sudo mysql_secure_installation

Output display:

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No y

Mysql asks us from the outset to validate the password complexity control during our configurations. We click on the y key on our keyboard and then press enter. Once done we need to configure our password complexity level.

Output display:

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

We click the 2 key on our keyboard to set a strong password. We'll then be prompted to set a password for the root user.

Output display:

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL Mysql
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into Mysql to secure it, we'll need the current
password for the root user. If you've just installed Mysql, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none)

The script guides us through a series of information inputs that will ask us to modify security options involving the Mysql database engine.

It prompts us to fill in the current root password, we'll enter the password Datascientest2023Datadog!.

Output display:

OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the Mysql root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] y

The next option is to define the root password or use unix_socket to ensure that nobody can connect with the Mysql root user without the appropriate authorization.

We've already set a password for the root user so we can safely reply n.

Output display:

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n]

Then we can answer n, as we no longer need to change the root user password we just defined.

Output display:

Change the root password? [Y/n] n
 ... skipping.

By default, a Mysql installation has an anonymous user, allowing anyone
to log into Mysql without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y

This step consists of defining whether we want to prohibit an anonymous connection to our database management system. For security reasons, we must forbid any anonymous connection. We therefore need to enter the value y.

Output display:

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y

This step consists in disallowing any remote login as user root on our system . For security reasons, we need to disallow remote connections for user root. We therefore enter the value y.

Output display:

Disallow root login remotely? [Y/n] y
 ... Success!

By default, Mysql comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y

This step asks if we want to remove the test database. It's advisable to delete this database for the simple reason that we won't need it once in our production server. This allows us to limit the possibilities of intrusion on our server. We therefore enter the value y to confirm.

Output display:

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y

The last step is to reload the privilege table so that changes can take effect immediately. We therefore enter the value y. Output display:

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your Mysql
installation should now be secure.

Thanks for using Mysql!

We've successfully completed the initial security configuration for Mysql. Let's see how to use it in the next part.

e - Monitoring Mysql with an agent installed on the host machine.

Let's now set up monitoring of our Mysql instance using Datadog. To do this, we can click on the Integrations menu and the Integrations submenu . We can then select Mysql from the list of agents we want to install . We can enter Mysql in the search bar to return the Mysql integration.

We can click on the Mysql configure button to set up the configuration for `Mysql.

MySQL is included within the Datadog Agent. No additional installation is required on our MySQL server. We do, however, need to prepare Mysql so that the Datadog Agent can have rights within the databases.

On each MySQL server, let's create a database user for the Datadog Agent.

Let's create the userdatadog with the following command:

sudo -i # pass root as the current user
mysql -u root -p # give your password
CREATE USER 'datadog'@'%' IDENTIFIED WITH mysql_native_password by 'Datascientest2023@';

Output display:

Query OK, 0 rows affected (0.00 sec)

Let's check that the user has been successfully created using the following commands:

FLUSH PRIVILEGES;
exit
mysql -u datadog --password=Datascientest2023@ -e "show status" | \
grep Uptime && echo -e "\033[0;32mMySQL user - OK\033[0m" || \
echo -e "\033[0;31mCannot connect to MySQL033[0m"

Output display:

mysql: [Warning] Using a password on the command line interface can be insecure.
Uptime 7590
Uptime_since_flush_status 7590
MySQL user - OK

The agent needs some privileges to collect metrics. Grant the datadog user only the following limited privileges.

let's grant the replication client right and define a maximum number of connections for our Datadogmaxuserconnections` user with the following commands:

mysql -u root -p # enter your password
GRANT REPLICATION CLIENT ON *.* TO 'datadog'@'%';
ALTER USER 'datadog'@'%' WITH MAX_USER_CONNECTIONS 5;

Output display:

Query OK, 0 rows affected (0.00 sec)

Let's grant user datadog process privileges:

GRANT PROCESS ON *.* TO 'datadog'@'%';

Output display:

Query OK, 0 rows affected (0.01 sec)

Let's check the replication client.

exit
mysql -u datadog --password=Datascientest2023@ -e "show slave status" && \
echo -e "\033[0;32mMySQL grant - OK\033[0m" || \
echo -e "\033[0;31mMissing REPLICATION CLIENT grant\033[0m"

Output display:

mysql: [Warning] Using a password on the command line interface can be insecure.
MySQL grant - OK

If this option is enabled, metrics can be collected from the performance_schema database by granting an additional privilege:

mysql -u root
GRANT SELECT ON performance_schema.* TO 'datadog'@'%';
show databases like 'performance_schema';

Output display:

+-------------------------------+
| Database (performance_schema) |
+-------------------------------+
| performance_schema |
+-------------------------------+
1 row in set (0.00 sec)

We're going to allow our datadog user to set up selection operations on this database as follows:

GRANT SELECT ON performance_schema.* TO 'datadog'@'%';

Output display:

Query OK, 0 rows affected (0.00 sec)

We can now configure our agent to send MYSQL metrics to our Datadog instance. Let's set up our configurations in the Datadog agent's configurations:

cd /etc/datadog-agent/conf.d/mysql.d/ #move into the mysql configuration for the datadog agent
sudo cp conf.yaml.example conf.yaml # copy an example of configuration for the agent
sudo nano conf.yaml #edit the file

We need to modify the conf.yaml file and insert the following content:

init_config:

instances:
  - server: 127.0.0.1 # the Mysql host
    user: datadog # user created in our database
    password: "Datascientest2023@" # from the CREATE USER step earlier
    port: "3306" # e.g. 3306 # the default for Mysql
    options:
      replication: false
      galera_cluster: true
      extra_status_metrics: true
      extra_innodb_metrics: true
      extra_performance_metrics: true
      schema_size_metrics: false
      disable_innodb_metrics: false

      # we activate or not feature needed for Mysql

once editing is complete, we can restart the Datadog agent as follows:

sudo systemctl restart datadog-agent

Some explanations about the values in the conf.yaml

file.

The extra_performance_metrics and schema_size_metrics options trigger heavier queries on our database, so we may experience performance impacts if we enable these options on servers with a large number of schemas or tables. Therefore, we'll certainly want to test these options on a limited basis before deploying them in production.

Other options include:

• extra_status_metrics: this option extends the set of server status variables reported to Datadog.
• extra_innodb_metrics: this option collects over 80 additional metrics specific to the InnoDB storage engine.
• replication: this option collects basic metrics (such as replica offset) on MySQL replicas.

To change the default behavior of any of the optional checks, we can simply uncomment the relevant lines in the configuration file. Once this has been done, we need to restart the Datadog agent.

Activating MYSQL log tracking

MySQL is supplied to us with an initial configuration. this configuration stores logs within the /var/log/syslog file by default. We must also have access with a privileged user such as root to be able to consult it. We can, however, change this to keep our configuration more user-friendly. We can now implement these various actions. We need to do the following:

• Edit the /etc/mysql/conf.d/mysqld_safe_syslog.cnf file and delete or comment out the lines.

 sudo nano /etc/mysql/conf.d/mysqld_safe_syslog.cnf
  

• Edit file /etc/mysql/my.cnf

 sudo nano /etc/mysql/my.cnf
  

• add the following lines to enable general, error and slow query logs:

 [mysqld_safe]
  log_error = /var/log/mysql/mysql_error.log

  [mysqld]
  general_log = on
  general_log_file = /var/log/mysql/mysql.log
  log_error = /var/log/mysql/mysql_error.log
  slow_query_log = on
  slow_query_log_file = /var/log/mysql/mysql_slow.log
  long_query_time = 2

The file should have the following contents:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
--print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

  [mysqld_safe]
  log_error = /var/log/mysql/mysql_error.log

  [mysqld]
  general_log = on
  general_log_file = /var/log/mysql/mysql.log
  log_error = /var/log/mysql/mysql_error.log
  slow_query_log = on
  slow_query_log_file = /var/log/mysql/mysql_slow.log
  long_query_time = 2

• Save the file and restart MySQL with the following command:

 sudo systemctl restart mysql
  

Log collection is disabled by default in the Datadog Agent, let's enable it in our /etc/datadog-agent/datadog.yaml file:

sudo nano /etc/datadog-agent/datadog.yaml

and add the following line to it:

logs_enabled: true

Let's modify the Datadog agent configuration to configure log harvesting:

sudo nano /etc/datadog-agent/conf.d/mysql.d/conf.yaml

Let's add the following content to the file and insert the following code to start harvesting our MySQL logs:

#Log section
logs:

    # - type : (mandatory) type of log input source (tcp / udp / file)
    # port / path : (mandatory) Set port if type is tcp or udp. Set path if type is file
    # service : (mandatory) name of the service owning the log
    # source : (mandatory) attribute that defines which integration is sending the log
    # sourcecategory : (optional) Multiple value attribute. Can be used to refine the source attribute
    # tags: (optional) add tags to each log collected

  - type: file
    path: /var/log/mysql/mysql_error.log
    source: mysql
    sourcecategory: database
    service: myapplication

  - type: file
    path: /var/log/mysql/mysql-slow.log
    source: mysql
    sourcecategory: database
    service: myapplication

  - type: file
    path: /var/log/mysql/mysql.log
    source: mysql
    sourcecategory: database
    service: myapplication
    # For multiline logs, if they start by the date with the format yyyy-mm-dd uncomment the following processing rule
    # log_processing_rules:
    # - type: multi_line
    # name: new_log_start_with_date
    # pattern: \d{4}\-(0?[1-9]|1[012])\-(0?[1-9]|[12][0-9]|3[01])

We can now restart the Datadog agent.

sudo systemctl restart datadog-agent

We can now go to our Datadog account interface. We can click on the Dashboard menu and the Dashboard List submenu:

We have in the list of dashboards a dashboard named Mysql. We can click on it to display it:

We arrive on the dashboard within which we can notice several available metrics.

We can try connecting to our Mysql server with the wrong password to check what Datadog returns.

mysql -u datascientest -p # the user datascientest does not exist

Output display:

Enter password:
ERROR 1045 (28000): Access denied for user 'datascientest'@'localhost' (using password: YES)

Now let's wait a few seconds and check our MySQL dashboard.

We can check that there has been a connection failure event. Let's place the cursor on this tile and click on the enlarge icon to get more details on the metric.

We do indeed have a peak of failed connections occurring on our MySQL instance as our metric reveals.

We can also check access to our MySQL Logs.

Let's click on the log menu .

Let's then click on the full observability menu and finally on the get started button.

We now arrive at a page that asks us to define the source of our logs. As we can see, Datadog supports a wide range of tools in both cloud and on-premise environments ( Kubernetes, Docker, Apache, Nginx, Consul, MongoDB, etc). We can select MySQL from this list.

f - Monitoring MySQL on a kubernetes agent

Collecting metrics

It's also possible to integrate metrics retrieval from a MySQL Pod deployed within a Kubernetes cluster by leveraging the Kubernetes Datadog agent. We'll simply need to define annotations in our pod to configure our Mysql Pod. We can create a file named mysql.yaml. We can write our Pod's Manifest as follows:

apiVersion: v1
kind: Pod
metadata:
  name: mysql
  annotations:
    ad.datadoghq.com/mysql.checks: |
      {
        "mysql": {
          "instances": [
            {
              "server": "%%host%%", # host of the database
              "username": "datadog", # username used by the datadog agent
              "password": "<UNIQUEPASSWORD>" # password of the datadog user on the database
            }
          ]
        }
      }
  labels:
    name: mysql
spec:
  containers:
    - name: mysql

Next we need to apply our Pod configuration file:

kubectl apply -f mysql.yaml

Once this is done, we can now view our MySQL Pod metrics within our Datadog instance.