Chapter 26 Customizing MySQL Enterprise Monitor
Prev |  | Next

## Chapter 26 Customizing MySQL Enterprise Monitor

You can customize your MySQL Enterprise Monitor rules, advisors, and graphs, based on your organization's business rules, best practices, and the types of issues you can anticipate.

## 26.1 Creating Advisors and Rules

For common scenarios, reuse or edit the advisors and graphs provided by MySQL Enterprise. To create new advisors and graphs for your own needs, go to the Configuration on top menu bar and choose the Advisors menu item, select the Create Advisor button on the General Advisors Control or select the Import/Export button to create a graph.

### 26.1.1 Creating Advisors

Similar existing Advisors are grouped together in Advisor category. To create a new Advisor, go to Configuration on top menu bar and choose the Advisors menu item, select the Create Advisor button on the General Advisors Control.

#### Default Advisor Categories

The following are the default Advisor categories:

- Administration

- Agent

- Availability

- Backup

- Cluster

- Graphing

- Memory Usage

- Monitoring and Support Services

- Operating System

- Performance

- Query Analysis

- Replication

- Schema

- Security

Note

You can also create your own Advisor category while creating an Advisor by changing the Advisor Category to a custom value.

### 26.1.2 Overview of Graph Creation

Graphs are defined using XML, and then imported into MEM. The new custom graph is displayed with the default graphs, sorted by `name` on the graphs page.

For an example of how to create a graph, see Section 26.1.9, “Creating a New Graph: An Example”.

The XML elements for creating a graph are as follows:

- version

  The version number of the graph. Generally only important with the bundled graphs, and is only used internally.

- uuid

  The unique id of the graph. Each revision (version) requires a new uuid, which is only used internally.

- name

  The visible graph name, which is displayed within the graph listing. Note: graphs are sorted alphabetically.

- frequency

  Optionally define the frequency for the graph, which defaults to 1 minute. May use seconds, minutes, hours, and days.

- rangeLabel

  The Y-axis range label. For example, a graph about disk space usage may use `MB`.

- series

  Each series contains a label and an expression. The label is the visible name of the series, and the simple expression defines it.

- variables

  Each variables definition contains a name, instance, and dcItem element. The instance defines what data the graph displays, and each dcItem element contains a nameSpace, className, and attribName:

  - nameSpace

    Namespace (type) of the data collection item.

  - className

    Class (namespace type) of the data collection item.

  - attribName

    Attribute name of the data collection item.

### 26.1.3 Overview of Advisor Creation

To create a new Advisor with all-new settings, click the Create Advisor button available on the Advisors page. To create an Advisor similar to an existing one, click the Advisor menu drop-down icon to the left of the Advisor title, and choose the Copy Advisor menu item . You can edit any Advisor element during the copying process, unlike editing an existing Advisor. You can also delete an existing Advisor created by you, click the Advisor menu drop-down icon to the left of the Advisor title, and choose click the Delete Advisor menu item.

You can change the Advisor name, change the Advisor category that an Advisor belongs to, set your own version number, and alter the threshold and frequency of an Advisor.

Note

If you do not specify a version number for the new Advisor, the version 1.0 is automatically added. Most importantly, you can alter an Advisor's expression. Expressions are the core of a MySQL Enterprise Advisor and define the scenario being monitored. An expression can be as simple as a single server parameter or can be complex, combining multiple parameters with mathematical operations.

Most importantly, you can alter an Advisor's expression. Expressions are the core of a MySQL Enterprise Advisors and define the scenario being monitored. An expression can be as simple as a single server parameter or can be complex, combining multiple parameters with mathematical operations.

An expression has two main characteristics:

- An expression tests whether a best practice is being violated.

- The result of an expression must always be 1 or 0 (corresponding to true or false).

For example, if you decide that enabling binary logging is a best practice for a production server (as Oracle recommends), then this best practice is violated if `log_bin` is `OFF`. Consequently, the expression for the “Binary Logging Not Enabled” rule is “%log_bin% == OFF”. If this evaluates to 1, an event is raised because the best practice is not being followed.

