Chapter 12 Configuring Connectors
Prev |  | Next

## Chapter 12 Configuring Connectors

This section describes how to configure the Connectors to pass query information to the Query Analyzer. The following configurations are described:

- Section 12.1, “Using the MySQL Enterprise Plugin for Connector/PHP”: describes the configuration of a PHP-based application which uses the MySQL Enterprise Plugin for Connector/PHP and MySQL Enterprise Monitor Aggregator to feed query information to the Query Analyzer

- Section 12.2, “Using the MySQL Enterprise Plugin for Connector/J”: describes the configuration of the Connector/J to feed query information to the Query Analyzer.

- Section 12.3, “Using the MySQL Enterprise Plugin for Connector/Net”: describes the configuration of the Connector/.NET to feed query information to the Query Analyzer.

## 12.1 Using the MySQL Enterprise Plugin for Connector/PHP

The MySQL Enterprise Plugin for Connector/PHP enables you to use the Query Analyzer to monitor MySQL queries from PHP applications, such as PHP-enabled web pages. The Query Analyzer enables you to locate and analyze queries that are inefficient or slow. Tuning such queries helps to shorten load times for web pages, and improves overall system responsiveness and scalability.

The PHP query data is routed through the MySQL Enterprise Monitor Aggregator. The Aggregator receives query information from the PHP plugin, aggregates and computes statistics, and sends this data to the MySQL Enterprise Service Manager, where it is displayed by the Query Analyzer. You must have the MySQL Enterprise Monitor Aggregator enabled and running to use Query Analyzer with PHP applications.

Figure 12.1 Plugin for PHP and Aggregator Architecture

Important

The PHP Connector is the only connector which requires the MySQL Enterprise Monitor Aggregator to aggregate queries and transmit them to the MySQL Enterprise Service Manager. The other Connectors can be configured to do this without the MySQL Enterprise Monitor Aggregator.

### Prerequisites

The MySQL Enterprise Plugin for Connector/PHP requires PHP 5.3.2 or above, with the MySQL native driver, `mysqlnd`, installed. This is the recommended configuration. If your PHP installation was not configured with the `mysqlnd` enabled, you must rebuild and install PHP from source using at least one of the following options:

- `--with-mysqli=mysqlnd`

- `--with-pdo-mysql=mysqlnd`

- `--with-mysql=mysqlnd`

The preceding options are supplied to the configure command, depending on which extension you are using (`mysql`, `mysqli` or `PDO_MYSQL`). If you use more than one extension, provide multiple options. Specifying any of the options listed rebuilds PHP with `mysqlnd` support. You also must enable the PHP JSON module.

The MySQL client application user, that makes PHP connections in your PHP code, must have `SELECT` privileges on the `mysql.inventory` table. This table contains the server UUID required to report the Query Analyzer data to the MySQL Enterprise Service Manager. Use the `GRANT` statement. For example:

    mysql> GRANT SELECT on mysql.inventory to 'user'@'localhost' IDENTIFIED BY 'password';

### Installation

The plugin is provided as a regular PHP module (PHP extension), and installation follows those PHP standard procedures as described on http://php.net/install.pecl.

Download the MySQL Enterprise Plugin for Connector/PHP, then use the following step-by-step instructions to install and configure the MySQL Enterprise Plugin for Connector/PHP extension.

1. Locate your `php.ini` configuration file. If you do not know the location, you can view information about your PHP installation by creating a script containing:

       <?php phpinfo(); ?>

   Place the script within a directory configured for providing PHP web pages. Now load the page in your web browser to see a list of configuration and other information about your PHP installation.

   Check the output for Loaded Configuration File. If the value is `(none)`, refer to the Configuration File (php.ini) Path and create a file called `php.ini` in there. If a Scan this dir for additional .ini files option is listed you can also create a file using any name you like, ending `.ini`, in that directory to set configuration options.

2. Identify whether or not your PHP build was built “thread safe” by checking the Thread Safety value in the output from the `phpinfo()` test. If your PHP build is thread safe, you need `mysqlenterprise_ts.so` on Linux, Unix, and OS X, or `php_mysqlenterprise_ts.dll` on Microsoft Windows. If not, use `mysqlenterprise.so` on Linux, Unix, and OS X, or `php_mysqlenterprise.dll` on Microsoft Windows.

