Datadog - Database's Monitoring
You have no instance yet for this lesson.
Launching new machine may take time.

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 Datadog
maxuserconnections` 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
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.
