14. Skill Check Achieving High Availability with MySQL InnoDB Cluster

Which are true about a MySQL InnoDB cluster? (Choose two)

It provides a confgurable capability for the administration of a group of server instances in a cluster.

It uses AdminAPI to work with relational and document data in the MySQL Document Store.

It provides real-time access with data consistency across partitioned and distributed datasets.

It relies on MySQL Router to interconnect replicated servers in a group.

It uses MySQL Group Replication to replicate data between all servers in a group.

How does MySQL Shell (mysqlsh) support the administration of a cluster? (Choose two)

It enables replicated server updates using binary log position and global transaction identifiers (GTIDs).

It manages failover by automatically routing server connections.

It provides scripting capabilities using JavaScript, Python, and SQL commands.

It implements a pluggable architecture and allows developers to create customized plug-ins.

It enables access to MySQL features via APIs

You have a live cluster with three servers. Executing the dba.getCluster() function returns the Cluster value “cluster”. Which methods must be used, all of which are required, to add a fourth server to the cluster? (Choose three)

cluster.rescan()

cluster.rejoinInstance()

dba.configureLocalInstance()

cluster.addInstance()

cluster.dissolve()

dba.configureInstance()

Which are functions of Group Replication? (Choose two)

assigning replication servers to groups of three to nine servers

in multi-primary mode, enabling group members to certify the order of transactions within the global sequence of transactions

enabling automatic avoidance of “split-brain” situations among group members

in multi-primary mode, enabling automatic election of a new primary when the primary server fails

in single-primary mode, enabling all servers to accept transactions but only the primary decides the order to commit

Examine this mysqlsh statement and output: mysql-js> cluster.checkInstanceState(“root@server4:3314”) … … { "reason": "recoverable", "state": "ok" } What does the output signify?

The instance has not executed any GTID transactions; therefore, it cannot conflict with the GTIDs executed by the cluster.

The instance has more executed GTIDs than the executed GTIDs of the cluster seed instances.

The instance has executed GTIDs, which diverge with the executed GTIDs of the cluster seed instances.

The instance has executed GTIDs, which do not conflict with the executed GTIDs of the cluster seed instances.