3. Add an entry for the MySQL Enterprise Plugin for Connector/PHP module. The following example uses the full path:

       extension=/path/to/mysqlenterprise.so

   Alternatively, add the file to the directory defined by the `extension_dir` configuration option, and specify the filename:

       extension=mysqlenterprise.so

   Note

   If `mysqlnd` is loaded as a shared library (`mysqlnd.so`), then it must be loaded before `mysqlenterprise.so` or errors such as "PHP Warning: PHP Startup: Unable to load dynamic library '/mysqlenterprise.so' - /mysqlenterprise.so: undefined symbol: mysqlnd_plugin_register in Unknown on line 0" will be emitted by PHP. Either:

   - If `php.ini` is used to load the PHP extensions, then list it first. For example:

         extension=mysqlnd.so
         extension=mysqlenterprise.so

   - If individual ini files are used to load the PHP extensions, then note that the ini files are loaded alphabetically, so adjust accordingly so that `mysqlnd.so` is loaded first. For example, `/etc/php.d/` might contain:

         mysqlnd.ini
         mysqlzz_enterprise.ini

4. Users of Debian-based systems, such as Ubuntu, are encouraged to use the `php5enmod` command to enable extensions. For example:

       		  $ php5enmod /path/to/mysqlenterprise.so

   `php5enmod` creates a symlink from the usual `conf.d` directory that points to where the real files are located in `mods-available`, and prefixes it with a priority number.

5. Restart your Web server application to reload PHP and the configured extensions.

6. Reload the `phpinfo()` page, and inspect the listing for the `mysqlenterprise` module.

Caution

If you are using PHP on Microsoft Windows with the Apache web server (httpd) built from apache.org, note the following:

MySQL no longer supports VC6, the MySQL Enterprise Plugin for Connector/PHP for Microsoft Windows is compiled with the newer VC9 compiler. You can not use PHP as a loaded module with an Apache web server build that uses VC6. Alternative Apache builds exist that use VC9. Check your source and ensure that your binaries are compiled using VC9.

PHP binaries for Microsoft Windows from php.net have compiled `mysqlnd` support by default, since PHP 5.3.0.

### Configuration

The configuration of the MySQL Enterprise Plugin for Connector/PHP is handled through the standard PHP configuration files, either globally using `php.ini`, or by using the per-directory options, as detailed in PHP Configuration. The following table shows the available configurable options.

Note

Each PHP configuration option for MySQL Enterprise Monitor is prefixed by `mysqlenterprise.`

Table 12.1 Connector/PHP Properties

Property | Description
--- | ---
`aggregator_connect_timeout_sec` | Timeout, in seconds, for communications with the MySQL Enterprise Monitor Aggregator. Property type: `integer` Default value: `1` This property can be combined with the `aggregator_connect_timeout_usec` property.
`aggregator_connect_timeout_usec` | Timeout, in microseconds, for communications with the MySQL Enterprise Monitor Aggregator. Property type: `integer` Default value: `0` This property can be combined with the `aggregator_connect_timeout_sec` property.
`aggregator_user` | The Aggregator's username. See Chapter 11, Proxy and Aggregator Installation for more information. Property type: `string`
`aggregator_password` | The Aggregator's password. Property type: `string`
`aggregator_url` | The IP address, or hostname, and port of the Aggregator installation. Property type: `string` Default value: `tcp://127.0.0.1:14000`
`debug_callback` | This property should be used only when debugging your MySQL Enterprise Monitor installation with MySQL Support personnel. Defines the name of the callback function to invoke when data is sent to the Aggregator. The callback is defined in the PHP application and is a function which requires a single parameter, the array of HTTP requests made to the Aggregator.
`disable_backtrace` | Defines whether a backtrace is performed. Backtrace is useful for debugging but has a performance impact. Property type: `boolean` Default value: `1`, the backtrace is disabled. To enable the backtrace, set this value to `0`.
`log_file` | Defines the location of a log file which logs all query information sent to the Aggregator. This should only be used for debugging purposes because every request is logged, resulting in a very large log file. Property type: `string`
`quan_enabled` | Defines whether query analysis is enabled. Property type: `boolean` Default value: `1`, query analysis is enabled. To disable query analysis, set this value to `0`.

