Documentation Home
MySQL Enterprise Monitor 8.0 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 6.0Mb
PDF (A4) - 6.0Mb


20.9 Replication Advisors

This section describes the Replication Advisors.

Binary Log Checksums Disabled

Binary logs written and read by the MySQL Server are now crash-safe, because only complete events (or transactions) are logged or read back. By default, the server logs the length of the event as well as the event itself and uses this information to verify that the event was written correctly.

You can also cause the server to write checksums for the events using CRC32 checksums by setting the binlog_checksum system variable, to add an extra level of safety to the logs and the replication process. To cause the server to read checksums from the binary log, use the master_verify_checksum system variable. The slave_sql_verify_checksum system variable causes the slave SQL thread to read checksums from the relay log.

Default frequency 06:00:00

Default auto-close enabled yes

Binary Log File Count Exceeds Specified Limit

The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables replication as well as point-in-time recovery, preventing data loss during a disaster recovery situation. It also enables you to review all alterations made to your database. However, binary logs consume disk space and file system resources, and can be removed from a production server after they are no longer needed by the slaves connecting to this master server, and after they have been backed up.

Default frequency 06:00:00

Default auto-close enabled no

Binary Log Row Based Images Excessive

As of MySQL Server 5.6, row-based replication now supports row image control. By logging only those columns required for uniquely identifying and executing changes on each row (as opposed to all columns) for each row change, it is possible to save disk space, network resources, and memory usage. You can determine whether full or minimal rows are logged by setting the binlog_row_image server system variable to one of minimal (log required columns only), full (log all columns), or noblob (log all columns except for unneeded BLOB or TEXT columns).

Default frequency 06:00:00

Default auto-close enabled yes

Binary Log Space Exceeds Specified Limit

The binary log is a set of files that contain information about data modifications made by the MySQL server. It enables replication as well as point-in-time recovery, preventing data loss during a disaster recovery situation. It also enables you to review all alterations made to your database.

However, binary logs can consume a very large amount of disk space and should be removed from a production server to free up space after they are no longer needed by the slaves connecting to this master server, and after they have been backed up.

Default frequency 06:00:00

Default auto-close enabled no

Replication Configuration Advisor

Analyzes the configuration of sources and replicas in replication topologies and alerts when configuration problems have been detected:

Master Not Verifying Checksums When Reading From Binary Log

Binary logs written and read by the MySQL Server are now crash-safe, because only complete events or transactions are logged or read back. By default, the server logs the length of the event as well as the event itself and uses this information to verify that the event was written correctly.

You can also cause the server to write checksums for the events using CRC32 checksums by setting the binlog_checksum system variable, to add an extra level of safety to the logs and the replication process. To cause the server to read checksums from the binary log, use the master_verify_checksum system variable. The slave_sql_verify_checksum system variable causes the slave SQL thread to read checksums from the relay log.

Default frequency 06:00:00

Default auto-close enabled yes

Replication Status Advisor

Monitors slave replication status and alerts when replication has stopped or is compromised in some way, for example one of the slave threads has stopped, displays the last error messages seen, and where possible provides specific advice to fix the errors.

Slave Detection Of Network Outages Too High

Slaves must deal with network connectivity outages that affect the ability of the slave to get the latest data from the master, and hence cause replication to fall behind. However, the slave notices the network outage only after receiving no data from the master for slave_net_timeout seconds. You may want to decrease slave_net_timeout so the outages, and associated connection retries, are detected and resolved faster. The default for this parameter is 3600 seconds (1 hour), which is too high for many environments.

Default frequency 06:00:00

Default auto-close enabled no

Replica Execution Position Too Far Behind

When a replica receives updates from its source, the I/O thread stores the data in local files known as relay logs. The replica's applier thread reads the relay logs and executes the updates they contain. If the position from which the applier thread is reading is far behind the position to which the I/O thread is currently writing, it is a sign that replication is getting behind and results of queries directed to the replica may not reflect the latest changes made on the source.