myCluster is an InnoDB cluster. Examine this mysqlsh statement and output: mysql-js > cluster.status() { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "server2:3312", "status": "OK_NO_TOLERANCE", … What does this status signify? (Choose two)

There are enough members online to form a quorum.

One or more members may be online but their status is not ONLINE or RECOVERING.

One or more members are online but cannot form a quorum.

There is no redundancy in the cluster, and the failure of a member will result in database outage.

There is sufficient redundancy available to tolerate at least one failure.

14. Skill Check Achieving High Availability with MySQL InnoDB Cluster

Which are true about a MySQL InnoDB cluster? (Choose two)

It provides a confgurable capability for the administration of a group of server instances in a cluster.

It uses AdminAPI to work with relational and document data in the MySQL Document Store.

It provides real-time access with data consistency across partitioned and distributed datasets.

It relies on MySQL Router to interconnect replicated servers in a group.

It uses MySQL Group Replication to replicate data between all servers in a group.

答案：
It provides a confgurable capability for the administration of a group of server instances in a cluster.
It uses MySQL Group Replication to replicate data between all servers in a group.

说明：
What Is MySQL InnoDB Cluster?
• Provides a complete and scalable high availability solution for MySQL
– Easy to configure and administer a group of server instances in a cluster
• Uses MySQL Group Replication to replicate data between all servers in the group
– The AdminAPI removes the need to work with group replication directly.
– You work with the AdminAPI via MySQL Shell using your choice of Python or
JavaScript language.
• Manages failover automatically
– If a server in the group goes down, the cluster reconfigures itself.
– It requires at least three servers for the group to be fault tolerant
• Enables clients to connect to the group transparently
– Clients connect to the group via MySQL Router and do not have to know the details
of the individual instances within the group.

How does MySQL Shell (mysqlsh) support the administration of a cluster? (Choose two)

It enables replicated server updates using binary log position and global transaction identifiers (GTIDs).

It manages failover by automatically routing server connections.

It provides scripting capabilities using JavaScript, Python, and SQL commands.

It implements a pluggable architecture and allows developers to create customized plug-ins.

It enables access to MySQL features via APIs

答案：
It provides scripting capabilities using JavaScript, Python, and SQL commands.

It enables access to MySQL features via APIs.

说明：
MySQL Shell (mysqlsh)
• Is an advanced client and code editor for MySQL
• Provides scripting capabilities by using JavaScript, Python, or SQL commands
– You can enter commands interactively or execute them in batches.
– You can switch between languages by entering \js, \py, or \sql, respectively.
• Enables access to MySQL features via APIs
– XDevAPI: Communicate with a MySQL server running the X Plugin to work with both relational
and document data in the MySQL Document Store.
– AdminAPI: Configure and administer a MySQL InnoDB cluster.
• Supports output in tab delimited, table, and JSON (JavaScript Object Notation) formats
• Interacts with a MySQL server via a global Session object
– If using Python and JavaScript, you create the Session object by calling the getSession()
method on the mysqlx module.
– If using SQL, the Session object is created when the client connects.

You have a live cluster with three servers. Executing the dba.getCluster() function returns the Cluster value “cluster”. Which methods must be used, all of which are required, to add a fourth server to the cluster? (Choose three)

cluster.rescan()

cluster.rejoinInstance()

dba.configureLocalInstance()

cluster.addInstance()

cluster.dissolve()

dba.configureInstance()

答案：
cluster.rescan()
cluster.addInstance()
dba.configureInstance()

Which are functions of Group Replication? (Choose two)

assigning replication servers to groups of three to nine servers

in multi-primary mode, enabling group members to certify the order of transactions within the global sequence of transactions

enabling automatic avoidance of “split-brain” situations among group members

in multi-primary mode, enabling automatic election of a new primary when the primary server fails

in single-primary mode, enabling all servers to accept transactions but only the primary decides the order to commit

答案：
assigning replication servers to groups of three to nine servers
in multi-primary mode, enabling group members to certify the order of transactions within the global sequence of transactions

说明:
How Group Replication Works
• Servers belong to a replication group.
– A replication group can contain up to nine servers.
— At least three servers are required to be fault tolerant.
– Group Replication uses global transaction identifiers (GTIDs).
– The group is defined by a UUID.
• Group membership is managed automatically.
– A server that joins or rejoins the group will automatically synchronize with the others.
– Servers can leave and join the group at any time.
• Replication groups operate in one of two modes:
– Single-primary: One server in the group accepts updates.
– Multi-primary: All servers in the group accept updates.
• Changes are replicated to all members of the group.

Examine this mysqlsh statement and output: mysql-js> cluster.checkInstanceState(“root@server4:3314”) … … { "reason": "recoverable", "state": "ok" } What does the output signify?

The instance has not executed any GTID transactions; therefore, it cannot conflict with the GTIDs executed by the cluster.

The instance has more executed GTIDs than the executed GTIDs of the cluster seed instances.

The instance has executed GTIDs, which diverge with the executed GTIDs of the cluster seed instances.

The instance has executed GTIDs, which do not conflict with the executed GTIDs of the cluster seed instances.

答案：
The instance has executed GTIDs, which do not conflict with the executed GTIDs of the cluster seed instances.

说明：
Checking the State of an Instance
• Execute cluster.checkInstanceState(instance) to verify the instance GTID
state in relation to the cluster.
– Analyzes the instance executed GTIDs with the executed/purged GTIDs on the
cluster to determine if the instance is valid for the cluster.
• The output of this method is one of the following:
– OK new: The instance has not executed any GTID transactions; therefore, it cannot
conflict with the GTIDs executed by the cluster.
– OK recoverable: The instance has executed GTIDs, which do not conflict with the
executed GTIDs of the cluster seed instances.
– ERROR diverged: The instance has executed GTIDs, which diverge with the
executed GTIDs of the cluster seed instances.
– ERROR lost_transactions: The instance has more executed GTIDs than the
executed GTIDs of the cluster seed instances.

myCluster is an InnoDB cluster. Examine this mysqlsh statement and output: mysql-js > cluster.status() { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "server2:3312", "status": "OK_NO_TOLERANCE", … What does this status signify? (Choose two)

There are enough members online to form a quorum.

One or more members may be online but their status is not ONLINE or RECOVERING.

One or more members are online but cannot form a quorum.

There is no redundancy in the cluster, and the failure of a member will result in database outage.

There is sufficient redundancy available to tolerate at least one failure.

答案：
There is no redundancy in the cluster, and the failure of a member will result in database outage.
There are enough members online to form a quorum.

说明:
OK_NO_TOLERANCE = 当前服务正常（有 quorum） + 无故障缓冲（单点即崩）。需尽快修复离线节点或扩容！

节点有哪状态
ONLINE – 节点状态正常。
OFFLINE – 实例在运行，但没有加入任何Cluster。
RECOVERING – 实例已加入Cluster，正在同步数据。
ERROR – 同步数据发生异常。
UNREACHABLE – 与其他节点通讯中断，可能是网络问题，可能是节点crash。
MISSING 节点已加入集群，但未启动group replication

集群有哪些状态
OK – 所有节点处于online状态，有冗余节点。
OK_PARTIAL – 有节点不可用，但仍有冗余节点。
OK_NO_TOLERANCE – 有足够的online节点，但没有冗余，例如：两个节点的Cluster，其中一个挂了，集群就不可用了。
NO_QUORUM – 有节点处于online状态，但达不到法定节点数，此状态下Cluster无法写入，只能读取。
UNKNOWN – 不是online或recovering状态，尝试连接其他实例查看状态。
UNAVAILABLE – 组内节点全是offline状态，但实例在运行，可能实例刚重启还没加入Cluster。

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.

12.Skill Check Configuring a Replication Topology

　Which are true for semisynchronous replication? (Choose two)

It improves query execution performance of the master and at least one slave.

It restricts masters and slaves to use only statement-based binary logging.

It ensures data integrity between the master and at least one slave.

If the master is configured as semisynchronous, then all slaves must be configured as semisynchronous.

After committing a transaction, the master blocks until at least one slave acknowledges the transaction.

　Which are advantages of MySQL replication? (Choose two)

Another server, either master or slave, can take over the duties of a crashed server to prevent application outages.

Applications can scale-out SQL queries to multiple servers to reduce the response time.

Clients can run queries on different servers simultaneously to prevent data conflicts.

Same-level slave servers can share updates in case of the master’s failure to enable data coherency.

When the master is down, a slave server can be configured to automatically become the master to prevent database inconsistencies.

Which are requirements for enabling multisource replication? (Choose two)

The slave must create a replication channel for each master.

The slave must use FILE-based repositories.

All masters and slaves must be in a star topology.

All masters and slaves must be configured to prevent data conflicts.

The slave must use TABLE-based repositories.

Which steps are required to configure Server2 as slave of Server1? (Choose two)

Execute this command on Server2: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server1-bin.000001′, -> MASTER_LOG_POS=;

Add these settings to the Server1 my.sql file: [mysqld] log-bin=server1-bin

Add these settings to the Server2 my.sql file: [mysqld] log-bin=server2-bin

Execute this command on Server1: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server2-bin.000001′, -> MASTER_LOG_POS=;

Create a user on Server2 and grant them the REPLICATION SLAVE permission.

Which statement uses the global transaction identifier (GTID) replication protocol?

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3313, MASTER_AUTO_POSITION=1;

CHANGE MASTER TO RELAY_LOG_FILE=’slave-relay-bin.006′, RELAY_LOG_POS=4025;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3312, MASTER_LOG_FILE=’server2-bin.000001′, MASTER_LOG_POS=723074;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3311, MASTER_LOG_FILE=’server1-bin.000001′, MASTER_LOG_POS=155;

Which parameter settings enable and enforce logging based on global transaction identifier (GTID) for all transactions? (Choose two)

gtid-mode=OFF_PERMISSIVE enforce-gtid-consistency=OFF

gtid-mode=OFF enforce-gtid-consistency=WARN

gtid-mode=ON enforce-gtid-consistency=ON

gtid-mode=ON_PERMISSIVE enforce-gtid-consistency=ON

gtid-mode=ON enforce-gtid-consistency=WARN

12.Skill Check Configuring a Replication Topology

　Which are true for semisynchronous replication? (Choose two)

It improves query execution performance of the master and at least one slave.

It restricts masters and slaves to use only statement-based binary logging.

It ensures data integrity between the master and at least one slave.

If the master is configured as semisynchronous, then all slaves must be configured as semisynchronous.

After committing a transaction, the master blocks until at least one slave acknowledges the transaction.

答案：
It ensures data integrity between the master and at least one slave.
After committing a transaction, the master blocks until at least one slave acknowledges the transaction.

说明：
Advantages and Disadvantages of Semisynchronous Replication
Semisynchronous replication:
• Ensures data integrity
• Results in reduced performance
– The master waits for the slave to respond before committing the transaction.
— The timeout period is controlled by the rpl_semi_sync_master_timeout variable
value. The default is 10,000 ms (10 seconds).
— If no response is received, the master still commits the transaction but reverts to
asynchronous mode.
– The extra time taken by each transaction includes:
— The TCP/IP roundtrip to send the commit to the slave
— The slave recording the commit in its relay log
— The master waiting for the slave’s acknowledgment of that commit
• Is suitable primarily for servers that are physically co-located, communicating over fast
networks

　Which are advantages of MySQL replication? (Choose two)

Another server, either master or slave, can take over the duties of a crashed server to prevent application outages.

Applications can scale-out SQL queries to multiple servers to reduce the response time.

Clients can run queries on different servers simultaneously to prevent data conflicts.

Same-level slave servers can share updates in case of the master’s failure to enable data coherency.

When the master is down, a slave server can be configured to automatically become the master to prevent database inconsistencies.

答案：
Another server, either master or slave, can take over the duties of a crashed server to prevent application outages.

Applications can scale-out SQL queries to multiple servers to reduce the response time.

Which are requirements for enabling multisource replication? (Choose two)

The slave must create a replication channel for each master.

The slave must use FILE-based repositories.

All masters and slaves must be in a star topology.

All masters and slaves must be configured to prevent data conflicts.

The slave must use TABLE-based repositories.

答案：
The slave must create a replication channel for each master.
The slave must use TABLE-based repositories.

说明：
Backing Up from Multiple Sources to a Single Server
• Multisource replication can be used to:
– Back up multiple servers to a single server
– Merge table shards
– Consolidate data from multiple servers to a single server
• The slave creates a replication channel for each master from which it receives
transactions.
– Use the CHANGE MASTER TO … FOR CHANNEL channelname syntax:
• Replicate from all channels concurrently or start and stop individual channels:
CHANGE MASTER TO …
MASTER_LOG_FILE=’binlog.000006′,
MASTER_LOG_POS=143 FOR CHANNEL ‘shard-1’;
START SLAVE IO_THREAD FOR CHANNEL ‘shard-1’;

Configuring Multi-Source Replication for a Binary Log Based Master

1. Enable binary logging on the master by using –log-bin.
2. Create a replication user.
3. Note the current binary log file and position.
   – MASTER_LOG_FILE and MASTER_LOG_POSITION
4. Verify that the slave is using TABLE-based replication repositories.
5. Execute CHANGE MASTER TO… to add a new master to a channel by using a
   FOR CHANNEL clause.
   – Example: Add a new master with host name master1 using port 3451 to channel
   master-1.
   CHANGE MASTER TO MASTER_HOST=’master1′, MASTER_PORT=3451,
   MASTER_USER=’rpl’, MASTER_PASSWORD=’pass’,
   MASTER_LOG_FILE=’master1-bin.000003′, MASTER_LOG_POS=719,
   FOR CHANNEL ‘master-1’;

Which steps are required to configure Server2 as slave of Server1? (Choose two)

Execute this command on Server2: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server1-bin.000001′, -> MASTER_LOG_POS=;

Add these settings to the Server1 my.sql file: [mysqld] log-bin=server1-bin

Add these settings to the Server2 my.sql file: [mysqld] log-bin=server2-bin

Execute this command on Server1: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server2-bin.000001′, -> MASTER_LOG_POS=;

Create a user on Server2 and grant them the REPLICATION SLAVE permission.

答案：
Execute this command on Server2: CHANGE MASTER TO -> MASTER_HOST=, -> MASTER_PORT=, -> MASTER_LOG_FILE=’server1-bin.000001′, -> MASTER_LOG_POS=;

Add these settings to the Server1 my.sql file: [mysqld] log-bin=server1-bin

Which statement uses the global transaction identifier (GTID) replication protocol?

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3313, MASTER_AUTO_POSITION=1;

CHANGE MASTER TO RELAY_LOG_FILE=’slave-relay-bin.006′, RELAY_LOG_POS=4025;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3312, MASTER_LOG_FILE=’server2-bin.000001′, MASTER_LOG_POS=723074;

CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3311, MASTER_LOG_FILE=’server1-bin.000001′, MASTER_LOG_POS=155;

答案：
CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_PORT=3313, MASTER_AUTO_POSITION=1;

说明：

Configuring Replication Slaves
• Restore the backup from the master.
– Verify that the gtid_purged variable is set if you are using GTIDs.
• Issue a CHANGE MASTER TO statement on each slave with the:
– Network location of the master
— MASTER_HOST and MASTER_PORT values
— Optionally, use MASTER_SSL and related options to encrypt network traffic between
masters and slaves during replication.
– Replication account username and password (with the REPLICATION SLAVE
privilege)
— MASTER_USER and MASTER_PASSWORD values
– Binary log coordinates from which to start replicating (if you are not using GTIDs)
— The MASTER_LOG_FILE and MASTER_LOG_POS values store the binary log position from
which the slave starts replicating.
— Specify MASTER_AUTO_POSITION=1 if you are using GTIDs.

Which parameter settings enable and enforce logging based on global transaction identifier (GTID) for all transactions? (Choose two)

gtid-mode=OFF_PERMISSIVE enforce-gtid-consistency=OFF

gtid-mode=OFF enforce-gtid-consistency=WARN

gtid-mode=ON enforce-gtid-consistency=ON

gtid-mode=ON_PERMISSIVE enforce-gtid-consistency=ON

gtid-mode=ON enforce-gtid-consistency=WARN

答案：
gtid-mode=ON enforce-gtid-consistency=ON

gtid-mode=ON_PERMISSIVE enforce-gtid-consistency=ON

说明：
Global Transaction Identifiers (GTIDs)
Global Transaction Identifiers (GTIDs) uniquely identify each transaction in a replication
topology.
• Each GTID is of the form:. For example:
• A GTID set contains a range of GTIDs:
• Enable GTID mode with the following options:
– gtid-mode=ON: Logs a unique GTID along with each transaction
– enforce-gtid-consistency: Disallows events that cannot be logged in a
transactionally safe way
– log-slave-updates: Records replicated events to the slave’s binary log

GTID_MOD
OFF :不产生GTID,Slave只接受不带GTID的事务
OFF_PERMISSIVE :不产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务
ON_PERMISSIVE :产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务
ON :产生GTID,Slave只能接受带GTID的事务。

11.Skill Check Performing Backups

How does the – – master-data option of the mysqldump command ensure data consistency during a database backup? (Choose two)

It starts a new binary log.

It acquires a global lock at the beginning of the backup operation.

It records the binlog position in the backup file.

It creates database and table structures but does not dump the data.

It locks tables during backup.

Which are features of the mysqlpump utility? (Choose three)

dumping InnoDB CREATE TABLESPACE statements

restoring databases using dumped data files

parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

taking physical backups of databases

faster reloading of secondary indexes for InnoDB tables

Which command forces the current binary log to close and the next incremental binary log to open?

mysql> GRANT RELOAD ON . TO ‘backupuser’@’localhost’;

mysql> FLUSH LOGS;

mysql> PURGE BINARY LOGS TO ‘binlog.000048’;

mysql> FLUSH BINARY LOGS;

Which are features or capabilities of multisource replication? (Choose three)

consolidating data from multiple servers to a single server

preventing slaves from making backups

backing up files not requiring additional storage or processing on the master

merging table shards

adding processing load on the master

backing up multiple servers to a single server

Which privileges does a user need to execute the mysqldump command? (Choose three)

SELECT for dumped tables

CREATE on each of the dumped objects

TRIGGER for dumped triggers

SHOW VIEW for dumped views

RELOAD on all databases and tables

ALTER on the database

Which commands back up the “employees” database to a single SQL script? (Choose two)

# mysql -uemployee -p employees < /labs/firewall_training.sql

# mysqldump -uroot -p –tab=/backups employees

# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

# mysqlimport -uroot -p emps2 /backups/dept_manager.txt

11.Skill Check Performing Backups

How does the – – master-data option of the mysqldump command ensure data consistency during a database backup? (Choose two)

It starts a new binary log.

It acquires a global lock at the beginning of the backup operation.

It records the binlog position in the backup file.

It creates database and table structures but does not dump the data.

It locks tables during backup.

答案：

It records the binlog position in the backup file.

It locks tables during backup.

说明:
Ensuring Data Consistency with mysqldump
Ensuring consistency:
• –master-data option alone
– Locks all tables during backup with FLUSH TABLES WITH READ LOCK
– Records the binlog position as a CHANGE MASTER TO statement in the backup file
— –master-data=2 records the position as a comment
• –master-data and –single-transaction options used together
– Does not lock tables – only InnoDB table consistency is guaranteed
– Acquires a global lock at the beginning of the backup operation to obtain a consistent
binary log position
• –lock-all-tables
– Satisfies consistency by locking all tables for the duration of the whole dump
• –flush-logs
– Starts a new binary log

Which are features of the mysqlpump utility? (Choose three)

dumping InnoDB CREATE TABLESPACE statements

restoring databases using dumped data files

parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

taking physical backups of databases

faster reloading of secondary indexes for InnoDB tables

答案:
parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

faster reloading of secondary indexes for InnoDB tables

说明:
mysqlpump
mysqlpump is very similar to mysqldump, but with the following enhancements:
• Provides better performance than mysqldump by extracting data in parallel threads
– mysqlpump divides the dump process into several subtasks and then adds these
subtasks to a multithreaded queue.
– This queue is then processed by N threads (two by default).
• Enables better control over which database objects to dump on each thread
• Dumps users as CREATE USER/GRANT statements instead of as INSERTs into the
mysql system database
• Enables compressed output
• Displays a progress indicator
• Provides faster secondary index reloading for InnoDB tables

Which command forces the current binary log to close and the next incremental binary log to open?

mysql> GRANT RELOAD ON . TO ‘backupuser’@’localhost’;

mysql> FLUSH LOGS;

mysql> PURGE BINARY LOGS TO ‘binlog.000048’;

mysql> FLUSH BINARY LOGS;

答案：
mysql> FLUSH LOGS;

Which are features or capabilities of multisource replication? (Choose three)

consolidating data from multiple servers to a single server

preventing slaves from making backups

backing up files not requiring additional storage or processing on the master

merging table shards

adding processing load on the master

backing up multiple servers to a single server

答案：
consolidating data from multiple servers to a single server
merging table shards
backing up multiple servers to a single server

说明：
Backing Up from Multiple Sources to a Single Server
• Multisource replication can be used to:
– Back up multiple servers to a single server
– Merge table shards
– Consolidate data from multiple servers to a single server
• The slave creates a replication channel for each master from which it receives
transactions.
– Use the CHANGE MASTER TO … FOR CHANNEL channelname syntax:
• Replicate from all channels concurrently or start and stop individual channels:
CHANGE MASTER TO …
MASTER_LOG_FILE=’binlog.000006′,
MASTER_LOG_POS=143 FOR CHANNEL ‘shard-1’;
START SLAVE IO_THREAD FOR CHANNEL ‘shard-1’;

Which privileges does a user need to execute the mysqldump command? (Choose three)

SELECT for dumped tables

CREATE on each of the dumped objects

TRIGGER for dumped triggers

SHOW VIEW for dumped views

RELOAD on all databases and tables

ALTER on the database

答案：
SELECT for dumped tables

TRIGGER for dumped triggers

SHOW VIEW for dumped views

说明：
Privileges Required for mysqldump
You must have the following privileges to use mysqldump:
• SELECT for dumped tables
• SHOW VIEW for dumped views
• TRIGGER for dumped triggers
• LOCK TABLES (unless you use the –-single-transaction option)
• Other options might require extra privileges. For example:
– To use the –flush-logs or –master-data options, you must have the RELOAD
privilege.
– To create a tab-delimited output with the –tab option, you must have the FILE
privilege.
– To use the –routines option to back up stored functions and procedures, you
must have the global SELECT privilege.

Which commands back up the “employees” database to a single SQL script? (Choose two)

# mysql -uemployee -p employees < /labs/firewall_training.sql

# mysqldump -uroot -p –tab=/backups employees

# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

# mysqlimport -uroot -p emps2 /backups/dept_manager.txt

答案：
# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

10.Skill Check Choosing a Backup Strategy

　Which techniques segregate data file backups from MySQL server data files? (Choose three)

replication

OS copy commands

binary logging

MySQL enterprise backup

distributed replicated block device

snapshots

Which are true about warm backups? (Choose two)

Updates are not possible during these backups.

When backups occurs, the server is in an inaccessible mode.

They do not impact system performance.

They permit applications to only read data.

They lock data using multiversion concurrency control (MVCC).

Which of these use full and binary log backups? (Choose two)

daily/hourly supplemental backups containing live data

backups using mysqldump with the –where option

weekly conditional backups of large transactional tables containing only fixed or historical data

multiple backups of data modifications taken each hour to minimize exposure during each day

backups using mysqlbackup from a replication slave

Which methods are used for logical backups? (Choose three)

MySQL enterprise backup

the mysqldump utility

the mysqlpump utility

snapshots

OS copy commands

SQL statements

Which are true about logical backups? (Choose three)

They use a snapshot capability that is external to MySQL.

They can be restored to MySQL databases on machines with different architectures than the one where the backup was created.

They can be taken to back up both local and remote MySQL servers.

They require the MySQL server to be running during the backups.

They generate SQL statements that can be used to recreate the orignal databases and tables.

They can identify objects that can cause damage and skip them during the backup.

Which are advantages of the RAID mirroring process? (Choose two)

It backs up both local and remote MySQL servers

It can reload a database on another server, running a different architecture

It recovers data in the event of a hardware failure

It enables the creation of live backups

It creates a SQL script that can be executed on a MySQL server

10.Skill Check Choosing a Backup Strategy

　Which techniques segregate data file backups from MySQL server data files? (Choose three)

replication

OS copy commands

binary logging

MySQL enterprise backup

distributed replicated block device

snapshots

答案:
replication
distributed replicated block device
snapshots

说明:
Physical Backup Conditions
• The server must not modify data files during backup.
– If you copy the live data files, prevent writes to those files:
— For InnoDB: MySQL server shutdown is required.
— For MyISAM: Lock tables to allow reads but not changes.
• You can also minimize the effect to MySQL and applications by using techniques that
separate the data files being backed up from the MySQL server:
– Snapshots
– Replication
– DRBD or other methods that copy the whole filesystem

Which are true about warm backups? (Choose two)

Updates are not possible during these backups.

When backups occurs, the server is in an inaccessible mode.

They do not impact system performance.

They permit applications to only read data.

They lock data using multiversion concurrency control (MVCC).

答案:
They permit applications to only read data.
Updates are not possible during these backups.

说明:
Warm Backups
• Take place while the data is being accessed
– In most cases, the data cannot be modified while the backup is taking place.
• Have the advantage of not having to completely lock out end users
– However, the disadvantage of not being able to modify the data while the backup is
taking place can make this type of backup not suitable for certain applications.
• Might result in performance issues because you cannot modify data during the backup.
– Updating users may be blocked for a long duration of time.

Which of these use full and binary log backups? (Choose two)

daily/hourly supplemental backups containing live data

backups using mysqldump with the –where option

weekly conditional backups of large transactional tables containing only fixed or historical data

multiple backups of data modifications taken each hour to minimize exposure during each day

backups using mysqlbackup from a replication slave

答案:
multiple backups of data modifications taken each hour to minimize exposure during each day

backups using mysqlbackup from a replication slave

说明:
More Complex Strategies
Combine multiple backup techniques to create more complex strategies:
• Example using full and binary log backups:
– Nightly backups using mysqlbackup from a replication slave
– Multiple binary log backups each hour to minimize exposure during each day
• Example using partial backups:
– Technique:
— mysqldump with –where option
— SELECT INTO OUTFILE
– Weekly conditional backups of large transactional tables containing only fixed or
historical data
— Data that does not change, for example, fulfilled orders that have passed the “return by”
date
– Daily/hourly supplemental backups containing live data

Which methods are used for logical backups? (Choose three)

MySQL enterprise backup

the mysqldump utility

the mysqlpump utility

snapshots

OS copy commands

SQL statements

答案:
the mysqldump utility
the mysqlpump utility
SQL statements

说明:
Logical Backups
Perform a complete data dump by using SQL statements, mysqldump or mysqlpump.
• These data dumps are based on a specific point in time but are the slowest of all the
backup techniques.
• Advantage:
– The process creates a SQL script that you can
execute on a MySQL server or data files that you
can import into database tables.
– You can use the script to reload the database on
another host, running a different architecture or
different version of MySQL Server.
• Disadvantage:
– By default (and always for non-InnoDB tables), mysqldump and mysqlpump lock
tables during the dump, which prevents users from modifying data during the backup.

Which are true about logical backups? (Choose three)

They use a snapshot capability that is external to MySQL.

They can be restored to MySQL databases on machines with different architectures than the one where the backup was created.

They can be taken to back up both local and remote MySQL servers.

They require the MySQL server to be running during the backups.

They generate SQL statements that can be used to recreate the orignal databases and tables.

They can identify objects that can cause damage and skip them during the backup.

答案:
They can be taken to back up both local and remote MySQL servers.

They require the MySQL server to be running during the backups.

They generate SQL statements that can be used to recreate the orignal databases and tables.

Which are advantages of the RAID mirroring process? (Choose two)

It backs up both local and remote MySQL servers

It can reload a database on another server, running a different architecture

It recovers data in the event of a hardware failure

It enables the creation of live backups

It creates a SQL script that can be executed on a MySQL server

答案:
It recovers data in the event of a hardware failure

It enables the creation of live backups

9.Skill Check Optimizing Query Performance

What does the FORCE command do? (Choose three)

analyzes and stores key distribution statistics of a table

reorganizes data evenly across data pages

reclaims file system space used by empty pages

provides the optimizer with extra information about data distribution

forces an index rebuild even though the SQL statement does not change the table structure

produces multiple output rows when analyzing complex queries

Which logs contain queries that the MySQL server executes? (Choose two)

the Slow query log

the General query log

the Binary log

the Relay log

the Audit log

Examine this command which executes successfully: mysql> EXPLAIN SELECT emp_no, title FROM titles \G It generates this output: id: 1 select_type: SIMPLE table: titles partitions: NULL type: eq_ref … Which type of comparison plan is used to access rows?

matching a primary or unique key against a constant at the start of a query

matching one or more referenced values for equality

matching rows in a range that the named index supports

matching a single referenced value for equality

Which storage engines support the mysqlcheck client program? (Choose three)

MEMORY

ARCHIVE

MyISAM

InnoDB

MERGE

BLACKHOLE

Which materially improves overall system performance? (Choose two)

A query that executes once per minute has its response time reduced from 20 ms to 15 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 1 second.

A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 800 ms.

Which properties are true about Invisible Indexes? (Choose three)

They provide an approximation of data distribution in indexed columns.

They analyze and store key distribution statistics of a table.

They can test the effect of removing an index on performance without dropping the index.

They can be applied to primary keys.

They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

What does the EXPLAIN command do? (Choose two)

It generates a query execution plan.

It performs data modification in a query.

It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

It enables efficient access to rows.

It returns data from tables.

9.Skill Check Optimizing Query Performance

What does the FORCE command do? (Choose three)

analyzes and stores key distribution statistics of a table

reorganizes data evenly across data pages

reclaims file system space used by empty pages

provides the optimizer with extra information about data distribution

forces an index rebuild even though the SQL statement does not change the table structure

produces multiple output rows when analyzing complex queries

答案：
reorganizes data evenly across data pages
reclaims file system space used by empty pages
forces an index rebuild even though the SQL statement does not change the table structure

说明：
Rebuilding Indexes
• Use FORCE:
– Forces a rebuild even though the statement does not change the table structure
– Reorganizes data more evenly across data pages
– Reclaims file system space used by empty pages
• Rebuild full text indexes by using OPTIMIZE TABLE:
– By default, OPTIMIZE TABLE rebuilds the entire table.
– The innodb_optimize_fulltext_only option ensures that OPTIMIZE TABLE
rebuilds only the full text index

Which logs contain queries that the MySQL server executes? (Choose two)

the Slow query log

the General query log

the Binary log

the Relay log

the Audit log

答案：
the Slow query log
the General query log

说明：
Identifying Slow Queries
• Identify the queries that the server executes:
– Use the general query log and slow query log.
• Identify the queries that take a long time:
– Use the slow query log.
– Prioritize these over outlier queries or queries that you execute
infrequently.
• Find queries that execute many times:
– Regularly execute SHOW PROCESSLIST or query the Performance
Schema threads table to see currently executing statements and
their durations and to identify emerging patterns.
– Use sys.statement_analysis to view normalized statements with
aggregated statistics.
– Use the slow query log with a low threshold to record most statements

Examine this command which executes successfully: mysql> EXPLAIN SELECT emp_no, title FROM titles \G It generates this output: id: 1 select_type: SIMPLE table: titles partitions: NULL type: eq_ref … Which type of comparison plan is used to access rows?

matching a primary or unique key against a constant at the start of a query

matching one or more referenced values for equality

matching rows in a range that the named index supports

matching a single referenced value for equality

答案：
matching a single referenced value for equality

说明：

Common type Values
The type column indicates the type of comparison used by the optimizer to access rows.
• ALL: Full table scan
• index: Full index scan
• const: Matching a primary or unique key against a constant at the start of a query
• eq_ref: Matching a single referenced value (identified by the ref column) for equality
• ref: Matching one or more referenced values for equality
• range: Matching rows in a range that the named index (key) supports

Which storage engines support the mysqlcheck client program? (Choose three)

MEMORY

ARCHIVE

MyISAM

InnoDB

MERGE

BLACKHOLE

答案：
MyISAM
InnoDB
ARCHIVE

说明：
mysqlcheck Client Program
• Can be more convenient than issuing SQL statements
• Works with InnoDB, MyISAM, and ARCHIVE tables
• Three levels of checking:
– Table specific
– Database specific
– All databases
• Some mysqlcheck maintenance options:
– –analyze: Performs ANALYZE TABLE
– –check: Performs CHECK TABLE (default)
– –optimize: Performs OPTIMIZE TABLE
• Examples:
mysqlcheck -uroot -p employees employees salaries
mysqlcheck –login-path=admin –analyze –all-databases

Which materially improves overall system performance? (Choose two)

A query that executes once per minute has its response time reduced from 20 ms to 15 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 1 second.

A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

A query that executes once per minute has its response time reduced from 2 seconds to 800 ms.

答案：
A query that executes several thousand times per minute has its response time reduced from 50 ms to 40 ms.

A query that executes several thousand times per minute has its response time reduced from 40 ms to 25 ms.

Which properties are true about Invisible Indexes? (Choose three)

They provide an approximation of data distribution in indexed columns.

They analyze and store key distribution statistics of a table.

They can test the effect of removing an index on performance without dropping the index.

They can be applied to primary keys.

They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

答案：
They can test the effect of removing an index on performance without dropping the index.
They can be used to facilitate a staged rollout of changes caused by index removal.

They are updated when indexed columns in their associated table is modified by DML statements.

说明：
Invisible Indexes
• Enable you to “hide” indexes from the optimizer
• Test the effect of removing indexes on query performance
— Without making destructive changes to drop the index
— Avoids expensive operation to re-create the index if it is found to be required
• Continue to be updated by the server when data is modified by DML statements
• Cannot be applied to primary keys
• Should be marked as INVISIBLE in CREATE TABLE, ALTER TABLE, or CREATE
INDEX statements
– Can be “unhidden” by using the VISIBLE keyword

What does the EXPLAIN command do? (Choose two)

It generates a query execution plan.

It performs data modification in a query.

It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

It enables efficient access to rows.

It returns data from tables.

答案：
It generates a query execution plan.
It examines SQL statements beginning with the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements.

说明：
EXPLAIN Command
• Generates a query execution plan

Skill Check Maintaining a Stable System

Which are true about MySQL behaviour when attempting to access a table for which an Exclusive (X) table-level lock is held? (Choose two)

It locks the table to allow shared row-level locking

it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

it allows other transactions to acquire shared locks on the table’s rows

it allows other sessions only to read rows.

Which actions are included in scaling out a database server? (Choose two)

increasing the network bandwidth

adding more servers to the environment

adding more CPU, storage, or main memory resources

writing software (application or storage engine) to use multiple locations

increasing the processing capacity of any single node

Examine this statement which executes successfully: "#mysqladmin –uroot –p kill 14" What does 14 represent in the output?

waiting_trx_id

blocking_trx_id

blocking_pid

waiting_pid

Which are server-level data locks? (Choose two)

Table locks

Storage engine data locks

Row-level locks

Locks that apply to internal resources

Metadata locks

You observe poor system performance. Which commands will display currently executing queries? (Choose two)

SHOW GLOBAL STATUS\G

SHOW PROCESSLIST\G

SHOW MASTER STATUS\G

SELECT * FROM sys.session\G

SELECT * FROM performance_schema.session_status;

Which conditions force an update to the performance baseline? (Choose two)

changes in server connection credentials

increasing data volumes

changes in application usage patterns

server migration to a new subnet

changes in the exploratory configuration

Skill Check Maintaining a Stable System

Which are true about MySQL behaviour when attempting to access a table for which an Exclusive (X) table-level lock is held? (Choose two)

It locks the table to allow shared row-level locking

it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

it allows other transactions to acquire shared locks on the table’s rows

it allows other sessions only to read rows.

答案：
it permits the transaction owning the lock to read and write rows

it prevents other transaction from locking any rows in the table.

Which actions are included in scaling out a database server? (Choose two)

increasing the network bandwidth

adding more servers to the environment

adding more CPU, storage, or main memory resources

writing software (application or storage engine) to use multiple locations

increasing the processing capacity of any single node

答案：
adding more servers to the environment
writing software (application or storage engine) to use multiple locations

说明:
Scaling Up and Scaling Out
• Scaling up:
– Add more CPU, storage, or main memory resources to increase the processing
capacity of any single node.
– In general, scaling up is less complicated than scaling out because of the difficulty in
writing software that performs well in parallel.
• Scaling out:
– Add more servers to the environment to enable more parallel processing.
– Software (application or storage engine) needs to be written to use multiple locations.
– Examples:
— Sharded database
— Replication for analytics or backups
— InnoDB Cluster
— NDB storage engine in MySQL Cluster

Examine this statement which executes successfully: "#mysqladmin –uroot –p kill 14" What does 14 represent in the output?

waiting_trx_id

blocking_trx_id

blocking_pid

waiting_pid

答案：
blocking_pid

Which are server-level data locks? (Choose two)

Table locks

Storage engine data locks

Row-level locks

Locks that apply to internal resources

Metadata locks

答案：
Table locks
Metadata locks

说明：
How MySQL Locks Rows
MySQL locks resources in the following ways:
• Server-level data locks:
– Table locks
– Metadata locks
• Storage engine data locks:
– Row-level locks
– Handled at the InnoDB layer
• Mutexes:
– Lower-level locks that apply to internal resources rather than to data
— Examples: Log files, AUTO_INCREMENT counters, and InnoDB buffer
pool mutexes
– Used for synchronizing low-level code operations, ensuring that
only one thread can access each resource at a time

You observe poor system performance. Which commands will display currently executing queries? (Choose two)

SHOW GLOBAL STATUS\G

SHOW PROCESSLIST\G

SHOW MASTER STATUS\G

SELECT * FROM sys.session\G

SELECT * FROM performance_schema.session_status;

答案：
SHOW PROCESSLIST\G
SELECT * FROM sys.session\G

说明:
Establishing a Baseline
• Define what is normal:
– The baseline is something to compare against when you encounter a problem.
– Over time, changes in the baseline provide you with useful information for capacity
planning.
• Record operating system metrics: filesystem, memory, and CPU usage
– top, iostat, vmstat , sysstat , sar on Linux- or UNIX-based systems
– Resource Monitor and Performance Monitor on Windows
• Record MySQL status and configuration:
– SHOW PROCESSLIST or sys.session to see the running processes
– mysqladmin extended-status to see status variables
— Use -iseconds –relative to record value deltas.
• Profile application use-case response times:
– Log in, search, create, read, update, and delete.

Which conditions force an update to the performance baseline? (Choose two)

changes in server connection credentials

increasing data volumes

changes in application usage patterns

server migration to a new subnet

changes in the exploratory configuration

答案：
increasing data volumes
changes in application usage patterns

说明：
Measuring What You Manage
• Establish a performance baseline to measure the system’s normal
variable values.
• After every configuration change, measure the variables again and
compare against your baseline.
– Hardware and software upgrades
– Exploratory configuration changes
– Changes in the infrastructure
• Measure variables regularly to update the baseline.
– Changes in application usage patterns
– Data growth over time
• Whenever you encounter a problem, compare values with the baseline.
– When you precisely define a problem, the solution often becomes
obvious
obvious

Skill Check Securing MySQL

Examine this command and output: SHOW STATUS LIKE ‘Connection_control%’; | Variable_name | Value | | Connection_control_delay_generated | 7 | 1 row in set (#.## sec) Which is true?

MySQL server added a delay for failed connection attempts seven times.

The maximum possilbe added delay is seven milliseconds.

A seven millisecond delay is added for each consecutive connection failure.

Seven successive failures are permitted before adding a delay.

Which command displays the name of the file containing a server’s digital certificate?

mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_cipher’;

mysql> SHOW SESSION STATUS LIKE ‘Ssl_cipher%’\G

mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_%’;

mysql> SHOW SESSION STATUS LIKE ‘Ssl_version’;

Which command registers the appuser@apphost account for firewall training?

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘PROTECTING’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘OFF’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RECORDING’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RESET’)

Which statements are true about Brute Force attacks? (Choose two)

They are slow as they require lots of CPU.

They perform hashing operations on combinations of dictionary words and characters.

They match target password hashes against rainbow tables.

They perform hashing operations on the characters to find matching hashes.

They compare password hashes against the stored hashes in the MySQL database.

After firewall training is complete, which modes will make the statement digest persistent in the account’s whitelist cache? (Choose two)

RECORDING

PROTECTING

OFF

DETECTING

RESET

The -ssl-mode option in your configuration is VERIFY_CA. What does this do?(Choose two)

It establishes secure connections or fails if unable to do so.

It checks whether host names match the Common Name value in the server certificate.

It establishes secure connections if it can but if not then unsecure connections are eastablished.

It verifies server digital certificates with the Certificate Authority.

It verifies that server digital certificates match the MySQL server hosts.

Skill Check Securing MySQL

Examine this command and output: SHOW STATUS LIKE ‘Connection_control%’; | Variable_name | Value | | Connection_control_delay_generated | 7 | 1 row in set (#.## sec) Which is true?

MySQL server added a delay for failed connection attempts seven times.

The maximum possilbe added delay is seven milliseconds.

A seven millisecond delay is added for each consecutive connection failure.

Seven successive failures are permitted before adding a delay.

答案：
MySQL server added a delay for failed connection attempts seven times.

说明：

• Inspects the value of the Connection_control_delay_generated status variable
– Counts the number of times the server added a delay for a failed connection attempt
– Example:
• Considers installing the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin
– Creates a table in the Information Schema to maintain more detailed information
about failed connection attempts
— The Connection-Control plugin populates the table.

Which command displays the name of the file containing a server’s digital certificate?

mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_cipher’;

mysql> SHOW SESSION STATUS LIKE ‘Ssl_cipher%’\G

mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_%’;

mysql> SHOW SESSION STATUS LIKE ‘Ssl_version’;

答案：
mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_%’;

说明：

The following is an example displaying the current name of the file in the data directory that contains
the list of trusted Certificate Authorities:
mysql> SHOW GLOBAL VARIABLES LIKE ‘ssl_ca’;
+—————+——–+
| Variable_name | Value |
+—————+——–+
| ssl_ca | ca.pem |
+—————+——–+
1 row in set (0.00 sec)

Which command registers the appuser@apphost account for firewall training?

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘PROTECTING’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘OFF’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RECORDING’)

CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RESET’)
答案：
CALL mysql.sp_set_firewall_mode(‘appuser@apphost’, ‘RECORDING’)

说明：
Registering Accounts with the Firewall
Register an account by setting its initial firewall mode.
• The account name is in the full user@host format, stored as a single string.
• To register an account that is not initially controlled by the firewall, set the mode to OFF.
• To register an account for firewall training, set the initial mode to RECORDING.
– If you set an initial mode of PROTECTING, the account cannot execute any
statements because its whitelist is empty.

Which statements are true about Brute Force attacks? (Choose two)

They are slow as they require lots of CPU.

They perform hashing operations on combinations of dictionary words and characters.

They match target password hashes against rainbow tables.

They perform hashing operations on the characters to find matching hashes.

They compare password hashes against the stored hashes in the MySQL database.

答案：
They are slow as they require lots of CPU.
They perform hashing operations on the characters to find matching hashes.

说明：
CHow Attackers Derive Passwords
Attackers can derive plain text passwords from hashed passwords by using the following
techniques:
• Brute force algorithms perform the hashing algorithm on many combinations of
characters to find matching hashes.
– These attacks are very slow and require large amounts of computation.
• Dictionary attacks perform hashing operations on combinations of dictionary words and
other characters.
– These are fast if the password is not secure.
• Rainbow tables are made up of the first and last hashes in long chains of repeatedly
hashed and reduced passwords.
– When you run a target password hash through the same algorithm chain and find a
match to the end of a stored chain, you can derive the password by replaying that
chain

After firewall training is complete, which modes will make the statement digest persistent in the account’s whitelist cache? (Choose two)

RECORDING

PROTECTING

OFF

DETECTING

RESET

答案：
OFF
PROTECTING

说明：
Training the Firewall
• Register the account in RECORDING mode.
• The firewall creates a normalized statement digest for each statement and places the
digest in the account’s whitelist cache.
• Switch the mode to PROTECTING or OFF when training is complete to persist the
whitelist.
– The firewall persists the cache when you change the account’s mode.
– If you restart the mysqld process while in RECORDING mode, any changes to that
account’s whitelist cache are lost.
• Return to RECORDING mode to learn new statements if the application changes.
– Changing mode from OFF or PROTECTING to RECORDING does not clear the
account’s whitelist.

The -ssl-mode option in your configuration is VERIFY_CA. What does this do?(Choose two)

It establishes secure connections or fails if unable to do so.

It checks whether host names match the Common Name value in the server certificate.

It establishes secure connections if it can but if not then unsecure connections are eastablished.

It verifies server digital certificates with the Certificate Authority.

It verifies that server digital certificates match the MySQL server hosts.

答案：
It establishes secure connections or fails if unable to do so.
It verifies server digital certificates with the Certificate Authority.

说明：
Setting Client Options for Secure Connections
Use the –ssl-mode option, which accepts the following values:
• PREFERRED: Establishes a secure connection if possible or falls back to
an unsecure connection. This is the default if –ssl-mode is not
specified.
• DISABLED: Establishes an insecure connection
• REQUIRED: Establishes a secure connection if possible or fails if unable
to establish a secure connection
• VERIFY_CA: As for REQUIRED, but also verifies the server digital
certificate with the Certificate Authority
• VERIFY_IDENTITY: As for VERIFY_CA, but also verifies that the
server digital certificate matches the MySQL server host

6. Skill Check Managing MySQL Users

　You must disable automatic password expiration on the ‘erika’@’localhost’ account. Which command will do this?

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE NEVER;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE DEFAULT;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE INTERVAL 30 DAY;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE;

　You must grant SELECT privilege on the salaries table in the employees’ database to user jan@localhost? Which command will do this?

GRANT SELECT ON employees.salaries TO jan@localhost;

REVOKE GRANT OPTION ON employees.salaries FROM ‘jan’@’localhost’;

REVOKE GRANT OPTION ON salaries.employees FROM ‘jan’@’localhost’;

GRANT SELECT ON salaries.employees TO jan@localhost;

Which command activates roles at the session level?

SET ROLE ALL;

ALTER USER kari@localhost DEFAULT ROLE ALL;

SET DEFAULT ROLE ALL TO kari@localhost;

SET PERSIST activate_all_roles_on_login = ON;

　You plan to use the test authentication plug-in test_plugin_server. Which statements are true? (Choose two)

It authenticates against operating system users and groups.

It is intended for use during testing and development.

It only allows MySQL users to log in via a UNIX socket.

It sends a plain text password to the server.

It implements native and old password authentication.

Which command removes DDL privileges from a role?

REVOKE DELETE, INSERT, UPDATE ON world.* FROM r_dev;

REVOKE GRANT OPTION ON world.* FROM r_dev

REVOKE CREATE, DROP ON world.* FROM r_dev;

REVOKE r_updater FROM r_dev;

You are using the caching_sha2_password plug-in? Which statements are true (Choose two)

It prevents clients from hashing passwords.

It authenticates MySQL accounts against the operating system.

It sends plain text passwords to a server.

It is the default authentication plug-in in MySQL 8.0.

It implements SHA-256 authentication and uses server side caching for better performance.

===

6. Skill Check Managing MySQL Users

　You must disable automatic password expiration on the ‘erika’@’localhost’ account. Which command will do this?

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE NEVER;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE DEFAULT;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE INTERVAL 30 DAY;

ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE;

答案：
ALTER USER ‘erika’@’localhost’ PASSWORD EXPIRE NEVER;

说明：
Configuring Password Expiration
• The default_password_lifetime global variable specifies the number of days after
which passwords must be changed.
– The default value is 0, which indicates that passwords do not expire.
• Set per-account password lifetime with the PASSWORD EXPIRE clause of CREATE
USER or ALTER USER:
• Apply the default password lifetime to an account:
• Disable automatic password expiration on an account:
CREATE USER ‘consultant’@’laptop3’ IDENTIFIED BY ‘change%me’
PASSWORD EXPIRE INTERVAL 30 DAY;
ALTER USER ‘consultant’@’laptop3’ PASSWORD EXPIRE NEVER;

　You must grant SELECT privilege on the salaries table in the employees’ database to user jan@localhost? Which command will do this?

GRANT SELECT ON employees.salaries TO jan@localhost;

REVOKE GRANT OPTION ON employees.salaries FROM ‘jan’@’localhost’;

REVOKE GRANT OPTION ON salaries.employees FROM ‘jan’@’localhost’;

GRANT SELECT ON salaries.employees TO jan@localhost;

答案：
GRANT SELECT ON employees.salaries TO jan@localhost;

说明：
GRANT Statement
• The GRANT statement assigns privileges or roles to MySQL user accounts and roles.
– Example GRANT syntax:
• Statement clauses:
– Privileges to be granted
— Example: SELECT, UPDATE, DELETE
– Privilege level:
— Global: .
— Database: db_name.*
— Table: db_name.table_name
— Stored routine: db_name.routine_name
– Account or role to which you are granting the privilege

Which command activates roles at the session level?

SET ROLE ALL;

ALTER USER kari@localhost DEFAULT ROLE ALL;

SET DEFAULT ROLE ALL TO kari@localhost;

SET PERSIST activate_all_roles_on_login = ON;

答案：
SET ROLE ALL;

说明:
Activating Roles at Session Level
• Use SET ROLE statement to modify the list of active roles in the current session. It
accepts a list of roles or one of the following role specifiers:
– DEFAULT: Activate the account default roles.
– NONE: Disable all roles.
– ALL: Activate all roles granted to the account.
– ALL EXCEPT: Activate all roles granted to the account except those named.
• Use CURRENT_ROLE() function to determine which roles are active in the current
session.
SET ROLE ALL;
SET ROLE r_viewer, r_updater;
SELECT CURENT_ROLE();

　You plan to use the test authentication plug-in test_plugin_server. Which statements are true? (Choose two)

It authenticates against operating system users and groups.

It is intended for use during testing and development.

It only allows MySQL users to log in via a UNIX socket.

It sends a plain text password to the server.

It implements native and old password authentication.

答案：

It implements native and old password authentication.
It is intended for use during testing and development.

说明：
Loadable Authentication Plugins
• Test Authentication plugin (test_plugin_server): Implements native and old
password authentication
– This plugin uses the auth_test_plugin.so file and is intended for testing and
development purposes.
• Socket Peer-Credential (auth_socket): Allows only MySQL users who are logged in
via a UNIX socket from a UNIX account with the same name
– This plugin uses the auth_socket.so file.

Which command removes DDL privileges from a role?

REVOKE DELETE, INSERT, UPDATE ON world.* FROM r_dev;

REVOKE GRANT OPTION ON world.* FROM r_dev

REVOKE CREATE, DROP ON world.* FROM r_dev;

REVOKE r_updater FROM r_dev;
答案：
REVOKE CREATE, DROP ON world.* FROM r_dev;

说明：

REVOKE: Examples
• Assume that Amon has SELECT, DELETE, INSERT, and UPDATE privileges on the
world database.
– You want to change the account so that he has SELECT access only.
• Revoke Jan’s ability to grant to other users any privileges that he holds for the world
database, by revoking the GRANT OPTION privilege from his account.
• Assume that the r_dev role has the CREATE, DROP, SELECT, DELETE, INSERT, and
UPDATE privileges on the world database.
– You want to remove all DDL privileges from the role.
• Revoke r_updater role from Kari user account.
REVOKE GRANT OPTION ON world. FROM ‘Jan’@’localhost’;
REVOKE DELETE, INSERT, UPDATE ON world. FROM ‘Amon’@’localhost’;
REVOKE CREATE, DROP ON world.* FROM r_dev;
REVOKE r_updater FROM kari@localhost;

You are using the caching_sha2_password plug-in? Which statements are true (Choose two)

It prevents clients from hashing passwords.

It authenticates MySQL accounts against the operating system.

It sends plain text passwords to a server.

It is the default authentication plug-in in MySQL 8.0.

It implements SHA-256 authentication and uses server side caching for better performance.

答案：
It is the default authentication plug-in in MySQL 8.0.
It implements SHA-256 authentication and uses server side caching for better performance.

说明：

Pluggable Authentication
• In MySQL 8.0, caching_sha2_password is the default authentication plugin.
• During connection using the caching_sha2_password plugin, MySQL uses the
following to authenticate an account:
– Username
– Password
– Client host
• When specifying host names, remember the proper perspective:
– Specify the server’s host name when connecting using a client.
– Specify the client’s host name when adding a user to the server