Datadog Tutorials: Datadog Agent integration with mysql databases?

How to setup Datadog Agent integration with mysql to collect many metrics from MySQL databases?

The Datadog Agent can collect many metrics from MySQL databases, including (but not limited to):

• Query throughput
• Query performance (e.g. average query run time, slow queries, etc.)
• Connections (e.g. currently open connections, aborted connections, errors, etc.)
• InnoDB (e.g. buffer pool metrics, etc.)

You can also create your own metrics using custom SQL queries.

The following metrics will be tracked by this integration:

Step 1 – Install Datadog Agent in Centos OR Ubuntu OR Windows

The MySQL check is included in the Datadog Agent package. No additional installation is needed on your MySQL server.

Step 2 – Install and Configure Mysql Server in Centos

• How to Install MySQL on CentOS 7
• https://www.devopsschool.com/blog/how-to-install-mysql-server-and-client-on-ubuntu/
• Alternate – You may use XAMPP as well. How to install and Configure XAMPP on Ubuntu?
• How to Install & configure Xampp with mysql in Linux?

Step 3 – Prepare MySQL. On each MySQL server, create a database user for the Datadog Agent:

# How to login mysql server?
$ mysql -h localhost -u root -p

mysql> CREATE USER 'datadog'@'localhost' IDENTIFIED BY '<UNIQUEPASSWORD>';
Query OK, 0 rows affected (0.00 sec)

For mySQL 8.0+ create the datadog user with the native password hashing method:

mysql> CREATE USER 'datadog'@'localhost' IDENTIFIED WITH mysql_native_password by '<UNIQUEPASSWORD>';
Query OK, 0 rows affected (0.00 sec)

Note: @'localhost' is only for local connections - use the hostname/IP of your Agent for remote connections. For more information, see the MySQL documentation.

Verify the user was created successfully using the following commands - replace <UNIQUEPASSWORD> with the password you created above:

mysql -u datadog --password=<UNIQUEPASSWORD> -e "show status" | \
grep Uptime && echo -e "3[0;32mMySQL user - OK3[0m" || \
echo -e "3[0;31mCannot connect to MySQL3[0m"

mysql -u datadog --password=<UNIQUEPASSWORD> -e "show slave status" && \
echo -e "3[0;32mMySQL grant - OK3[0m" || \
echo -e "3[0;31mMissing REPLICATION CLIENT grant3[0m"

Step 4 – The Agent needs a few privileges to collect metrics. Grant the user the following limited privileges ONLY:

mysql> GRANT REPLICATION CLIENT ON *.* TO 'datadog'@'localhost' WITH MAX_USER_CONNECTIONS 5;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT PROCESS ON *.* TO 'datadog'@'localhost';
Query OK, 0 rows affected (0.00 sec)

For MySQL 8.0+ set max_user_connections with:

mysql> ALTER USER 'datadog'@'localhost' WITH MAX_USER_CONNECTIONS 5;
Query OK, 0 rows affected (0.00 sec)

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

mysql> show databases like 'performance_schema';
+-------------------------------+
| Database (performance_schema) |
+-------------------------------+
| performance_schema            |
+-------------------------------+
1 row in set (0.00 sec)

mysql> GRANT SELECT ON performance_schema.* TO 'datadog'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Step 5 – Configure Datadog agent to start collection mysql metrix.

Edit the mysql.d/conf.yaml file, in the conf.d/ folder at the root of your Agent’s configuration directory to start collecting your MySQL metrics and logs. See the sample mysql.d/conf.yaml for all available configuration options.

$ cd /etc/datadog-agent/conf.d/mysql.d/
$ sudo cp conf.yaml.example conf.yaml
$ sudo vi conf.yaml (CHECK IMAGE BELOW)
$ sudo service datadog-agent restart [UBUNTU]
$ sudo systemctl restart datadog-agent [Centos]


Metric collection
Add this configuration block to your mysql.d/conf.yaml to collect your MySQL metrics:

init_config:

instances:
  - server: 127.0.0.1
    user: datadog
    pass: "<YOUR_CHOSEN_PASSWORD>" # from the CREATE USER step earlier
    port: "<YOUR_MYSQL_PORT>" # e.g. 3306
    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
Note: Wrap your password in single quotes in case a special character is present.

Step 5 – Validate mysql metrices

$ systemctl restart datadog-agent

$ datadog-agent status

MariaDB changes

mysql> CREATE USER 'datadog'@'%' IDENTIFIED BY 'Raju$123456';

mysql> GRANT REPLICATION CLIENT ON *.* TO 'datadog'@'%'
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'datadog'@'%' WITH MAX_USER_CONNECTIONS 5;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT PROCESS ON *.* TO 'datadog'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show databases like 'performance_schema';
+-------------------------------+
| Database (performance_schema) |
+-------------------------------+
| performance_schema            |
+-------------------------------+
1 row in set (0.00 sec)

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

• Author
• Recent Posts

Follow me

Rajesh Kumar

Mentor for DevOps - DevSecOps - SRE - Cloud - Container & Micorservices at Software AG

Join my following certification courses...
- DevOps Certified Professionals (DCP)
- Site Reliability Engineering Certified Professionals (SRECP)
- Master in DevOps Engineering (MDE)
- DevSecOps Certified Professionals (DSOCP)
URL - https://www.devopsschool.com/certification/

My Linkedin - https://www.linkedin.com/in/rajeshkumarin

Follow me

Latest posts by Rajesh Kumar (see all)

• Best AI tools for Software Engineers - November 4, 2024
• Installing Jupyter: Get up and running on your computer - November 2, 2024
• An Introduction of SymOps by SymOps.com - October 30, 2024