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的事务。