The following is an example of the Aggregator-specific section of the `php.ini`:

    extension = /usr/local/apache/php/lib/php/extensions/mysqlenterprise.so
    mysqlenterprise.aggregator_url = tcp://aggregator:14000
    mysqlenterprise.quan_enabled = 1
    mysqlenterprise.debug_callback = cta_callback
    mysqlenterprise.disable_backtrace = 1
    mysqlenterprise.aggregator_user = username
    mysqlenterprise.aggregator_password = "password"

Note

You must restart your server after setting these properties. Verify the settings are correct by checking the output of `phpinfo()`.

## 12.2 Using the MySQL Enterprise Plugin for Connector/J

The MySQL Enterprise Plugin for Connector/J, enables query analysis for your applications without requiring any modification to the application code.

Important

The Connector/J does not require the MySQL Enterprise Monitor Aggregator for query aggregation.

Figure 12.2 Connector Plugin Architecture

### Prerequisites

1. MySQL Connector/J version 5.1.12 or later.

2. JDK-1.7.0 or later.

3. MySQL Enterprise Service Manager version 3.0 or later.

4. The MySQL client application user must have `SELECT` privileges on the `mysql.inventory` table. This table contains the server UUID which is required to report the Query Analyzer data to the MySQL Enterprise Service Manager. Use the `GRANT` statement. For example:

       mysql> GRANT SELECT on mysql.inventory to 'user'@'localhost' IDENTIFIED BY 'password';

5. Apache Commons logging in the `CLASSPATH` of the application being analyzed. If you are not already using Commons Logging, modify the application's `CLASSPATH` as described in the following section to point to the JAR file bundled with the MySQL Enterprise Monitor product.

### Installation

Place the JAR file `lib/c-java-mysql-enterprise-plugin-`version`.jar` in the application's `CLASSPATH` where it is visible to the version of MySQL Connector/J in use. Ideally, use the same location as MySQL Connector/J's JAR file, or in a parent classloader to that JAR file's location.

If the application being analyzed does not have Apache Commons Logging in the `CLASSPATH`, install the file `lib/required/commons-logging-1.1.1.jar` in the application's `CLASSPATH` as well. If no other component in your application uses Apache Commons Logging, install it in the same place where the Query Analyzer plugin was installed.

There is static shutdown() method on `com.mysql.etools.jdbc.StatementPerformanceCounters`, which can be used to cleanly shutdown the query analysis plugin when the application is going to be shutdown.

If the application is deployed in a J(2)EE application server, there is a `ContextListener` distributed with the plugin which calls this method when the application's context is shutdown (or reloaded). Application Servers which support `@WebListener` (such as JEE6 and above) do not need to do any extra configuration, but users with older Application Servers need to add the following line to their application's `web.xml` file:

    <listener>
        <listener-class>
            com.mysql.etools.jdbc.ContextListener
        </listener-class>
    </listener>

### Using the MySQL Enterprise Plugin for Connector/J

This section describes how to configure the MySQL Plugin for Connector/J.

Table 12.2 MySQL Plugin for Connector/J Properties

Property Name | Description
--- | ---
`statementInterceptors` | Enables the plugin. Set this property as follows: `statementInterceptors = com.mysql.etools.jdbc.StatementPerformanceCounters`
`disableSourceLocation` | Defines whether to send stack traces with example queries to MySQL Enterprise Service Manager. Property type: `boolean` Values: `true` or `false` (default).
`serviceManagerUrl` | Defines the URL of the MySQL Enterprise Service Manager. Include the full URL and port number. Property type: string Value: URL and port number of MySQL Enterprise Service Manager.
`serviceManagerUser` | Defines the Agent username to use when connecting to MySQL Enterprise Service Manager. Property type: `string`
`serviceManagerPassword` | Defines the Agent password to use when connecting to MySQL Enterprise Service Manager. Property type: `string`
`serviceManagerConnectTimeout` | Defines the number of seconds to wait for a connection to MySQL Enterprise Service Manager. Property type: `numeric` Default value: 0
`serviceManagerResponseTimeout` | Defines the number of seconds to wait for a response from MySQL Enterprise Service Manager. Property type: `numeric` Default value: 0
`mysqlServerUUID` | If you are unable to retrieve the server's UUID, define it with this property. Property type: `string` To retrieve the UUID, the plugin requires `SELECT` privileges on `mysql.inventory`.

