Monitor a fleet of MySQL servers with Ansible

In an era of digital innovation, ensuring the health and performance of our digital infrastructures becomes mission-critical. MySQL, one of the world's most popular open-source relational database management systems, often forms the backbone of many applications. However, managing and monitoring a fleet of MySQL servers can pose a complex challenge. This is where Ansible and New Relic come into play, simplifying server management and offering comprehensive monitoring solutions.

At its core, Ansible is an open-source automation tool that aids in tasks like configuration management, application deployment, and the orchestration of complex IT workflows. On the other hand, New Relic is a leading observability platform that delivers real-time insights into your software's performance, allowing for effective monitoring, troubleshooting, and optimizing system performance. When combined, these tools provide a powerful solution for managing and monitoring your MySQL server fleet. In this blog, you will walk through the process of monitoring a fleet of MySQL servers with Ansible using New Relic. You’ll learn to:

• Install and configure Ansible on your systems
• Configure New Relic for Ansible
• Enable MySQL monitoring with Ansible
• View MySQL data in New Relic

Before you begin

This guide assumes that you already have a fleet of Ubuntu servers with MySQL installed. If you don’t already, you can use any virtualization software like VMware or VirtualBox to create an Ubuntu VM. Once your VM is in place, install MySQL on it before proceeding. You’ll also need:

• New Relic account details, specifically the API key, Account ID, and Region.
• Root or sudo access on your server.

Install & configure Ansible:

Before you install Ansible, make sure your system’s package list is up to date.

sudo apt update

Next, install the software-properties-common package. This utility provides the add-apt-repository command, an essential tool for adding external repositories, such as Ansible's Personal Package Archive (PPA):

sudo apt install software-properties-common

With the necessary tools in place, proceed to add Ansible’s official PPA to your system:

sudo add-apt-repository --yes --update ppa:ansible/ansible

This repository ensures you access the latest, official version of Ansible. Install Ansible by running:

sudo apt install ansible

To verify that Ansible is installed correctly, you can check its version:

ansible --version

Once you’ve successfully installed Asnible, the next step is to create an inventory of your servers by adding the IP address or fully qualified domain name (FQDN) of the remote systems to /etc/ansible/hosts. Execute the following command to open this file in the nano text editor. 

sudo nano /etc/ansible/hosts

Add the IP addresses of the servers you want Ansible to manage. 

[myvirtualmachines]
192.168.191.132
192.168.191.133

Save and exit the file. Next, for Ansible to manage these remote systems, it needs to establish a connection, typically done through SSH. To facilitate this connection, your public SSH key from the control node (the machine where Ansible is installed) must be added to the authorized_keys file on each of the remote systems. This step ensures secure, password-less access, making operations smooth and automated. To verify if the SSH setup is accurate, try connecting to one of the remote systems. For instance:

ssh ubuntu@192.168.191.132

If your username on the Ansible control node differs from the one on the remote host, remember to specify the correct username when running Ansible commands using the -u option. Lastly, to ensure that everything is set up correctly, and Ansible can communicate with all the nodes, run a simple test command that 'pings' all the nodes:

        ansible all -m ping

A successful response indicates that Ansible is well-configured and ready to manage the remote systems in your inventory.

    192.168.191.132 | SUCCESS => {
        "ansible_facts": {
            "discovered_interpreter_python": "/usr/bin/python3"
        },
        "changed": false,
        "ping": "pong"
    }
    192.168.191.133 | SUCCESS => {
        "ansible_facts": {
            "discovered_interpreter_python": "/usr/bin/python3"
        },
        "changed": false,
        "ping": "pong"
    }

Configure New Relic with Ansible

To configure New Relic with Ansible, start by installing the Ansible role newrelic.newrelic_install. This role allows Ansible to automate the New Relic Infrastructure agent's installation.

ansible-galaxy install newrelic.newrelic_install

Install the required collections from Ansible Galaxy: ansible.windows and ansible.utils.

ansible-galaxy collection install ansible.windows ansible.utils

Next, create an Ansible playbook for New Relic setup, for instance, newrelic.yml. This playbook specifies how Ansible will install and configure the New Relic Infrastructure agent on the servers. Replace the placeholders with your actual New Relic details.

    - name: Install New Relic infrastructure and logs
      hosts: all
      roles:
        - role: newrelic.newrelic_install
      environment:
        NEW_RELIC_API_KEY: '<API key>'
        NEW_RELIC_ACCOUNT_ID: '<Account ID>'
        NEW_RELIC_REGION: '<Region>'

Run the playbook using the following command:

ansible-playbook newrelic.yml

Once done, the New Relic Infrastructure agent will be operational on your servers, sending metrics to your New Relic account.

Enabling MySQL Monitoring with New Relic

After configuring the New Relic infrastructure agent, the next step is to enable MySQL monitoring. This provides specific metrics about MySQL server performance. To enable MYSQL monitoring, update the Ansible playbook with MySQL-specific parameters:

• Role Configuration: The newrelic.newrelic_install role is designated for New Relic agent installations. In this role, the vars section denotes the New Relic components to be installed. By default, this will include infrastructure and logs. Add mysql into this section to activate MySQL monitoring.
• Environment Variables: The NEW_RELIC_API_KEY, NEW_RELIC_ACCOUNT_ID, and NEW_RELIC_REGION variables serve authentication and routing purposes. Include NEW_RELIC_MYSQL_ROOT_PASSWORD to grant the agent MySQL access, facilitating the creation of a specialized New Relic user.

Your updated playbook should look as follows:

- name: Install New Relic infrastructure, logs & MySQL`
      hosts: all
      roles:
        - role: newrelic.newrelic_install
          vars:
            targets:
              - infrastructure
              - logs
              - mysql
      environment:
        NEW_RELIC_API_KEY: '<API key>'
        NEW_RELIC_ACCOUNT_ID: '<Account ID>'
        NEW_RELIC_REGION: '<Region>'
        NEW_RELIC_MYSQL_ROOT_PASSWORD: 'your_mysql_root_password'

Now execute the updated playbook:

ansible-playbook newrelic.yml

Once the playbook has been executed successfully, New Relic will start collecting MySQL-specific metrics. In the next section, we'll guide you on how to review these metrics within the New Relic dashboard.

View MySQL data in New Relic

After setting up MySQL monitoring, it's crucial to verify that the data is being correctly forwarded to New Relic. From New Relic One, navigate to Infrastructure to see the incoming data from your servers. The following image shows the data coming from our two dummy servers Ubuntu-1 and Ubuntu-2.

To start monitoring your MySQL databases, navigate to Third-party Services. You should see MySQL under your Active Integrations.

Under DASHBOARDS, click MySQL dashboard to see your MySQL data.

The image above shows different metrics such as Operations per second, Queries per second, Slow Queries, and others related to our MySQL databases on both of the dummy servers.

Conclusion:

With the New Relic Infrastructure agent set up via Ansible and the MySQL monitoring enabled, you now have a bird's eye view of your MySQL fleet's health and performance. The built-in dashboards offered by New Relic provide invaluable insights that allow you to make informed decisions on the fly. This tutorial aimed to get you started on this journey, and there's much more to explore in the realm of New Relic's monitoring capabilities.