reading events from the binary log and sending them to the slave I/O thread
distributing relay log events between worker threads on a multithreaded slave
allowing transactions updating different databases to run in parallel on a slave
connecting with the master’s binlog dump thread
copying events from the master relay log to the slave’s relay log
transactions that are part of the same binary log group commit on a master can to run in parallel on a slave
transactions that update different databases can run in parallel on a slave
master-slave data consistency is ensured when there are cross-database constraints
MASTER_AUTO_POSITION must be set to 1 for this setting to take effect
SLAVE_PARALLEL_WORKERS must be greater than zero for this setting to take effect
Verifying that the slave I/O thread serializes all event updates in the relay log.
Verifying that the Seconds_Behind_Master column displayed by the SHOW SLAVE STATUS command is zero.
Reviewing the events in the master binary log file.
Reviewing the Last_IO_Error and Last_SQL_Error columns displayed by the SHOW SLAVE STATUS command.
Verifing that both master and slave have unique nonzero server ID values.
The value of the relay_log_space_limit parameter is zero.
The combined size of the relay logs exceeds the value of relay_log_space_limit.
The I/O thread is executing a LOAD DATA statement and appending the data to a temporary file which will be processed by the slave.
The I/O thread is copying an event to the relay log for processing by the SQL thread.
replication_applier_status
replication_applier_status_by_coordinator
replication_applier_configuration
replication_applier_status_by_worker
The binary log was re-sent as the slave failed to acknowledge completion
Log events are being copied faster than they are being executed on the slave.
There is latency in the SQL thread rather than the I/O thread.
There is network latency when transferring events between master and slave.
The slave is still executing previous binary log events but has received another binary log from the master.
reading events from the binary log and sending them to the slave I/O thread
distributing relay log events between worker threads on a multithreaded slave
allowing transactions updating different databases to run in parallel on a slave
connecting with the master’s binlog dump thread
copying events from the master relay log to the slave’s relay log
答案:
allowing transactions updating different databases to run in parallel on a slave
distributing relay log events between worker threads on a multithreaded slave
transactions that are part of the same binary log group commit on a master can to run in parallel on a slave
transactions that update different databases can run in parallel on a slave
master-slave data consistency is ensured when there are cross-database constraints
MASTER_AUTO_POSITION must be set to 1 for this setting to take effect
SLAVE_PARALLEL_WORKERS must be greater than zero for this setting to take effect
答案:
SLAVE_PARALLEL_WORKERS must be greater than zero for this setting to take effect
transactions that update different databases can run in parallel on a slave
说明:
Multithreaded Slaves
• Use multithreaded slaves to reduce slave lag.
• Set the slave_parallel_workers variable to a value greater than zero to create that
number of worker threads.
– With multiple worker threads, the slave SQL thread does not apply events directly,
but delegates responsibility to worker threads.
• Set the slave_parallel_type variable to specify the parallelization policy.
– DATABASE (default): Transactions that update different databases are applied in
parallel.
– LOGICAL_CLOCK: Transactions that are part of the same binary log group commit on
a master are applied in parallel on a slave.
— The master server can configure the binary log to record commit timestamps or write sets
using the binlog_transaction_dependency_tracking variable.
— Set slave_preserve_commit_order variable to ON to preserve the commit order.
Verifying that the slave I/O thread serializes all event updates in the relay log.
Verifying that the Seconds_Behind_Master column displayed by the SHOW SLAVE STATUS command is zero.
Reviewing the events in the master binary log file.
Reviewing the Last_IO_Error and Last_SQL_Error columns displayed by the SHOW SLAVE STATUS command.
Verifing that both master and slave have unique nonzero server ID values.
答案:
Verifing that both master and slave have unique nonzero server ID values.
Reviewing the Last_IO_Error and Last_SQL_Error columns displayed by the SHOW SLAVE STATUS command.
说明：
Troubleshooting MySQL Replication
• View the error log.
– The error log can provide you with enough information to identify and correct
problems in replication.
• Issue a SHOW MASTER STATUS statement on the master.
– Logging is enabled if the position value is non-zero.
• Verify that both the master and slave have a unique non-zero server ID value.
– The master and the slave must have different server IDs.
• Issue a SHOW SLAVE STATUS command on the slave or query the replication tables in
Performance Schema.
– Slave_IO_Running and Slave_SQL_Running display Yes when the slave is
functioning correctly.
– Last_IOError and Last
The value of the relay_log_space_limit parameter is zero.
The combined size of the relay logs exceeds the value of relay_log_space_limit.
The I/O thread is executing a LOAD DATA statement and appending the data to a temporary file which will be processed by the slave.
The I/O thread is copying an event to the relay log for processing by the SQL thread.
答案:
The combined size of the relay logs exceeds the value of relay_log_space_limit.
说明：
Replication Slave I/O Thread States
• Reconnecting after a failed master event read
– The thread is trying to reconnect to the master.
– When the thread reconnects, the state becomes Waiting for master to send
event.
• Waiting for the slave SQL thread to free enough relay log space
– The combined size of the relay logs exceeds the value of
relay_log_space_limit.
— Only if non-zero. A zero value means there is no limit imposed on the size of the relay
logs.
– The I/O thread is waiting until the SQL thread frees enough space in the relay logs by
processing and then deleting their contents.
replication_applier_status
replication_applier_status_by_coordinator
replication_applier_configuration
replication_applier_status_by_worker
答案:
replication_applier_status_by_coordinator
说明：
Replication Tables in Performance Schema
Table Name Contains
replication_connection_configuration Configuration parameters for connecting to the master
replication_connection_status Status of the current connection to the master
replication_applier_configuration Configuration parameters for the transaction applier on
the slave
replication_applier_status Current status of the transaction applier on the slave
replication_applier_status_by_coordinator Status of the coordinator thread in a multithreaded slave
replication_applier_status_by_worker Status of applier thread in a single-threaded slave or
worker threads in a multithreaded slave
replication_applier_filters Information about the replication filters configured on
specific replication channels
replication_applier_global_filters Information about global replication filters (all channels)
replication_group_members Network and status information for group members
replication_group_member_stats Statistical information about group members and the
transactions they participate in
The binary log was re-sent as the slave failed to acknowledge completion
Log events are being copied faster than they are being executed on the slave.
There is latency in the SQL thread rather than the I/O thread.
There is network latency when transferring events between master and slave.
The slave is still executing previous binary log events but has received another binary log from the master.
答案:
Log events are being copied faster than they are being executed on the slave.
There is latency in the SQL thread rather than the I/O thread.
The post MySQL 8.0 for Database Administrators OCP 课程习题13 first appeared on C1G军火库.