Skip navigation links
2.3.6.4 Configuring an Agent to Monitor a Remote MySQL Server »
Section Navigation      [Toggle]

2.3.6.3. Monitoring Multiple MySQL Servers

You can monitor multiple MySQL servers (either on the same machine, or across different machines) using two different methods:

  • By using a single agent instance to monitor multiple MySQL servers. You can use this method if you want to monitor multiple servers, but do not want or need to support Query Analysis on the additional servers.

  • By using multiple copies of the MySQL Enterprise Monitor Agent to monitor each server individually. Using this method requires additional overhead to monitor each server, while also allowing you to supply Query Analyzer data.

Using a Single Agent Instance

Warning

Do not use the single agent instance method if you want to use Query Analyzer. If you set your application to use the proxy port provided by the single instance then the queries may not be directed to the correct server. Using Query Analyzer, the proxy, and the single agent instance method is not supported.

Warning

When using the single agent instance method, the agent will attempt to determine the right information about the backend server that it is monitoring in order to use the information when applying rule and advisor information. Currently, this operation is performed for only one of the servers in the list of configured servers. If the servers being monitoring are using different MySQL versions then the rules applied to the servers may be incorrect, and you could get wrong or misleading advice about issues or problems on a given server.

To use a single agent to monitor multiple instances, you can create additional directories and configuration files within the instances directory for the agent. For example, you can see the default structure of the agent configuration directory: 

./init.d
./init.d/mysql-monitor-agent
./instances
./instances/agent
./instances/agent/agent-instance.ini
./mysql-monitor-agent.ini

Within the instances directory, you can add further directories, one for each monitored server. Each additional directory must have a suitable agent-instance.ini file containing the connection information for the new MySQL server instance. For example, the following structure demonstrates an agent monitoring four MySQL servers: 

./init.d
./init.d/mysql-monitor-agent
./instances
./instances/agent
./instances/agent/agent-instance.ini
./instances/mysql2
./instances/mysql2/agent-instance.ini
./instances/mysql-rep
./instances/mysql-rep/agent-instance.ini
./instances/mysql-backup
./instances/mysql-backup/agent-instance.ini
./mysql-monitor-agent.ini

To add another MySQL monitored server, follow these steps:

  1. Make sure that the MySQL instance that you want to monitor has a suitable user to use for connecting to the server. For more information, see Section 2.3.1, “Creating a MySQL User Account for the Monitor Agent”.

  2. Copy an existing configuration directory and configuration files to the new directory: 

    shell> cp -R etc/instances/agent etc/instances/mysql2

  3. Edit the configuration file within the new directory, for example mysql2/agent-instance.ini, and set the user, password and either the hostname and port, or socket parameters.

  4. Restart the agent: 

    shell> mysql-monitor-agent restart

Using Multiple Agent Instances

To use multiple agents to monitor multiple MySQL servers you need to create a new configuration structure for both the agent and the MySQL server instances you need to monitor, including the binaries and configuration files, and then update the configuration to set the corresponding parameters to monitor the new server. Using this method allows you to enable query analyis by redirecting requests to the target server using the built-in proxy service within the agent.

For example, the directory structure below shows the configuration directory for two agents monitoring a single MySQL server each: 

./init.d
./init.d/mysql-monitor-agent
./instances
./instances/agent
./instances/agent/agent-instance.ini
./instances-second/agent
./instances-second/agent/agent-instance.ini
./mysql-monitor-agent.ini
./mysql-second-agent.ini

The mysql-monitor-agent.ini file contains the configuration for the first agent, with the MySQL servers monitored defined within the instances directory. The mysql-second-agent.ini file contains the configuration information for the second agent, with the MySQL servers monitor defined within the instances-second directory.

To set up multiple agents:

  1. Make sure that the MySQL instance that you want to monitor has a suitable user to use for connecting to the server. For more information, see Section 2.3.1, “Creating a MySQL User Account for the Monitor Agent”.

  2. You need to generate a new UUID for the new agent: 

    shell> /opt/mysql/enterprise/agent/bin/mysql-monitor-agent --agent-generate-uuid
ee9296d7-f7cd-4fee-8b26-ead884ebf398
2009-03-05 11:49:37: (critical) shutting down normally

    Keep a record of the UUID to update the configuration file.

    Note, the agent should not be running when the UUID is generated.

  3. Copy the main agent configuration file, which is by default in /opt/mysql/enterprise/agent/etc/mysql-monitor-agent.ini: 

    shell> cp mysql-monitor-agent.ini mysql-second-agent.ini

  4. Edit the new configuration file, changing the following settings:

    • Change the mysqld-instance-dir to the new directory that will contain the individual MySQL server configuration files.

    • Change the proxy-address to a different value than the first agent configuration.

    • Change the proxy-backend-addresses to specify the IP address and MySQL port number for the MySQL server.

    • Change the agent-uuid to the new value obtained in an earlier step.

    • Change the log-file parameter to specify a different file to use when logging errors and problems. You cannot log to the same file from two different agents.

    • Change the pid-file parameter to specify the file that will be used to store the process ID of the agent.

  5. Copy an existing configuration directory and configuration files to the new directory: 

    shell> cp -R etc/instances etc/instances-second

  6. Edit the configuration file, instances/second/agent/agent-instance.ini within the new directory, and set the user, password and either the hostname and port, or socket parameters.

  7. With multiple instances, you must start each agent individually, specifying the location of the main configuration file. For example, to start the original (default) service: 

    shell> /opt/mysql/enterprise/agent/etc/init.d/mysql-monitor-agent start /opt/mysql/monitor/agent/etc/mysql-monitor-agent.ini

    To start the second instance: 

    shell> /opt/mysql/enterprise/agent/etc/init.d/mysql-monitor-agent start /opt/mysql/monitor/agent/etc/mysql-second-agent.ini