You can also configure MySQL Enterprise Plugin for Connector/J to use SSL for all communication with MySQL Enterprise Service Manager. To enable SSL, add the following properties to your connection string:

Table 12.3 MySQL Plugin for Connector/J SSL Properties

Property Name | Description
--- | ---
`verifySslHostnames` | If set to true, it enables verification of the host names in the SSL Server certificate. Host names are verified using the same schema as used by Firefox, and Curl, and specified by RFC 2818. Property type: `boolean` Default value: `false`
`verifySslCerts` | Defines whether the plugin verifies the certificate presented by the server was signed by a CA in the `trustCertificateKeystore`. Property type: `boolean` Default: `false`, verification is disabled.
`trustCertificateKeystoreUrl` | Defines the URL of the trusted root certificate KeyStore. If none is specified, the Java defaults are used. Property type: `string`
`trustCertificateKeystorePassword` | Defines the password for the KeyStore. Property type: `string`
`trustCertificateKeystoreType=[type]` | Defines the KeyStore type for trusted root certificates. If type is set to NULL or empty, JKS is used by default. The standard keystore types supported by the JVM are JKS and PKCS12. Your environment may have more available depending on what security products are installed and available to the JVM.
`clientCertificateKeystoreUrl` | Defines the URL of the client KeyStore. If none specified, Java defaults are used.
`clientCertificateKeystorePassword=[password]` | Defines the password to use for the client certificate store.
`clientCertificateKeystoreType` | Defines the KeyStore type for client certificates. If type is set to NULL or empty, JKS is used by default.

The following example configures a Connector/J to communicate with the MySQL Enterprise Service Manager localhost, on port 18443, using the agent username `agent`, and password `PASSWORD`. Add the properties to your connection string on a single line:

    statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters
      &serviceManagerUrl=https://localhost:18443/
      &serviceManagerUser=agent
      &serviceManagerPassword=PASSWORD

You must also add the application-specific properties to the JDBC URL. For example, the following fragment connects to the MySQL database test on localhost, using the user and password of mysqltest, while also collecting query data and sending it to the MySQL Enterprise Service Manager on localhost:

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;

    Connection conn = null;
    ...
    try {
        conn =
           DriverManager.getConnection("jdbc:mysql://localhost/test?" +
               "?user=mysqltest" +
               "&password=mysqltest" +
               "&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters" +
               "&serviceManagerUrl=https://localhost:18443/" +
               "&serviceManagerUser=agent" +
               "&serviceManagerPassword=PASSWORD"
           );

        // Do something with the Connection

       ...
    } catch (SQLException ex) {
        // handle any errors
    }

Note

If a DataSource is in use (typically when using Glassfish, Weblogic, or Websphere), these properties must be passed as part of the URL property, they cannot be added to the DataSource configuration itself.

If an alternate logging system has not been selected for Connector/J, it is recommended that Connector/J's log factory is configured to use something other than the standard logger by adding the following property to the URL or DataSource:

- `logger=Log4JLogger` (for applications using Log4J)

- `logger=CommonsLogger` (for applications using Apache Commons Logging)

- `logger=Jdk14Logger` (for applications using Java 1.4 or later logging)

Further configuration of the plugin is done via the Enterprise Service Manager's user interface, using the Query Analyzer tab. From here, the capture of query performance data for a given MySQL instance that the plugin is being with can be enabled or disabled. Further, the level of detail that is captured; summary, examples (with source code locations) and `EXPLAIN` plans can be configured as well.

## 12.3 Using the MySQL Enterprise Plugin for Connector/Net