An expression is made up of one or more variables and zero or more mathematical operators. The MySQL Enterprise Monitor product uses the Java Expression Parser. The operators and functions consist of:

- The `IN()` operator.

- The MySQL functions `LEAST()`, `LOCATE()`, `ABS()`, `MOD()`, `NOW()` (returns time since Unix epoch UTC in seconds), `UNIX_TIMESTAMP` (technically a no-op), and `INTERVAL [n] SECOND, MINUTE, HOUR, WEEK, MONTH`.

- The operators functions listed on this page: http://www.singularsys.com/jep/doc/html/operators.html.

- Comparisons with MySQL timestamps and datetimes collected by the agent in the standard MySQL format `'YYYY-MM-DD hh:mm:ss[.`nanos`]'`.

- The `IF` function: `IF (`condition`, `true_expression`, `false_expression`)` returns either `true_expression` or `false_expression`, depending on whether `condition` is true or false. This function uses short-circuit evaluation, so only one of the return expressions is evaluated.

- The `LEFT`(`string`, `length`) and `RIGHT`(`string`, `length`) functions.

- The `NUM`(`string`) function.

  Note

  The `CAST`(`expression` as `type`) function is not implemented. Instead, use `NUM`(`string`) to use strings as numbers.

For a complete list of the built-in variables used to create Advisors, see Server Option and Variable Reference.

Creating an expression is dependent on variables defined in the Variable Assignment frame. This frame links variables used in the expression field with data gathered from the target MySQL server instance: server status variables, operating system status information, and table information. Variable names are associated with elements in the Data Item drop-down menu. To define more than one variable, click the add row button.

The remaining fields determine the information that you receive in a notification email or the informational pop-up window associated with each advisor.

Note

When saving a new Advisor, choose a unique name not used by any existing Advisor.

### 26.1.4 Variables

When MySQL Enterprise Monitor evaluates an expression, it replaces variables with values. For example, part of the expression for the “MyISAM Key Cache Has Sub-Optimal Hit Rate” rule calculates the hit rate as follows:

    100-((%Key_reads% / %Key_read_requests%)*100)

If the current value of `%Key_reads%` is 4522 and the current value of `%Key_read_requests%` is 125989, the hit ratio is 96.4%:

    100 -((4522 / 125989) * 100)

By convention, the Advisors supplied by MySQL use ‘`%`’ as the delimiter, for example, `%Key_reads%`. This makes variables more readily identifiable.

Variables can be used in the `Description`, `Advice`, `Action`, and `Links` attributes of a rule, as well as in expressions. This lets you report the current value of an expression. For instance, you can add the message, “The current value of Key_reads is %Key_reads%.” to the `Advice` text box. When this is displayed on the screen, the value of `%Key_reads%` is substituted into the text. If `%Key_reads%` has a value of `4522`, the message becomes “The current value of Key_reads is 4522.”

### 26.1.5 Thresholds

Each expression has a threshold value that triggers an alert. The `THRESHOLD` keyword associates that value with an alert level: either an `Notice`, `Warning`, or `Critical` alert.

For example, the expression for the performance advisor, “Thread Cache Size May Not Be Optimal”, is:

    100-((%Threads_created% / %Connections%) * 100) < THRESHOLD

The `THRESHOLD` is set at 95% for an Info level alert, 85% for a Warning alert, and 75% for a Critical alert, producing alerts of three different levels.

Expressions can be straightforward. The expression for “Binary Logging Not Enabled” (one of the Administration alerts) is:

    %log_bin% == THRESHOLD

When the result is `OFF`, only one alert is triggered: a Warning level alert. You cannot just use the expression `%log_bin% == "OFF"`, because this would not test binary logging against a threshold and so would not result in an alert.

Specify precise conditions when each expression should evaluated, to avoid false alarms. For example, the expression for the “MyISAM Key Cache Has Sub-Optimal Hit Rate” rule is:

    (%Uptime% > 10800) && (%Key_read_requests% > 10000) && (100-((%Key_reads% / %Key_read_requests%) * 100) < THRESHOLD)

