MySQL Bin Log Test

The binary log is a set of log files that contain information about data modifications made to a MySQL server instance. The binary log contains all statements that update data. It also contains statements that potentially could have updated it (for example, a DELETE which matched no rows), unless row-based logging is used. Statements are stored in the form of "events" that describe the modifications. The binary log also contains information about how long each statement took that updated data.

The binary log also contains some other metadata, including:

  • Information about the state of the server that is needed to reproduce statements correctly

  • Error codes

  • Metadata needed for the maintenance of the binary log itself (for example, rotate events)

The binary log has two important purposes:

  • For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. Many details of binary log format and handling are specific to this purpose. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. A slave stores events received from the master in its relay log until they can be executed. The relay log has the same format as the binary log.

  • Certain data recovery operations require use of the binary log. After a backup file has been restored, the events in the binary log that were recorded after the backup are re-executed. These events bring databases up to date from the point of the backup.

If the server is unable to write to the binary log, flush binary log files, or synchronize the binary log to disk, the binary log on the replication source server can become inconsistent and replicas can lose synchronization with the source. This may result in outdated data in the replicas and sometimes, data loss too may occur if the source server fails/crashes before synchronization with the replicas. To avoid such eventualities, it is essential to keep track on the growth of the binary log. At the same time, it is also essential for the administrators to keep track on any abnormalities noticed in the size and growth of the binary log. The MySQL Binary Log test helps administrators in this regard!

This test reports the number of log files in the binary log and tracks the total size of the binary log. This test also periodically reports the growth of the binary log. Using this test, administrators can figure out abnormal growth patterns of the binary log and analyze the reason behind the same. Detailed Diagnostics reveal the exact name of the log files that have been modified recently.

Target of the test : A MySQL server

Agent deploying the test : An internal/remote agent

Outputs of the test : One set of results for the target database server instance being monitored

Configurable parameters for the test
Parameter Description

Test Period

How often should the test be executed.

Host

The IP address of the MySQL server.

Port

The port at which the specified host listens.

Database

Specify the name of a database on the target MySQL database server being monitored

Username and Password

The eG agent has to be configured with the credentials of a user who has server-wide Process and Select privileges on the monitored MySQL server. To know how to create such a user, refer to Pre-requisites for Monitoring the MySQL Server topic.

For the MySQL Bin Log test to run, the user should additionally possess the following privileges. To configure the above-mentioned user with the specific privileges, issue the commands given below:

GRANT REPLICATION CLIENT on *.* to '<username>'@'<IP_address_of_eG_agent>';

GRANT REPLICATION SLAVE on *.* to '<username>'@'<IP_address_of_eG_agent>';

GRANT EXECUTE on *.* to '<username>'@'<IP_address_of_eG_agent>';

Confirm Password

Confirm the password by retyping it here.

Detailed Diagnosis

To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option.

The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled:

  • The eG manager license should allow the detailed diagnosis capability
  • Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Bin log file count

Indicates the number of log files in the binary log.

Number

  

Total size

Indicates the total size of the binary log.

MB

 

Growth rate

Indicates the rate at which the binary log file had grown during the last measurement period.

MB/sec

A high value for this measure or a consistent increase in its value indicates that the binary log is rapidly growing and may end up occupying too much space on the disk.

The detailed diagnosis of this measure lists the name of the log file, the size of the log file and the rate at which each log file had grown.