The MySQL Enterprise Plugin for Connector/Net enables you to use the Query Analyzer to monitor MySQL queries from any application using Connector/Net, including both standalone and web-based applications. As described in Section 27.3, “Query Analyzer User Interface”, the Query Analyzer can help you locate queries that are inefficient or slow. Tuning such queries helps to shorten load times for web pages, and improves overall system responsiveness and scalability.

Information about the queries is sent directly to the MySQL Enterprise Service Manager. Once you install the MySQL Enterprise Plugin for Connector/Net, query analysis becomes available for your applications without requiring any modification to the application code.

Figure 12.3 Connector Plugin Architecture

### Prerequisites

- You must be using Connector/Net version 6.2.3 or later.

- The MySQL client application user must have `SELECT` privileges on the `mysql.inventory` table. This table contains the server UUID; it is required to report the Query Analyzer data to the MySQL Enterprise Service Manager. Use the `GRANT` statement. For example:

      mysql> GRANT SELECT on mysql.inventory to 'user'@'localhost' IDENTIFIED BY 'password';

- Your application should already be using the `Mysql.data.dll` and have been built with the library requirement.

- If you are using the released builds of Connector/Net, you must include the `logging=true` option within your connection string.

### Installation

Download the MySQL Enterprise Plugin for Connector/Net package. Extract the package using a suitable zip tool, and place the plugin library, `MySql.MonitorPlugin.dll`, in the same directory as your compiled application.

### Configuration

If the application does not have an `app.config` application configuration file, then make one.

To enable Query Analyzer functionality, register the trace listeners in the `System.Diagnostics` section of the `app.config` file. The following example shows the format of a typical configuration file:

    <system.diagnostics>
      <sources>
        <source name="mysql" switchName="SourceSwitch"
        switchType="System.Diagnostics.SourceSwitch">
          <listeners>
            <add name="EMTrace" type="MySql.EMTrace.EMTraceListener, MySql.MonitorPlugin"
                    initializeData=""
                    Host="SERVERHOST:SERVERPORT"
                    PostInterval="POSTINTERVAL"
                    UserId="AGENTUSERID"
                    Password="AGENTPASSWORD"/>
          </listeners>
        </source>
      </sources>
      <switches>
        <!-- You can set the level at which tracing is to occur -->
        <add name="SourceSwitch" value="All"/>
      </switches>
    </system.diagnostics>

    <system.data>

      <DbProviderFactories>
        <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient"
        description=".Net Framework Data Provider for MySQL"
        type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.2.1.0, \
        Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
      </DbProviderFactories>

    </system.data>

Within the configuration, set the values of the following parameters:

- `Host`

  The hostname and port number, separated by a colon, of the MySQL Enterprise Service Manager that receives the Query Analyzer data.

- `PostInterval`

  Query analyzer information is collected and then transmitted (“posted”) in a batch from your application to the MySQL Enterprise Service Manager. This value specifies the number of seconds between each transmission. Choose this value carefully. Too long and it might take some time for queries to appear in the Query Analyzer.

- `UserId`

  The name of a user within MySQL Enterprise Service Manager that has rights to send agent information.

- `Password`

  The password of a user within MySQL Enterprise Service Manager that has rights to send agent information.

To get extended information on queries and have that information available through the MySQL Enterprise Monitor User Interface, enable the Connector/Net usage advisor. The extended information identifies potential issues such as a query not using an index, or not accessing all columns from a result set.

To enable the usage advisor, add `usage advisor=true` to the connection string within your application. Enabling this option also automatically enables logging within Connector/Net. For more information, see Connector/Net Connection String Options Reference.

During execution of the application during development within Visual Studio, a significant amount of output is displayed in the Output window. To view this same trace output when running the application outside Visual Studio, configure an additional listener by adding the following within the `system.diagnostics` section of your `app.config` file:

    <trace autoflush="false" indentsize="4">
      <listeners>
        <add name="consoleListener" type="System.Diagnostics.ConsoleTraceListener" />
      </listeners>
    </trace>

### Usage

After you set up MySQL Enterprise Plugin for Connector/Net, you monitor the performance of your .NET applications through the Query Analyzer tab, as described in Section 27.3, “Query Analyzer User Interface”.

Prev | Up | Next
Chapter 11 Proxy and Aggregator Installation | Home | Part III Using MySQL Enterprise Monitor