The first part of the expression, `(%Uptime% > 10800)`, delays evaluating this expression until the system has been running for 10800 seconds (3 hours). When a server starts up, it might take a while to reach a state that is representative of normal operations. For example, the `InnoDB` buffer pool, `MyISAM` key cache, and the SQL query cache might require some time to fill up with application data, after which the cached data boosts performance.

In addition, if some part of the system is not heavily used, an alert might be triggered based on limited data. For example, if your application does not use the MyISAM storage engine, the “MyISAM Key Cache Has Sub-Optimal Hit Rate” rule could be triggered based on very limited use of other MyISAM tables such as the `mysql.user` table. For this reason, this advisor has a second part: `(%Key_read_requests% > 10000)`. The rule is not evaluated unless there is plenty of activity associated with the key cache.

### 26.1.6 Using Strings

Enclose string values within double quotation marks in the `Expression` or the `Thresholds` text boxes. For example, the expression for the “Slave I/O Thread Not Running” rule is:

    (%Slave_running% == "ON") && (%Slave_IO_Running% != THRESHOLD)

Similarly, the `Critical Alerts` threshold text box is set to a value of `"Yes"`.

When the expression is evaluated, either `"OFF"` or `"ON"` is substituted for `%Slave_running%`, and `"Yes"` or `"No"` for `%Slave_IO_Running%`, depending on the state of your system. If the slave is running but the I/O thread is not, the expression becomes:

    ("ON" == "ON") && ("No" != "Yes")

Without quotation marks, this expression would not evaluate to `TRUE` as it should.

Note

So that it is interpreted properly, the `==` operator is converted to `=` before being passed to the MySQL expression parser.

### 26.1.7 Wiki Format

When editing or defining a rule, you can enter text in Wiki format in the `Problem Description`, `Advice`, `Recommended Action`, and `Links and Further Reading` text boxes. You can format and highlight text and add hyperlinks, using the notation listed in the following table.

Table 26.1 MySQL Enterprise Monitor: Wiki Formatting

