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_IO Error 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