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 the values 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 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 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

Replication Configuration Advisor

Analyzes the configuration of masters and slaves in replication
topologies and alerts when configuration problems have been
detected:

More than one server has the same value for server_id
(duplicate server IDs)

The max_allowed_packet size on a slave is less than its
master

When a master is replicating to a slave that has an older
version of the MySQL Server than the master

Replication Status Advisor

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

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

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

Slave Execution Position Too Far Behind Read Position

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. If the position from which the SQL thread is reading is
way 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 slave may not reflect the
latest changes made on the master.

Default frequency 00:05:00

Default auto-close enabled no

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 Master Info/Relay Log Info Not Crash Safe

MySQL now supports logging of master connection information and
of slave relay log information to tables as well as files. In
order for replication to be crash-safe, that information must be
logged to tables and those tables must each use a transactional
storage engine such as InnoDB.

Default frequency 06:00:00

Default auto-close enabled yes

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

Slave 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.

Default frequency 06:00:00

Default auto-close enabled yes

Slave SQL Thread Reading From Older Relay Log Than I/O Thread

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. If the SQL thread is reading from an older relay log
than the one 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 slave may not reflect the latest changes made on
the master.

Default frequency 00:05:00

Default auto-close enabled no

Slave Too Far Behind Master

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

Default frequency 00:01: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.