Example | Description
--- | ---
__`bold`__ | boldface text
~~`italic`~~ | italicize text
\\ | create a line break
\\ \\ | create a double line break
\\\\`G` | create a backslash
*`item 1` | create a bulleted list item
#`item 1` | create a numbered list item
\`_` | use the ‘`\`’ to escape special characters
'{'moreInfo:name|url'}' | create a hyperlink

So the following Wiki text:

    Replication is a __very nice feature__ of MySQL.  Replication can be very
    useful for solving problems in the following areas:
    * Data Distribution
    * Load Balancing
    * Backup and Recovery
    You can check replication status and start a slave using the following
    commands: SHOW SLAVE STATUS \\\\G\\START SLAVE; '{'moreInfo:MySQL Manual: Replication
          FAQ|http://dev.mysql.com/doc/refman/5.6/en/faqs-replication.html'}'

Would be translated into the following HTML markup:

    Replication is a <b>very nice feature</b> of MySQL.  Replication can be very
    useful for solving problems in the following areas:
    <ul>
      <li>Data distribution</li>
      <li>Load Balancing</li>
      <li>Backup and recovery</li>
    </ul>You can check replication status and start a slave with the following
    commands: SHOW SLAVE STATUS \G;<br/>START SLAVE;
    <a href="http://dev.mysql.com/doc/refman/5.6/en/faqs-replication.html"
      target="_blank" >MySQL Manual: Replication FAQ</a>

### 26.1.8 Creating a New Advisor: An Example

This section documents the steps to create an Advisor.

To create an Advisor, select the Create Advisor button from the Advisors page. The new advisor page is displayed.

This example creates an Advisor that checks if connections have been killed using the `KILL` statement and generates an event.

Create your custom rule by following these steps:

1. Using the Advisor Name text box, give the Advisor an appropriate name, such as "Connections killed".

2. From the Advisor Category drop down list box, choose an Advisor category for your Advisor.

3. Define the variable for your expression in the Variable Assignment frame.

   - In the Variable text box, enter `%connections_killed%`, the variable used in the Expression text box.

   - In the Data Item drop-down list, select the `mysql:status:Com_kill` entry.

   - In the Instance text box, enter `local`.

4. Enter the following expression in the Expression text area.

       '%connections_killed% > THRESHOLD'

5. Set the following threshold:

   - Set the Info Alert level to `0`. An informational event is generated if 1 or more connections are killed.

6. Add appropriate entries for the Problem Description, Advice, and Links text areas. Optionally, use Wiki markup for these text areas. You can also reference the `%connections_killed%` variable in these text areas.

7. Save the Advisor

After you create the Advisor, schedule it against the MySQL server you want to monitor. For instructions on Configure Advisor, see Table 19.3, “Advisor Edit Menu Controls”.

### 26.1.9 Creating a New Graph: An Example

This section documents the steps to create a graph. Before creating a graph, review the preceding sections of this chapter as Graphs and Rules use similar components. And for an overview that's specific to graphs, see Section 26.1.2, “Overview of Graph Creation”

This example creates a graph that checks and compares disk usage, by displaying the usage and total available disk space over time.

Begin by navigating to the Configuration, Advisors page, and click the `Import/Export` link. Then note the `Custom Rule/Graph/Data Items Import` section. This is where the XML file is imported.

A definition to check disk space usage may look like the following:

    <?xml version="1.0"?>
    <com_mysql_merlin_server_graph_Design>
        <version>1.0</version>
        <uuid>a57c2bba-ea9b-102b-b396-94aca32bee29</uuid>
        <name>my filename usage test</name>
        <rangeLabel>MB</rangeLabel>
        <series>
            <label>used</label>
            <expression>used_fs/1024/1024</expression>
        </series>
        <series>
            <label>total size</label>
            <expression>total_fs/1024/1024</expression>
        </series>
        <variables>
            <name>used_fs</name>
            <dcItem>
                <nameSpace>os</nameSpace>
                <className>fs</className>
                <attribName>fs_used</attribName>
            </dcItem>
            <instance>/</instance>
        </variables>
        <variables>
            <name>total_fs</name>
            <dcItem>
                <nameSpace>os</nameSpace>
                <className>fs</className>
                <attribName>fs_total</attribName>
            </dcItem>
            <instance>/</instance>
        </variables>
    </com_mysql_merlin_server_graph_Design>

Upon successfully loading a graph, a popup notification may say "1 graph imported" in the MySQL Enterprise Monitor User Interface.

This also creates a new Advisor with the same title, which is unscheduled by default. Go to Configuration, Advisors, Graphing to locate and enable this new Advisor.

This graph is displayed on the appropriate graphs page (like every other graph) under the name defined within the definition, which is "`my filename usage test`" in the example above.

## 26.2 Custom Data Collection

This section describes how to configure custom data collections for the monitoring agent.

The monitoring agent can be configured to collect data directly from the MySQL server, using a query. This enables you to extend the functionality of the agent and create custom advisors which analyze the data collected by the custom data collection.

To create a custom data collection, you must add a class to `custom.xml`, located in the `etc` directory of your agent installation. Each defined class is a custom data collection.

Note

`custom.xml` is validated against `items-mysql-monitor.dtd` .

.

After defining the custom data collection, it is available to select in the Data Item drop-down menu on the Variable Assignment frame of the new Advisor page.

The following sections describe this process in detail.

### 26.2.1 Custom.xml

The following XML shows the structure of a custom data collection:

    <?xml version="1.0" encoding="utf-8"?>
    <!DOCTYPE classes SYSTEM "items-mysql-monitor.dtd">
    <classes>

    	<class>
    		<namespace>NameSpace</namespace>
    		<classname>ClassName</classname>
                  <precondition><![CDATA[Add Precondition Query Here]]></precondition>
                  <query><![CDATA[Add Main Query Here]]></query>
                  <attributes
                      <attribute name="AttributeName1"/>
                      <attribute name="AttributeName2"/>
                  </attributes>
    	</class>

    </classes>

Table 26.2 Custom Data Collection Class Elements

Element | Description
--- | ---
`classes` | Container element for all defined classes.
`class` | Container element for the definition of the collection.
`namespace` | Logical grouping for the new data collection item.
`classname` | Name of the custom data collection. Do not use spaces or special characters in this element.
`precondition` | (Optional) Query which checks some conditions. If the query returns true, the main query is executed. For example, the precondition query can be used to check the version of the MySQL server. See Section 26.2.2.1, “Precondition Queries” for more information.
`query` | The main query. For more information, see Section 26.2.2.2, “Main Queries”
`attributes` | Enables you to label the types of data returned by the query. Possible types are: STRING, INTEGER, and FLOAT. This information is required by the advisor receiving the data. It is also possible to define one or more attributes as counters. See Section 26.2.3, “Data Collection Attributes” for more information.

The values in the namespace and classname elements are used as the first two elements of the name.

### 26.2.2 Queries

This section describes the precondition and main queries used to create custom data collections.

#### 26.2.2.1 Precondition Queries

This section describes the optional precondition queries. Precondition queries determine that specific conditions are true before executing the main query. For example, they are used in the default advisors to check the MySQL server version, because some main queries cannot be executed on older versions of the server. The following is an example of a precondition query which checks the version of the MySQL server:

      <precondition>
          <![CDATA[SELECT @@version NOT LIKE '5.0%' AND @@version NOT LIKE '5.1%']]>
      </precondition>

If the server version is higher than 5.1, the precondition returns true and the main query is executed. If the MySQL server is version 5.0.x or 5.1.x, the precondition returns false and the main query is not executed.

#### 26.2.2.2 Main Queries

The main queries enable you to retrieve data from the monitored server.

When defining queries, the following restrictions apply:

- The query must be defined within a `<![CDATA[]]>` container. For example: `<![CDATA[SELECT X FROM Y AS FOO]]>`. Do not enter any characters between CDATA and the following [, nor between the [ and the start of the query. The same rule applies to the closing ]].

- Only SELECT statements are possible. It is not possible to use INSERT, UPDATE, DELETE, and so on.

- It is not possible to define more than one query per class.

- The agent must have sufficient rights to run the query.

- Do not define queries which take longer to run than the schedule defined on the advisor. For example, if the query takes 2 minutes to run, but the advisor-defined schedule requires the query to run every 1 minute, no results are returned. To avoid this, test your query thoroughly on the monitored server. If the custom data collection is deployed on multiple agents, it must be tested on each monitored server and the schedule modified accordingly.

- The query can return only one row, except if the result type `CLASS_TYPE_1STCOL_ATTRIBUTES` is used. See Section 26.2.3.2, “Returning Multiple Rows” for more information.

For each value retrieved from the server, you must assign a name. That is, you must use the following format, where NAME is the name applied to the data collection:

    SELECT X AS NAME FROM Y

The items are displayed in the Data Item drop-down menu on the Variable Assignment frame of the new Advisor page. They take the following format: `namespace:classname:name`. For example, mysql:status:open_files_limit.

Note

The examples used in this section are taken from the default advisors delivered with your MySQL Enterprise Monitor installation.

The following example is used by the Server Has Anonymous Accounts advisor:

    <class>
    	<namespace>mysql</namespace>
    	<classname>anonymous_user</classname>
    	<query><![CDATA[SELECT COUNT(*) AS user_count FROM mysql.user WHERE user='']]></query>
    </class>

In this advisor, the variable `%user_count%` is mapped to the Data Item `mysql:anonymous_user:user_count` defined in the query.

#### 26.2.2.3 Wiki Formatting in Queries

It is possible to format the query result with wiki markup. This enables you to display information from the query directly in the event generated by the advisor.

The following example is taken from the data collection used by the Server Has Accounts Without A Password advisor:

    <query>
    <![CDATA[SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host)
           as user FROM mysql.user WHERE password='' /*!50507 AND (plugin = '' OR plugin IS NULL
           OR plugin = 'mysql_native_password') OR (plugin = 'sha256_password'
           AND authentication_string  = '')*/]]>
    </query>

The wiki markup formats the user and host into information readily displayed in the Events page of MySQL Enterprise Monitor User Interface. This example lists the user name and host for all accounts without a defined password.

See Section 26.1.7, “Wiki Format” for more information on the supported wiki markup.

### 26.2.3 Data Collection Attributes

To properly evaluate the data returned by the data collection, assign attributes to the returned values.

Attributes are defined using the following format:

    <attributes>
                <attribute name="AttributeName1" counter="true" type="INTEGER"/>
                <attribute name="AttributeName2" counter="false" type="STRING"/>
    </attributes>

Table 26.3 Attribute Elements

Name | Description
--- | ---
name | The name of the attribute defined in the AS clause of the query.
counter | Whether the attribute is a counter type. `true`: the attribute is a counter type. `false`: the attribute is not a counter type.
type | The attribute value type. Possible values are INTEGER, STRING or FLOAT.

Important

If an attribute type is incorrectly defined in the attribute definition, such as INTEGER instead of STRING, it is not possible to change the value in the `custom.xml` after the agent has started. This is because it is not possible for the agent to alter attribute types after they are defined. Attempting to change it in that manner results in an `InvalidValueForTypeException` error. To correct this, you must stop the agent, edit the type definition, rename the attribute, and restart the agent.

#### 26.2.3.1 Default Values

If all the attributes are of the same type, it is not necessary to define the types for each attribute. Instead, define a default element at the beginning of the attribute list. In the following example, the default element assigns the same counter and type to each attribute:

    <attributes>
      <default counter="true" type="INTEGER"/>
      <attribute name="bytes_read"/>
      <attribute name="bytes_written"/>
    </attributes>

It is possible to override the default setting by assigning a `counter`, `type`, or both to the attribute definition. For example:

      <attributes>
    	<default counter="true" type="INTEGER"/>
    	<attribute name="total_wait_time_ms"/>
    	<attribute name="total_statements"/>
    	<attribute name="max_wait_time_ms" counter="false"/>
    	<attribute name="total_errors"/>
    	<attribute name="total_warnings"/>
    	<attribute name="total_rows_returned"/>
    	<attribute name="total_lock_time_ms"/>
    </attributes>

#### 26.2.3.2 Returning Multiple Rows

It is possible to return more than one row, using the result type `CLASS_TYPE_1STCOL_ATTRIBUTES`. This result type enables the return of a two-column result set as key-value pair. Unlike the default attributes, which are taken from the column name, the key is the attribute name and the value is the attribute value.

Important

The key value must be unique across the result set.

The following example shows how a 2-column result set is returned and formatted by the `resulttype` element:

      <class>
            <namespace>mysql</namespace>
            <classname>rpl_semi_sync_vars</classname>
            <query><![CDATA[
    SHOW GLOBAL VARIABLES WHERE
    Variable_name='rpl_semi_sync_master_timeout' OR
    Variable_name='rpl_semi_sync_master_trace_level' OR
    Variable_name='rpl_semi_sync_master_wait_no_slave' OR
    Variable_name='rpl_semi_sync_master_enabled' OR
    Variable_name='rpl_semi_sync_slave_enabled'
    ]]></query>
        <resulttype>CLASS_TYPE_1STCOL_ATTRIBUTES</resulttype>
         <attributes>
          <attribute name="rpl_semi_sync_master_timeout" counter="false" type="INTEGER"/>
          <attribute name="rpl_semi_sync_master_trace_level" counter="false" type="INTEGER"/>
          <attribute name="rpl_semi_sync_master_wait_no_slave" counter="false" type="STRING"/>
          <attribute name="rpl_semi_sync_master_enabled" counter="false" type="STRING"/>
          <attribute name="rpl_semi_sync_slave_enabled" counter="false" type="STRING"/>
         </attributes>
    </class>

## 26.3 Event Notification Blackout Periods

During maintenance periods for database servers, you can suspend Event Handlers. During such a blackout period, Event Handlers are suspended. Agents continue to collect data, data is stored in the repository, and events are generated and displayed. Notifications, such as SNMP traps, emails and so on, are not generated.

To enable a blackout period for an individual instance, you can use the context menu on the MySQL Instances page. Open the instance menu and select Enable Event Handler Blackout. The instance name is greyed out to indicate the presence of an active blackout. No Event Handlers are triggered for the selected instance for the duration of the blackout period.

You can also enable a blackout period by entering the following URL into the address bar of your browser, substituting the appropriate host name, port and server name:

    https://HostName:18443/rest?command=blackout&server_name=ServerName:3306&blackout_state=true

Check the `configuration_report.txt` file for the host name and port to use. Specify the correct port for the Tomcat server. Specify the server to blackout using the name that appears in the Server Tree, including the colon and port number as shown in the preceding example.

When the HTTP authentication dialog box requesting your MySQL Enterprise Monitor User Interface user name and password opens, specify the credentials for the Manager user. Use the ID and password you specified when you initially logged in to the Monitor UI.

You can also blackout a server group by entering the following URL into the address bar of your browser, substituting the appropriate host name, and server group name:

    https://localhost:18443/rest?command=blackout&group_name=Finance&blackout_state=true

When the HTTP authentication dialog box opens, enter the administrator's credentials.

To confirm that a server is blacked out, check that its name is greyed out in the Monitor UI.

To reactivate the blacked-out server or server group, use the appropriate URL and query string, changing the `blackout_state=true` name/value pair to `blackout_state=false`. Again, this must be done by a user with administrative privileges.

Note

Restarting MySQL Enterprise Monitor does not reactivate a blacked out server.

### 26.3.1 Scripting Blackouts

You can write a script to black out a server, rather than opening a web browser and typing entries into the address bar. This section documents a sample blackout script that can be run from the command line.

Create the following file and save it as `blackout.pl`.

      #!/usr/bin/perl

      use LWP 5.64;

      # USAGE: blackout.pl servicemanager:18443 admin password servername:3306 true

      # $ARGV[0] = management server hostname:port
      # $ARGV[1] = management server username
      # $ARGV[2] = management server password
      # $ARGV[3] = mysqld managed instance server name and port
      # $ARGV[4] = blackout state (true/false)

      my $browser = LWP::UserAgent->new;
      $browser->credentials(
        $ARGV[0],
        'MEM',
        $ARGV[1],
        $ARGV[2]
      );

      my $url = URI->new('https://'.$ARGV[0].'/rest');

      $url->query_form( # And here the form data pairs:
        'command' => 'blackout',
        'server_name' => $ARGV[3],
        'blackout_state' => $ARGV[4]
      );

      my $response = $browser->post( $url );

      if (!$response->is_success) {
        die $response->status_line . "\n";
      }

      if ($response->content =~ /UserUnauthorizedException/ ||
           $response->content =~ /ServerDoesNotExistException/) {
      	die $response->content;
      }

Note

Windows users can omit the initial `#!` line.

On Unix systems, use the chmod +x blackout.pl command to make the file executable.

At the command line, enter `blackout.pl `servicemanager:18443` admin `password servername:3306` true`.

Check the `configuration_report.txt` file for the host name and port to use. Specify the correct port for the Tomcat server. Specify the server to black out using the name that appears in the Server Tree, including the colon and port number as shown in the preceding example. Specify the name of a user who is a "manager". A user with "dba" rights cannot black out a server, and the script does not display any error in this case.

To confirm that a server is blacked out, check that its name is greyed out in the Monitor UI. To end the blackout, run the same script, changing the final argument to `false`.

Note

Restarting MySQL Enterprise Monitor does not reactivate a blacked out server.

Prev | Up | Next
Chapter 25 Global Settings | Home | Part IV Using the Query Analyzer