Set up Site24x7 MySQL NDB Cluster Monitoring

Set up Site24x7 MySQL NDB Cluster monitoring to better understand your MySQL NDB clusters. MySQL NDB Cluster monitoring will equip you with the knowledge and tools necessary to ensure the node availability, performance, and scalability of your NDB Cluster environment.

Table of Contents:

Prerequisites

Before setting up your MySQL NDB Cluster monitors, make sure you have the required conditions satisfied.

• NDB versions 7.5.29 and above
• Linux Site24x7 server monitoring agent version 19.1.0

Follow the instructions below to setup a MySQL NDB Cluster monitor.

1. You need to create a database user for the Site24x7 agent on each MySQL server. With the following instructions, you can create a user and grant the MySQL user with the permission to log in.

   CREATE USER 'username'@'localhost';

2. You also have the option to require users to log in only using localhost permissions by using @'localhost'.

   CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

3. Create the user with the native password hashing method as shown below (for MySQL versions 8.0 and above):

   CREATE USER 'username'@localhost IDENTIFIED WITH mysql_native_password by 'password';

4. The Site24x7 agent needs a few privileges to collect the metrics. You need to provide the MySQL user with the following limited privileges:

   GRANT REPLICATION CLIENT ON *.* TO 'username'@'localhost';
    GRANT REPLICATION SLAVE ON *.* TO 'username'@'localhost';
    GRANT PROCESS ON *.* TO 'username'@'localhost';
    GRANT SHOW DATABASES ON *.* TO username@'localhost';

5. You also need to grant the user the SELECT permission on the performance_schema database to collect metrics like average query run time, query executed, error count, warn count, and so on.

   GRANT SELECT ON performance_schema.* TO 'username'@localhost;
    GRANT select on ndbinfo.* to 'site24x7'@localhost;

6. Grant the following permission to the sys database to collect the in-depth metrics for each database discovered, like the total latency, the number of scans performed, error statements, input-output (IO) activity, and so on.

   GRANT SELECT ON sys.* TO 'username'@'localhost'; 
    GRANT EXECUTE ON sys.* TO 'username'@'localhost';

Add a MySQL NDB Cluster monitor

If there are multiple MySQL instances, repeat the above steps for each MySQL instance.

After providing the required permissions, you can proceed to add a MySQL Server Monitor using the following steps:

For Linux

1. Install the Site24x7 Linux server monitoring agent on your Linux server.
2. Execute the following command in your terminal to configure the server monitoring agent:

   /opt/site24x7/monagent/scripts/AgentManager.sh mysql --add_instance

3. Enter your MySQL instance's user credentials, including the host name, the port of the MySQL instance to be monitored, and the username and password previously created for the Site24x7 agent.
4. After logging in to Site24x7, click Server > MySQL NDB on the navigation pane.
5. You will be directed to the monitor page to view the list of the MySQL NDB nodes you have added to monitor.

Performance Metrics

Find the extensive list of essential metrics that can be monitored using our advanced MySQL NDB Cluster monitor. Monitoring these metrics is crucial for gaining deep insights into the performance and health of your NDB Cluster. By closely tracking these parameters, you can proactively identify potential bottlenecks, troubleshoot issues, and optimize the overall efficiency of your cluster.

Uninstall Agent

To uninstall the Linux agent, execute the below commands based on the user type:

Root user:

sudo /opt/site24x7/monagent/bin/uninstall

Non-root user:

/site24x7/monagent/bin/uninstall

Best Practice

To have uninterrupted access to the metrics of your MySQL NDB Cluster, we recommend installing the Site24x7 Linux server monitoring agent in all of the SQL nodes. Site24x7 recommends this as a best practice because, if the monitoring agent is installed in all of the SQL nodes, you will have uninterrupted data even when one of your SQL nodes is down. Installing the server monitoring agent on the data (NDB) nodes and management (MGM) node will get you that particular node's metrics like:

• Disk utilization
• CPU utilization
• Memory utilization