Enables definition of the following thresholds:

  • Same File Position Thresholds: Alert generated if the applier thread reading position and the I/O thread writing position difference is larger than the configured threshold and are reading/writing to the same file.

  • Different File Position Thresholds: Alert are generated if the applier thread reading position is larger than the configured threshold and the I/O thread is writing to a different file.

Default frequency 00:05:00

Default auto-close enabled no

Replication Too Far Behind

If a replica is too far behind the source, results of queries directed to the replica may not reflect the latest changes made on the source.

Default frequency 00:01:00

Default auto-close enabled yes

Slave Has Login Accounts With Inappropriate Privileges

Altering and dropping tables on a slave can break replication. Unless the slave also hosts non-replicated tables, there is no need for accounts with these privileges. As an alternative, you should set the read_only flag ON so the server allows no updates except from users that have the SUPER privilege or from updates performed by slave threads.

Default frequency 06:00:00

Default auto-close enabled no

Slave Not Configured As Read Only

Arbitrary or unintended updates to a slave may break replication or cause a slave to be inconsistent with respect to its master. Making a slave read_only can be useful to ensure that a slave accepts updates only from its master server and not from clients; it minimizes the possibility of unintended updates.

Default frequency 06:00:00

Default auto-close enabled no

Slave Not Verifying Checksums When Reading From Relay Log

Binary logs written and read by the MySQL Server are now crash-safe, because only complete events (or transactions) are logged or read back. By default, the server logs the length of the event as well as the event itself and uses this information to verify that the event was written correctly.

You can also cause the server to write checksums for the events using CRC32 checksums by setting the binlog_checksum system variable, to add an extra level of safety to the logs and the replication process. To cause the server to read checksums from the binary log, use the master_verify_checksum system variable. The slave_sql_verify_checksum system variable causes the slave SQL thread to read checksums from the relay log.

Default frequency 06:00:00

Default auto-close enabled yes

Relay Log Space Is Very Large

When a slave receives updates from its master, the I/O thread stores the data in local files known as relay logs. The slave's SQL thread reads the relay logs and executes the updates they contain. After the SQL thread has executed all the updates in a relay log, the file is no longer needed and can be deleted to conserve disk space.

Default frequency 06:00:00

Default auto-close enabled no

Slave Relay Logs Not Automatically Purged

When a slave receives updates from its master, the I/O thread stores the data in local files known as relay logs. The slave's SQL thread reads the relay logs and executes the updates they contain. After the SQL thread has executed all the updates in a relay log, the file is no longer needed and can be deleted to conserve disk space.

Default frequency 06:00:00

Default auto-close enabled no

Slave SQL Processing Not Multi-Threaded

As of MySQL Server version 5.6, replication now supports parallel execution of transactions with multi-threading on the slave. When parallel execution is enabled, the slave SQL thread acts as the coordinator for a number of slave worker threads as determined by the value of the slave_parallel_workers server system variable.

The current implementation of multi-threading on the slave assumes that data and updates are partitioned on a per-database basis, and that updates within a given database occur in the same relative order as they do on the master. However, it is not necessary to coordinate transactions between different databases. Transactions can then also be distributed per database, which means that a worker thread on the slave can process successive transactions on a given database without waiting for updates to other databases to complete.

Transactions on different databases can occur in a different order on the slave than on the master, simply checking for the most recently executed transaction is not a guarantee that all previous transactions on the master have been executed on the slave. This has implications for logging and recovery when using a multi-threaded slave.

Finally, note that beginning with MySQL Server 5.7.2, there is also support for intra-schema parallelization (LOGICAL_CLOCK). See slave-parallel-type for more information.

Default frequency 06:00:00

Default auto-close enabled yes

Slave Without REPLICATION SLAVE Accounts

If the master ever fails, you may want to use one of the slaves as the new master. An account with the REPLICATION SLAVE privilege must exist for a server to act as a replication master (so a slave can connect to it), so it's a good idea to create this account on your slaves to prepare it to take over for a master if needed.

Default frequency 06:00:00

Default auto-close enabled no