13.Skill Check Administering a Replication Topology

Which are functions of a slave SQL thread? (Choose two)

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

Examine this parameter setting: SLAVE_PARALLEL_TYPE = DATABASE Which are true? (Choose two)

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

Which are required when troubleshooting a MySQL replication error? (Choose two)

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.

Querying the I/O thread status of a replication slave displays this status message: Waiting for the slave SQL thread to free enough relay log space What does this status signify?

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.

Which replication table in a performance schema contains the status of the SQL thread in a multithreaded slave?

replication_applier_status

replication_applier_status_by_coordinator

replication_applier_configuration

replication_applier_status_by_worker

You used the SHOW SLAVE STATUS command on a slave and received this output: … Master_Log_File: server1-bin.000004 Read_Master_Log_Pos: 275 Relay_Log_File: server2-relay-bin.000011 Relay_Log_Pos: 453 Relay_Master_Log_File: server1-bin.000002 Exec_Master_Log_Pos: 1307 … What does this status signify? (Choose two)

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.

---

13.Skill Check Administering a Replication Topology

Which are functions of a slave SQL thread? (Choose two)

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

Examine this parameter setting: SLAVE_PARALLEL_TYPE = DATABASE Which are true? (Choose two)

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.

Which are required when troubleshooting a MySQL replication error? (Choose two)

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

Querying the I/O thread status of a replication slave displays this status message: Waiting for the slave SQL thread to free enough relay log space What does this status signify?

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.

Which replication table in a performance schema contains the status of the SQL thread in a multithreaded slave?

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

You used the SHOW SLAVE STATUS command on a slave and received this output: … Master_Log_File: server1-bin.000004 Read_Master_Log_Pos: 275 Relay_Log_File: server2-relay-bin.000011 Relay_Log_Pos: 453 Relay_Master_Log_File: server1-bin.000002 Exec_Master_Log_Pos: 1307 … What does this status signify? (Choose two)

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.